1. iOS 数据库(1)

#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface myDB : NSObject
{
    sqlite3 *mySqlite;
}

-(void)openDataBase;
-(void)createTable;
-(void)insertTbale:(NSArray *)array;
-(NSMutableArray *)selectDB;
@end

#import "myDB.h"

@implementation myDB

-(void)openDataBase
{
    NSString *path=[NSHomeDirectory() stringByAppendingFormat:@"/Documents/data.sqlite"];
    
    int result= sqlite3_open([path UTF8String], &mySqlite);
    if (result!= SQLITE_OK) {
        NSLog(@"打开数据失败");
        return;
    }
}
-(void)createTable
{
    [self openDataBase];
    
    NSString *sql=@"CREATE TABLE IF NOT EXISTS abc(usename text PRIMARY KEY ,password text,email text)";
    char *error;
    int result=sqlite3_exec(mySqlite, [sql UTF8String], NULL, NULL, &error);
    if (result != SQLITE_OK) {
        NSLog(@"创建表失败");
        sqlite3_close(mySqlite);
        return;
    }
    
    sqlite3_close(mySqlite);
    


}

-(void)insertTbale:(NSArray *)array
{
    sqlite3_stmt *state;
    //打开数据库
    [self openDataBase];
    
    //sql语句
    NSString *sql=@"INSERT into abc(usename,password,email) VALUES (?,?,?)";
    //编译sql语句
    sqlite3_prepare_v2(mySqlite, [sql UTF8String], -1, &state, NULL);
    NSString  *usename=[array objectAtIndex:0];
    NSString *password=[array objectAtIndex:1];
    NSString  *email=[array objectAtIndex:2];
    
    sqlite3_bind_text(state, 1, [usename UTF8String], -1, NULL);
    sqlite3_bind_text(state, 2, [password UTF8String], -1, NULL);
    sqlite3_bind_text(state, 3, [email UTF8String], -1, NULL);
    
    //执行SQL语句
    int result=sqlite3_step(state);
    if (result==SQLITE_ERROR||result==SQLITE_MISUSE) {
        NSLog(@"执行SQL语句失败");
        return;
    }
    //关闭数据句柄
     sqlite3_finalize(state);
    //关闭数据库
    sqlite3_close(mySqlite);
    NSLog(@"数据插入成功");
   
}

//查询数据
-(NSMutableArray *)selectDB
{
    NSMutableArray *muArray=[NSMutableArray array];
    //打开数据库
    [self openDataBase];
    sqlite3_stmt *statement=nil;
    NSString *sql=@"SELECT usename, password, email FROM abc";
    //编译SQL语言
    int result=sqlite3_prepare_v2(mySqlite, [sql UTF8String], -1, &statement, NULL);
    if (result != SQLITE_OK) {
        NSLog(@"ERROR");
        return 0;
    }
    //查询数据
    result=sqlite3_step(statement);

    while (result==SQLITE_ROW) {
        char *usename=(char *)sqlite3_column_text(statement, 0);
        char *password=(char *)sqlite3_column_text(statement, 1);
        char *email=(char *)sqlite3_column_text(statement, 2);
        NSString *useNameStr=[NSString stringWithCString:usename encoding:NSUTF8StringEncoding];
        NSString *passwordStr=[NSString stringWithCString:password encoding:NSUTF8StringEncoding];
        NSString *emailStr=[NSString stringWithCString:email encoding:NSUTF8StringEncoding];
        NSLog(@"测试：%@，%@，%@",useNameStr,passwordStr,emailStr);
        NSArray *TempArray=@[useNameStr,passwordStr,emailStr];

        [muArray addObject:TempArray];

        result=sqlite3_step(statement);
    }
    
    sqlite3_finalize(statement);
    sqlite3_close(mySqlite);
    
    return muArray;
}
@end