封装好的sqlite数据库类
首先你先导入静态库文件 libsqlite3.dylib
在.h声明方法
#pragma mark - 单例
@H_404_9@//创建单例方法 注意：是类方法
+(instancetype)shareDataBaseHandle;
#pragma mark - 数据库相关操作
@H_404_9@//打开数据库
-(void)open;
@H_404_9@//创建表单
-(void)createTable;
@H_404_9@//插入数据（学生信息）
-(void)insertStudent:(Student *)student;
@H_404_9@//更新学生信息
-(void)updataStudent:(Student *)student number:(NSInteger)number;
@H_404_9@//删除数据
-(void)deleteStudent:(NSInteger)number;
@H_404_9@//删除表格
-(void)dropTable;
@H_404_9@//关闭数据库
-(void)close;
@H_404_9@//查询
-(NSArray *)selectStudentWithSex:(NSString *)sex;
在.m写入方法
@H_404_9@//创建一个数据库对象
static sqlite3 *db;
+(instancetype)shareDataBaseHandle
{
@H_404_9@// static 修饰词作用：1 在静态区开空间，程序退出时，内存才释放 2 只初始化一次
static DataBaseHandle *databaseHandle = nil;
if (databaseHandle == nil) {
databaseHandle = [[DataBaseHandle alloc] init];
}
return databaseHandle;
}
@H_404_9@//打开数据库
-(void)open
{
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) lastObject];
NSString *sqlPath = [docPath stringByAppendingPathComponent:@"students.sqlite"];
@H_404_9@//如果数据库已经打开，直接返回，不运行下面代码
if (db != nil) {
NSLog(@"数据库已经打开");
return;
}
@H_404_9@//核心API ： sqlite3_open 函数
int result = sqlite3_open(sqlPath.UTF8String,&db);
@H_404_9@//根据函数返回值，判断操作是否正确
if (result == sqlITE_OK) {
NSLog(@"数据库打开成功");
}else{
NSLog(@"数据库打开失败");
}
}
@H_404_9@//创建表单
-(void)createTable
{
@H_404_9@//创建sql语句
NSString *createsql = @"CREATE TABLE IF NOT EXISTS lanou0613(number integer PRIMARY KEY AUTOINCREMENT,name TEXT,sex TEXT,age integer)"; @H_404_9@//lanou0613是表单名字可以更改
@H_404_9@//核心API
int result = sqlite3_exec(db,createsql.UTF8String,NULL,nil);
if (sqlITE_OK == result) {
NSLog(@"创建表格成功");
}else{
NSLog(@"创建表格失败");
}
}
@H_404_9@//插入数据（学生信息）
-(void)insertStudent:(Student *)student
{
@H_404_9@//创建sql语句
NSString *insertsql = [NSString stringWithFormat:@"INSERT INTO lanou0613(name,sex,age) VALUES('%@','%@','%ld')",student.name,student.sex,student.age];
@H_404_9@//核心API
int result = sqlite3_exec(db,insertsql.UTF8String,nil);
if (sqlITE_OK == result) {
NSLog(@"插入数据成功");
}else{
NSLog(@"插入数据失败");
}
}
@H_404_9@//更新学生信息
-(void)updataStudent:(Student *)student number:(NSInteger)number
{
NSString *updatasql = [NSString stringWithFormat:@"UPDATE lanou0613 SET name = '%@',sex = '%@',age = '%ld' WHERE number = '%ld'",student.age,number];
int result = sqlite3_exec(db,updatasql.UTF8String,nil);
if (sqlITE_OK == result) {
NSLog(@"更新数据成功");
}else{
NSLog(@"更新数据失败");
}
}
@H_404_9@//删除数据
-(void)deleteStudent:(NSInteger)number
{
NSString *deletesql = [NSString stringWithFormat:@"DELETE FROM lanou0613 WHERE number = '%ld'",number];
int result = sqlite3_exec(db,deletesql.UTF8String,nil);
if (sqlITE_OK == result) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败");
}
}
@H_404_9@//删除表格
-(void)dropTable
{
NSString *dropTable = [NSString stringWithFormat:@"DROP TABLE lanou0613"];
int result = sqlite3_exec(db,dropTable.UTF8String,nil);
if (sqlITE_OK == result) {
NSLog(@"删除表格成功");
}else{
NSLog(@"删除表格失败");
}
}
@H_404_9@//关闭数据库
-(void)close
{
int result = sqlite3_close(db);
if (sqlITE_OK == result) {
db = nil;
NSLog(@"关闭数据库成功");
}else{
NSLog(@"关闭数据库失败");
}
}
@H_404_9@//查询
-(NSArray *)selectStudentWithSex:(NSString *)sex
{
@H_404_9@//创建数组
NSMutableArray *arr = [NSMutableArray array];
@H_404_9@//创建SQL查询语句
NSString *selectsql = [NSString stringWithFormat:@"SELECT *FROM lanou0613 WHERE sex = '%@'",sex];
@H_404_9@//准备好的sql语句对象
sqlite3_stmt *stmt = nil;
@H_404_9@//
int result = sqlite3_prepare_v2(db,selectsql.UTF8String,-1,&stmt,nil);
if (sqlITE_OK == result) {
NSLog(@"查询成功");
@H_404_9@// //执行准备好的sql语句
@H_404_9@// sqlite3_step(stmt);
@H_404_9@//sqlite3_step(stmt); 这个函数的返回值为 sqlITE_ROW
@H_404_9@//循环 判断到什么时候结束
while (sqlITE_ROW == sqlite3_step(stmt)) {
@H_404_9@//但需要对象查询之后的结果进行处理操作时，使用sqlite3_column_ 函数
const unsigned char *name = sqlite3_column_text(stmt,1);@H_404_9@//取出name
const unsigned char *sex = sqlite3_column_text(stmt,2);@H_404_9@//取出sex
int age = sqlite3_column_int(stmt,3); @H_404_9@//取出age
@H_404_9@//创建Model对象，赋值
Student *stu = [[Student alloc] init];
stu.name = [NSString stringWithUTF8String:(const char *)name];
stu.sex = [NSString stringWithUTF8String:(const char *)sex];
stu.age = age;
@H_404_9@//model 对象添加到数组中
[arr addObject:stu];
[stu release];
}
@H_404_9@//销毁 准备好的sql语句对象
sqlite3_finalize(stmt);
}else{
NSLog(@"查询失败");
sqlite3_finalize(stmt);
}
return arr;
}
