一段包括数据库创建、升级、增删查改iOS代码

jopen 10年前

里面的那些类不必细究,主要是数据库的代码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..\n",__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\n",__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\n",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\n",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\n",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\n",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\n",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\n",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\n",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\n",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\n",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\n",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\n",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\n",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\n",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