iOS原生SQLite3使用

#import

#import

@interface Database : NSObject

+ (sqlite3 *)openDB;

+ (void)closeDB;

@end

#define FILE_NAME @"Database.sqlite"

#import "Database.h"

static sqlite3 *db =nil;

@implementation Database

+ (sqlite3 *)openDB{

    if (!db) {

        //1.获取document文件夹的路径

        //参数1:文件夹的名字参数2:查找域参数3:是否使用绝对路径

        NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) objectAtIndex:0];

        //获取数据库文件的路径

        NSString *dbPath = [documentPathstringByAppendingPathComponent:@"Database.sqlite"];

        //iOS中管理文件的类,负责复制文件,删除文件,移动文件

//        NSFileManager *fm = [NSFileManager defaultManager];

        //判断document中是否有sqlite文件

        int result =sqlite3_open([dbPath UTF8String], &db);

        if (result ==SQLITE_OK) {

            NSLog(@"打开数据库");

        }else{

            NSLog(@"打开数据库失败");

        }

//        if (![fm fileExistsAtPath:dbPath]) {

//            //获取*.app中sqlite文件的路径

//            NSString *boundlePath = [[NSBundle mainBundle] pathForResource:@"Database" ofType:@"sqlite"];

//            NSError *error = nil;

//            //将*.app中sqlite文件复制一份到dbPath

//             BOOL result = [fm copyItemAtPath:boundlePath toPath:dbPath error:&error];

//            //若复制文件失败,打印错误信息

//            if (!result) {

//                NSLog(@"%@",error);

//            }

//        }

        //打开数据库参数1:文件路径(UIF8String可以将OC的NSString转为c中的char)参数2:接受数据库的指针

       

    }

    returndb;

}

+ (void)closeDB{

    sqlite3_close(db);

    db = nil;

}

@end

#import

#import "Student.h"

@interface DatabaseHelper : NSObject

+ (NSMutableArray *)getAllStudents;

+ (Student *)getStudentWithID:(NSInteger)aID;

+ (BOOL)insertStudent:(Student *)aStudent;

+ (BOOL)updateStudentName:(NSString *)aName byID:(NSInteger)aID;

+ (BOOL)deleteStudentWithID:(NSInteger)aID;

+ (void)createTablels;

@end

#import "DatabaseHelper.h"

#import "Database.h"

#import

@implementation DatabaseHelper

+ (void)createTablels{

    sqlite3 *db = [DatabaseopenDB];

    //create table if not exists t_persons (c_id integer primary key autoincrement,c_deviceType text,c_UDN text,c_friendlyName text,c_history_time integer,c_desc text)

    char *sql ="create table if not exists t_persons (c_id integer primary key autoincrement,c_name text,c_sex text,c_age integer,c_desc text)";

    char *error;

    //执行创建语句并接受

    int result =sqlite3_exec(db, sql, NULL, NULL, &error);

    //判断是否创建成功

    if (result !=SQLITE_OK) {

        NSLog(@"创建数据表失败  %s",error);

    }else{

        NSLog(@"数据库表创建成功");

    }

    [DatabasecloseDB];

}

+ (NSMutableArray *)getAllStudents{

    //打开数据库

    sqlite3 *db = [DatabaseopenDB];

    //数据库操作指针 stmt:statement

    sqlite3_stmt *stmt=nil;

    //验证SQL的正确性参数1:数据库指针,参数2:SQL语句,参数3:SQL语句的长度   -1代表无限长(会自动匹配长度),参数4:返回数据库操作指针,参数5:为未来做准备的,预留参数,一般写成NULL

    int result =sqlite3_prepare_v2(db, "select * from t_persons", -1, &stmt,NULL);

    NSMutableArray *studentArr =@[].mutableCopy;

    //判断SQL执行的结果

    if (result ==SQLITE_OK) {

        //存在一行数据

        while (sqlite3_step(stmt) ==SQLITE_ROW) {

            int ID =sqlite3_column_int(stmt,0);

            constunsigned char *name =sqlite3_column_text(stmt,1);

            constunsigned char *sex =sqlite3_column_text(stmt,2);

            int age =sqlite3_column_int(stmt,3);

            //blob类型的获取二进制对象

            //1 获取长度

            int length =sqlite3_column_bytes(stmt,4);

            //2 获取数据

            constvoid *photo = sqlite3_column_blob(stmt,4);

            //3 转成NSData

            NSData *photoData = [NSDatadataWithBytes:photo length:length];

            //4 转成UIImage

           UIImage *image = [UIImageimageWithData:photoData];

            //封装Student模型

            Student *student = [[Studentalloc] init];

            student.ID = ID;

            student.name = [NSStringstringWithUTF8String:(constchar *)name];

            student.sex = [NSStringstringWithUTF8String:(constchar *)sex];

            student.age = age;

            student.photo = image;

            //添加到数组

            [studentArr addObject:student];

        }

    }

    //释放stmt指针

    sqlite3_finalize(stmt);

    //关闭数据库

    [DatabasecloseDB];

    return studentArr;

}

