1.新建一个数据库管理类DBManager用于数据库的新建、打开、关闭。
#import <Foundation/Foundation.h>
#import <sqlite3.h>
#define DBNAME @"projectxxxxx.sqlite"
@interface DBManager : NSObject{
sqlite3 *database;
}
@property (nonatomic) sqlite3 *database;
+(DBManager *) shareInstance;
-(void)openDB;
-(void)closeDB;
@end
static DBManager *instance;
+ (DBManager *)shareInstance {
@synchronized(self) {
if (instance == nil) {
instance = [[self alloc] init];
}
}
return instance;
}
-(void)closeDB {
sqlite3_close(database);
}
//获取数据库文件夹目录
-(void)openDB {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documents = [paths objectAtIndex:0];
NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
if (sqlite3_open([database_path UTF8String], &database) != SQLITE_OK) {
sqlite3_close(database);
DebugLog(@"Sqlite Open Failed");
}else{
//创建一张表
NSString *sqlTable = @"CREATE TABLE IF NOT EXISTS USER_TABLE (id INTEGER PRIMARY KEY AUTOINCREMENT, passport VARCHAR, Id INTEGER, name VARCHAR)";
[self initCreateTable:sqlTable];
}
}
-(void)initCreateTable:(NSString *)sql
{
char *err;
if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
sqlite3_close(database);
DebugLog(@"Sqlite Operation Failed");
}
}
2.对每一个表创建一个相应的类用于对该表进行增删改查的常规操作。
如:UserInfoDB 类开放一些接口对用户信息这张表进行操作。
@interface UserInfoDB : NSObject{
sqlite3 *database;
}
//获得实例
+(UserInfoDB *) GetInstance;
//插入一条用户信息
-(void)insertOneUser:(nsstring *)name GroupId:(int)GroupId;
//根据id,查询信息
-(NSMutableArray *)selectByGroupId:(int)GroupId;
//获取表情组数量
-(int)getGroupCount;
//查询所有用户
-(NSMutableArray *)selectAllUser;
//删除该表
-(void)clear;