SQLite数据库使用总结(下)

概述FMDataBase是对SQLite数据库比较好的封装其基本使用方法如下: 1.数据库的创建           FMDatabase*   dbHandle = [[FMDatabase alloc]initWithPath:dbFilePath]; //创建数据库原理跟上一样，这里不多讲 -(BOOL)createDataBase{     //[sqlControl_ close];   

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 exist\n");
// }

//如果文件不存在则拷贝
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_User\n"
"(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,sLoginType\n"
"from T_iHealth_Login order by LoginTime desc limit 1\n"];

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_Stat\n"
" where START_TIME = ? and\n"
" STAT_TYPE = ? and\n"
" 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_Stat\n"
"(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_VIEw\n" "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;

总结

以上是内存溢出为你收集整理的SQLite数据库使用总结(下)全部内容，希望文章能够帮你解决SQLite数据库使用总结(下)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。