//查询单个学生

+ (Student *)getStudentWithID:(NSInteger)aID

{

    sqlite3 *db = [DatabaseopenDB];

    sqlite3_stmt *stmt =nil;

    NSString *sqlStr = [NSStringstringWithFormat:@"select * from t_persons where c_id = %ld", (long)aID];

    int result =sqlite3_prepare_v2(db, [sqlStrUTF8String], -1, &stmt,NULL);

    Student *student =nil;

    if (result ==SQLITE_OK) {

        if (sqlite3_step(stmt) ==SQLITE_ROW) {

            int ID =sqlite3_column_int(stmt,0);

            constunsigned char *name =sqlite3_column_text(stmt,1);

            constunsigned char *sex =sqlite3_column_text(stmt,2);

            int age =sqlite3_column_int(stmt,3);

            int length =sqlite3_column_bytes(stmt,4);

            constunsigned char *photo =sqlite3_column_blob(stmt,4);

            NSData *photoData = [NSDatadataWithBytes:photo length:length];

            UIImage *image = [UIImageimageWithData:photoData];

            student = [[Studentalloc] init];

            student.ID = ID;

            student.name = [NSStringstringWithUTF8String:(constchar *)name];

            student.sex = [NSStringstringWithUTF8String:(constchar *)sex];

            student.age = age;

            student.photo = image;

        }

    }

    sqlite3_finalize(stmt);

    [DatabasecloseDB];

    return student;

}

+ (BOOL)insertStudent:(Student *)aStudent

{

    sqlite3 *db = [DatabaseopenDB];

    sqlite3_stmt *stmt =nil;

    //c_name c_sex c_age

    NSString *sqlStr = [NSStringstringWithFormat:@"insert into t_persons (c_name,c_sex,c_age,c_desc) values ('Silence of the Lambs, The', 'Jodie Foster', 1991, 'Jodie Foster');"];

    

    int result =sqlite3_prepare_v2(db, [sqlStrUTF8String], -1, &stmt,NULL);

    

    if (result ==SQLITE_OK) {

        //判断语句执行完成没有

        if (sqlite3_step(stmt) ==SQLITE_DONE) {

            sqlite3_finalize(stmt);

            [DatabasecloseDB];

            returnYES;

        }

    }

    sqlite3_finalize(stmt);

    [DatabasecloseDB];

    returnNO;

}

//修改学生的姓名

+ (BOOL)updateStudentName:(NSString *)aName byID:(NSInteger)aID

{

    sqlite3 *db = [DatabaseopenDB];

    sqlite3_stmt *stmt =nil;

    NSString *sqlStr = [NSStringstringWithFormat:@"update t_persons set name = '%@' where c_id = %ld", aName, (long)aID];

    int result =sqlite3_prepare_v2(db, [sqlStrUTF8String], -1, &stmt,NULL);

    if (result ==SQLITE_OK) {

        if (sqlite3_step(stmt) ==SQLITE_ROW) {//觉的应加一个判断,若有这一行则修改

            if (sqlite3_step(stmt) ==SQLITE_DONE) {

                sqlite3_finalize(stmt);

                [DatabasecloseDB];

                returnYES;

            }

        }

    }

    sqlite3_finalize(stmt);

    [DatabasecloseDB];

    returnNO;

}

//删除一个学生

+ (BOOL)deleteStudentWithID:(NSInteger)aID

{

    sqlite3 *db = [DatabaseopenDB];

    sqlite3_stmt *stmt =nil;

    NSString *sqlStr = [NSStringstringWithFormat:@"delete from t_persons where c_id = %ld", (long)aID];

    int result =sqlite3_prepare_v2(db, [sqlStrUTF8String], -1, &stmt,NULL);

    if (result ==SQLITE_OK) {

        if (sqlite3_step(stmt) ==SQLITE_ROW) {//觉的应加一个判断,若有这一行则删除

            if (sqlite3_step(stmt) ==SQLITE_DONE) {

                sqlite3_finalize(stmt);

                [DatabasecloseDB];

                returnYES;

            }

        }

    }

    sqlite3_finalize(stmt);

    [DatabasecloseDB];

    returnNO;

}

@end

//调用

@implementation ViewController

- (void)viewDidLoad {

    [superviewDidLoad];

    [selfcreateTable];

    

    if ([selfinsetOne]) {

        NSLog(@"插入成功");

    }

    

    [selfgetOneStu];

}

- (void)createTable{

    [DatabaseHelpercreateTablels];

}

- (BOOL)insetOne{

    Student *stu = [[Studentalloc] init];

    stu.name =@"chi";

    stu.sex =@"man";

    stu.age =24;

    stu.photo = [UIImageimageNamed:@"record"];

   return [DatabaseHelperinsertStudent:stu];

}

- (void)getOneStu{

    Student *stu = [DatabaseHelpergetStudentWithID:1];

    NSLog(@"%@", stu);

}