SQLite-常规用法

一、配置
在Build Phases——Link Binary With Libraries下 添加一个
libsqlite3.0.tbd 文件

二：使用
1.声明方法

#import <Foundation/Foundation.h>
#import "Person.h"
@interface ZQ_DataBaseTool : NSObject

//把她做成单例
+ (instancetype)sharedInstance;

//打开数据库
- (void)openDB;

//关闭数据库
- (void)closeDB;

//创建表
- (void)createTable:(NSString *)tableName;

//添加数据
- (void)addPerson:(Person *)person;

//删除数据
- (void)deletePerson:(NSInteger)pid;

//更新数据
- (void)updataPersonWithPid:(NSInteger)pid ForName:(NSString *)name;

//查寻所有数据
- (NSArray<Person *> *)selectAll;

//查询指定Pid的数据
- (Person *)selectById:(NSInteger)pid;

//绑定参数添加数据
- (void)bindAddPerson:(Person *)p;

- (void)sayHi;
@end

#import "ZQ_DataBaseTool.h"
#import <sqlite3.h>
//声明一个静态的自己
static ZQ_DataBaseTool *handler;
@implementation ZQ_DataBaseTool
+(instancetype)sharedInstance{
    if (handler == nil) {
        handler = [[ZQ_DataBaseTool alloc] init];
    }
    return handler;
}
- (void)sayHi{
    NSLog(@"雾是江浙厚，霾是北京纯");
}
#pragma mark--sqlite使用需要创建一个静态的数据库对象
//是一个数据库操作对象，所有的操作都是通过它对文件进行操作的
static sqlite3 *db = nil;
//打开数据库
- (void)openDB{
    if (db != nil) {
        NSLog(@"数据库已经打开，无需重复打开");
        return;
    }
    //获取document路径
    NSString *docPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    //拼接数据库文件路径 (注意：后缀名不能改)
    NSString *fieldPath = [docPath stringByAppendingString:@"/dataBase.sqlite"];
    NSLog(@"%@", fieldPath);
    //打开数据库
   int result = sqlite3_open(fieldPath.UTF8String, &db);//oc调用方法用的是“[]”,这是用c语言写的；所以把fieldPath后加：UTF8String，(混编)；
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功");
    }else {
        NSLog(@"打开数据库失败");
    }
}

//关闭数据库
- (void)closeDB{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
        db = nil;
    }else {
        NSLog(@"关闭数据库失败");
    }
}
//创建表
- (void)createTable:(NSString *)tableName{
    //1、生成sql语句
    NSString *sql = @"CREATE  TABLE  if NOT  EXISTS Personinfo (pid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , name TEXT NOT NULL , sex TEXT NOT NULL , age INTEGER NOT NULL )";
    //2.执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);//纯c不识别nil；
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else {
        NSLog(@"创建表失败：%d", result);
    }
}
//添加数据
- (void)addPerson:(Person *)person{
    //生成sql语句
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Personinfo (name,sex,age) VALUES ('%@','%@',%ld)", person.name, person.sex, person.age];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入数据成功");
    }else {
        NSLog(@"插入数据失败：%d", result);
    }
}

//删除数据
- (void)deletePerson:(NSInteger)pid{
    //生成sql语句
    NSString *sql = [NSString stringWithFormat:@"delete from PersonInfo where pid = %ld",pid];
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else {
        NSLog(@"删除失败：%d", result);
    }
}

