ios：SQLITE的增删改查

ios开发常用的数据库是SQLITE，今天我们来简单聊一下怎么实现SQLITE的增删改查。不多说上代码！

先创建一个数据库的单例（记得用SQLITE开发的时候导入libsqlite3静态库）

#import <Foundation/Foundation.h>
#import "AddressBookModel.h"

@interface DataBaseHandle : NSObject

+(DataBaseHandle *)shareDataBaseHandle;
/**
 *  打开数据库
 */
-(void) openDB;

/**
 *  创建表
 */
- (void) creatTable;

/**
 *  增
 */
- (void) insertIntoTable:(AddressBookModel *)AB;

/**
 *  删
 */

- (void) deleteFromTable:(NSString *)ADID;

/**
 *  改
 */
- (void) updateFromTableWhereName:(NSString *)name toName:(NSString *)toName;

/**
 *  查
 */
- (NSArray *) selectAll;
/**条件查询*/
- (NSArray *) selectFromTableWhereName:(NSString* )name;

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

#import "DataBaseHandle.h"
#import <sqlite3.h>

@implementation DataBaseHandle

+(DataBaseHandle *)shareDataBaseHandle{
    
    //线程锁
    //单例类声明的对象，在第一次初始化后，就会在静态区存在。在程序结束之后才会释放。不会第二次初始化。
    static DataBaseHandle *dataBH = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        if (dataBH == nil) {
            dataBH = [[DataBaseHandle alloc] init];
        }
    });
    return dataBH;
}

/**数据库放在静态区(在静态区声明一个数据库的指针)*/
static sqlite3 *db = nil;
-(void)openDB{
    if(nil != db){
        return;
    }
    
    NSString* documentStr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    
    NSString* dbPath = [documentStr stringByAppendingPathComponent:@"adressBook.sqlite"];
    NSLog(@"%@",dbPath);
    //在dbPath里创建一个文件，并作为数据库，而且这一步做完之后就已经初始化了数据库了，静态区的数据库已经不为nil了
    int result = sqlite3_open(dbPath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    }else{
        NSLog(@"打开失败");
    }
}

-(void)creatTable{
    //创建表的SQL语句 格式: CREATE TABLE + 表名(字段 类型，0.。。。字段 类型)
    NSString* createStr = @"CREATE TABLE AddressBook(ADID text PRIMARY KEY, name text, phone text , age integer)";
    
    //第一个参数：代表在哪一个数据库操作
    //第二个参数：代表要执行哪一条SQL语句
    //第三个参数：回调的函数
    //第四个参数:   回调函数的一些函数
    //第五个参数:   错误信息
    int result = sqlite3_exec(db, createStr.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        NSLog(@"创建成功");
    }else{
        NSLog(@"创建失败");
    }
}

-(void)deleteFromTable:(NSString *)ADID{
    //删除的sql语句
    //DELETE FROM + 表名 WHERE 删除的数据索引参数 = 参数
    NSString* deleteStr = [NSString stringWithFormat:@"DELETE FROM AddressBook WHERE ADID = '%@'",ADID];
    int resulet = sqlite3_exec(db, deleteStr.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == resulet) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

-(void)updateFromTableWhereName:(NSString *)name toName:(NSString *)toName{
    NSString* updateStr = [NSString stringWithFormat:@"UPDATE AddressBook SET name = '%@' WHERE name = '%@'",name, toName];
    int result = sqlite3_exec(db, updateStr.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        NSLog(@"修改成功");
    }else{
        NSLog(@"修改失败");
    }
}

- (void)insertIntoTable:(AddressBookModel *)AB{
    NSString* insertStr = [NSString stringWithFormat:@"INSERT INTO AddressBook(ADID, name, phone, age) VALUES('%@','%@','%@','%ld')",AB.ADID, AB.name, AB.phone, (unsigned long)AB.age];
    
    int result = sqlite3_exec(db, insertStr.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == result) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
}

-(NSArray *)selectAll{
    //查询SQL语句
    //SELECT * FROM + 表名
    NSMutableArray* dataArray = nil;
    NSString* selectStr = @"SELECT * FROM AddressBook";
    
    //伴随指针(保存SQl语句，保存查询出来的数据)
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(db, selectStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == result) {
        NSLog(@"查询成功");
        //有数据才初始化数组
        dataArray = [NSMutableArray array];
        //判断stmt里面是否还有合适的数据(一整条的)
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSString* ADID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            NSString* name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString* phone = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            NSInteger age = sqlite3_column_int(stmt, 3);
            
            AddressBookModel* model = [[AddressBookModel alloc] init];
            model.ADID = ADID;
            model.name = name;
            model.phone = phone;
            model.age = age;
            
            [dataArray addObject:model];
        }
        //查询完释放伴随指针
        sqlite3_finalize(stmt);
        for (AddressBookModel* model in dataArray) {
            NSLog(@"%@",model.ADID);
            NSLog(@"%@",model.name);
            NSLog(@"%@",model.phone);
            NSLog(@"%ld",model.age);
        }
    }else{
        NSLog(@"查询失败");
    }
    return dataArray;
}

-(NSArray *)selectFromTableWhereName:(NSString *)name{
    //查询SQL语句
    //SELECT * FROM + 表名 + WHERE + 条件 + ？；
    NSMutableArray* dataArray = nil;
    NSString* selectStr = @"SELECT * FROM AddressBook WHERE name = ?";
    
    //伴随指针(保存SQl语句，保存查询出来的数据)
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(db, selectStr.UTF8String, -1, &stmt, NULL);
    
    if (SQLITE_OK == result) {
        NSLog(@"查询成功");
        //填充查询语句里的？
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        //有数据才初始化数组
        dataArray = [NSMutableArray array];
        //判断stmt里面是否还有合适的数据(一整条的)
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSString* ADID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            NSString* name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString* phone = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            NSInteger age = sqlite3_column_int(stmt, 3);
            
            AddressBookModel* model = [[AddressBookModel alloc] init];
            model.ADID = ADID;
            model.name = name;
            model.phone = phone;
            model.age = age;
            
            [dataArray addObject:model];
        }
        //查询完释放伴随指针
        sqlite3_finalize(stmt);
        for (AddressBookModel* model in dataArray) {
            NSLog(@"%@",model.ADID);
            NSLog(@"%@",model.name);
            NSLog(@"%@",model.phone);
            NSLog(@"%ld",model.age);
        }
    }else{
        NSLog(@"查询失败");
    }
    return dataArray;
}

-(void)closeDB{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"关闭成功");
    }else{
        NSLog(@"关闭失败");
    }
}

程序运行界面

其他删除修改查询也能实现，具体结果我就不显示了。初学SQLITE不足之处希望大神指出，多多交流。想要DEMO或者SQLITE可视化工具的留下邮箱，我们共同进步！