IOS中的sqlite数据库的使用

sqlite在移动客户端的开发非常常见，我之前做过一个ios项目中也用到，无耐技术水平有限，做的不是很好！但是一查网上这方面的总结不是很多，可能大牛们都忙着赚钱去了，我就献丑将我在项目中用到的东西贡献出来，有什么不好的地方请指教。值得说明的是我是看了某个网站贴出的demo而稍作改进，若此君不小心看到本篇小作，请指出，如有需要可以删除此文。下面开始介绍我的做的东西：

1.用到主要的函数有sqlite3_open,sqlite3_close,sqlite3_exe这里我不解释这些函数的用法了（http://www.cppblog.com/woaidongmao/archive/2009/06/23/88361.html，有详细介绍），直接进入正题。

2.这个例子里面涉及如下几个文件，DatabaseConnection,DatabaseStatement,DataBaseCenter.在这个demo中我只要用到一个数据库

DatabaseConnection.h

#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "DataStatement.h"

@interface DataConnection : NSObject
//建表
+(void)exeCreate:(NSString*)createString;
+(sqlite3*)sharedDataBase;      //访问单例
+(void)closeDataBase;              //关闭数据库连接，置空句柄
+(void)beginTransaction;          //开始事务
+(void)commitTransaction;      //提交事务
+ (DataStatement*)statementWithQuery:(const char*)sql; //初始化一个连接
+(void)alert; //提出警告
+(int)getRowsWithQury:(const char*)sql;  //获取select语句中结果数据的行数
@end

DatabaseConnection.m

#import "DataConnection.h"
#define DATABASENAME @"Demo.db"

static sqlite3 *dataBaseInstance = nil;
@implementation DataConnection
+(void)exeCreate:(NSString*)createString
{
    char *errorMsg;
    if (sqlite3_exec(dataBaseInstance, [createString UTF8String], NULL, NULL, &errorMsg)!=SQLITE_OK)
    {
        NSAssert1(0, @"can not create users table,'%s'.",errorMsg);
    }
    [self closeDataBase];
}
+(sqlite3*)openDataBase:(NSString*)dataBaseName
{
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString * path = [array objectAtIndex:0];
    NSString * databasePath = [path stringByAppendingPathComponent:dataBaseName];
    if (sqlite3_open([databasePath UTF8String], &dataBaseInstance)!=SQLITE_OK)
    {
        NSLog(@"can not open the database:%@",sqlite3_errmsg(dataBaseInstance));
        sqlite3_close(dataBaseInstance);
        return nil;
    }
    return dataBaseInstance;
    
}
+(sqlite3*)sharedDataBase
{
    if (nil == dataBaseInstance) {
        @synchronized(self)
        {
            dataBaseInstance = [self openDataBase:DATABASENAME];
            
        }
        if (nil == dataBaseInstance) {
            [self  createEditableCopyofDataBaseIfNeeded:YES]; 
        }

    }
    return dataBaseInstance;
}
+(void)commitTransaction
{
    char *errMessage;
    sqlite3_exec(dataBaseInstance, "COMMIT", nil, nil, &errMessage);
}
+(void)beginTransaction
{
    char *errMessage;
    sqlite3_exec(dataBaseInstance, "BEGIN", nil, nil, &errMessage);
}

+(DataStatement*)statementWithQuery:(const char*)sql
{
    [self sharedDataBase];
    DataStatement *dataStatement = [DataStatement statementWithDB:dataBaseInstance withQuery:sql];
    return dataStatement;
  }
+(void)closeDataBase
{
    sqlite3_close(dataBaseInstance);
    dataBaseInstance = nil;                  //数据库句柄一定要置空，不然重新建立连接的时候会有问题
}
+(void)alert
{
    NSString *errorString = [NSString stringWithUTF8String:   sqlite3_errmsg(dataBaseInstance)];
    CLog(@"alert message:%@",errorString); 
}

Datastatement.h

#import <Foundation/Foundation.h>
#import "sqlite3.h"

@interface DataStatement : NSObject
{
    sqlite3_stmt* stmt;
}
+(id)statementWithDB:(sqlite3*)DB withQuery:(const char *)sql;
-(id)initWithDB:(sqlite3*)db withQuery:(const char *)sql;
// method
- (int)step;
- (void)reset;
- (int)getResultCount;

