分享一段ios数据库代码，包括对表的创建、升级、增删查改

 

分享一段ios数据库代码。包括创建、升级、增删查改。

里面的那些类不必细究，主要是数据库的代码100%可用。

数据库升级部分，使用switch，没有break，低版本一次向高版本修改。

//  DB.h
//iukey
#import <Foundation/Foundation.h>
#import "sqlite3.h"
#import "User.h"
#import "ChatInfo.h"
#import "DescInfo.h"
@interface DBHelper : NSObject{
    sqlite3* db;//数据库句柄
//   @public DBHelper *instance;
}

@property(nonatomic,assign)sqlite3* db;
- (BOOL)insertUserWithTUsersName:(NSString*)name account:(NSString*)account pwd:(NSString*)pwd;
- (NSMutableArray*)quary:(NSString*)str;//查询

- (NSString*)getFilePath;//获取数据库路径
- (BOOL)createDB;//创建数据库
- (BOOL)createTable:(NSString*) creteSql;//创建表
- (User*)getUserWithTUsersByAccount:(NSString* )account;
- (BOOL)updateUserPwdWithTUsers:(NSString*)pwd byAccount:(NSString*)account ;
//+ (DBHelper*) getDBhelperInstance;
- (BOOL)deleteItemWithTable:(NSString*)table_ ByKey:(NSString*)key_ ;
-(BOOL)insertChatRecordWithTChatRecordByChatInfo:(ChatInfo*)ci owner:(NSString *)owner;
- (int)getRecordCountWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner;
- (NSMutableArray*)getChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid fromIndex:(int)fromIndex count:(int)count owner:(NSString *)owner;
- (BOOL)deleteChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner;

-(NSMutableDictionary*)getRecordCountNotREadWithTChatRecord:(NSString*)toJid owner:(NSString *)owner;
-(BOOL)updateRecordCountNotReadWithChatRecord:(NSString *)fromJid;
-(NSMutableArray*)getRecordNotReadWithTChatRecordFromJid:(NSString*)fromJid owner:(NSString *)owner;
//history
-(BOOL)insertHistoryRecordWithTHistoryRecordByDescInfo:(DescInfo*)di account:(NSString*)account routerjid:(NSString *)routerjid;
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByAccount:(NSString* )account routerjid:(NSString *)routerjid;
- (BOOL)deleteHistoryWithTHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid;
- (BOOL)deleteHistoryWithTHistoryRecordById:(int)c_id account:(NSString* )account routerjid:(NSString *)routerjid;
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByUDN:(NSString* )UDN withAccount:(NSString* )account routerjid:(NSString *)routerjid;
-(BOOL)updateHistoryCountNotReadWithHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid;
-(int)getHistoryCountNotReadWithHistoryRecordByAccount:(NSString *)account routerjid:(NSString *)routerjid;

@end

//  DB.m
//iukey
#import "DBHelper.h"
#import "YHConfig.h"
#import "DescInfo.h"
#import "FromJid.h"
// tid ----table index id
@implementation DBHelper

static  NSString *createTB_user=@"create table if not exists t_users (c_account text primary key ,c_name text,c_pwd text)";

/*
 info_ key-value
 db_version --1
 ...
 */
static  NSString *createTB_info=@"create table if not exists t_info (c_key text primary key ,c_value text)";
/*
 c_time 存储1970秒数
 */
static  NSString *createTB_chat_record=@"create table if not exists t_chat_record (c_id integer primary key autoincrement,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text,c_has_read integer)";



static NSString *createTB_history_record=@"create table if not exists t_history_record (c_id integer primary key autoincrement,c_deviceType text,c_UDN text,c_friendlyName text,c_history_time integer,c_desc text)";

@synthesize db;