//更新数据
- (void)updataPersonWithPid:(NSInteger)pid ForName:(NSString *)name{
   NSString *sql = [NSString stringWithFormat:@"UPDATE Personinfo SET name = '%@' WHERE  pid = %ld",name, pid];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"更新数据成功");
    }else {
        NSLog(@"更新数据失败：%d", result);
    }
}
//查寻所有数据
- (NSArray<Person *> *)selectAll{
    NSMutableArray *arr = [NSMutableArray array];
   //生成sql语句
    NSString *sql = @"SELECT * FROM personinfo";
    //（伴随指针）游标
    //创建游标：
    sqlite3_stmt *stmt = NULL;
    //预执行
    int result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, NULL);// "-1"
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {// step :游标默认在数据上边，step 会让游标往下移一行
            Person *p = [[Person alloc] init];
            int pid = sqlite3_column_int(stmt, 0);
            NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];//将c语言类型，强转成OC格式
            NSString *sex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            NSInteger age = sqlite3_column_int(stmt, 3);
            p.name = name;
            p.pid = pid;
            p.sex = sex;
            p.age = age;
            [arr addObject:p];

        }
    }
    //注意！！！用到了游标，最后需要释放，否则无法关闭数据库
    sqlite3_finalize(stmt);
    return arr;
}
//查询指定Pid的数据
- (Person *)selectById:(NSInteger)pid{
    Person *p = [[Person alloc] init];
    for (Person *pt in [self selectAll]) {
        if (pt.pid == pid) {
            p = pt;
        }
    }
    return p;
}
- (void)bindAddPerson:(Person *)p{
   //生成sql语句
    NSString *sql = @"INSERT INTO Personinfo (name,sex,age) VALUES (?, ?, ?)";
    //创建游标
    sqlite3_stmt *stmt;
    //预执行
    int result = sqlite3_prepare(db, sql.UTF8String,-1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //绑定参数
        sqlite3_bind_text(stmt, 1, p.name.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, p.sex.UTF8String, -1, NULL);
        sqlite3_bind_int(stmt, 3, (int)p.age);
        //游标下移，进行过滤
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入数据成功");
        } else {
            NSLog(@"插入数据失败");
        }

    } else{
        NSLog(@"语句错误");
    }
     sqlite3_finalize(stmt)
  }
@end

- (IBAction)openDBAction:(UIButton *)sender {
    [[ZQ_DataBaseTool sharedInstance] openDB];
}
- (IBAction)closeDBAction:(UIButton *)sender {
    [[ZQ_DataBaseTool sharedInstance] closeDB];
}
- (IBAction)createTableAction:(UIButton *)sender {
    [[ZQ_DataBaseTool sharedInstance] createTable:@""];//参数写什么无所谓，这边没用到
}
- (IBAction)addPersonAction:(UIButton *)sender {
    Person *p = [[Person alloc] init];
    p.name = self.nameField.text;
    p.sex = self.sexField.text;
    p.age = [self.ageField.text intValue];
    [[ZQ_DataBaseTool sharedInstance] addPerson:p];
  }
- (IBAction)deletePersonAction:(UIButton *)sender {
    [[ZQ_DataBaseTool sharedInstance] deletePerson:[self.nameField.text integerValue]];
}
- (IBAction)upDataPersonAction:(UIButton *)sender {
    [[ZQ_DataBaseTool sharedInstance] updataPersonWithPid:[self.nameField.text integerValue]  ForName:self.sexField.text];
}
- (IBAction)selectAllAction:(UIButton *)sender {
   NSArray *arr = [[ZQ_DataBaseTool sharedInstance] selectAll];
    for (Person *p in arr) {
        NSLog(@"%ld--%@--%@--%ld", p.pid, p.name, p.sex, p.age);
    }

}
- (IBAction)selestByAction:(UIButton *)sender {
   Person *p = [[ZQ_DataBaseTool sharedInstance] selectById:[self.nameField.text integerValue]];
    NSLog(@"%ld--%@--%@--%ld", p.pid, p.name, p.sex, p.age);
}
//绑定参数执行sql语句
- (IBAction)BindAction:(UIButton *)sender {
    Person *p = [[Person alloc] init];
    p.name = self.nameField.text;
    p.sex = self.sexField.text;
    p.age = [self.ageField.text intValue];
    [[ZQ_DataBaseTool sharedInstance] bindAddPerson:p];
}