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