iOS 简单使用sqlite3数据库

在这里我简单做一下苹果的sqlite3数据库,为了方便,我是在把数据库创建成了单例,按钮也是通过storybroad拖的.

在创建文件之前,要引入数据库

引入之后,我建立了一个学生的Model,里面有姓名,性别,年龄,学号的属性.

//StudentModel.h
#import <Foundation/Foundation.h>

@interface StudentModel : NSObject

@property (nonatomic, retain)NSString *name;
@property (nonatomic, retain)NSString *gender;
@property (nonatomic, assign)NSInteger age;
@property (nonatomic, assign)NSInteger number;

@end

我是用的ARC,如果是MRC,要在.m文件里进行dealloc里释放.

建立一个数据库单例:

//DataBaseSingleton.h
#import <Foundation/Foundation.h>
@class StudentModel;   //引入学生model
@interface DataBaseSingleton : NSObject

+ (instancetype)shareDataBase;
- (void)openDB;
- (void)createTable;
- (void)insertStudent:(StudentModel *)stu;
- (void)updateStudent:(StudentModel *)stu number:(NSInteger)number;
- (void)deleteStudent:(NSString *)name;
- (NSMutableArray *)selectStudent;
- (void)dropTable;
@end

注意 : SQL语句不要写错,写错一个字母都会失败,SQL语句不一定要用大写,小写也可以,我是为了方便区分,也是因为大写比较整齐好看.

#import "DataBaseSingleton.h"
#import <sqlite3.h>   //要引入头文件
#import "StudentModel.h"

@implementation DataBaseSingleton

+ (instancetype)shareDataBase{
    static DataBaseSingleton *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBaseSingleton alloc] init];
    }
    return dataBase;
}
//声明数据库对象
static sqlite3 *db = nil;
//sqlite数据库基于C语言的底层数据库

//打开数据库
- (void)openDB{
    if (db != nil) {
        NSLog(@"数据库己经打开");
        return;
    }
    //打开数据库
    //参数1:数据库文件
    //参数2:数据库对象
    NSString *sqlfile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlPath = [sqlfile stringByAppendingPathComponent:@"student.sqlite"];
    BOOL result = sqlite3_open(sqlPath.UTF8String ,&db);
    //打开成功时,result == 0,所以判断时要判断 result == SQLITE_OK
    NSLog(@"===== %@", sqlPath);
    NSLog(@"result == %d", result);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    }else {
        NSLog(@"打开失败");
    }
}
//创建表
- (void)createTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS lanou0920(number INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, gender TEXT, age INTEGER)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }
}
//插入学生
- (void)insertStudent:(StudentModel *)stu{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO lanou0920(name, gender, age) VALUES('%@', '%@', '%ld')", stu.name, stu.gender, stu.age];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else {
        NSLog(@"插入失败");
    }
}
//修改学生
- (void)updateStudent:(StudentModel *)stu number:(NSInteger)number{
    NSString *sql = [NSString stringWithFormat:@"UPDATE lanou0920 SET name = '%@', gender = '%@', age = '%ld' WHERE number = '%ld'", stu.name, stu.gender, stu.age, number];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }else {
        NSLog(@"修改失败");
    }
}
//删除
- (void)deleteStudent:(NSString *)name{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM lanou0920 WHERE name = '%@'", name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else {
        NSLog(@"删除失败");
    }
}
//查询所有学生
- (NSMutableArray *)selectStudent{

    NSMutableArray *array = [NSMutableArray array];

    NSString *sql = @"SELECT * FROM lanou0920";
    //创建查询数据库跟随指针
    sqlite3_stmt *stmt= nil;
    //查询前的准备工作
    //参数3:限制SQL语句长度,-1为不限制
    //将数据库对象db,sql语句,跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }else {
        NSLog(@"查询准备失败");
    }
    //循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //取出第一列数据
        int number = sqlite3_column_int(stmt, 0);
        //取出第二列数据
        const unsigned char *name = sqlite3_column_text(stmt, 1);
        //
        const unsigned char *gender = sqlite3_column_text(stmt, 2);
        int age = sqlite3_column_int(stmt, 3);
        //将C语言类型转成OC类型
        NSInteger num = number;
        NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
        NSString *genderStr = [NSString stringWithUTF8String:(const char *)gender];
        NSInteger age2 = age;

        //创建学生对象
        StudentModel *stu = [[StudentModel alloc] init];
        stu.name = nameStr;
        stu.number = num;
        stu.gender = genderStr;
        stu.age = age2;

        [array addObject:stu];
    }

    return array;

}

- (void)dropTable{
    NSString *sql = @"DROP TABLE lanou0920";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删表成功");
    }else {
        NSLog(@"删表失败");
    }
}

@end

完成了上面这些就可以调用数据库了.

//在ViewController里导入数据库和单例的头文件
#import "DataBaseSingleton.h"
#import "StudentModel.h"

因为按钮是拖得,我就直接放按钮的代码了.
注意 : 打开数据库和建表一定是最先执行的,不然下面的操作执行会失败.

//打开数据库
- (IBAction)openBtn:(UIButton *)sender {
    [[DataBaseSingleton shareDataBase] openDB];
}
//建表
- (IBAction)tableBtn:(UIButton *)sender {
    [[DataBaseSingleton shareDataBase] createTable];
}
//插入学生信息
- (IBAction)insertBtn:(UIButton *)sender {
    StudentModel *stu = [[StudentModel alloc] init];
    stu.name = @"lisa";
    stu.gender = @"girl";
    stu.age = 12;
    [[DataBaseSingleton shareDataBase] insertStudent:stu];
}
//修改学生信息
- (IBAction)updateBtn:(UIButton *)sender {
    StudentModel *stu = [[StudentModel alloc] init];
    stu.name = @"Tom";
    stu.gender = @"boy";
    stu.age = 14;
    [[DataBaseSingleton shareDataBase] updateStudent:stu number:1];
}

- (IBAction)deleteBtn:(UIButton *)sender {
    //删除学生信息
    [[DataBaseSingleton shareDataBase] deleteStudent:@"lisa"];

}
//查找学生信息
- (IBAction)selectBtn:(UIButton *)sender {

    NSMutableArray *arr = [[DataBaseSingleton shareDataBase] selectStudent];
    NSLog(@"====== %@", arr);
    for (StudentModel *stu in arr) {
        NSLog(@" %@+++++%@++++++%ld", stu.name, stu.gender, stu.age);
    }
}

- (IBAction)dropBtn:(UIButton *)sender {
    //删表
    [[DataBaseSingleton shareDataBase] dropTable];

}

到这里,sqlite3数据库的简单使用介绍就完成了.