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 = ;
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = ;
NSString *writableDBPath = ;
success = ;
;
if (success) return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[ resourcePath] stringByAppendingPathComponent:@"test.sqlite"];
success = ;
if (!success) {
NSAssert1(0, @"Failed to create writable database file with message '%@'.", );
}
}
#pragma mark 路径
- (void)getPath{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = ;
path = ;
}
#pragma mark 查询数据库
/************
sql：sql语句
col：sql语句需要操作的表的所有字段数
***********/
- (NSMutableArray *)selectData:(NSString *)sql columns:(int)col {
;
NSMutableArray *returndata = [ init];//所有记录
if (sqlite3_open(, &database) == SQLITE_OK) {
sqlite3_stmt *statement = nil;
if (sqlite3_prepare_v2(database, , -1, &statement, NULL) == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
NSMutableArray *row;//一条记录
row = [ init];
//NSLog(@"=====%@",statement);
for(int i=0; i<col; i++){
];
}
;
;
}//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 ;
}
#pragma mark 增，删，改数据库
/************
sql：sql语句
param：sql语句中?对应的值组成的数组
***********/
- (BOOL)dealData:(NSString *)sql paramarray:(NSArray *)param {
;
if (sqlite3_open(, &database) == SQLITE_OK) {
sqlite3_stmt *statement = nil;
int success = sqlite3_prepare_v2(database, , -1, &statement, NULL);
if (success != SQLITE_OK) {
NSLog(@"Error: failed to prepare");
return NO;
}
//绑定参数
for (int i=0; i<; i++) {
NSString *temp = ;
sqlite3_bind_text(statement, i+1, , -1, SQLITE_TRANSIENT);
;
}
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 = ;
sqlstring = ;
;
增
NSArray *paramarray = [ initWithObjects:@"Miles", @"28", @"69", nil];
NSString *sql = @"INSERT INTO t_test (name, age, score) VALUES (?, ?, ?)";
;
删
NSArray *paramarray = [ initWithObjects:@"2", nil];
NSString *sql = @"DELETE FROM t_test WHERE id=?";
;
改
NSArray *paramarray = [ initWithObjects:@"Miles", @"30", @"2", nil];
NSString *sql = @"UPDATE t_test SET name=?, age=? WHERE id=?";
;
查
NSString *sqls = ;
;
页:
[1]