SQLite3数据库

SQLite3采用SQLite数据库存储数据

支持SQL语句，方便查询

插件：MesaSQLite

////  ViewController.m//  SQLiteTest////  Created by jerehedu on 15/2/2.//  Copyright (c) 2015年 jereh. All rights reserved.//#import "ViewController.h"//1.导入SQLite3头文件#import <sqlite3.h>

@interface ViewController (){    //2.声明SQLite3对象    sqlite3 *db;}@end

#pragma mark 获得沙盒路径- (NSString *)getUserDocumentPath{    //获得沙盒路径    NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);    NSString *documentPath = [path lastObject];    return documentPath;}

#pragma mark 追加路径- (NSString *)appendingPathComponent:(NSString *)documentPath andFileName:(NSString *)fileName{    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:fileName];    return sqlitePath;}

#pragma mark 创建或打开数据库-(BOOL)openOrCreateSQLiteWithDBPath:(NSString *)dbpath{    const char *p = [dbpath UTF8String];    int res = sqlite3_open(p, &db);    if (res == SQLITE_OK) {        return YES;    }else{        return NO;    }}

#pragma mark 执行sql语句-(BOOL)execSQLNoQueryWithSQL:(NSString *)sql{    int res = sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);    if (res == SQLITE_OK){        return YES;    }else{        return NO;    }}

#pragma mark 返回stmt，无参数-(sqlite3_stmt *)execQueryWithSQL:(NSString *)sql{    sqlite3_stmt *stmt;    int pre_res = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);    if (pre_res == SQLITE_OK){        return stmt;    }    return NULL;}

#pragma mark 有参数-(sqlite3_stmt *)execQueryWithSQL:(NSString *)sql andWithParams:(NSArray *)params{    sqlite3_stmt *stmt;    int pre_res = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);    if (pre_res == SQLITE_OK){        //绑定参数        //判断参数列表是否为空        if (params != nil) {            //循环绑定参数            for (int i = 0; i < params.count; i++) {                //                sqlite3_bind_int(stmt, i+1, [params[i] intValue]);                //要判断参数类型                id obj = params[i];                if (obj == nil) {                    sqlite3_bind_null(stmt, i+1);                }                else if ([obj respondsToSelector:@selector(objCType)]){                    //respondsToSelector判断对象是否包含objCType方法(数字有)                    //strstr(char *,char *)判断是否在char中出现过                    if (strstr("ilsILS", [obj objCType])) {                        sqlite3_bind_int(stmt, i+1, [obj intValue]);                    }else if (strstr("fd", [obj objCType])) {                        sqlite3_bind_double(stmt, i+1, [obj doubleValue]);                    }else{                        stmt = NULL;                    }                }else if ([obj respondsToSelector:@selector(UTF8String)]){                    sqlite3_bind_text(stmt, i+1, [obj UTF8String], -1, NULL);                }else{                    stmt = NULL;                }            }        }                return stmt;    }    return NULL;}

@implementation ViewController- (void)viewDidLoad{    [super viewDidLoad];//获得沙盒路径    NSString *dbPath;    dbPath = [self getUserDocumentPath];        NSString *sqlitePath;    sqlitePath = [self appendingPathComponent:dbPath andFileName:@"test.sqlite"];//追加路径（实际不存在）    NSLog(@"%@",dbPath);//5.判断是否打开成功    if ([self openOrCreateSQLiteWithDBPath:sqlitePath]) {        NSLog(@"db is open");        //构造SQL语句        NSString *sql = @"create table if not exists demo(d_id integer primary key autoincrement,d_name varchar(20))";        int exec_res = sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);        if (exec_res == SQLITE_OK) {            NSLog(@"table is created");        }        /*         参数：         1.sqlite3 对象         2.sql语句         3.回调函数         4.回调函数的参数         5.错误信息         */                //插入记录，构造sql语句        NSString *insert_sql = @"insert into demo(d_name) values ('zhangziyao')";if ([self execSQLNoQueryWithSQL:insert_sql]) {            NSLog(@"one recoder is inserted");        }                //修改        NSString *update_sql = @"update demo set d_name = 'chenyi' where d_id = 1";if ([self execSQLNoQueryWithSQL:update_sql]) {            NSLog(@"one recoder is updated");        }                //删除        NSString *delete_sql = @"delete from demo where d_name='wuyuqiu'";if ([self execSQLNoQueryWithSQL:delete_sql]) {            NSLog(@"one recoder is deleted");        }                //查询int search_d_id = 3;        NSString *search_name = @"_h%";        NSString *search_sql = @"select * from demo where d_id>? and d_name like ?";sqlite3_stmt *stmt;if ([self execQueryWithSQL:search_sql]) {            stmt = [self execQueryWithSQL:search_sql andWithParams:@[[NSNumber numberWithInt: search_d_id],search_name]];            while (sqlite3_step(stmt) == SQLITE_ROW) {                int d_id = sqlite3_column_int(stmt, 0);                const unsigned char *d_name = sqlite3_column_text(stmt, 1);                NSString *name = [NSString stringWithUTF8String:(char*)d_name];                NSLog(@"id=%d,name=%@",d_id,name);                _idLabel.text = [NSString stringWithFormat:@"id=%d",d_id];                _nameLabel.text = [NSString stringWithFormat:@"name=%@",name]; }        }        //释放stmt        sqlite3_finalize(stmt);                //关闭数据库        sqlite3_close(db);                NSNumber *n = [NSNumber numberWithDouble:1.0];        NSLog(@"%s",[n objCType]);    }}- (void)didReceiveMemoryWarning{    [super didReceiveMemoryWarning];    // Dispose of any resources that can be recreated.}@end