【學習筆記】sqlite操作

#import "db.h"//db.h文件中需要#import "sqlite3.h",项目需要导入sqlite3库。在这个目录下面，/Xcode3.1.4/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS3.1.2.sdk/usr/lib/libsqlite3.0.dylib@implementation db@synthesize info;-(NSString*) paths{NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);NSString *documentDirectory = [paths objectAtIndex:0];return [documentDirectory stringByAppendingFormat:@"data.sqlite3"];//数据库文件地址，如果不存在会自动创建}-(int) create//创建表方法。sql语句部分和普通SQL语句几乎完全一样{int result = sqlite3_open([[self paths] UTF8String], &database);//需要打开数据库const char *sql = "create table if not exists info(id integer primary key, inf integer);";char *errorMsg;int success = sqlite3_exec(database, sql, NULL,NULL,&errorMsg);//执行操作。除了select外其他的都用sqlite3_exec，select用sqlite3_prepare_v2。sqlite3_close(database);//关闭数据库//sqlite3.m里面定义了很多状态码。每个代码块里面的success result都是接受的状态码。if(success == SQLITE_OK)//判断是否执行成功，true则成功，反之..也可以用result来查看数据库打开的时候是否成功。在测试的时候经常要用道这些状态码。完成测试之后可以不使用success之类的。return 22;else return 33;}//插入一条数据，下面部分就差不多了。查询会重新说-(int) insert{int result = sqlite3_open([[self paths] UTF8String], &database);const char *sql = "insert into info(id,inf) values((select MAX(id) from info)+1,11);";char *errorMsg;int success = sqlite3_exec(database, sql, NULL,NULL,&errorMsg);sqlite3_close(database);if(success == SQLITE_OK)return 22;else return 33;}-(int) update{int result = sqlite3_open([[self paths] UTF8String], &database);const char *sql = "update info set inf = 99 where id = 2;";char *errorMsg;int success = sqlite3_exec(database, sql, NULL,NULL,&errorMsg);sqlite3_close(database);if(success == SQLITE_OK)return 22;else return 33;}-(int) deletes{int result = sqlite3_open([[self paths] UTF8String], &database);const char *sql = "delete from info where id = 2;";char *errorMsg;int success = sqlite3_exec(database, sql, NULL,NULL,&errorMsg);sqlite3_close(database);if(success == SQLITE_OK)return 22;else return 33;}-(NSString*) selects//对于熟悉jdbc的朋友来说，这里的理解要容易的多，这里有点想jdbc{int result = sqlite3_open([[self paths] UTF8String], &database);char *sql = "SELECT * FROM info where id = 2";sqlite3_stmt *statement;int success = (sqlite3_prepare_v2(database, sql, -1, &statement, NULL));int ids;int inf;int signs = 0;//逐行取出检索出来的数据。0,1是一条纪录中的字段，例如我的表里有两个字段，id,inf，那么一条纪录中0位置是id，1位置是inf，如果在sql语句中进行了指定，则按照sql语句中的来，只是要从0开始。signs是我要用的数据，大家可忽略while (sqlite3_step(statement) == SQLITE_ROW) {ids = sqlite3_column_int(statement, 0);inf = sqlite3_column_int(statement, 1);signs = 1;}sqlite3_finalize(statement);sqlite3_close(database);if(success == SQLITE_OK)if(signs ==1)return [[NSString alloc] initWithFormat:@"%i==%i",ids,inf];else return @"there is no data!";else return [[NSString alloc] initWithFormat:@"%ierror%i",ids,inf];}@end有些时候，大家的sql并不是都想我的例子中的一样简单，可以有些里面还参杂了变量，那么我们一般的做法是NSString *str = [[NSString alloc] initWithFormat:@"%i",11];但是str是不在sqlite3中执行的。我们需要用char类型的。这个原因我好像忘记了....类型转换的例子：NSString *str = [[NSString alloc] initWithFormat: @"SELECT * FROM infos WHERE year = %i and month = %i and day = %i",year,month,day];char *sql = [str UTF8String];