iOS中Sqlite轻量级数据库的使用
来源：互联网 发布：2016淘宝排名权重因素 编辑：程序博客网 时间：2024/04/30 03:03
Problem：
iOS的MVC模型中，将模型数据持久存储到iOS文件系统，通常有四种机制：属性列表、对象归档、iOS嵌入式关系数据库(SQLite3)、Apple提供的Core Data。属性列表和对象归档适合存取数据量不是很大的数据，在需要保存的数据比较多的时候，往往考虑使用数据库。SQLite3为iOS内嵌的一个轻量级的数据库，它提供了C实现的接口。为了更方便使用，你自己写一个封装了这些接口的类，如DBHelper，然后在其中定义自己的表、数据查询、插入、删除等等的接口。对于新手，那么该如何了解和开始使用SQLite3呢？这里或许能给你一些有意义的信息。
Solution：
(1) 了解SQLite，最好不过的就是去它的官方网站看详细的文档，这将是一个不错的起点。
http://www.sqlite.org/lang.html和http://www.sqlite.org/cintro.html
如果你认真把上面的文档读完了的话，那么相信你会对SQLite有了不错的理解，特别是了解了它所提供的接口。那么，你现在或许需要回忆一下以前所学过的数据库知识了，比如建表、查询、插入、删除等等的命令，当然这些命令在iOS的Sqlite使用中会略有差别。
(2) 《iOS 5基础教程》的数据持久化的那一章。
这本书不是一本很好的开发入门书籍，但是不能否认其中某些章节的内容还是不错的。如果是一个新手，一上来看这本书，或许会比较痛苦，它不太适合作为入门读得第一本书。但是如果能耐着心看上一两遍，其实发现这本书其实也还可以。而数据持久化的那一章，就有SQLite的简要介绍，可以提供一些例子给你参考。
看了这两个资料，相信你基本具备了编写代码使用SQLite数据库的能力了。
Example：
下面是自己摸索、实践并应用于项目中的代码示例。对照例子，或许能给你学习SQLite的使用带来更多的灵感。
//interface文件
// Created by fengtao.ft on 14-5-13.#import <Foundation/Foundation.h>@interface TestDBHelper : NSObject+ (TestDBHelper *)sharedInstance;- (BOOL)insertNewRecord:(NSString *)imgURL userId:(NSString *)userId email:(NSString *)email date:(NSString *)date;- (BOOL)deleteRecordWithUserId:(NSString *)userId email:(NSString *)email date:(NSString *)date;- (BOOL)deleteRecordWithUserId:(NSString *)userId;- (BOOL)deleteAllRecords;- (NSArray *)queryAllRecords;- (NSArray *)quereRecordWithUserId:(NSString *)userId;@end
// Created by fengtao.ft on 14-5-13.#import "TestDBHelper.h"#import <sqlite3.h>#define kTestDBName @"TestDB.sqlite"#define kImgUrl @"imgURL"#define kUserId @"userId"#define kEmail @"email"#define kDate @"date"@interface TestDBHelper (){ sqlite3 *testDB; dispatch_queue_t testDBQueue;}@end@implementation TestDBHelperstatic TestDBHelper *sharedManager;+ (TestDBHelper *) sharedInstance{ if (nil == sharedManager){ sharedManager = [[TestDBHelper alloc] init]; } return sharedManager;}- (NSString *)databasePath{ NSString *dbFolderPath = [NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES) objectAtIndex:0]; dbFolderPath = [dbFolderPath stringByAppendingPathComponent:@"DBFolder"]; NSFileManager *manager = [NSFileManager defaultManager]; BOOL isDir; if (![manager fileExistsAtPath:dbFolderPath isDirectory:&isDir]){ [manager createDirectoryAtPath:dbFolderPath withIntermediateDirectories:YES attributes:nil error:nil]; } dbFolderPath = [dbFolderPath stringByAppendingPathComponent:kTestDBName]; return dbFolderPath;}- (id)init{ if (self = [super init]){ testDBQueue = dispatch_queue_create("TestDBQueue", NULL); [self createDatabaseIfNotExist]; } return self;}- (void)createDatabaseIfNotExist{ //dispatch_async(testDBQueue, ^{ NSString *dbPath = [self databasePath]; int isOpen; isOpen = sqlite3_open([dbPath UTF8String], &testDB); if (isOpen != SQLITE_OK){ NSLog(@"Fail to open database"); sqlite3_close(testDB); }else{ const char *createSQL = "CREATE TABLE IF NOT EXISTS testInfoTable \ (imgURL TEXT, userId TEXT, email TEXT, date TEXT)"; char *errMsg; int isCreate = sqlite3_exec(testDB, createSQL, NULL, NULL, &errMsg); if (isCreate != SQLITE_OK){ NSLog(@"Fail to create table"); } sqlite3_close(testDB); } //}); }- (BOOL)insertNewRecord:(NSString *)imgURL userId:(NSString *)userId email:(NSString *)email date:(NSString *)date{ BOOL isInsert = NO; NSString *dbPath = [self databasePath]; if (sqlite3_open([dbPath UTF8String], &testDB) != SQLITE_OK){ sqlite3_close(testDB); }else{ const char *insertSQL = "INSERT INTO testInfoTable (imgURL, userId, email, date) VALUES (?,?,?,?)"; sqlite3_stmt *insertStmt; if(sqlite3_prepare_v2(testDB, insertSQL, -1, &insertStmt, NULL) == SQLITE_OK){ sqlite3_bind_text(insertStmt, 1, [imgURL UTF8String], -1, NULL); sqlite3_bind_text(insertStmt, 2, [userId UTF8String], -1, NULL); sqlite3_bind_text(insertStmt, 3, [email UTF8String], -1, NULL); sqlite3_bind_text(insertStmt, 4, [date UTF8String], -1, NULL); if(sqlite3_step(insertStmt) == SQLITE_DONE){ NSLog(@"Insert success"); isInsert = YES; } } sqlite3_finalize(insertStmt); sqlite3_close(testDB); } return isInsert;}- (BOOL)deleteRecordWithUserId:(NSString *)userId email:(NSString *)email date:(NSString *)date{ BOOL isDelete = NO; NSString *dbPath = [self databasePath]; if(sqlite3_open([dbPath UTF8String], &testDB) != SQLITE_OK){ sqlite3_close(testDB); }else{ const char *deleteSQL = "DELETE FROM testInfoTable WHERE userId = ? AND email = ? AND date = ?"; sqlite3_stmt *deleteStmt; if(sqlite3_prepare_v2(testDB, deleteSQL, -1, &deleteStmt, NULL) == SQLITE_OK){ sqlite3_bind_text(deleteStmt, 1, [userId UTF8String], -1, NULL); sqlite3_bind_text(deleteStmt, 2, [email UTF8String], -1, NULL); sqlite3_bind_text(deleteStmt, 3, [date UTF8String], -1, NULL); if(sqlite3_step(deleteStmt) == SQLITE_DONE){ NSLog(@"Delete sucess"); isDelete = YES; } } sqlite3_finalize(deleteStmt); sqlite3_close(testDB); } return isDelete;}- (BOOL)deleteRecordWithUserId:(NSString *)userId{ BOOL isDelete = NO; NSString *dbPath = [self databasePath]; if(sqlite3_open([dbPath UTF8String], &testDB) != SQLITE_OK){ sqlite3_close(testDB); }else{ const char *deleteSQL = "DELETE FROM TestInfoTable WHERE userId = ?"; sqlite3_stmt *deleteStmt; int isPrepare = sqlite3_prepare_v2(testDB, deleteSQL, -1, &deleteStmt, NULL); if( isPrepare == SQLITE_OK){ sqlite3_bind_text(deleteStmt, 1, [userId UTF8String], -1, NULL); if(sqlite3_step(deleteStmt) == SQLITE_DONE){ NSLog(@"Delete success"); isDelete = YES; } } sqlite3_finalize(deleteStmt); sqlite3_close(testDB); } return isDelete; }- (BOOL)deleteAllRecords{ BOOL isDelete = NO; NSString *dbPath = [self databasePath]; if(sqlite3_open([dbPath UTF8String], &testDB) != SQLITE_OK){ sqlite3_close(testDB); }else{ const char *deleteSQL = "DELETE FROM TestInfoTable"; sqlite3_stmt *deleteStmt; if(sqlite3_prepare_v2(testDB, deleteSQL, -1, &deleteStmt, NULL) == SQLITE_OK){ if(sqlite3_step(deleteStmt) == SQLITE_DONE){ NSLog(@"Delete all success"); isDelete = YES; } } sqlite3_finalize(deleteStmt); sqlite3_close(testDB); } return isDelete;}- (NSArray *)queryAllRecords{ NSString *dbPath = [self databasePath]; NSMutableArray *dataList = nil; if(sqlite3_open([dbPath UTF8String], &testDB) != SQLITE_OK){ sqlite3_close(testDB); }else{ const char *querySQL = "SELECT * FROM TestInfoTable"; sqlite3_stmt *queryStmt; if(sqlite3_prepare_v2(testDB, querySQL, -1, &queryStmt, NULL) == SQLITE_OK){ dataList = [NSMutableArray array]; while(sqlite3_step(queryStmt) == SQLITE_ROW){ const char *imgurlChar = (const char *)sqlite3_column_text(queryStmt, 0); const char *useridChar = (const char *)sqlite3_column_text(queryStmt, 1); const char *emailChar = (const char *)sqlite3_column_text(queryStmt, 2); const char *dateChar = (const char *)sqlite3_column_text(queryStmt, 3); NSMutableDictionary *data = [NSMutableDictionary dictionaryWithCapacity:4]; [data setObject:[NSString stringWithUTF8String:imgurlChar] forKey:kImgUrl]; [data setObject:[NSString stringWithUTF8String:useridChar] forKey:kUserId]; [data setObject:[NSString stringWithUTF8String:emailChar] forKey:kEmail]; [data setObject:[NSString stringWithUTF8String:dateChar] forKey:kDate]; [dataList addObject:data]; } } sqlite3_finalize(queryStmt); sqlite3_close(testDB); } return dataList;}- (NSArray *)quereRecordWithUserId:(NSString *)userId{ NSString *dbPath = [self databasePath]; NSMutableArray *dataList = nil; if(sqlite3_open([dbPath UTF8String], &testDB) != SQLITE_OK){ sqlite3_close(testDB); }else{ const char *querySQL = "SELECT * FROM TestInfoTable WHERE userId = ?"; sqlite3_stmt *queryStmt; if(sqlite3_prepare_v2(testDB, querySQL, -1, &queryStmt, NULL) == SQLITE_OK){ dataList = [NSMutableArray array]; sqlite3_bind_text(queryStmt, 1, [userId UTF8String], -1, NULL); while(sqlite3_step(queryStmt) == SQLITE_ROW){ const char *imgurlChar = (const char *)sqlite3_column_text(queryStmt, 0); const char *useridChar = (const char *)sqlite3_column_text(queryStmt, 1); const char *emailChar = (const char *)sqlite3_column_text(queryStmt, 2); const char *dateChar = (const char *)sqlite3_column_text(queryStmt, 3); NSMutableDictionary *data = [NSMutableDictionary dictionaryWithCapacity:4]; [data setObject:[NSString stringWithUTF8String:imgurlChar] forKey:kImgUrl]; [data setObject:[NSString stringWithUTF8String:useridChar] forKey:kUserId]; [data setObject:[NSString stringWithUTF8String:emailChar] forKey:kEmail]; [data setObject:[NSString stringWithUTF8String:dateChar] forKey:kDate]; [dataList addObject:data]; } } sqlite3_finalize(queryStmt); sqlite3_close(testDB); } return dataList;}@endDiscussion:
（一些需要注意的东西，待总结补上）
Open Source Demo：
(provide git link coming soon......)
0 0
- iOS中Sqlite轻量级数据库的使用
- 轻量级数据库sqlite的使用
- 3.1Android中，Sqlite轻量级数据库的使用
- 轻量级数据库 SQLite 的安装和使用
- 安卓中轻量级数据库SQLite的使用
- IOS中数据库SQLite的简单使用
- C# 使用sqlite 轻量级数据库
- C# 使用sqlite 轻量级数据库
- iOS中使用SQLite数据库
- c#中使用轻量级数据库sqlite开发总结
- iOS-数据库sqlite的使用
- iOS开发中sqlite数据库的基本使用
- C/C++下使用SQLite轻量级数据库
- C/C++下使用SQLite轻量级数据库
- iOS中SQLite的使用
- 一些轻量级的数据库：SQLite Perst Firebird
- python与轻量级数据库SQLite的运用
- android下的轻量级Sqlite创建数据库
- 设计模式学习之——六大设计原则之三：依赖倒置原则
- 几个介绍xss漏洞的地方
- hitTest:withEvent:方法流程
- 电脑台式一体机好不好？
- Java List 实现排序的简单方法
- iOS中Sqlite轻量级数据库的使用
- matlab中的ezsurf函数
- 秒杀多线程第三篇 原子操作 Interlocked系列函数
- 配置hbm的hibernate小demo（入门级小案例ajax+struts2.0+hibernate3.0）
- 纯CSS 图片在DIV中垂直居中的显示方法
- Tabcontrol中隐藏显示某个选项卡
- 多线程程序常见Bug剖析（下）
- wamp多网站配置for window 本地测试 单Ip
- html页面关闭事件
