/**
* 导入类库libsqlite3.0.dylib
* 头文件sqlite3.h
* 创建数据库
*/
+ (BOOL)createSqlite
{
//1、打开数据库,获取存放路径
NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingString:@"/user.sqlite"];
//创建数据库返回值是init类型
/*
const char *filename, Database filename (UTF-8)
sqlite3 **ppDb OUT: SQLite db handle
*/
//打开数据库所在的内存地址
sqlite3 *sqlite =NULL;
//如果文件存在不会重新创建,不存在就创建新的文件
int isSuccess = sqlite3_open([path UTF8String], &sqlite);
//判断是否创建成功
if (isSuccess != SQLITE_OK) {
NSLog(@"打开数据库失败");
return NO;
}
//2、sql语句执行
/*
sqlite3*, An open database
const char *sql, SQL to be evaluated
int (*callback)(void*,int,char**,char**), Callback function
void *, 1st argument to callback
char **errmsg Error msg written here
*/
NSString *sql = @"create table if not exists user_info(user_name text, user_id text , user_age int)";
char *error = nil;
int result = sqlite3_exec(sqlite, [sql UTF8String], nil, nil, &error);
//判断执行是否成功
if (result != SQLITE_OK) {
NSLog(@"执行创建表语句失败");
//执行失败关闭数据库
sqlite3_close(sqlite);
return NO;
}
//3、关闭数据库
sqlite3_close(sqlite);
return YES;
}
/**
* 插入数据
*/
+ (BOOL)insertSqliteWithDataModal:(UserModal *)modal;
{
//1、打开数据库,获取存放路径
NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingString:@"/user.sqlite"];
//创建数据库返回值是init类型
//打开数据库所在的内存地址
sqlite3 *sqlite =NULL;
int result = sqlite3_open([path UTF8String], &sqlite);
//判断是否创建成功
if (result != SQLITE_OK) {
NSLog(@"创建数据库失败");
return NO;
}
//创建句柄
sqlite3_stmt *stmt = nil;
//2、创建sql,插入的数据是在外界传入的,使用?占位符,使用句柄将外界传入的数据,和对应的占位符关联
NSString *sqlStr = @"insert into user_info(user_name, user_id, user_age) values(?,?,?)";
//3、sql编译将写好的sql语句和句柄关联
result = sqlite3_prepare(sqlite , [sqlStr UTF8String], -1, &stmt, NULL);
if (result != SQLITE_OK) {
NSLog(@"SQL语句编译失败");
//关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return NO;
}
//4、获取modal中的数据
NSString *name = modal.userName;
NSString *ID = modal.userID;
NSInteger age = modal.userAge;
//通过句柄向sql语句中的占位符填充数据
/**
* @param stmt 句柄
* @param 1 在sql语句中的位置
* @param 要填充的内容
* @param 写入的字符块的大小 -1没有限制
* @param
*/
sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [ID UTF8String], -1, NULL);
sqlite3_bind_int64(stmt, 3, age);
//5、执行sql语句
result = sqlite3_step(stmt);
if (result != SQLITE_DONE) {
NSLog(@"执行语句失败");
//关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return NO;
}
//6、关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return YES;
}
/**
* 修改数据
*/
+ (BOOL)updateData
{
//1、打开数据库,获取存放路径
NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingString:@"/user.sqlite"];
//创建数据库返回值是init类型
//打开数据库所在的内存地址
sqlite3 *sqlite =NULL;
int result = sqlite3_open([path UTF8String], &sqlite);
//判断是否创建成功
if (result != SQLITE_OK) {
NSLog(@"打开数据库失败");
return NO;
}
//创建句柄
sqlite3_stmt *stmt = nil;
//2、创建sql语句
NSString *sqlStr = @"update user_info set user_name = ? where user_id = ?";
//编译sql
result = sqlite3_prepare(sqlite , [sqlStr UTF8String], -1, &stmt, NULL);
if (result != SQLITE_OK) {
NSLog(@"SQL语句编译失败");
//关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return NO;
}
//3、//通过句柄向sql语句中的占位符填充数据
NSString *name = @"Tom";
NSString *ID = @"1088";
sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [ID UTF8String], -1, NULL);
//4、执行sql语句
result = sqlite3_step(stmt);
if (result != SQLITE_DONE) {
NSLog(@"修改失败");
//关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return NO;
}
//5、关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return YES;
}
/**
* 查询数据库
*/
+ (BOOL)quaryData
{
//获取路径
NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingString:@"/user.sqlite"];
//开启数据库
sqlite3 *sqlite = nil;
int result = sqlite3_open([dbPath UTF8String], &sqlite);
if (result != SQLITE_OK) {
NSLog(@"代开数据库失败");
return NO;
}
//创建sql语句字符串
// NSString *sqlStr = @"select * from user_info where user_age > ? and user_name = ?";
NSString *sqlStr = @"select * from user_info where user_age > ? and user_id like ?";
//编译sql语句字符串
sqlite3_stmt *stmt;
result = sqlite3_prepare(sqlite , [sqlStr UTF8String], -1, &stmt, NULL);
if (result != SQLITE_OK) {
NSLog(@"SQL语句编译失败");
//关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return NO;
}
//绑定sql字符串上的数据
NSInteger age = 100;
// NSString *name = @"Tom";
NSString *str = @"108%";
sqlite3_bind_int64(stmt, 1, age);
// sqlite3_bind_text(stmt, 2, [name UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [str UTF8String], -1, NULL);
//执行语句
result = sqlite3_step(stmt);
while (result == SQLITE_ROW) {
/**
* 句柄、在数据库中的角标
*/
char *name = (char *)sqlite3_column_text(stmt, 0);
char *ID = (char *)sqlite3_column_text(stmt, 1);
sqlite_int64 age = sqlite3_column_int64(stmt, 2);
NSString *nameStr = [NSString stringWithCString:name encoding:NSUTF8StringEncoding];
NSString *idStr = [NSString stringWithCString:ID encoding:NSUTF8StringEncoding];
NSLog(@"user_name = %@,user_id = %@,uiser_age = %lld\n",nameStr,idStr,age);
//执行下一条数据,如果不执行,就会在当前所查到的行数不断地循环执行
result = sqlite3_step(stmt);
}
//5、关闭数据库和句柄
sqlite3_close(sqlite);
sqlite3_finalize(stmt);
return YES;
}