iOS 中进行 SQLite 操作

分享到:

给出完整代码,都是自己做了,已经验证可以工作。运行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];

昵    称:
验证码:

相关文档: