数据库

1.  创建一个继承于NSObject的类   :  DataBaseHandler

DataBaseHandler.h中

{

    sqlite3 * dbPoint;//指针指向的是本地数据库文件

   

}

//作为一个数据处理的类,其对象最好为一个单例

+ (DataBaseHandler *)shareInstance;

 

//开启数据库

- (void)openDB;

 

//关闭数据库

- (void)closeDB;

 

 

//创建表

- (BOOL)creatTable;

 

//插入数据

- (BOOL)insertTableWithName:(NSString *)name age:(int)age number:(int)number;

 

 

//删除数据

- (BOOL)deleteTableWithName:(NSString *)name;

 

 

//更新数据

- (BOOL)updateName:(NSString *)scorceName toName:(NSString *)targetName;

 

 

//查询数据

- (NSArray *)selectTable;

 

 

 

 

 

 

 

 

 

DataBaseHandler.m

   

+ (DataBaseHandler *)shareInstance

{

   

    static DataBaseHandler * handler= nil;

 

    //保证线程安全的情况下进行一次

    static dispatch_once_t onceToken;

    dispatch_once(&onceToken, ^{

        handler = [[DataBaseHandler alloc] init];

       

        //第一次执行的时候打开数据库  创建单例

        [handler openDB];

       

       

 

    });

 

    return handler;

 

}

 

#warning //开启数据库

- (void)openDB

{

    //1.拼接一个数据库文件的地址

    NSArray * arrPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

   

    //沙盒中的documents文件夹

    NSString * documentPath = [arrPath lastObject];

    NSLog(@"%@",documentPath);

    //拼接处地址

    NSString * dbPointPath =  [documentPath stringByAppendingPathComponent:@"lanou.db"];

    NSLog(@"%@",dbPointPath);

 

    //参数1: 数据库的文件路径

    //参数2: sqlite3 指针的地址&dbPoint   属性

    //[dbPointPath UTF8String] 将NSString转成constchar * 类型

    int result = sqlite3_open([dbPointPath UTF8String], &dbPoint);

   

    //对数据库结果进行判断

    if (result == SQLITE_OK) {

       

        NSLog(@"打开成功");

       

    }

    else

    {

        NSLog(@"打开失败");

       

    }

   

   

   

}

 

- (void)closeDB

{

    int result = sqlite3_close(dbPoint);

   

    if (result == SQLITE_OK) {

       

        NSLog(@"关闭数据库成功");

       

    }else

    {

        NSLog(@"关闭数据库失败");

    }

   

}

 

 

- (BOOL)creatTable

{

   

   

    //注意两个单引号

    NSString * sqlStr = [NSString stringWithFormat:@"create tableStudent (name text,age int,number int primary key)"];

   

    char * error = nil;

   

    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);

   

    if (result == SQLITE_DONE) {

        return YES;

       

    }

   

    return NO;

   

   

  

   

   

    //1.数据库的替身缓冲地带  确保更新完成之后会自动的更新数据库

    //作用 :存储所有对数据库的修改

    sqlite3_stmt * stmt = nil;

   

    //2.检查SQL语句,为执行sql语句做的准备

    //参数1:数据库指针

    //参数2:SQL语句  不需要@ 直接双引号里面填写

    //参数3:限制sql语句的长度不想限制长度写-1

    //参数4:数据库替身stmt的地址

    int result =sqlite3_prepare(dbPoint, "create table Student (name text,ageint,number int primary key)", -1, &stmt, nil);

 

    //3.判断检查的结果

    if (result == SQLITE_OK) {

       

       

        //4.执行sql语句

       int sqlResult = sqlite3_step(stmt);

       

       

        //判断sqlResult

        if (sqlResult == SQLITE_DONE ) {

            //stmt 数据保存到数据库指针释放掉

            sqlite3_finalize(stmt);

            return YES;

           

        }

  

    }

   

    //如果检查sql语句失败,将stmt指针释放掉

    sqlite3_finalize(stmt);

   

    return  NO;

 

}

 

 

 

#warning //插入

- (BOOL)insertTableWithName:(NSString *)name age:(int)age number:(int)number

