iOS中对数据库FMDB的操作,建议是将方法写成单例,下面是对数据库的一些基本操作
一、在本地数据库中创建表
//
获得
Documents
目录路径
NSString *documentsPath = [ NSSearchPathForDirectoriesInDomains ( NSDocumentDirectory , NSUserDomainMask , YES ) lastObject ];
// 文件路径
NSString *filePath = [documentsPath stringByAppendingPathComponent : @"model.sqlite" ];
// 实例化 FMDataBase 对象
_db = [ FMDatabase databaseWithPath :filePath];
NSString *documentsPath = [ NSSearchPathForDirectoriesInDomains ( NSDocumentDirectory , NSUserDomainMask , YES ) lastObject ];
// 文件路径
NSString *filePath = [documentsPath stringByAppendingPathComponent : @"model.sqlite" ];
// 实例化 FMDataBase 对象
_db = [ FMDatabase databaseWithPath :filePath];
[ _db open ];
FMResultSet *rs = [ _db executeQuery : @"select count(*) as 'count' from sqlite_master where type ='table' and name = ?" , @"numberData" ];
while ([rs next ])
{
NSInteger count = [rs intForColumn : @"count" ];
NSLog ( @"isTableOK %ld" , ( long )count);
if ( 0 == count){ // 不存在
break ;
}
else { // 存在
[ _db close ];
return ;
}
}
// 初始化数据表
NSString *personSql = @"CREATE TABLE 'numberData' ('numID' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,'number' VARCHAR(255),'dateStr' VARCHAR(255),'qiNum' VARCHAR(255)) " ;
[ _db executeUpdate :personSql];
[ _db close ];
二、增
[_dbopen];
NSNumber *maxID = @(0);
FMResultSet *res = [_dbexecuteQuery:@"SELECT * FROM numberData "];
//获取数据库中最大的ID
while ([res next]) {
if ([maxID integerValue] < [[resstringForColumn:@"numID"]integerValue]) {
maxID = @([[resstringForColumn:@"numID"]integerValue]);
}
}
maxID = @([maxIDintegerValue] +1);
[_dbexecuteUpdate:@"INSERT INTO numberData(numID,number,dateStr,qiNum)VALUES(?,?,?,?)",[NSStringstringWithFormat:@"%@",maxID],Num.number,Num.dateStr,Num.qiNum];
[_dbclose];
NSNumber *maxID = @(0);
FMResultSet *res = [_dbexecuteQuery:@"SELECT * FROM numberData "];
//获取数据库中最大的ID
while ([res next]) {
if ([maxID integerValue] < [[resstringForColumn:@"numID"]integerValue]) {
maxID = @([[resstringForColumn:@"numID"]integerValue]);
}
}
maxID = @([maxIDintegerValue] +1);
[_dbexecuteUpdate:@"INSERT INTO numberData(numID,number,dateStr,qiNum)VALUES(?,?,?,?)",[NSStringstringWithFormat:@"%@",maxID],Num.number,Num.dateStr,Num.qiNum];
[_dbclose];
三、删
[
_db
open
];
if ([ _db executeUpdate : @"DELETE FROM numberData WHERE dateStr = ?" ,dateStr]) {
[ WSProgressHUD showSuccessWithStatus : @" 删除成功! " ];
} else {
[ WSProgressHUD showErrorWithStatus : @" 删除失败! " ];
}
[ _db close ];
if ([ _db executeUpdate : @"DELETE FROM numberData WHERE dateStr = ?" ,dateStr]) {
[ WSProgressHUD showSuccessWithStatus : @" 删除成功! " ];
} else {
[ WSProgressHUD showErrorWithStatus : @" 删除失败! " ];
}
[ _db close ];
四、改
[_dbopen];
BOOL su = [_dbexecuteUpdate:@"UPDATE 'numberData' SET number = ? WHERE dateStr = ? and qiNum = ?",newNum,dateStr,qiNum];
[_dbclose];
BOOL su = [_dbexecuteUpdate:@"UPDATE 'numberData' SET number = ? WHERE dateStr = ? and qiNum = ?",newNum,dateStr,qiNum];
[_dbclose];
五、查
[
_db
open
];
NSMutableArray *dataArray = [[ NSMutableArray alloc ] init ];
FMResultSet *res = [ _db executeQuery : @"SELECT * FROM numberData" ];
while ([res next ]) {
Number *num = [[ Number alloc ] init ];
num. numID = [res stringForColumn : @"numID" ];
num. number = [res stringForColumn : @"number" ];
num. dateStr = [res stringForColumn : @"dateStr" ];
num. qiNum = [res stringForColumn : @"qiNum" ];
[dataArray addObject :num];
}
[ _db close ];
NSMutableArray *dataArray = [[ NSMutableArray alloc ] init ];
FMResultSet *res = [ _db executeQuery : @"SELECT * FROM numberData" ];
while ([res next ]) {
Number *num = [[ Number alloc ] init ];
num. numID = [res stringForColumn : @"numID" ];
num. number = [res stringForColumn : @"number" ];
num. dateStr = [res stringForColumn : @"dateStr" ];
num. qiNum = [res stringForColumn : @"qiNum" ];
[dataArray addObject :num];
}
[ _db close ];
这是个人初步接触FMDB时学的一些语句,后期对这些东西进行了封装,使用更加简单,具体请查看链接:
参考: