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数据库的简单使用介绍就完成了.