- (id)init{
    self = [super init];    
    int dbVersion =0;
    //检查是否存在数据库文件
    if (![self isExistDB]) {
        //不存在，则创建
         [self createDB];
    }else {
        //若存在，检测数据库版本，则进行升级，

        char* info=NULL;
        [self getDBInfoValueWithKey:"db_version" value:&info];
        if(info == NULL){
            return self;
        }
        dbVersion= atoi(info);
        free (info);
        
        
    }
    //升级数据库。若第一次创建，则从0开始升级。顺序升级，因此不可以有break
    switch (dbVersion) {
        case 0:
            //第一次，新建并初始化各表
            [self createTable:createTB_user];
            //记录版本
            [self createTable:createTB_info];
            [self setDBInfoValueWithKey:"db_version" value:"1"];            
            [self createTable:createTB_chat_record];
            [self createTable:createTB_history_record];            
        case 1:
            [self setDBInfoValueWithKey:"db_version" value:"2"];
        case 2:
        {
            NSString *modify=@"alter table t_history_record add column c_user text not null default ''";
            [self setDBInfoValueWithKey:"db_version" value:"3"];
            [self execSql:modify];
        }
        case 3:
        {
            NSString *modify=@"alter table t_chat_record add column c_owner text not null default ''";
            [self setDBInfoValueWithKey:"db_version" value:"4"];
            [self execSql:modify];
        }
        case 4:
        {
            NSString *modify=@"alter table t_history_record add column c_router text not null default ''";
            [self setDBInfoValueWithKey:"db_version" value:"5"];
            [self execSql:modify];
        }
        case 5:
        {
            NSString *modify=@"alter table t_history_record add column c_has_read integer not null default ''";
            [self setDBInfoValueWithKey:"db_version" value:"6"];
            [self execSql:modify];
        }
            //注：数据库升级时候，只需要一次添加case即可，同时更新<span style="font-family: Arial, Helvetica, sans-serif;">db_version值</span>
        /*
        case 3:
        {
         //先不加密
            //1、将db文件移至portrait，并重命名yunho.db->_yunho.png
            //2、得到所有的密码，使用base64存储
            //3、用户名输入时候能自动检测是否有匹配的密码并实时的显示
            
//            NSString *modify=@"alter table t_history_record add column c_user text not null default ''";
//            [self setDBInfoValueWithKey:"db_version" value:" 4"];
//            [self execSql:modify];
        }
         */
        default:
            break;
    }
    return self;
}


- (NSString*)getFilePath{//get db path
    NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask  , YES); 
    NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:[YHConfig DBName ]];
    return databaseFilePath ;
}

#pragma mark db manage
- (BOOL)createDB{
    int ret = sqlite3_open([[self getFilePath] UTF8String], &db);//打开数据库，数据库不存在则创建
    if (SQLITE_OK == ret) {//创建成功
        sqlite3_close(db);//关闭
        return YES;
    }else{
        return NO;//创建失败
    }
}
-(BOOL) isExistDB{
    NSFileManager* fm = [[[NSFileManager alloc] init]autorelease];
    return [fm fileExistsAtPath:[self getFilePath] ];
}
/*
 create table dictionary(ID integer primary key autoincrement,en nvarchar(64),cn nvarchar(128),comment nvarchar(256))
 */
- (BOOL)dropTableWithTableName:(NSString*) tableName{
    NSString* dropSql = [[NSString alloc] initWithFormat:@"delete table %@",tableName];
    return [self execSql:[dropSql autorelease]];
}
- (BOOL)createTable:(NSString*) creteSql{
    return [self execSql:creteSql];
   }
-(BOOL) execSql:(NSString*) creteSql{
    char* err;
    const char* sql = [creteSql UTF8String];//创建表语句
    if (sql==NULL) {
        return NO;
    }
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
        return NO;
    }
    
    if (SQLITE_OK == sqlite3_exec(db, sql, NULL, NULL, &err)) {//执行创建表语句成功
        sqlite3_close(db);
        return YES;
    }else{//创建表失败
        return NO;
    }

}
//"select * from dictionary where en like ? or cn like ? or comment like ?;";//查询语句
//TODO
- (NSMutableArray*)quary:(NSString *) querySql{
    
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
        return nil;
    }    
    const char* sql = [querySql UTF8String];//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
        //        sqlite3_bind_text(stmt, 1,[[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
        //        sqlite3_bind_text(stmt, 2, [[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
        //        sqlite3_bind_text(stmt, 3, [[NSString stringWithFormat:@"%%%@%%",str]UTF8String], -1, NULL);
    }else{
        return nil;
    }
    NSMutableArray* arr =[[NSMutableArray alloc]init];//存放查询结果
    while( SQLITE_ROW == sqlite3_step(stmt) ){//执行
        char* _en = (char*)sqlite3_column_text(stmt, 1);
        char* _cn = (char*)sqlite3_column_text(stmt, 2);
        char* _comment = (char*)sqlite3_column_text(stmt, 3);
        
        NSMutableDictionary* dict = [[NSMutableDictionary alloc]init];//单条纪录
        [dict setObject:[NSString stringWithCString:_en encoding:NSUTF8StringEncoding] forKey:@"kEN"];
        [dict setObject:[NSString stringWithCString:_cn encoding:NSUTF8StringEncoding] forKey:@"kCN"];
        [dict setObject:[NSString stringWithCString:_comment encoding:NSUTF8StringEncoding] forKey:@"kCOMMENT"];
        [arr addObject:dict];//插入到结果数组
        [dict release];
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return [arr autorelease];//返回查询结果数组
}


#pragma mark  table t_info manage
- (void)getDBInfoValueWithKey:(const char*)key value:(char**)value{
    //TODO
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        printf("%s:%d query error..
",__FUNCTION__,__LINE__);
        return ;
    }
    const char* sql = "select * from t_info where c_key =?";//查询语句
    sqlite3_stmt* stmt;
    
    
    int error = sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    if (error==SQLITE_OK) {//准备
        sqlite3_bind_text(stmt, 1,key, -1, NULL);
    }else{
        printf("%s:%d query error.. %d
",__FUNCTION__,__LINE__,error);
        return;
    }
    
    
    if( SQLITE_ROW == sqlite3_step(stmt) ){//执行
         char* v= (char*)sqlite3_column_text(stmt, 1);
        *value = strdup(v);
        
    }
    sqlite3_finalize(stmt);
    sqlite3_close(db);
}
- (BOOL)setDBInfoValueWithKey:(const char*)key value:(const char*)value {
    char* info=NULL;
    [self getDBInfoValueWithKey:key value:&info];
    if (info!= NULL) {
        //存在，则更新
        [self updateDBInfoValueWithKey:key value:value];
    }else{
        //不存在，插入
        [self insertDBInfoValueWithKey:key value:value];
        
    }
    free(info);
    return YES;
}

- (BOOL)insertDBInfoValueWithKey:(const char*)key value:(const char*)value{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
        return NO;
    }
    const char* sql = "insert into t_info(c_key,c_value) values(?,?);";
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, key, -1, NULL);//绑定参数
        sqlite3_bind_text(stmt, 2, value, -1, NULL);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
}


- (BOOL)updateDBInfoValueWithKey:(const char*)key value:(const char*)value{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){
        return NO;
    }
    const char* sql = "update t_info set c_value = ? where c_key = ?;";
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, value, -1, NULL);
        sqlite3_bind_text(stmt, 2, key, -1, NULL);
    }else{
        return NO;
    }
    ret = sqlite3_step(stmt);
    printf("ret:%d
",ret);
    if (SQLITE_DONE ==ret ) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
}



