SQLite 在 iOS 中的简单使用(一个小例子)

前提准备

数据库文件:Mydatabase.sqlite,存在一个”Student”表,列为 id(主键),name,sex,age.

导入相关的动态链接库 libsqlite3.0.dylib

现在实现简单的增删改查

1.创建一个 Student 类,用来表示表中一条学生记录

@interface Student : NSObject
@property (nonatomic) NSInteger stuid;
@property (nonatomic,copy) NSString * name;
@property (nonatomic,copy) NSString * sex;
@property (nonatomic) NSInteger age;
- (instancetype)initWithId:(NSInteger)aId
                      name:(NSString *)name
                       sex:(NSString *)sex
                       age:(NSInteger)age;
  @end

. m 文件

@implementation Student
- (instancetype)initWithId:(NSInteger)aId
                      name:(NSString *)name
                       sex:(NSString *)sex
                       age:(NSInteger)age
                     image:(UIImage *)image
{
    self = [super init];
    if (self) {
        self.stuid = aId;
        self.name = name;
        self.sex = sex;
        self.age = age;
        self.image = image;
    }
    return self;
}
- (NSString *)description
{
    return [NSString stringWithFormat:@"%ld,%@,%ld",_stuid,_name,_sex,_age];
}

2.创建一个类,专门实现数据库的打开和关闭(Database 类)

.h 文件

@interface Database : NSObject
+ (sqlite3 *)openDB;
+ (void)closeDB;
@end

.m 文件

static sqlite3 * db = nil;
@implementation Database

+ (sqlite3 *)openDB
{
    if (!db) {
        NSString * originPath = [[NSBundle mainBundle] pathForResource:@"Mydatabase" ofType:@"sqlite"];
        NSString * docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES) firstObject];
        NSString * filePath = [docPath stringByAppendingPathComponent:@"Mydatabase.sqlite"];  //documents 下该数据库文件的路径
        NSFileManager * fm = [NSFileManager defaultManager];
        if (![fm fileExistsAtPath:filePath]) {  //如果不存在
            NSError * error = nil;
            [fm copyItemAtPath:originPath toPath:filePath error:&error]; //向沙盒中的 document 文件下拷贝一份该数据库文件
            if (error) {
                NSLog(@"%@",error);
            }
        }
        //打开数据库
        sqlite3_open([filePath UTF8String],&db);
    }
    return db;
}
+ (void)closeDB
{
    sqlite3_close(db);
    db = nil;
}
@end

3.创建一个数据处理类,专门负责和数据库相关的操作,这里是 StudentDataHandle 类

.h 文件

@class Student;
@interface StudentDataHandle : NSObject
//返回所有学生信息
+ (NSArray *)getAllStudents;
//返回一个学生
+ (Student *)getSingleStudentWithID:(int)stuid;
//插入一个学生
+ (BOOL)insertStudent:(Student *)stu;
+ (BOOL)changeStudentAge:(int)age
                    byId:(int)stuid;
//删除一个学生
+ (BOOL)deleteStudentWithId:(int)stuid;
@end

. m 文件

