根据前两篇的文章和网上的一些资料写了一个ios中SQLite3的使用示例,下面直接贴出代码,code中会有必要的注释
实现了数据库表的创建 插入 查询 删除等功能
数据类型:
NULL
INTEGER 整形
REAL 浮点型
TEXT 文本类型
BLOB 二进制对象
———————————————————————更新——————————————————————
sqlite3_prepare() 编码方式:UTF-8
sqlite3_prepare_v2() 编码方式:UTF-8
(PS:看到有资料说sqlite3_prepare()是为了前向兼容,推荐使用sqlite3_prepare_v2())
sqlite3_prepare_v16() 编码方式:UTF-16
———————————————————————————————————————————————
- (void)viewDidLoad
{
[super viewDidLoad];
[self createStudentTable];
[self insertTable];
[self inquire];
[self deleteTable];
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
}
-(NSString *) databasePath
{
NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *pathname = [path objectAtIndex:0];
return [pathname stringByAppendingPathComponent:@"database.sqlite3"];
}
-(BOOL) opendatabase
{
if (sqlite3_open([[self databasePath] UTF8String], &database) != SQLITE_OK) { //根据指定目录打开数据库文件,如果没有就创建一个新的
sqlite3_close(database);
printf("failed to open the database.\n");
return NO;
}
else {
printf("open the database successfully.\n");
return YES;
}
}
-(BOOL) createStudentTable
{
if ([self opendatabase] == YES) {
char *erroMsg;
NSString *TableName = @"StudentTable";
NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(userid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, math INTEGER, iconurl BLOB, virbrate INTEGER, status INTEGER, message TEXT)", TableName];//创建一个表 AUTOINCREMENT 这里userid的值是创建表是自动生成的,从1开始依次自增
if (sqlite3_exec(database, [createSQL UTF8String], NULL, NULL, &erroMsg) != SQLITE_OK) {
sqlite3_close(database);
printf("create table failed.\n");
return NO;
}
else {
printf("table was created.\n");
return YES;
}
}
else
return NO;
}
-(void) ErrorReport:(NSString *)item
{
char *errorMsg;
if (sqlite3_exec(database, [item UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {
NSLog(@"%@ ok\n", item);
}
else {
printf("error: %s", errorMsg);
sqlite3_free(errorMsg);
}
}
-(void) insertTable
{
char *errorMsg;
NSString *insertSql = @"insert into StudentTable (name) values ('Mr_Guo')"; //插入语句
if (sqlite3_exec(database, [insertSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {
printf("insert ok.\n");
}
else {
printf("can't insert it to table\n");
[self ErrorReport:insertSql];
}
}
//查询数据库
-(void) inquire
{
//char *errMsg;
NSString *inquireSQL = @"select userid, name from StudentTable";
sqlite3_stmt *statement;
/*
[注:]
sqlite3_prepare_v2把一条SQL语句(这里是inquireSQL)解析到sqlite3_stmt结构中
试了一下这里使用sqlite3_prepare的结果是完全一样的
[最后面有这两个函数的实现]
*/
if (sqlite3_prepare_v2(database, [inquireSQL UTF8String], -1, &statement, nil) == SQLITE_OK) {
printf("select ok.\n");
while (sqlite3_step(statement) == SQLITE_ROW) {
int _id = sqlite3_column_int(statement, 0); //这里的0是userid在sql语句中的索引,因为我们要查询的内容有userid和name,所以userid的索引为0,name的索引为1
NSString *_time = [[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];
printf("userid = %d, _time = %s.\n",_id, [_time UTF8String]);
}
}
else {
[self ErrorReport:inquireSQL];
}
sqlite3_finalize(statement);
}
-(void) deleteTable
{
char *errMsg;
//[self opendatabase];
NSString *deleteSQL = @"delete from StudentTable where userid=4"; //删除userid为4的表
if (sqlite3_exec(database, [deleteSQL UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
printf("delete ok.\n");
}
else {
printf("can't delete it\n");
[self ErrorReport:deleteSQL];
}
}
/*
SQLITE_API int sqlite3_prepare(
sqlite3 *db, // Database handle.
const char *zSql, // UTF-8 encoded SQL statement.
int nBytes, // Length of zSql in bytes.
sqlite3_stmt **ppStmt, // OUT: A pointer to the prepared statement
const char **pzTail // OUT: End of parsed string
){
int rc;
rc = sqlite3LockAndPrepare(db,zSql,nBytes,0,0,ppStmt,pzTail);
assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 ); // VERIFY: F13021
return rc;
}
SQLITE_API int sqlite3_prepare_v2(
sqlite3 *db, // Database handle.
const char *zSql, // UTF-8 encoded SQL statement.
int nBytes, // Length of zSql in bytes.
sqlite3_stmt **ppStmt, // OUT: A pointer to the prepared statement
const char **pzTail // OUT: End of parsed string
){
int rc;
rc = sqlite3LockAndPrepare(db,zSql,nBytes,1,0,ppStmt,pzTail);
assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 ); // VERIFY: F13021
return rc;
}
*/