#pragma mark table "t_users" manage
- (User*)getUserWithTUsersByAccount:(NSString* )account{
    
    
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    
    const char* sql = "select * from t_users where c_account = ?";//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
        sqlite3_bind_text(stmt, 1,[account UTF8String], -1, NULL);
    }else{
        return nil;
    }
    User* user = nil;
    if( SQLITE_ROW == sqlite3_step(stmt) ){//执行
        user = [[[User alloc]init]autorelease];
        NSString *name=nil;
        NSString *pwd= nil;
        if (sqlite3_column_text(stmt, 0) != NULL) {
            name = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 0)];
            
        }
        if (sqlite3_column_text(stmt,2 ) != NULL) {
            pwd = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 2)];
        }
        
        
        user.name =name;
        user.account= account;
        user.pwd = pwd;
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return  user;
}


- (BOOL)insertUserWithTUsersName:(NSString*)name account:(NSString*)account pwd:(NSString*)pwd{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }
    const char* sql = "insert into t_users(c_name,c_account,c_pwd) values(?,?,?);";
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, [name UTF8String], -1, NULL);//绑定参数
        sqlite3_bind_text(stmt, 2, [account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3, [pwd UTF8String], -1, NULL);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
}

//根据account 修改用户 的name和pwd
- (BOOL)updateUserPwdWithTUsers:(NSString*)pwd byAccount:(NSString*)account {
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }
    const char* sql = "update t_users set c_pwd = ? where c_account = ?";
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, [pwd UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2, [account UTF8String], -1, NULL);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
}
#pragma mark table "t_chat_record" manage
-(BOOL)insertChatRecordWithTChatRecordByChatInfo:(ChatInfo*)ci owner:(NSString *)owner{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }
    //@"create table if not exists t_chat_record (c_id text primary key ,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text)";
    const char* sql = "insert into t_chat_record(c_from_jid,c_to_jid,c_chat_time,c_msg,c_has_read,c_owner) values(?,?,?,?,?,?);";
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, [[ci fromJid] UTF8String], -1, NULL);//绑定参数
        sqlite3_bind_text(stmt, 2, [[ci toJid] UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 3, (int)[ci.chatTime timeIntervalSince1970] );
        sqlite3_bind_text(stmt, 4, [ci.msg UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 5, [ci hasRead]);
        sqlite3_bind_text(stmt, 6, [owner UTF8String], -1, NULL);
        log4debug(@"%d",[ci hasRead]);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }

}
//update the count of chat record not read
-(BOOL)updateRecordCountNotReadWithChatRecord:(NSString *)fromJid
{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    const char* sql = "update t_chat_record set c_has_read = 1 where c_from_jid = ?";//修改语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
            sqlite3_bind_text(stmt, 1, [fromJid UTF8String], -1, NULL);
          
        }else{
            return nil;
        }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }

       
}

-(NSMutableArray*)getRecordNotReadWithTChatRecordFromJid:(NSString*)fromJid owner:(NSString *)owner{
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    const char* sql = "select c_chat_time,c_msg from t_chat_record where c_has_read = 0 and c_from_jid =? and c_owner =? order by c_chat_time asc";//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
       sqlite3_bind_text(stmt, 1,[fromJid UTF8String], -1, NULL);
       sqlite3_bind_text(stmt, 2,[owner UTF8String], -1, NULL);
    }else{
        return nil;
    }
    
    NSMutableArray* msgs=[[[NSMutableArray alloc]init]autorelease];
    while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
        NSString *msg=nil;
        int chatTime = 0;
        if (sqlite3_column_text(stmt, 1) != NULL) {
            msg = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 1)];
            
        }
        chatTime = sqlite3_column_int(stmt, 0);
        NSDate * showTime = [NSDate dateWithTimeIntervalSince1970:chatTime];
        [msgs addObject:showTime];
        [msgs addObject:msg];

    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return msgs ;
}

