sqlite mysql 知乎_封装好的SQLite数据库类

封装好的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, 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, NULL, NULL, 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.name, student.sex, student.age, number];

int result = sqlite3_exec(db, updataSQL.UTF8String, NULL, NULL, 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, NULL, NULL, nil);

if (SQLITE_OK == result) {

NSLog(@"删除成功");

}else{

NSLog(@"删除失败");

}

}

//删除表格

-(void)dropTable

{

NSString *dropTable = [NSString stringWithFormat:@"DROP TABLE lanou0613"];

int result = sqlite3_exec(db, dropTable.UTF8String, NULL, NULL, 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;

}

```