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; @end
sqliteManager.m
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) {
char *errorMsg = sqlite3_errmsg(db);
NSString *errorStr = [NSString stringWithFormat:The database could not be opened: %@ [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;
char *query = [sql UTF8String];
sqlite3_prepare_v2(db,query,-1,&statement,NULL);
if (sqlite3_step(statement) == sqlITE_ERROR) {
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,0)">*/
- (NSError *)doUpdateQuery:(NSString *)sql withParams:(NSArray *)params {
NSError *openError = nil;
NSError *errorQuery = nil;
BIND the params!
int count =0;
for (id param in params ) {
count++;
if ([param isKindOfClass:[NSString class]] )
sqlite3_bind_text(statement,count,[param UTF8String],128)">1,sqlITE_TRANSIENT);
if ([param isKindOfClass:[NSNumber class]] ) {
if (!strcmp([param objCType],@encode(float)))
sqlite3_bind_double(statement,[param doubleValue]);
else int)))
sqlite3_bind_int(statement,[param intValue]);
if (!strcmp([param objCType],@encode(BOOL)))
sqlite3_bind_int(statement,255)">else
NSLog(unknown NSNumber");
}
if ([param isKindOfClass:[NSDate class]]) {
sqlite3_bind_double(statement,[param timeIntervalSince1970]);
}
if ([param isKindOfClass:[NSData class]] ) {
sqlite3_bind_blob(statement,[param bytes],[param length],sqlITE_STATIC);
}
}
return errorQuery;
}
- (NSInteger)getLastInsertRowID {
NSError *openError = nil;
sqlite3_int64 rowid = 0;
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;
char *query = [sql UTF8String];
int returnCode = sqlite3_prepare_v2(db,-if (returnCode == sqlITE_ERROR) {
char *errorMsg = sqlite3_errmsg(db);
NSError *errorQuery = [self createDBErrorWithDescription:[NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding]
andCode:kDBErrorQuery];
NSLog(while (sqlite3_step(statement) == sqlITE_ROW) {
int columns = sqlite3_column_count(statement);
NSMutableDictionary *result = [[NSMutableDictionary alloc] initWithCapacity:columns];
int i = 0; i<columns; i++) {
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];
case sqlITE_TEXT:
{
char *value = (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) {
void *blob = sqlite3_column_blob(statement,i);
if (blob != NULL) {
[result setObject:[NSData dataWithBytes:blob length:bytes] forKey:columnName];
}
}
case sqlITE_NULL:
[result setObject:[NSNull null] forKey:columnName];
break;
default:
{
char *)sqlite3_column_text(statement,255)">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,0)">*/
- (NSError *) closeDatabase {
NSError *error = nil;
if (db != nil) {
if (sqlite3_close(db) != sqlITE_OK){
char *errorMsg = sqlite3_errmsg(db);
NSString *errorStr = [NSString stringWithFormat:The database could not be closed: %@ [self createDBErrorWithDescription:errorStr andCode:kDBFailAtClose];
}
db = nil;
}
*
* 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 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
0; i<[rows count]; i++) {
NSDictionary *obj = [rows objectAtIndex:i];
get sql "create table" sentence
NSString *sql = [obj objectForKey:sql"];
[dump appendString:[NSString stringWithFormat:%@;\nsql]];
get table name
NSString *tableName = [obj objectForKey:name"];
get all table content
NSArray *tableContent = [self getRowsForQuery:[NSString stringWithFormat:SELECT * FROM %@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 %@ (loop through all keys (aka column names)
NSEnumerator *enumerator = [keys objectEnumerator];
id obj;
while (obj = [enumerator nextObject]) {
[dump appendString:[NSString stringWithFormat:%@,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:if it's a null
if ([obj isKindOfClass:[NSNull class]]){
[dump appendString:null,0)">"];
}
else is a string ;)
else{
[dump appendString:[NSString stringWithFormat:'%@',obj]];
}
}
delete last comma again
range.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;
}
以下是增删改查以及常规操作:
以下是增删改查以及常规操作:
打开数据库
创建表
NSError *error = nil; 创建表 id int 主键 user text password text */ error = [dbManager doQuery:CREATE TABLE IF NOT EXISTS users (id integer primary key autoincrement,password text);if (error != nil) { NSLog(Error: %@ 插入在表中插入一条记录 error = [dbManager doQuery:insert into users (user,password) values ('YouXian','19871220'); 查询查询记录 [[dbManager getRowsForQuery:SELECT * FROM users"] enumerateObjectsUsingBlock:^(id obj,NSUInteger idx,BOOL *stop) { NSLog( 修改修改一条记录 error = [dbManager doQuery:update users set user = 'YX',password = '1' where id = 1; 删除猜你在找的Sqlite相关文章
