iOS 中进行 SQLite 操作

openkk 9年前

给出完整代码,都是自己做了,已经验证可以工作。运行code之前,先加入libsqlite3.lib 【add from exist framework】.这里的操作主要针对 int 型 数据,其他数据对应的修改即可。

 

//  //  SQLiteDb.h  //  Sqlite3Test  //  //  Created by podevor@gmail.com on 12-8-17.  //  Copyright (c) 2012年 Beijing Interaction Times Network Technology Co.,Ltd. All rights reserved.  //    #import <Foundation/Foundation.h>  #import <sqlite3.h>@interface  SQLiteDb : NSObject{           sqlite3 *database;      NSMutableArray *openedMapIndex;      NSString *_tableName;      NSArray* _fieldArray;      NSString *_fieldsList;  }  +(id) initDbHandle;  -(void) openDatabase;  -(void) createTableByNameAndFields:(NSString*)tableName fields:(NSArray*) fieldArray;  -(void) insertData:(id)insertValue;  -(void) deleteData:(id) deleteValue;  -(void) queryData;  -(int) dataCount;  -(int) mapIndexAtOpenedMapIndex:(int)index;  -(void) closeDatabase;  -(void) dealloc;@end 

//  //  SQLiteDb.m  //  Sqlite3Test  //  //  Created by podevor@gmail.com on 12-8-17.  //  Copyright (c) 2012年 Beijing Interaction Times Network Technology Co.,Ltd. All rights reserved.  //    #import "SQLiteDb.h"    @implementation SQLiteDb    +(id) initDbHandle{      return  [[SQLiteDb alloc] init];  }  -(void) openDatabase{            openedMapIndex = [NSMutableArray arrayWithCapacity:10];      _fieldArray = nil;      _fieldsList = nil;        NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory                                                                  , NSUserDomainMask                                                                  , YES);      NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:@"mapopened"];            if (sqlite3_open([databaseFilePath UTF8String], &database)==SQLITE_OK) {          NSLog(@"SQLites is opened.");      }else {          NSLog(@"SQLites open Error.");      }  }  -(void) createTableByNameAndFields:(NSString *)tableName fields:(NSArray*) fieldArray{      if (_fieldArray == nil) {          _fieldArray = [NSArray arrayWithArray:fieldArray];      }       if (_fieldsList != nil) {          _fieldsList = @"";      }      int begin = 0;      if (![_fieldArray containsObject:@"primary key"]) {          _fieldsList  = [NSString stringWithFormat:@"%@ %@ %@",[_fieldArray objectAtIndex:0],[_fieldArray objectAtIndex:1],@"primary key"];          begin = 2;      }      for (int i = begin; i < [_fieldArray count]; i++) {          _fieldsList  = [NSString stringWithFormat:@"%@ %@",_fieldsList,[_fieldArray objectAtIndex:i]];      }      NSLog(@"%@",_fieldsList);      _tableName = tableName;      char *errorMsg;  //    id integer primary key autoincrement,      NSString *createSql= [NSString stringWithFormat:@"create table if not exists %@ ( %@ )",_tableName,_fieldsList];            if (sqlite3_exec(database, [createSql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {          NSLog(@"Create Success.");      }else {          NSLog(@"Create Failure %s",errorMsg);      }    }    -(void) insertData:(id)insertValue{      NSLog(@"Insert Values is :%@",insertValue);       char *errorMsg;      NSString *insertSql= [NSString stringWithFormat:@"insert into %@ values (%d)",_tableName,[insertValue intValue]];            if (sqlite3_exec(database, [insertSql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {          NSLog(@"Insert Success.");      }else {          NSLog(@"Insert Failure %s",errorMsg);      }  }    -(void) deleteData:(id)deleteValue{      NSLog(@"Delete Values is :%@",deleteValue);      char *errorMsg;      NSString *deleteSql= [NSString stringWithFormat:@"delete from %@ where mapIndex = %d",_tableName,[deleteValue intValue]];            if (sqlite3_exec(database, [deleteSql UTF8String], NULL, NULL, &errorMsg)==SQLITE_OK) {          NSLog(@"Delete Success.");      }else {          NSLog(@"Delete Failure %s",errorMsg);      }  }  -(void) queryData{      //clear the openedMap      [openedMapIndex removeAllObjects];      NSString *query = [NSString stringWithFormat:@"select * from %@",_tableName];      sqlite3_stmt *statement;      if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {          while (sqlite3_step(statement) == SQLITE_ROW) {              //get data              int index = sqlite3_column_int(statement, 0);              [openedMapIndex addObject:[NSString stringWithFormat:@"%d",index]];          }          sqlite3_finalize(statement);      }  }    -(int) dataCount{      int count = [openedMapIndex count];      NSLog(@"the data count is %d",count);      return count;  }  -(int) mapIndexAtOpenedMapIndex:(int)index{      NSString *value = (NSString*)[openedMapIndex objectAtIndex:index];      return [value intValue];  }  -(void) closeDatabase{      sqlite3_close(database);  }    -(void) dealloc{     // [fieldsList release];      [self closeDatabase];  }@end 

使用方法:

    sqlitedb = (SQLiteDb *)[SQLiteDb initDbHandle];      [sqlitedb openDatabase];      //field array format: fields name ,fields type, other flag      NSArray *fieldsArray = [NSArray arrayWithObjects:@"username",@"text",@",",@"password",@"text",@",",@"id",@"integer",@",",@"description",@"text", nil];      [sqlitedb createTableByNameAndFields:@"zcq" fields:fieldsArray];      NSString *insertValues = @"12312313";      [sqlitedb insertData:insertValues];      NSString *deleteValues = @"123";      [sqlitedb deleteData:deleteValues];      [sqlitedb queryData];      [sqlitedb dataCount];      for (int i = 0; i < [sqlitedb dataCount]; i++) {          NSLog(@"data %d",[sqlitedb mapIndexAtOpenedMapIndex:i]);      }      [sqlitedb closeDatabase];