论坛 › SQLite › sqlite运用总结

santaclaus 发表于 2016-11-28 12:11:25

sqlite运用总结

1、打开数据库。

//打开数据库
-(BOOL)openDatabase{
NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);
NSString *paths = [ stringByAppendingPathComponent:@"NineCellDiaryDB.sqlite3"];
if(sqlite3_open(, &database) != SQLITE_OK){
;
return NO;
}return YES;
}

2、关闭数据库。

//关闭数据库
-(BOOL)closeDatabase{
return (sqlite3_close(database) == SQLITE_OK)?YES:NO;
}

3、插入数据。

//打开数据库
if(!){
;
return NO;
}
sqlite3_stmt *insert_statement;
//准备sql语句
const char* sql = "insert into userinfo(uname,upassword,uexp,uportrait) values(?,?,?,?)";
//绑定语句值
if (sqlite3_prepare_v2(database,sql, -1, &insert_statement, NULL) != SQLITE_OK)return NO;
sqlite3_bind_text(insert_statement, 1, , -1, SQLITE_TRANSIENT);
sqlite3_bind_text(insert_statement, 2, , -1, SQLITE_TRANSIENT);
sqlite3_bind_int(insert_statement, 3, );
//提供png格式和jpg格式支持
int returnValue = -1;
NSData *imageData = nil;
UIImage *por = ;
imageData = UIImagePNGRepresentation(por);//png格式
if (nil == por) {
returnValue = sqlite3_bind_blob(insert_statement, 4, nil, -1, NULL);
}else {
returnValue = sqlite3_bind_blob(insert_statement, 4,,, NULL);
}
//执行插入
if (sqlite3_step(insert_statement) != SQLITE_DONE) {
sqlite3_finalize(insert_statement);
;
return NO;
}
sqlite3_finalize(insert_statement);
;

4、更新数据。

//打开数据库
if(!){
;
return NO;
}
//准备陈述命令
sqlite3_stmt *update_statement;
//准备sql语句
const char* sql = "update userinfo set uname=?,upassword=?,uexp=?,uportrait=? where id=1";
//绑定语句值
if (sqlite3_prepare_v2(database,sql, -1, &update_statement, NULL) != SQLITE_OK)return NO;
sqlite3_bind_text(update_statement, 1, , -1, SQLITE_TRANSIENT);
sqlite3_bind_text(update_statement, 2, , -1, SQLITE_TRANSIENT);
sqlite3_bind_int(update_statement, 3, );
//提供png格式和jpg格式支持
int returnValue = -1;
NSData *imageData = nil;
UIImage *por = ;
if (nil == por) {
returnValue = sqlite3_bind_blob(update_statement, 4, nil, -1, NULL);
}else {
imageData = UIImageJPEGRepresentation(por,1.0f);//jpg格式
returnValue = sqlite3_bind_blob(update_statement, 4,,, NULL);
}
}
//执行
if (sqlite3_step(update_statement) != SQLITE_OK){
sqlite3_finalize(update_statement);
;
return NO;
}
sqlite3_finalize(update_statement);
;

5、查询数据

//打开数据库
if (!) {
return self;
}
//查询所有数据
sqlite3_stmt *statement;
const char* zTail;
sqlite3_prepare_v2(database,"SELECT * from userinfo;",-1,&statement,&zTail);
const unsigned char *name;
const unsigned char *password;
int exp=0;
NSString *newName = nil;
NSString *newPassword = nil;
NSData *imgData = nil;
//sqlite3_reset(statement);
if (SQLITE_ROW == sqlite3_step(statement)) {
name = sqlite3_column_text(statement,1);
password = sqlite3_column_text(statement,2);
exp = sqlite3_column_int(statement,3);
if (NULL != sqlite3_column_blob(statement,4)) {
imgData = [ initWithBytes:sqlite3_column_blob(statement,4) length:sqlite3_column_bytes(statement, 1)];
}
newName = ;
newPassword = ;
}

查看完整版本: sqlite运用总结