//get the count of the chat record not read
-(NSMutableDictionary*)getRecordCountNotREadWithTChatRecord:(NSString*)toJid owner:(NSString *)owner
{
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    const char* sql = "select c_from_jid, count(*) from t_chat_record where c_has_read = 0 and c_to_jid =? and c_owner =? group by c_from_jid ";//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
             sqlite3_bind_text(stmt, 1,[toJid UTF8String], -1, NULL);
             sqlite3_bind_text(stmt, 2,[owner UTF8String], -1, NULL);
    }else{
        return nil;
    }
    
    NSMutableDictionary* fis=[[[NSMutableDictionary alloc]init]autorelease];
    while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
       FromJid* fi  = [[[FromJid alloc]init]autorelease];
        NSString *fromJid=nil;
        int noReadCount = 0;
        
        
        if (sqlite3_column_text(stmt, 0) != NULL) {
            fromJid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 0)];
        }
        noReadCount = sqlite3_column_int(stmt, 1);
        
        fi.fromJid = fromJid;
        fi.noReadCount = noReadCount;
        [fis setObject:fi forKey:fi.fromJid];
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return fis ;

}

//get the count of the chat record
- (int)getRecordCountWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner
{
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    //static  NSString *createTB_chat_record=@"create table if not exists t_chat_record (c_id integer primary key autoincrement,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text)";
    //不区分from&to，因此两个条件查询
    const char* sql = "select count (*) from t_chat_record where ((c_from_jid = ? and c_to_jid=?) or  (c_from_jid = ? and c_to_jid=?)) and c_owner = ?";//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
        sqlite3_bind_text(stmt, 1,[oneJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2,[anotherJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3,[anotherJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 4,[oneJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 5,[owner UTF8String], -1, NULL);
    }else{
        return nil;
    }
    int count=0;
    if ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
        count=sqlite3_column_int(stmt, 0);
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return  count ;
}

- (NSMutableArray*)getChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid fromIndex:(int)fromIndex count:(int)count owner:(NSString *)owner
{
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    //static  NSString *createTB_chat_record=@"create table if not exists t_chat_record (c_id integer primary key autoincrement,c_from_jid text,c_to_jid text,c_chat_time integer,c_msg text)";
    //不区分from&to，因此两个条件查询  
    const char* sql = "select * from t_chat_record where ((c_from_jid = ? and c_to_jid=?) or  (c_from_jid = ? and c_to_jid=?)) and c_owner = ? order by c_chat_time asc limit ?,?  ";//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备 
        sqlite3_bind_text(stmt, 1,[oneJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2,[anotherJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3,[anotherJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 4,[oneJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 5,[owner UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 6, fromIndex);
        sqlite3_bind_int(stmt, 7, count);
    }else{
        return nil;
    }
    NSMutableArray *cis=[[[NSMutableArray alloc]initWithCapacity:3]autorelease];
    while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
        ChatInfo* ci  = [[[ChatInfo alloc]init]autorelease];
        NSString *fromJid=nil;
        NSString *toJid= nil;
        NSString *msg=nil;      
        int time = 0;
        if (sqlite3_column_text(stmt, 1) != NULL) {
            fromJid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 1)];
        }
        if (sqlite3_column_text(stmt,2 ) != NULL) {
            toJid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 2)];
        }
        if (sqlite3_column_text(stmt,3 ) != NULL) {
            time = sqlite3_column_int(stmt, 3);
        }
        if (sqlite3_column_text(stmt,4 ) != NULL) {
            msg = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 4)];
        }
        ci.fromJid=fromJid;
        ci.toJid=toJid;
        ci.chatTime=[NSDate dateWithTimeIntervalSince1970:time];
        ci.msg=msg;
        [cis addObject:ci];
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return  cis ;
}

- (BOOL)deleteChatInfoWithTChatRecordByoneJid:(NSString* )oneJid anotherJid:(NSString* )anotherJid owner:(NSString *)owner
{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }
    NSString* sql = [NSString stringWithFormat:@"delete from t_chat_record where ((c_from_jid = ? and c_to_jid=?) or  (c_from_jid = ? and c_to_jid=?)) and c_owner = ?"];
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1,[oneJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2,[anotherJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3,[anotherJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 4,[oneJid UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 5,[owner UTF8String], -1, NULL);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
    
}

#pragma mark table "t_history_record" manage
-(BOOL)insertHistoryRecordWithTHistoryRecordByDescInfo:(DescInfo*)di account:(NSString*)account routerjid:(NSString *)routerjid{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }
    //@"create table if not exists t_history_record (c_id text primary key ,c_deviceType text,c_UDN text,c_friendlyName text,c_history_time integer,c_desc text)";
    const char* sql = "insert into t_history_record(c_deviceType,c_UDN,c_friendlyName,c_history_time,c_desc,c_user,c_router,c_has_read) values(?,?,?,?,?,?,?,?);";
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, [[di deviceType] UTF8String], -1, NULL);//绑定参数
        sqlite3_bind_text(stmt, 2, [[di deviceUDN] UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3, [[di friendlyName] UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 4, (int)[di.time timeIntervalSince1970] );
        sqlite3_bind_text(stmt, 5, [di.deviceDesc UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 6, [account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 7, [routerjid UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 8, [di hasRead]);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
    
}
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByUDN:(NSString* )UDN withAccount:(NSString* )account routerjid:(NSString *)routerjid
{
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    const char* sql = "select * from t_history_record where c_UDN = ? and c_user =? and c_router =? order by c_history_time desc";//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
        sqlite3_bind_text(stmt, 1,[UDN UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2,[account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3,[routerjid UTF8String], -1, NULL);
    }else{
        return nil;
    }
    NSMutableArray *dis=[[[NSMutableArray alloc]initWithCapacity:5]autorelease];
    while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
        DescInfo* di  = [[[DescInfo alloc]init]autorelease];
        NSString* deviceType = nil;
        NSString* friendlyName= nil;
        NSString* deviceUDN= nil;
        NSString* deviceDesc= nil;
        NSDate* time= nil;
        int c_id = 0;
        if (sqlite3_column_text(stmt, 0) != NULL) {
            c_id = sqlite3_column_int(stmt, 0);
            
        }
        if (sqlite3_column_text(stmt, 1) != NULL) {
            deviceType = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 1)];
        }
        if (sqlite3_column_text(stmt,2 ) != NULL) {
            deviceUDN = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 2)];
        }
        if (sqlite3_column_text(stmt,3 ) != NULL) {
            friendlyName = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 3)];
        }
        if (sqlite3_column_text(stmt,4 ) != NULL) {
            int i  = sqlite3_column_int(stmt, 4);
            time = [NSDate dateWithTimeIntervalSince1970:i];
            //            time = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 4)];
        }
        if (sqlite3_column_text(stmt,5 ) != NULL) {
            deviceDesc = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 5)];
        }
        di.deviceType=deviceType;
        di.deviceUDN=deviceUDN;
        di.friendlyName=friendlyName;
        di.time = time;
        di.deviceDesc =deviceDesc;
        di.c_id = c_id;
        [dis addObject:di];
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return  dis ;


}
- (NSMutableArray*)getHistoryRecordWithTHistoryRecordByAccount:(NSString* )account routerjid:(NSString *)routerjid
{
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    const char* sql = "select * from t_history_record where c_user = ? and c_router = ? order by c_history_time desc";//查询语句
    sqlite3_stmt* stmt;
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, nil)==SQLITE_OK) {//准备
        sqlite3_bind_text(stmt, 1,[account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2,[routerjid UTF8String], -1, NULL);
    }else{
        return nil;
    }
    NSMutableArray *dis=[[[NSMutableArray alloc]initWithCapacity:5]autorelease];
    while ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
       DescInfo* di  = [[[DescInfo alloc]init]autorelease]; 
        NSString* deviceType = nil;
         NSString* friendlyName= nil;
          NSString* deviceUDN= nil;
         NSString* deviceDesc= nil;
        NSDate* time= nil;
        int c_id = 0;
        if (sqlite3_column_text(stmt, 0) != NULL) {
            c_id = sqlite3_column_int(stmt, 0);
            
        }
        if (sqlite3_column_text(stmt, 1) != NULL) {
            deviceType = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 1)];
        }
        if (sqlite3_column_text(stmt,2 ) != NULL) {
           deviceUDN = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 2)];
        }
        if (sqlite3_column_text(stmt,3 ) != NULL) {
            friendlyName = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 3)];
        }
        if (sqlite3_column_text(stmt,4 ) != NULL) {
            int i  = sqlite3_column_int(stmt, 4);
            time = [NSDate dateWithTimeIntervalSince1970:i];
//            time = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 4)];
        }
        if (sqlite3_column_text(stmt,5 ) != NULL) {
            deviceDesc = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 5)];
        }
        di.deviceType=deviceType;
        di.deviceUDN=deviceUDN;
        di.friendlyName=friendlyName;
        di.time = time;
        di.deviceDesc =deviceDesc;
        di.c_id = c_id;
        [dis addObject:di];
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return  dis ;


}
- (BOOL)deleteHistoryWithTHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid
{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }
    NSString* sql = [NSString stringWithFormat:@"delete from t_history_record where c_deviceType = ? and c_user = ? and c_router = ?"];
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, [deviceType UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2, [account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3, [routerjid UTF8String], -1, NULL);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
}
- (BOOL)deleteHistoryWithTHistoryRecordById:(int)c_id account:(NSString* )account routerjid:(NSString *)routerjid

