iOS 中进行 SQLite 操作
openkk
12年前
给出完整代码，都是自己做了，已经验证可以工作。运行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];