// Getter
- (NSString*)getString:(int)index;
- (int)getInt32:(int)index;
- (long long)getInt64:(int)index;
- (NSData*)getData:(int)index;
- (double)getDouble:(int)index;

// Binder
- (void)bindString:(NSString*)value forIndex:(int)index;
- (void)bindInt32:(int)value forIndex:(int)index;
- (void)bindInt64:(long long)value forIndex:(int)index;
- (void)bindData:(NSData*)data forIndex:(int)index;
- (void)bindDouble:(double)value forIndex:(int)index;
@end

Datastatement.m

//
//  DataStatement.m
//  mainPage
//
//  Created by myiee on 11-11-3.
//  Copyright (c) 2011年 __MyCompanyName__. All rights reserved.
//

#import "DataStatement.h"

@implementation DataStatement
-(void)dealloc
{
    [super dealloc];
    sqlite3_finalize(stmt);
}
-(id)initWithDB:(sqlite3 *)db withQuery:(const char *)sql
{
    self = [super init];
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL)!=SQLITE_OK) {
        NSAssert2(0, @"can not compare '%s',(%s)", sql, sqlite3_errmsg(db));
    }
    return self;
}
+(id)statementWithDB:(sqlite3 *)DB withQuery:(const char *)sql
{
    return [[[DataStatement alloc]initWithDB:DB withQuery:sql]autorelease];
}

-(int)step
{
    return  sqlite3_step(stmt);
}
-(void)reset
{
    sqlite3_reset(stmt);
}
- (int)getResultCount
{
   return  sqlite3_column_count(stmt);
}
-(NSString*)getString:(int)index
{
    if (NULL != (char*)sqlite3_column_text(stmt, index)) {
        return [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, index)];
    }
    else
        return nil;
}
-(int)getInt32:(int)index
{
    return sqlite3_column_int(stmt, index);
}
-(long long)getInt64:(int)index
{
    return sqlite3_column_int64(stmt, index);
}
-(NSData*)getData:(int)index
{
    int length =  sqlite3_column_bytes(stmt, index);
    return [NSData dataWithBytes:sqlite3_column_blob(stmt, index) length:length];
}
-(double)getDouble:(int)index
{
    return sqlite3_column_double(stmt, index);
}
-(void)bindData:(NSData *)data forIndex:(int)index
{
    sqlite3_bind_blob(stmt, index, data.bytes, data.length, SQLITE_TRANSIENT);
}
-(void)bindString:(NSString *)value forIndex:(int)index
{
    sqlite3_bind_text(stmt, index,[value UTF8String] , -1, SQLITE_TRANSIENT);
}
-(void)bindInt32:(int)value forIndex:(int)index
{
    sqlite3_bind_int(stmt, index,value);
}
-(void)bindInt64:(long long)value forIndex:(int)index
{
    sqlite3_bind_int64(stmt, index, value);
}
-(void)bindDouble:(double)value forIndex:(int)index
{
    sqlite3_bind_double(stmt, index, value);
}
@end

DatabaseCenter.h

#import <Foundation/Foundation.h>
#import "sqlite3.h"
//#import "Users.h"
#import "DataConnection.h"
#import "DataStatement.h"
@interface DataBaseCenter : NSObject
{
    sqlite3_stmt *stmt;
}
+ (void)createDatabase;
//user
+ (id)getLastLoginUser;
+ (BOOL)updateUser:(NSDictionary*)dictionary;
+ (id)getUserWithName:(NSString*)name;
@end

DatabaseCenter.m

