iOS_数据存取（二）

#import "ViewController.h"
#import <sqlite3.h>
@interface ViewController ()
{
    sqlite3 *_db;
}
@end
@implementation ViewController
-(void)queryAll
{
    //准备结果集
    sqlite3_stmt *pStmt = NULL;
    /*
     第一个参数为sqlite3 *类型，为指向sqlite3_open（）类函数打开的数据库连接。
     第二个参数为需要编译成字节码的sql语句。如果输入的参数有多条sql语句，只有第一个SQL语句被编译。
     第三个参数，若为小于0的值，系统会自动读取第一个参数一直到出现字符结束符。若该参数大于0，则它表明要读入的SQL的最大的程度，建议设置其值为sql语句的字节数加上字符结束符后的值，此时执行的效率会有提升。
     第四个参数，返回编译好的sqlite3_stmt指针，若第一个参数不包含SQL语句或传进来的SQL语句有错，则此函数返回时被置为NULL。
     第五个参数若不为NULL，则它会指向第一条SQL语句结尾后面的第一个字节。这个参数用于指向剩下的未编译的语句。
     */
      sqlite3_prepare_v2(_db, "select *from user", -1, &pStmt, NULL);
    while (sqlite3_step(pStmt) == SQLITE_ROW) {
        //取出对应列的字段值，
        int ID = sqlite3_column_int(pStmt, 0);
        const unsigned char *name = sqlite3_column_text(pStmt, 1);
        const unsigned char *password = sqlite3_column_text(pStmt, 2);
        NSLog(@"%d,%s,%s",ID,name,password);
    }
}
-(void)execSql:(NSString *)sql
{
    char *errorMsg = nil;
    /*
     第一个参数为sqlite3 *类型。通过sqlite3_open（）函数得到
     第二个参数是一个指向一个字符串的指针，该字符串的内容为一条完整的SQL语句（不需要在语句结束后加"；"），字符串都是以/0结尾的，这个也不例外。
     第三个参数为一个回调函数，当这条语句执行之后，sqlite3会去调用这个函数。其原型为
     typedef  int （*sqlite_callback）（void *，int，char **colvalue，char **colname）；
     第四个参数为提供给回调函数的参数。如果不需要传递参数，则可以写为NULL。
     第五个参数为错误信息。注意，是指针的指针。通过打印printf（"%s\n"，errmsg），可以知道错误发生在什么地方。
     */
    sqlite3_exec(_db, [sql UTF8String], nil, nil, &errorMsg);
    if (errorMsg) {
        NSLog(@"执行失败:%s",errorMsg);
    }
    else
    {
        NSLog(@"执行成功");
    }
}
- (void)viewDidLoad {
    [super viewDidLoad];
    //拼接数据库保存路径
    NSArray *documents = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *path = [documents lastObject];
    NSString *dbName = [path stringByAppendingPathComponent:@"test.db"];
  
    //打开数据库
    if (sqlite3_open([dbName UTF8String], &_db) == SQLITE_OK) {
        //创建表
        [self execSql:@"create table user(ID integer,name text,password text)"];
        //插入两条新纪录
        [self execSql:@"insert into user values(1,‘admin‘,‘123456‘)"];
        [self execSql:@"insert into user values(2,‘guest‘,‘123456‘)"];
        //查询记录
        [self queryAll];
        //更新记录
        [self execSql:@"update user set password = password + 10 where name = ‘admin‘"];
        [self queryAll];
        //关闭数据库
        sqlite3_close(_db);
    }
    else
    {
        NSLog(@"打开失败");
    } 
}
@end

Student.h头文件代码如下：

#import <Foundation/Foundation.h>

@interface Student : NSObject
@property(assign,nonatomic)NSInteger ID;
@property(copy,nonatomic)NSString *name;
@property(assign,nonatomic)int age;
@property(assign,nonatomic)char gender;
@property(assign,nonatomic)float chineseScore;
@property(assign,nonatomic)float mathScore;
@property(assign,nonatomic)float englishScore;
@end

