|
FMDataBase是对SQLite数据库比较好的封装其基本使用方法如下:
1.数据库的创建
FMDatabase* dbHandle = [[FMDatabase alloc]initWithPath:dbFilePath];
//创建数据库原理跟上一样，这里不多讲
-(BOOL)createDataBase{
//[sqlControl_ close];
// if (![[NSFileManager defaultManager]removeItemAtPath:self.dbFilePath error:nil]) {
// printf("file can't delete or file is not existn");
// }
//如果文件不存在则拷贝
if (![[NSFileManager defaultManager]fileExistsAtPath:self.dbFilePath]) {
NSString *backDbPath = [[NSBundle mainBundle]pathForResource:K_DATABASE_FILE_NAME
ofType:K_DATABASE_RESOURCE_TYPE];
if (nil != backDbPath) {
BOOL copiedBackupDB = [[NSFileManager defaultManager]copyItemAtPath:backDbPath
toPath:self.dbFilePath
error:nil];
//拷贝则打开
if (copiedBackupDB) {
return [dbHandle open];
}
}
}
else {
return [dbHandle open];
}
//如果还不存在，则创建
if (![[NSFileManager defaultManager]fileExistsAtPath:self.dbFilePath]) {
if ([dbHandle open]) {
//创建表
//
[self createAllTables];
return YES;
}
}
[dbHandle close];
return NO;
}
2.创建表
BOOL ret = FALSE;
if ([dbHandle goodConnection]) {
if(![dbHandle tableExists:@"T_iHealth_User"]) {
NSString *strCreatUsertable = [NSString stringWithFormat:
@"create table if not exists T_iHealth_Usern"
"(uid INTEGER,n"
"USERNAME VARCHAR(255) PRIMARY KEY NOT NULL,n"
"PASSWORD VARCHAR(64) NOT NULL,n"
"sDESCRIPTION VARCHAR(512),n"
"NICKNAME VARCHAR(512),n"
"SEX CHAR(1),n"
"Birthday DATETIME,n"
"nAge INTEGER,n"
"HEIGHT INTEGER,n"
"WEIGHT VARCHAR(64),n"
"USEREMAIL VARCHAR(255),n"
"CITY VARCHAR(50))n"
];
// NSLog(@"%@",strCreatUsertable);
ret = [dbHandle executeUpdate:strCreatUsertable];
}
}
2.查询
-(UserLoginInfo *)getLastUserLoginInfo{
UserLoginInfo *userLoginInfo = nil;
if ([dbHandle goodConnection]) {
if (![dbHandle tableExists:[NSString stringWithFormat:@"T_iHealth_Login"]]) {
return nil;
}
userLoginInfo = [[[UserLoginInfo alloc]init]autorelease];
NSString *strSQL = [NSString stringWithFormat:
@"select sUserName,sPassword,sIfRMBUsername,sIfRMBPassword,LoginTime,sLoginTypen"
"from T_iHealth_Login order by LoginTime desc limit 1n"];
FMResultSet *rs = [dbHandle executeQuery:strSQL];
while([rs next]){
userLoginInfo.sUserName = [rs stringForColumn:@"sUserName"];
userLoginInfo.sPassword = [rs stringForColumn:@"sPassword"];
userLoginInfo.sIfRMBUsername = [rs intForColumn:@"sIfRMBUsername"];
userLoginInfo.sIfRMBPassword = [rs intForColumn:@"sIfRMBPassword"];
userLoginInfo.LoginTime = [rs stringForColumn:@"LoginTime"];
userLoginInfo.sLoginType = [rs intForColumn:@"sLoginType"];
}
[rs close];
}
return userLoginInfo;
}
3.新增
BOOL ret = NO;
if ((nil == pedometerStatInfo) || (0 == [pedometerStatInfo count])) {
return ret;
}
if ([dbHandle goodConnection]) {
if (![dbHandle tableExists:[NSString stringWithFormat:@"T_iHealth_Pedometer_Stat"]]) {
return ret;
}
[dbHandle beginTransaction];
PedometerStatInfo *pedometerStatInfotemp = [pedometerStatInfo objectAtIndex:0];
NSString *delSql=[NSString stringWithFormat:@"delete from T_iHealth_Pedometer_Statn"
" where START_TIME = ? andn"
" STAT_TYPE = ? andn"
" uid = ? n"];
ret = [dbHandle executeUpdate:delSql,pedometerStatInfotemp.startTime,K_INTTOOBJ(pedometerStatInfotemp.statType),K_INTTOOBJ(pedometerStatInfotemp.uid)];
for (id value in pedometerStatInfo) {
PedometerStatInfo *pedometerStatInfo = (PedometerStatInfo *)value;
NSString *strSQL = [NSString stringWithFormat:
@"insert into T_iHealth_Pedometer_Statn"
"(UID,STEP,DISTANCE,FLOOR,CALORIE,START_TIME,END_TIME,STAT_TYPE)n"
"values(?,?,?)"];
// NSLog(@"%@",strSQL);
[dbHandle executeUpdate:strSQL,K_INTTOOBJ(pedometerStatInfo.uid),K_INTTOOBJ(pedometerStatInfo.step),
K_INTTOOBJ(pedometerStatInfo.distance),K_INTTOOBJ(pedometerStatInfo.floor),K_INTTOOBJ(pedometerStatInfo.calorie),
pedometerStatInfo.startTime,pedometerStatInfo.endTime,K_INTTOOBJ(pedometerStatInfo.statType)];
}
ret = [dbHandle commit];
}
return ret;
4.修改
BOOL ret = NO; if (nil == pedometerViewInfo) { return ret; } if ([dbHandle goodConnection]) { if (![dbHandle tableExists:[NSString stringWithFormat:@"T_iHealth_Pedometer_Stat_View"]]) { return NO; } NSString *strSQL = [NSString stringWithFormat: @"update T_iHealth_Pedometer_Stat_Viewn" "set VIEW_END_TIME = ?,LAST_UPDATE_TIME = ?n" "where substr(view_begin_time,1,8) = '%@'n" "and UID = ?n" "and stat_type = ?",[pedometerViewInfo.begin_time substringToIndex:8]]; ret = [dbHandle executeUpdate:strSQL,pedometerViewInfo.end_time,[[AgentCenterModel sharedInstance]sender].uId,K_INTTOOBJ(pedometerViewInfo.stat_type)]; } return ret; （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