//返回所有学生信息
+ (NSArray *)getAllStudents
{
    sqlite3 * db = [Database openDB];   //打开数据库
    sqlite3_stmt * smt = nil;
    //参数1: 操作哪个数据库指针,2:sql 语句,3:sql语句长度,-1代表无限长,4:操作信息保存在哪个指针,5:预留参数
    int result = sqlite3_prepare_v2(db,"select * from student",-1,&smt,nil);    //第二个参数表示 sql 语句长度,-1表示无限长
    NSMutableArray * stuArr = nil;
    if (result == sqlITE_OK) {     //成功
        //若还有下一行,就继续取数据
        stuArr = [NSMutableArray arrayWithCapacity:1];
        while (sqlite3_step(smt) == sqlITE_ROW) {
            int stuid = sqlite3_column_int(smt,0);     //取出列中的数据
            const unsigned char * name = sqlite3_column_text(smt,1);
            int age = sqlite3_column_int(smt,2);
            const unsigned char * sex = sqlite3_column_text(smt,3);
            int length = sqlite3_column_bytes(smt,4);  //取出 data 类型数据长度
            const void * photo = sqlite3_column_blob(smt,4);   //取出内容
            NSData * data = [NSData dataWithBytes:photo length:length];
            //将一个2进制数据文件转换为图片
            UIImage * image = [UIImage imageWithData:data];
            Student * stu = [[Student alloc] initWithId:stuid name:[NSString stringWithUTF8String:(const char *)name] sex:[NSString stringWithUTF8String:(const char *)sex] age:age image:image];
            [stuArr addObject:stu];
            [stu release];
        }
    }
    sqlite3_finalize(smt);
    [Database closeDB];
    return [NSArray arrayWithArray:stuArr];
}
+ (Student *)getSingleStudentWithID:(int)stuid  //根据学号返回单个学生
{
    sqlite3 * db = [Database openDB];   //打开数据库
    sqlite3_stmt * smt = nil;
    int result = sqlite3_prepare_v2(db,"select * from student where id = ?",nil);
    Student * stu = nil;
    if (result == sqlITE_OK) {
        sqlite3_bind_int(smt,1,stuid);        //?的顺序是从1开始,给问号绑定参数
        if (sqlite3_step(smt) == sqlITE_ROW) {   //如果取到一条数据
            int stuid = sqlite3_column_int(smt,0);
            const unsigned char * name = sqlite3_column_text(smt,4);   //取出内容
            NSData * data = [NSData dataWithBytes:photo length:length];
            //将一个2进制数据文件转换为图片
            UIImage * image = [UIImage imageWithData:data];
            stu = [[Student alloc] initWithId:stuid name:[NSString stringWithUTF8String:(const char *)name] sex:[NSString stringWithUTF8String:(const char *)sex] age:age image:image];
        }
    }
    sqlite3_finalize(smt);
    [Database closeDB];
    return stu;
}
+ (BOOL)insertStudent:(Student *)stu    //插入一个学生
{
    sqlite3 * db = [Database openDB];
    sqlite3_stmt * smt = nil;
    int result = sqlite3_prepare_v2(db,"insert into student(id,age,sex) values(?,?,?)",nil);
    if (result == sqlITE_OK) {
        sqlite3_bind_int(smt,(int)stu.stuid);
        sqlite3_bind_text(smt,2,[stu.name UTF8String],nil);
        sqlite3_bind_int(smt,3,(int)stu.age);
        sqlite3_bind_text(smt,4,[stu.sex UTF8String],nil);
        int flag = sqlite3_step(smt);
        if (flag == sqlITE_DONE) {      //表示操作成功
            sqlite3_finalize(smt);
            [Database closeDB];
            return YES;
        }
    }
    sqlite3_finalize(smt);
    [Database closeDB];
    return NO;
}
+ (BOOL)changeStudentAge:(int)age
                    byId:(int)stuid     //修改一个学生
{
    sqlite3 * db = [Database openDB];
    sqlite3_stmt * smt = nil;
    int result = sqlite3_prepare_v2(db,"update student set age = ? where id = ?",age);
        sqlite3_bind_int(smt,stuid);
        int flag = sqlite3_step(smt);
        if (flag == sqlITE_DONE) {
            sqlite3_finalize(smt);
            [Database closeDB];
            return YES;
        }
    }
    sqlite3_finalize(smt);
    [Database closeDB];
    return NO;
}
+ (BOOL)deleteStudentWithId:(int)stuid    //删除一个学生
{
    sqlite3 * db = [Database openDB];
    sqlite3_stmt * smt = nil;
    int result = sqlite3_prepare_v2(db,"delete from student where id = ?",stuid);
        int flag = sqlite3_step(smt);
        if (flag == sqlITE_DONE) {
            sqlite3_finalize(smt);
            [Database closeDB];
            return YES;
        }
    }
    sqlite3_finalize(smt);
    [Database closeDB];
    return NO;
}

SQLite 在 iOS 中的简单使用(一个小例子)

猜你在找的Sqlite相关文章