Student.m代码如下：
#import "Student.h"

@implementation Student
-(NSString *)description
{
    return [NSString stringWithFormat:@"%ld,%@,%d,%c,%.2f,%.2f,%.2f",_ID,_name,_age,_gender,_chineseScore,_mathScore,_englishScore];
}
@end

#import <Foundation/Foundation.h>
#import <sqlite3.h>
@class Student;
@interface StudentDAO : NSObject
{
    sqlite3 *_db;
}
+(StudentDAO *)shardManger;
//初始化:创建表，添加数据；
-(void)initDataBase;
//添加学生记录
-(BOOL)addStudent:(Student *)student;
//删除学生记录
-(BOOL)deleteStudentByName:(NSString *)name;
//更新学生记录
-(BOOL)updateStudent:(Student *)student;
//查询学生记录
-(NSArray *)queryStudentAll;
-(Student*)queryStudentByName:(NSString *)name;
@end

#import "StudentDAO.h"
#import "Student.h"
#import <sqlite3.h>
static StudentDAO *instace = nil;
@implementation StudentDAO
//创建单例对象，确保数据库只被初始化一次，
+(StudentDAO *)shardManger
{
    static dispatch_once_t once;
    dispatch_once(&once,^{
        instace = [StudentDAO new];
        [instace initDataBase];
    });
    return instace;
}
//拼接数据库路径
-(NSString *)dbPath
{
    NSArray *documents = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *path = [documents lastObject];
   return [path stringByAppendingPathComponent:@"test.db"];
}
//此方法会多次被调用，用户创建表
-(BOOL)execSql:(NSString *)sql
{
    char *errorMsg = NULL;
    //参数详解，请参考??上个案例
    sqlite3_exec(_db, [sql UTF8String], nil, nil, &errorMsg);
    if (errorMsg)
    {
        NSLog(@"执行失败：%s",errorMsg);
        return NO;
    }
    else
    {
        NSLog(@"执行成功");
        return YES;
        
    }
}
-(void)initDataBase
{
    //打开数据库
    if (sqlite3_open([[self dbPath]UTF8String], &_db) == SQLITE_OK)
    {
        //创建表
        if ([self execSql:@"create table student (ID integer primary key autoincrement,name text,age integer,gender integer,chineseScore real,mathScore real,englishScore real)"])
        {
            for (int i = 0; i < 5; i++)
            {
                Student *stu = [[Student alloc]init];
                stu.name = [NSString stringWithFormat:@"name%d",i+1];
                stu.age = 22+i;
                stu.gender = (i % 2 == 0 ? ‘F‘:‘M‘);
                stu.chineseScore = 74+i;
                stu.mathScore = 85+i;
                stu.englishScore = 92+i;
                [self addStudent:stu];
                
            }
        }
    }
    sqlite3_close(_db);
}
//添加学生记录
-(BOOL)addStudent:(Student *)student
{
    NSString *insertSql = @"insert into student(name text,age integer,gender integer,chineseScore real,mathScore real,englishScore real)";
    sqlite3_stmt *pStmt = nil;
    if (sqlite3_prepare_v2(_db, [insertSql UTF8String], -1, &pStmt, nil) == SQLITE_OK)
    {
        //使用sqlite3_bind_xxx()对字段进行绑定，通配符与绑定的字段一一对应。序号从1开始。
        sqlite3_bind_text(pStmt, 1, [student.name UTF8String], -1, NULL);
        sqlite3_bind_int(pStmt, 2, student.age);
        sqlite3_bind_int(pStmt, 3, student.gender);
        sqlite3_bind_double(pStmt, 4, student.chineseScore);
        sqlite3_bind_double(pStmt, 5, student.mathScore);
        sqlite3_bind_double(pStmt, 6, student.englishScore);
//        SQLITE_DONE：SQL语句执行完成
        if (sqlite3_step(pStmt) == SQLITE_DONE)
        {
            return YES;
        }
        //释放结果集
        sqlite3_finalize(pStmt);
    }
    return NO;
}
//更新学生记录
-(BOOL)updateStudent:(Student *)student
{
    NSString *updateSql = @"update student set math = ? where name = ?";
    //准备结果集
    sqlite3_stmt *pStmt = NULL;
    if (sqlite3_prepare_v2(_db, [updateSql UTF8String], -1, &pStmt, NULL)== SQLITE_OK)
    {
        sqlite3_bind_int(pStmt, 1, student.mathScore);
        sqlite3_bind_text(pStmt, 2, [student.name UTF8String], -1, NULL);
        if (sqlite3_step(pStmt) == SQLITE_DONE)
        {
            return YES;
        }
    }
    //清理结果集
    sqlite3_finalize(pStmt);
    return NO;
}