{

   

    //注意两个单引号

    NSString * sqlStr = [NSString stringWithFormat:@"insert into Studentvalues ('%@',%d,%d)",name,age,number];

   

    char * error = nil;

   

    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);

   

    if (result == SQLITE_DONE) {

        return YES;

       

    }

   

    return NO;

   

 

    //1.替身

    sqlite3_stmt * stmt = nil;

   

    //注意NULL

    int result =sqlite3_prepare(dbPoint, "insert into Student values(?,?,?)", -1, &stmt, NULL);

   

    //3.给问号 ? 绑定参数和数据

    //参数1 : 替身

    //参数2 : 给哪个位置的 ?绑定  第一个元素为1

    //参数3: 提供的数据

    sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);

 

    sqlite3_bind_int(stmt, 2, age);

   

    sqlite3_bind_int(stmt, 3, number);

   

   

    //4.sql检查无误

    if (result == SQLITE_OK) {

       

        //5.执行

        if (sqlite3_step(stmt) == SQLITE_DONE) {

            //6.释放

            sqlite3_finalize(stmt);

            return YES;

           

        }

    }

    sqlite3_finalize(stmt);

    return NO;

 

}

 

 

#warning //更新

- (BOOL)updateName:(NSString *)scorceName toName:(NSString *)targetName

{

   

    //注意两个单引号

    NSString * sqlStr = [NSString stringWithFormat:@"update Student setname = '%@' where name = '%@'",scorceName,targetName];

   

    char * error = nil;

   

    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);

   

    if (result == SQLITE_DONE) {

        return YES;

       

    }

   

    return NO;

   

 

    //1.替身

    sqlite3_stmt * stmt = nil;

   

    //注意NULL

    int result =sqlite3_prepare(dbPoint, "update Student set name = ? wherename = ?", -1, &stmt, NULL);

   

    //3.给问号 ? 绑定参数和数据

    //参数1 : 替身

    //参数2 : 给哪个位置的 ?绑定  第一个元素为1

    //参数3: 提供的数据

    sqlite3_bind_text(stmt, 1, [scorceName UTF8String], -1, NULL);

 

    sqlite3_bind_text(stmt, 2, [targetName UTF8String], -1, NULL);

   

    //4.sql检查无误

    if (result == SQLITE_OK) {

       

        //5.执行

        if (sqlite3_step(stmt) == SQLITE_DONE) {

            //6.释放

            sqlite3_finalize(stmt);

            return YES;

           

        }

    }

    sqlite3_finalize(stmt);

    return NO;

 

}

 

 

 

 

 

 

#warning 删除    改进  除了查询之外的方法都可以简化

 

- (BOOL)deleteTableWithName:(NSString *)name

{

   

    //注意两个单引号

    NSString * sqlStr = [NSString stringWithFormat:@"delete from Studentwhere name = '%@'",name];

   

    char * error = nil;

   

    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, &error);

   

    if (result == SQLITE_DONE) {

        return YES;

       

    }

   

    return NO;

   

   

   

   

#warning 原来的复杂算法

    sqlite3_stmt * stmt = nil;

   

    int result =sqlite3_prepare(dbPoint, "delete from Student where name =?", -1, &stmt, NULL);

   

    sqlite3_bind_text(stmt, 1, [name UTF8String ], -1, NULL);

   

   

    //4.Sql检查无误

    if (result == SQLITE_OK) {

       

        //5.执行

        if (sqlite3_step(stmt) == SQLITE_DONE) {

           

            //6.保存/释放

            sqlite3_finalize(stmt);

            return YES;

           

        }

    }

   

    sqlite3_finalize(stmt);

    return NO;

 

   

}

#warning //查询   显示所有的

- (NSArray *)selectTable

{

    sqlite3_stmt * stmt = nil;

   

    int result = sqlite3_prepare(dbPoint, "select * fromStudent", -1, &stmt, NULL);

   

    if (result == SQLITE_OK) {

       

        //数组用来存储结果

        NSMutableArray * arr = [NSMutableArray array];

       

        while (sqlite3_step(stmt) == SQLITE_ROW)

        {

           

            //获取每行的数据

            const unsigned char * nameChar = sqlite3_column_text(stmt, 0);

           

            NSString * name = [NSString stringWithUTF8String:(const char *)nameChar];//产生第一条数据

           

            int age =  sqlite3_column_int(stmt, 1);

            

            int number = sqlite3_column_int(stmt, 2);

           

            NSString * str = [NSString stringWithFormat:@"姓名:%@,年龄:%d,学号: %d",name,age,number];

           

            NSLog(@"%@",str);

           

            [arr addObject:str];

           

        }

        sqlite3_finalize(stmt);

        return arr;

         

    }

    sqlite3_finalize(stmt);

    return [NSMutableArray array];    

}

在线代码格式化

http://tool.oschina.net/codeformat/json