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;

@end

DataBaseVC.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 if
	sqlite3_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];