首页 > 代码库 > SQLite常用语句
SQLite常用语句
// 错误存储路径
- (NSString *)dataFilePath{
NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
NSString *db_absoluteString = [dbPath stringByAppendingPathComponent:@"personal.db"]; // 找到personal.db数据库
NSLog(@"db_absoluteString: %@",db_absoluteString);
return db_absoluteString; // 返回数据库的路径
}
//插入数据
- (NSString *)insertDBFilePath{
NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
NSString *db_absoluteString = [dbPath stringByAppendingPathComponent:@"cmread.sqlite"];
// NSLog(@"db_absoluteString: %@",db_absoluteString);
return db_absoluteString;
}
//获得所有个人信息
- (NSMutableArray *)getStoredPersons{
// 打开数据库
// UTFString格式
// 数据文件路径
if(sqlite3_open([[self dataFilePath] UTF8String],&database) !=SQLITE_OK){
sqlite3_close(database); // 关闭数据库
NSLog(@"open database failed!");
return nil;
}
NSMutableArray *array = [[NSMutableArray alloc] init];
NSString *query = [[NSString alloc] initWithFormat: @"select zaccountname,zlastlogintime,zlogincount from zLoginRecord order by zlastlogintime desc"];
sqlite3_stmt *statement;
if(sqlite3_prepare_v2(database, [query UTF8String],-1,&statement,nil) == SQLITE_OK){
while(sqlite3_step(statement) == SQLITE_ROW){
CMLoginRecord *loginRecord = [[CMLoginRecord alloc] init];
char *loginRecordKey = (char *)sqlite3_column_text(statement, 0);
if (loginRecordKey == NULL) {
loginRecord.accountname = nil;
}else {
NSString *_loginRecord = [[NSString alloc] initWithUTF8String:loginRecordKey];
loginRecord.accountname = _loginRecord;
}
char *lastlogintimeKey = (char *)sqlite3_column_text(statement, 1);
if (lastlogintimeKey == NULL) {
loginRecord.lastlogintime = nil;
}else {
NSString *_lastlogintime = [[NSString alloc] initWithUTF8String:lastlogintimeKey];
loginRecord.lastlogintime = _lastlogintime;
}
int logincount = sqlite3_column_int(statement, 2);
loginRecord.logincount = @(logincount);
[array addObject:loginRecord];
}
sqlite3_finalize(statement);
}
sqlite3_close(database);
return array;
}
// 插入个人信息
- (NSInteger)insertPersonl:(NSMutableArray *)array{
BOOL isInsertSuccess = NO;
// 打不开的话，就关闭
if(sqlite3_open([[self insertDBFilePath] UTF8String],&database) !=SQLITE_OK){
sqlite3_close(database);
return 1;
}
char *insert = "insert into zAccount(zaccountname,zlastlogintime,zlogincount) values(?,?,?);";// 要记住分号
for (CMLoginRecord *loginRecord in array) {
sqlite3_stmt *stmt;
if(sqlite3_prepare_v2(database,insert,-1,&stmt,nil) == SQLITE_OK){
sqlite3_bind_text(stmt, 1, [loginRecord.accountname UTF8String],-1,NULL);
sqlite3_bind_text(stmt, 2, [loginRecord.lastlogintime UTF8String],-1,NULL);
sqlite3_bind_int(stmt, 3, [loginRecord.logincount intValue]);
}
if(sqlite3_step(stmt)!= SQLITE_DONE){
NSLog(@"Error insert data into zLoginRecord error !");
}else{
//删除原来的数据库
isInsertSuccess = YES;
}
sqlite3_finalize(stmt);
}
sqlite3_close(database);
if (isInsertSuccess) {
NSString *path = [self dataFilePath];
NSFileManager *fileManager = [NSFileManager defaultManager];
if ([fileManager fileExistsAtPath:path]) {
[fileManager removeItemAtPath:path error:nil];
}
NSLog(@"删除原来的数据库");
}
return 0;
}
SQLite常用语句