{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }
    NSString* sql = [NSString stringWithFormat:@"delete from t_history_record where c_id = ? and c_user = ? and c_router = ?"];
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
//        sqlite3_bind_text(stmt, 1, [c_id UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 1, c_id);
        sqlite3_bind_text(stmt, 2, [account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3, [routerjid UTF8String], -1, NULL);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }

}

//update the count of history record not read
-(BOOL)updateHistoryCountNotReadWithHistoryRecordByDeviceType:(NSString *)deviceType account:(NSString* )account routerjid:(NSString *)routerjid
{
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    NSString * sql = [NSString stringWithFormat:@"update t_history_record set c_has_read = 1 where c_deviceType = ? and c_user = ? and c_router = ?"];//查询语句
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备
        sqlite3_bind_text(stmt, 1, [deviceType UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2, [account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3, [routerjid UTF8String], -1, NULL);
    }else{
        return nil;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
}

-(int)getHistoryCountNotReadWithHistoryRecordByAccount:(NSString *)account routerjid:(NSString *)routerjid
{
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String] , &db)){
        return nil;
    }
    NSString * sql = [NSString stringWithFormat:@"select count (*) from t_history_record where c_has_read = 0 and c_user = ? and c_router = ?"];//查询语句
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备
        sqlite3_bind_text(stmt, 1, [account UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 2, [routerjid UTF8String], -1, NULL);
    }else{
        return nil;
    }
    int count=0;
    if ( SQLITE_ROW == sqlite3_step(stmt) ){//执行
        count=sqlite3_column_int(stmt, 0);
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return  count ;
}

#pragma mark manage normal tables
- (BOOL)deleteItemWithTable:(NSString*)table_ ByKey:(NSString*)key_ {
    int ret = 0;
    if (SQLITE_OK != sqlite3_open([[self getFilePath] UTF8String], &db)){//打开数据库
        return NO;
    }

    NSString* sql= [NSString stringWithFormat:@"delete from  %@  where  c_account =  ?",table_]; 
    
    sqlite3_stmt* stmt;//
    int result =sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
    printf("%s
",sqlite3_errmsg(db));
    if (result==SQLITE_OK) {//准备语句
        sqlite3_bind_text(stmt, 1, [key_ UTF8String], -1, NULL);
    }else{
        return NO;
    }
    if (SQLITE_DONE == (ret = sqlite3_step(stmt))) {//执行查询
        sqlite3_finalize(stmt);
        sqlite3_close(db);
        return YES;
    }else{
        return NO;
    }
}







@end

转：http://blog.csdn.net/kafeidev/article/details/17028423