iOS开发中的SQLite知识总结

iOS开发中的SQLite知识总结

更详细的数据库SQlite介绍：  http://mobile.51cto.com/iphone-388476.htm

                                                http://www.w3cschool.cc/sqlite/sqlite-group-by.html

 /Users/zhaochao/Library/Application Support/iPhone Simulator/7.1/Applications/07D17328-B63C-4D87-9B6C-03AA5CD681EA/Documents/zhaochao.sqlite， 就是 NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",name]; 这个目录。文件可以直接用SQLiteManager软件打开，也可以在firefox 浏览器 中安装sqlitemanager插件打开，如下图 

3.BaseDB.m

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

// //  BaseDB.m //  SqliteDemo // //  Created by 赵超 on 14-8-26. //  Copyright (c) 2014年 赵超. All rights reserved. //   # import "BaseDB.h" @implementation BaseDB   /*   *  获取沙盒目录   *  name:追加的目录aa   *   */ -(NSString*) DataBaseName:(NSString *) name {       NSString *fileName=[NSHomeDirectory() stringByAppendingFormat:@ "/Documents/%@" ,name];      return fileName; } /**   *  选择数据   *  sql:查询的SQL语句   *  params:查询SQL语句中的参数   *  dataName:查询数据库名称   */    -(NSMutableArray*) selectSql:(NSString *)sql parmas:(NSArray *)params dataBaseName:(NSString *)dataName{            sqlite3 *sqlite=nil;      sqlite3_stmt *stmt=nil;      //打开数据库      NSString *fileName=[self DataBaseName:dataName];      int result= sqlite3_open([fileName UTF8String], &sqlite);      if (result!=SQLITE_OK) {          NSLog(@ "打开失败" );          return nil;      }      const char * sqlCh=[sql UTF8String];      //编译SQL语句      sqlite3_prepare_v2(sqlite, sqlCh, - 1 , &stmt, NULL);      //绑定参数      for ( int i= 0 ; i<params.count; i++)= "" {= "" nsstring= "" *param= "[params" objectatindex:i];= "" sqlite3_bind_text(stmt,= "" i+ 1 ,= "" [param= "" utf8string],= "" - 1 ,= "" null );= "" }= "" 执行查询语句= "" result= "sqlite3_step(stmt);" nsmutablearray= "" *resultdata= "[NSMutableArray" array];= "" 遍历结果= "" while = "" (result= "=SQLITE_ROW)" nsmutabledictionary= "" *resultrow= "[NSMutableDictionary" dictionary];= "" 获取字段个数= "" int = "" col_count= "sqlite3_column_count(stmt);" for = "" ( int = "" i= "0;" i<col_count;= "" 获取字段名称= "" const = "" char *columname= "sqlite3_column_name(stmt,i);" 获取字段值= "" char *= "" columvalue= "(char*)" sqlite3_column_text(stmt,= "" i);= "" *columkeystr= "[NSString" stringwithcstring:columname= "" encoding:nsutf8stringencoding];= "" *columvaluestr= "[NSString" stringwithcstring:columvalue= "" [resultrow= "" setobject:columvaluestr= "" forkey:columkeystr];= "" [resultdata= "" addobject:resultrow];= "" 关闭数据库句柄= "" sqlite3_finalize(stmt);= "" 关闭数据库= "" sqlite3_close(sqlite);= "" nslog(@ "查询完！" );= "" return = "" resultdata;= "" **= "" *= "" 执行sql语句，主要完成增加、修改、删除= "" sql:执行的sql语句= "" params:sql语句中的参数= "" dataname:数据库名称= "" -(bool)= "" execsql:(nsstring= "" *)sql= "" parmas:(nsarray= "" *)params= "" databasename:(nsstring= "" *)dataname{= "" sqlite3= "" *sqlite= "nil;" sqlite3_stmt= "" *stmt= "nil;" 打开数据库= "" *filename= "[self" databasename:dataname];= "" &sqlite);= "" if = "" (result!= "SQLITE_OK)" nslog(@ "打开失败" );= "" no;= "" sqlch= "[sql" utf8string];= "" 编译sql语句= "" sqlite3_prepare_v2(sqlite,= "" sqlch,= "" &stmt,= "" 绑定参数= "" i<params.count;= "" *parm= "[params" [parm= "" 执行sql= "" ||= "" nslog(@ "执行sql语句失败" );= "" nslog(@ "执行成功！" );= "" yes;= "" 创建一个表= "" -( void )createtable:(nsstring= "" } else {= "" error;= "" null ,= "" &error);= "" nslog(@ "创建失败" );= "" nslog(@ "%s" ,error);= "" ;= "" nslog(@ "创建成功" );= "" @end = "" <= "" pre= "" ><br> <h3> 4 .调用格式</h3>   <pre class = "brush:java;" >         BaseDB *db=[[BaseDB alloc] init];      //创建表      NSString *dbCreate=@ "create table zhaochao( username text primary key,userPasswd test)" ;      NSString *dbName=@ "zhaochao.sqlite" ;     // [db createTable:dbCreate dataBaseName:dbName];            //添加数据      NSString *insertTable=@ "insert into zhaochao (username,userPasswd) values (?,?)" ;      NSArray  *insertParmas=@[@ "acasdfaa" ,@ "bb" ];     // [db execSql:insertTable parmas:insertParmas dataBaseName:@"zhaochao.sqlite"];            //修改数据      NSString *updateTable=@ "update zhaochao set username=? where username=?" ;      NSArray  *updateParams=@[@ "admin" ,@ "zhaochao" ];     // [db execSql:updateTable parmas:updateParams dataBaseName:@"zhaochao.sqlite"];            //删除数据      NSString *deleteTable=@ "delete from zhaochao where username=?" ;      NSArray   *deleteParams=@[@ "aa" ];     // [db execSql:deleteTable parmas:deleteParams dataBaseName:@"zhaochao.sqlite"];            //查询数据      NSString *selectTable=@ "select username,userPasswd from zhaochao where userPasswd=?" ;      NSString *selectParam=@[@ "bb" ];      NSArray *result=[db selectSql:selectTable parmas:selectParam dataBaseName:@ "zhaochao.sqlite" ];      for ( int i= 0 ; i<result.count; i++)= "" {= "" nsmutabledictionary= "" *arr= "[result" objectatindex:i];= "" nslog(@ "%@" ,arr);= "" }= "" <= "" pre= "" ><br> <br>   <br>                        </result.count;></pre></params.count;>