+(void)createDatabase
{
    [DataConnection sharedDataBase];
    NSString *createUser   = @"create table if not exists user(userId integer primary key,name text not null,password text not null,flag integer not null);";
   //这个数据库中只建立了一个user的数据表，当flag为1的时候代表这条用户的数据纪录是最后一个合法登陆的数据用户
    [DataConnection exeCreate:createDataString];
}
+ (id)getLastLoginUser
{
    static DataStatement *stmt = nil;
    if (!stmt) 
    {
        stmt = [DataConnection statementWithQuery:"select * from user where flag = 1"];
        [stmt retain];
    }
    if (SQLITE_ROW != [stmt step]) {
        [stmt reset];
        return nil;
    }
    NSString *userName = [stmt getString:1];
    NSString *password = [stmt getString:2];
    NSDictionary *dic = [[NSDictionary alloc]initWithObjectsAndKeys:userName,@"name",password,@"password", nil];
    return [dic autorelease];
}
+ (BOOL)updateUser:(NSDictionary*)dictionary  //将当前的的数据的flag更新为1 ，其他置为0,当前的用户数据不存在的时候插入到表中
{
    NSDictionary *dic  = [dictionary retain];
    NSString *userName = [dic objectForKey:@"name"];
    NSString *password = [dic objectForKey:@"password"];
    int flag           = [[dic objectForKey:@"flag"]intValue];
    
    NSString *selectString = [NSString stringWithFormat:@"select * from user where name = '%@'",userName];
    const char *cString      = [selectString cStringUsingEncoding:NSUTF8StringEncoding ];
    unsigned int r = [DataConnection getRowsWithQury:cString];
    DataStatement *stmt = nil;
  
    if (!stmt) {
        stmt  = [DataConnection statementWithQuery:"update user set flag = 0 where flag != 0"];
        [stmt retain];
    }
    if (SQLITE_DONE != [stmt step]) {
        [stmt release];
        [stmt reset];
        stmt = nil;
        return NO;
    }
    [stmt release];
    stmt = nil;
    if (r ==1 ) {//当之前已经存在该数据的时候更新变为1
        NSString *stmtString = [NSString stringWithFormat:@"update user set flag = 1 where name = '%@'",userName];
        stmt  = [DataConnection statementWithQuery:[stmtString UTF8String]];
        [stmt retain];
        if (SQLITE_DONE != [stmt step]) {
            [stmt release];
            [stmt reset];
            stmt = nil;
            return NO;
        }
        [stmt release];
    }
    else 
    {
        stmt = nil;
        if (!stmt) {
            stmt = [DataConnection statementWithQuery:"insert or replace into user(name,password,flag) values(?,?,?)"];
            [stmt retain];
        }
        [stmt bindString:userName forIndex:1];
        [stmt bindString:password forIndex:2];
        [stmt bindInt32:flag forIndex:3];
        if (SQLITE_DONE != [stmt step]) {
            [stmt reset];
            [stmt release];
            return NO;
        }
        [stmt release];
     }
    return YES;
    
}
//根据用户名获取用户的数据
+ (id)getUserWithName:(NSString *)name
{
    DataStatement *stmt = nil;
    NSString *selectString = [NSString stringWithFormat:@"select * from user where name = %@",name];
    const char *cString    = [selectString cStringUsingEncoding:NSUTF8StringEncoding];
    if (!stmt) 
    {
        stmt = [DataConnection statementWithQuery:cString];
        [stmt retain];
    }
    if (SQLITE_ROW != [stmt step]) {
        [stmt reset];
        return nil;
    }
    NSString *userName = [stmt getString:1];
    NSString *password = [stmt getString:2];
    NSDictionary *dic = [[NSDictionary alloc]initWithObjectsAndKeys:userName,@"name",password,@"password", nil];
    return [dic autorelease];
    
}

4.现在经过简单的封装，我们就能用数据库了

现在我们能在任何一个地方使用这个单例了，现在我们新建一个文件TestDatabase文件中的使用他

      

- (void)loadView
{
      [super loadView];
      [DataBasecenter createDatabase];
      NSDictionary *dic = [NSDictionary dicionaryValueAndkeys:@"name",@"name",@"1234567",@"password",@"1",@"flag"];
     [DataBaseCenter updateUser:dic];
      }

这样我们就能使用数据库了，其实挺简单的。

4.我在这个例子中只有一个数据库，所以用单例很方便，当然你想建立多个数据库，那也是很简单，一个数据库对应的一个数据库connection，一个connection可以对应多个statement，我这样的封装适合单个数据库，你也可以封装成工厂的方式，见前面我对xml解析的一文，也是很简单的。

总结：对sqlite的使用，我之前看到一个很好的例子，我将转过来作为一个单独的系列。

注：转载请注明出处。