iPhone开发之SQLite的使用
SQLite确实是个好东西，不需要引擎，啥程序都可以使用，特别在嵌入式开发中使用得特别多。
记得刚开始在iPhone中使用SQLite的时候，琢磨了几天，才完成增删改查，费了九牛二虎之力呀。
iPhone中使用SQLite其实也不算简单，链接数据库、执行SQL，都感觉挺复杂的。经过多番研究，将iPhone中SQLite的使用方法封装到一个类中了，增删改查使用起来都极其方便，已经在多个项目中使用了我封装的这个类，目前还没发现有啥bug。
DataBaseVC.h
#import <Foundation/Foundation.h>#import <sqlite3.h>#import <CoreLocation/CoreLocation.h>@interface DataBaseVC : NSObject { sqlite3 *database;NSString *path;}- (void)readyDatabse;- (void)getPath;- (NSMutableArray *)selectData:(NSString *)sql columns:(int)col;- (BOOL)dealData:(NSString *)sql paramarray:(NSArray *)param;@endDataBaseVC.m
#import "DataBaseVC.h"@implementation DataBaseVC#pragma mark 准备数据库- (void)readyDatabse { BOOL success; NSFileManager *fileManager = [NSFileManager defaultManager]; NSError *error; NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"test.sqlite"]; success = [fileManager fileExistsAtPath:writableDBPath];[self getPath]; if (success) return; // The writable database does not exist, so copy the default to the appropriate location. NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"test.sqlite"]; success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error]; if (!success) { NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]); }}#pragma mark 路径- (void)getPath{ NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; path = [documentsDirectory stringByAppendingPathComponent:@"test.sqlite"];}#pragma mark 查询数据库/************ sql：sql语句 col：sql语句需要操作的表的所有字段数 ***********/- (NSMutableArray *)selectData:(NSString *)sql columns:(int)col {[self readyDatabse];NSMutableArray *returndata = [[NSMutableArray alloc] init];//所有记录 if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) { sqlite3_stmt *statement = nil; if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK) { while (sqlite3_step(statement) == SQLITE_ROW) {NSMutableArray *row;//一条记录row = [[NSMutableArray alloc] init];//NSLog(@"=====%@",statement);for(int i=0; i<col; i++){[row addObject:[NSString stringWithFormat:@"%s", sqlite3_column_text(statement, i)]];}[returndata addObject:row];[row release]; }//end while }else {NSLog(@"Error: failed to prepare");return NO;}//end if//NSLog(@"returndata：%@",returndata);return returndata; sqlite3_finalize(statement); } else { sqlite3_close(database); NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database)); }//end ifsqlite3_close(database);return [returndata autorelease];}#pragma mark 增，删，改数据库/************ sql：sql语句 param：sql语句中?对应的值组成的数组 ***********/- (BOOL)dealData:(NSString *)sql paramarray:(NSArray *)param {[self readyDatabse]; if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) { sqlite3_stmt *statement = nil; int success = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);if (success != SQLITE_OK) {NSLog(@"Error: failed to prepare");return NO;}//绑定参数for (int i=0; i<[param count]; i++) {NSString *temp = [param objectAtIndex:i];sqlite3_bind_text(statement, i+1, [temp UTF8String], -1, SQLITE_TRANSIENT);[temp release];}success = sqlite3_step(statement); sqlite3_finalize(statement);if (success == SQLITE_ERROR) {NSLog(@"Error: failed to insert into the database");return NO;} }sqlite3_close(database);NSLog(@"dealData 成功");return TRUE;}@end在此类中将查询写成了一个方法，增、删、改写成了一个方法。
调用起来都灰常简单：
sqlparam = [NSMutableArray arrayWithObject:lineslastdatedate];sqlstring = [NSMutableString stringWithFormat:@"UPDATE configuration SET LinesLastUpdateDate=?"]; [self dealData:sqlstring paramarray:sqlparam]; 增 NSArray *paramarray = [[NSArray alloc] initWithObjects:@"Miles", @"28", @"69", nil]; NSString *sql = @"INSERT INTO t_test (name, age, score) VALUES (?, ?, ?)"; [self dealData:sql paramarray:paramarray]; 删 NSArray *paramarray = [[NSArray alloc] initWithObjects:@"2", nil]; NSString *sql = @"DELETE FROM t_test WHERE id=?"; [self dealData:sql paramarray:paramarray]; 改 NSArray *paramarray = [[NSArray alloc] initWithObjects:@"Miles", @"30", @"2", nil]; NSString *sql = @"UPDATE t_test SET name=?, age=? WHERE id=?"; [self dealData:sql paramarray:paramarray]; 查 NSString *sqls = [NSString stringWithFormat:@"SELECT * FROM t_test WHERE score>%i", 0]; [self selectData:sqls columns:4];