SQLiteManager
https://github.com/misato/SQLiteManager4iOS
本人以前从事过嵌入式开发,后来转职为iOS开发,即使如此,也绝不想去碰C语言级别的面向过程的 sqlite3 来操作数据库,做高级语言开发还去折腾面向过程的东西,一个小小的nil没判断好就导致程序崩溃, 这就是 sqlite3 APIs 给你带来的问题,只有封装成面向对象的接口才有可能会去用.有一个封装得挺好的 FMDB 是不二的选择,但这个 SQLiteManager 属于轻量级封装,满足了最最基本的需求,但是,他是在开了ARC的情形下使用的,也就是说,没开ARC后会各种泄露......
上面的链接可以下载源码,我修改了源码,适用于我自己,提供如下:
SQLiteManager.h
#import <Foundation/Foundation.h>
#import "sqlite3.h"
enum errorCodes {
kDBNotExists,
kDBFailAtOpen,
kDBFailAtCreate,
kDBErrorQuery,
kDBFailAtClose
};
@interface SQLiteManager : NSObject {
sqlite3 *db; // The SQLite db reference
NSString *databaseName; // The database name
}
- (id)initWithDatabaseNamed:(NSString *)name;
// SQLite Operations
- (NSError *) openDatabase;
- (NSError *) doQuery:(NSString *)sql;
- (NSError *)doUpdateQuery:(NSString *)sql withParams:(NSArray *)params;
- (NSArray *) getRowsForQuery:(NSString *)sql;
- (NSError *) closeDatabase;
- (NSInteger)getLastInsertRowID;
- (NSString *)getDatabaseDump;
@endSQLiteManager.m
#import "SQLiteManager.h"
#define FOLDER_PATH @"/Library/Caches/YOU_FOLDER_NAME"
// Private methods
@interface SQLiteManager (Private)
- (NSString *)getDatabasePath;
- (NSError *)createDBErrorWithDescription:(NSString*)description andCode:(int)code;
@end
@implementation SQLiteManager
#pragma mark Init & Dealloc
/**
* Init method.
* Use this method to initialise the object, instead of just "init".
*
* @param name the name of the database to manage.
*
* @return the SQLiteManager object initialised.
*/
- (id)initWithDatabaseNamed:(NSString *)name; {
self = [super init];
if (self != nil) {
databaseName = [[NSString alloc] initWithString:name];
db = nil;
}
return self;
}
#pragma mark SQLite Operations
/**
* Open or create a SQLite3 database.
*
* If the db exists, then is opened and ready to use. If not exists then is created and opened.
*
* @return nil if everything was ok, an NSError in other case.
*
*/
- (NSError *) openDatabase {
NSError *error = nil;
NSString *databasePath = [self getDatabasePath];
NSLog(@"%@", databasePath);
const char *dbpath = [databasePath UTF8String];
int result = sqlite3_open(dbpath, &db);
if (result != SQLITE_OK) {
const char *errorMsg = sqlite3_errmsg(db);
NSString *errorStr = [NSString stringWithFormat:@"The database could not be opened: %@",[NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding]];
error = [self createDBErrorWithDescription:errorStr andCode:kDBFailAtOpen];
}
return error;
}
/**
* Does an SQL query.
*
* You should use this method for everything but SELECT statements.
*
* @param sql the sql statement.
*
* @return nil if everything was ok, NSError in other case.
*/
- (NSError *)doQuery:(NSString *)sql {
NSError *openError = nil;
NSError *errorQuery = nil;
//Check if database is open and ready.
if (db == nil) {
openError = [self openDatabase];
}
if (openError == nil) {
sqlite3_stmt *statement;
const char *query = [sql UTF8String];
sqlite3_prepare_v2(db, query, -1, &statement, NULL);
if (sqlite3_step(statement) == SQLITE_ERROR) {
const char *errorMsg = sqlite3_errmsg(db);
errorQuery = [self createDBErrorWithDescription:[NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding]
andCode:kDBErrorQuery];
}
sqlite3_finalize(statement);
errorQuery = [self closeDatabase];
}
else {
errorQuery = openError;
}
return errorQuery;
}
/**
* Does an SQL parameterized query.
*
* You should use this method for parameterized INSERT or UPDATE statements.
*
* @param sql the sql statement using ? for params.
*
* @param params NSArray of params type (id), in CORRECT order please.
*
* @return nil if everything was ok, NSError in other case.
*/
- (NSError *)doUpdateQuery:(NSString *)sql withParams:(NSArray *)params {
NSError *openError = nil;
NSError *errorQuery = nil;
//Check if database is open and ready.
if (db == nil) {
openError = [self openDatabase];
}
if (openError == nil) {
sqlite3_stmt *statement;
const char *query = [sql UTF8String];
sqlite3_prepare_v2(db, query, -1, &statement, NULL);
//BIND the params!
int count =0;
for (id param in params ) {
count++;
if ([param isKindOfClass:[NSString class]] )
sqlite3_bind_text(statement, count, [param UTF8String], -1, SQLITE_TRANSIENT);
if ([param isKindOfClass:[NSNumber class]] ) {
if (!strcmp([param objCType], @encode(float)))
sqlite3_bind_double(statement, count, [param doubleValue]);
else if (!strcmp([param objCType], @encode(int)))
sqlite3_bind_int(statement, count, [param intValue]);
else if (!strcmp([param objCType], @encode(BOOL)))
sqlite3_bind_int(statement, count, [param intValue]);
else
NSLog(@"unknown NSNumber");
}
if ([param isKindOfClass:[NSDate class]]) {
sqlite3_bind_double(statement, count, [param timeIntervalSince1970]);
}
if ([param isKindOfClass:[NSData class]] ) {
sqlite3_bind_blob(statement, count, [param bytes], [param length], SQLITE_STATIC);
}
}
if (sqlite3_step(statement) == SQLITE_ERROR) {
const char *errorMsg = sqlite3_errmsg(db);
errorQuery = [self createDBErrorWithDescription:[NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding]
andCode:kDBErrorQuery];
}
sqlite3_finalize(statement);
errorQuery = [self closeDatabase];
}
else {
errorQuery = openError;
}
return errorQuery;
}
- (NSInteger)getLastInsertRowID {
NSError *openError = nil;
sqlite3_int64 rowid = 0;
//Check if database is open and ready.
if (db == nil) {
openError = [self openDatabase];
}
if (openError == nil) {
rowid = sqlite3_last_insert_rowid(db);
}
return (NSInteger)rowid;
}
/**
* Does a SELECT query and gets the info from the db.
*
* The return array contains an NSDictionary for row, made as: key=columName value= columnValue.
*
* For example, if we have a table named "users" containing:
* name | pass
* -------------
* admin| 1234
* pepe | 5678
*
* it will return an array with 2 objects:
* resultingArray[0] = name=admin, pass=1234;
* resultingArray[1] = name=pepe, pass=5678;
*
* So to get the admin password:
* [[resultingArray objectAtIndex:0] objectForKey:@"pass"];
*
* @param sql the sql query (remember to use only a SELECT statement!).
*
* @return an array containing the rows fetched.
*/
- (NSArray *)getRowsForQuery:(NSString *)sql {
NSMutableArray *resultsArray = [[NSMutableArray alloc] initWithCapacity:1];
if (db == nil) {
[self openDatabase];
}
sqlite3_stmt *statement;
const char *query = [sql UTF8String];
int returnCode = sqlite3_prepare_v2(db, query, -1, &statement, NULL);
if (returnCode == SQLITE_ERROR) {
const char *errorMsg = sqlite3_errmsg(db);
NSError *errorQuery = [self createDBErrorWithDescription:[NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding]
andCode:kDBErrorQuery];
NSLog(@"%@", errorQuery);
}
while (sqlite3_step(statement) == SQLITE_ROW) {
int columns = sqlite3_column_count(statement);
NSMutableDictionary *result = [[NSMutableDictionary alloc] initWithCapacity:columns];
for (int i = 0; i<columns; i++) {
const char *name = sqlite3_column_name(statement, i);
NSString *columnName = [NSString stringWithCString:name encoding:NSUTF8StringEncoding];
int type = sqlite3_column_type(statement, i);
switch (type) {
case SQLITE_INTEGER:
{
int value = sqlite3_column_int(statement, i);
[result setObject:[NSNumber numberWithInt:value] forKey:columnName];
break;
}
case SQLITE_FLOAT:
{
float value = sqlite3_column_double(statement, i);
[result setObject:[NSNumber numberWithFloat:value] forKey:columnName];
break;
}
case SQLITE_TEXT:
{
const char *value = (const char*)sqlite3_column_text(statement, i);
[result setObject:[NSString stringWithCString:value encoding:NSUTF8StringEncoding] forKey:columnName];
break;
}
case SQLITE_BLOB:
{
int bytes = sqlite3_column_bytes(statement, i);
if (bytes > 0) {
const void *blob = sqlite3_column_blob(statement, i);
if (blob != NULL) {
[result setObject:[NSData dataWithBytes:blob length:bytes] forKey:columnName];
}
}
break;
}
case SQLITE_NULL:
[result setObject:[NSNull null] forKey:columnName];
break;
default:
{
const char *value = (const char *)sqlite3_column_text(statement, i);
[result setObject:[NSString stringWithCString:value encoding:NSUTF8StringEncoding] forKey:columnName];
break;
}
} //end switch
} //end for
[resultsArray addObject:result];
} //end while
sqlite3_finalize(statement);
[self closeDatabase];
return resultsArray;
}
/**
* Closes the database.
*
* @return nil if everything was ok, NSError in other case.
*/
- (NSError *) closeDatabase {
NSError *error = nil;
if (db != nil) {
if (sqlite3_close(db) != SQLITE_OK){
const char *errorMsg = sqlite3_errmsg(db);
NSString *errorStr = [NSString stringWithFormat:@"The database could not be closed: %@",[NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding]];
error = [self createDBErrorWithDescription:errorStr andCode:kDBFailAtClose];
}
db = nil;
}
return error;
}
/**
* Creates an SQL dump of the database.
*
* This method could get a csv format dump with a few changes.
* But i prefer working with sql dumps ;)
*
* @return an NSString containing the dump.
*/
- (NSString *)getDatabaseDump {
NSMutableString *dump = [[NSMutableString alloc] initWithCapacity:256];
// info string ;) please do not remove it
[dump appendString:@";\n; Dump generated with SQLiteManager4iOS \n;\n; By Misato (2011)\n"];
[dump appendString:[NSString stringWithFormat:@"; database %@;\n", [databaseName lastPathComponent]]];
// first get all table information
NSArray *rows = [self getRowsForQuery:@"SELECT * FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';"];
// last sql query returns something like:
// {
// name = users;
// rootpage = 2;
// sql = "CREATE TABLE users (id integer primary key autoincrement, user text, password text)";
// "tbl_name" = users;
// type = table;
// }
//loop through all tables
for (int i = 0; i<[rows count]; i++) {
NSDictionary *obj = [rows objectAtIndex:i];
//get sql "create table" sentence
NSString *sql = [obj objectForKey:@"sql"];
[dump appendString:[NSString stringWithFormat:@"%@;\n",sql]];
//get table name
NSString *tableName = [obj objectForKey:@"name"];
//get all table content
NSArray *tableContent = [self getRowsForQuery:[NSString stringWithFormat:@"SELECT * FROM %@",tableName]];
for (int j = 0; j<[tableContent count]; j++) {
NSDictionary *item = [tableContent objectAtIndex:j];
//keys are column names
NSArray *keys = [item allKeys];
//values are column values
NSArray *values = [item allValues];
//start constructing insert statement for this item
[dump appendString:[NSString stringWithFormat:@"insert into %@ (",tableName]];
//loop through all keys (aka column names)
NSEnumerator *enumerator = [keys objectEnumerator];
id obj;
while (obj = [enumerator nextObject]) {
[dump appendString:[NSString stringWithFormat:@"%@,",obj]];
}
//delete last comma
NSRange range;
range.length = 1;
range.location = [dump length]-1;
[dump deleteCharactersInRange:range];
[dump appendString:@") values ("];
// loop through all values
// value types could be:
// NSNumber for integer and floats, NSNull for null or NSString for text.
enumerator = [values objectEnumerator];
while (obj = [enumerator nextObject]) {
//if it's a number (integer or float)
if ([obj isKindOfClass:[NSNumber class]]){
[dump appendString:[NSString stringWithFormat:@"%@,",[obj stringValue]]];
}
//if it's a null
else if ([obj isKindOfClass:[NSNull class]]){
[dump appendString:@"null,"];
}
//else is a string ;)
else{
[dump appendString:[NSString stringWithFormat:@"'%@',",obj]];
}
}
//delete last comma again
range.length = 1;
range.location = [dump length]-1;
[dump deleteCharactersInRange:range];
//finish our insert statement
[dump appendString:@");\n"];
}
}
return dump;
}
@end
#pragma mark -
@implementation SQLiteManager (Private)
/**
* Gets the database file path (in NSDocumentDirectory).
*
* @return the path to the db file.
*/
- (NSString *)getDatabasePath{
if([[NSFileManager defaultManager] fileExistsAtPath:databaseName]){
// Already Full Path
return databaseName;
} else {
// 判断文件夹是否存在,不存在则创建文件夹
NSString *docsDir = [NSHomeDirectory() stringByAppendingString:FOLDER_PATH];
if (SQLiteManager_fileOrFolderExistFromSandbox(docsDir) == NO) {
SQLiteManager_createFolderForSandbox(docsDir);
}
return [docsDir stringByAppendingPathComponent:databaseName];
}
}
#pragma mark 沙盒中创建文件夹
BOOL SQLiteManager_createFolderForSandbox(NSString *filePath)
{
return [[NSFileManager defaultManager] createDirectoryAtPath:filePath
withIntermediateDirectories:YES attributes:nil error:nil];
}
#pragma mark 文件或者文件夹是否存在
BOOL SQLiteManager_fileOrFolderExistFromSandbox(NSString *filePath)
{
return [[NSFileManager defaultManager] fileExistsAtPath:filePath isDirectory:NO];
}
/**
* Creates an NSError.
*
* @param description the description wich can be queried with [error localizedDescription];
* @param code the error code (code erors are defined as enum in the header file).
*
* @return the NSError just created.
*
*/
- (NSError *)createDBErrorWithDescription:(NSString*)description andCode:(int)code {
NSDictionary *userInfo = [[NSDictionary alloc] initWithObjectsAndKeys:description, NSLocalizedDescriptionKey, nil];
NSError *error = [NSError errorWithDomain:@"SQLite Error" code:code userInfo:userInfo];
return error;
}
@end
以下是增删改查以及常规操作:
打开数据库
//打开数据库
SQLiteManager *dbManager = [[SQLiteManager alloc] initWithDatabaseNamed:@"Y.X.db"];创建表
NSError *error = nil;
//创建表
/*
id int 主键
user text
password text
*/
error = [dbManager doQuery:@"CREATE TABLE IF NOT EXISTS users (id integer primary key autoincrement, user text, password text);"];
if (error != nil) {
NSLog(@"Error: %@",[error localizedDescription]);
}插入
//在表中插入一条记录
error = [dbManager doQuery:@"insert into users (user, password) values ('YouXian','19871220');"];
if (error != nil) {
NSLog(@"Error: %@",[error localizedDescription]);
}查询
//查询记录
[[dbManager getRowsForQuery:@"SELECT * FROM users"] enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
NSLog(@"%@", obj);
}];修改
//修改一条记录
error = [dbManager doQuery:@"update users set user = 'YX',password = '1' where id = 1;"];
if (error != nil) {
NSLog(@"Error: %@",[error localizedDescription]);
}删除
//删除一条记录
error = [dbManager doQuery:@"delete from users where id = 1;"];
if (error != nil) {
NSLog(@"Error: %@",[error localizedDescription]);
}心得:
如果觉得自己想去捣鼓 C 语言级别的 API 不怕崩溃,可以去试试,对于我而言,不是我对其不感兴趣不愿意研究他,只是,在对性能没有要求的前提下花精力去造车轮子完全没有必要,真心话.
