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