//通过姓名进行删除
-(BOOL)deleteStudentByName:(NSString *)name
{
    NSString *deleteSql = @"delete from student where name = ?";
    sqlite3_stmt *pStmt = NULL;
    if (sqlite3_prepare_v2(_db, [deleteSql UTF8String], -1, &pStmt, nil) == SQLITE_OK)
    {
        sqlite3_bind_text(pStmt, 1, [name UTF8String], -1, NULL);
        if (sqlite3_step(pStmt) == SQLITE_OK)
        {
            return YES;
        }
    }
    sqlite3_finalize(pStmt);
    return NO;
}
//查询学生记录
-(NSArray *)queryStudentAll
{
    NSMutableArray *array = [NSMutableArray array];
    //准备结果集
    sqlite3_stmt *pStmt = NULL;
    if (sqlite3_prepare_v2(_db, [@"select * from student" UTF8String], -1, &pStmt, NULL) == SQLITE_OK)
    {
        //遍历结果集
        while (sqlite3_step(pStmt) == SQLITE_ROW)
        {
            
            Student *stu = [[Student alloc]init];
            stu.ID = sqlite3_column_int(pStmt, 0);
            stu.name = [NSString stringWithFormat:@"%s",sqlite3_column_text(pStmt, 1)];
            stu.age = sqlite3_column_int(pStmt, 2);
            stu.gender = sqlite3_column_int(pStmt, 3);
            stu.chineseScore = sqlite3_column_double(pStmt, 4);
            stu.mathScore = sqlite3_column_double(pStmt, 5);
            stu.englishScore = sqlite3_column_double(pStmt, 6);
            
            [array addObject:stu];
        }
    }
    //清理结果集
    sqlite3_finalize(pStmt);
    return array;
}
//通过姓名进行查找
-(Student *)queryStudentByName:(NSString *)name
{
    NSString *sql = @"select * from student where name = ?";
    //准备结果集
    sqlite3_stmt *pStmt = NULL;
    if (sqlite3_prepare_v2(_db,[sql UTF8String], -1, &pStmt, NULL) == SQLITE_OK) {
        sqlite3_bind_text(pStmt, 1, [name UTF8String], -1, NULL);
        //执行语句
        if(sqlite3_step(pStmt)== SQLITE_ROW)
        {
            Student *stu = [Student new];
//            使用sqlite3_column_xxx()进行获取某列的字段值。
            stu.ID = sqlite3_column_int(pStmt, 0);
            stu.name = [NSString stringWithFormat:@"%s",sqlite3_column_text(pStmt, 1)];
            stu.age = sqlite3_column_int(pStmt, 2);
            stu.gender = sqlite3_column_int(pStmt, 3);
            stu.chineseScore = sqlite3_column_double(pStmt, 4);
            stu.mathScore = sqlite3_column_double(pStmt, 5);
            stu.englishScore = sqlite3_column_double(pStmt, 6);
            
            return stu;
        }
    }
    sqlite3_finalize(pStmt);
    return nil;
}
@end