目录:
一、sqlite3常用函数
二、将sqlite3集成到项目,实现増删改查
三、封装DBManager
四、Demo
一、sqlite3常用函数及解释
(1)sqlite3_open:
用来创建和打开数据库文件,接收两个参数,第一个是数据库的名字,第二个是数据库的句柄。如果数据库文件不存在,将首先新建它,然后再打开它,否则只是打开它。
(2)sqlite3_prepare_v2:
使用格式化的字符串来获得sql准备语句(prepared statement),然后转化为可被SQLite3识别的执行语句。(实际上这个函数并不执行这个SQL语句)
(3)sqlite3_step:
这个函数执行上一个函数调用创建的准备语句,这个语句执行到结果的第一行可用的位置,再次调用sqlite3_setp(),会继续前进到结果的第二行。当执行插入、更新、删除操作时会被调用一次,当执行取回数据时可以执行多次。这个函数不能在sqlite3_preprare_v2之前调用。
(4)sqlite3_column_count:
返回表的列数
(5)sqlite3_column_text:
以text的格式返回列的内容(实际上是C的char*类型)。它接收两个参数,SQLite语句和列的索引。
(6)sqlite3_column_name:
返回列的名字,参数和上一个函数一样
(7)sqlite3_changes:
返回执行语句后受影响的行数
(8)sqlite3_last_insert_rowid:
返回最后插入的行的id
(9)sqlite3_errmsg:
返回SQLite错误描述
(10)sqlite3_finalize:
从内存删除之前sqlite3_prepare_v2函数创建的准备语句
(11)sqlite3_close:
关闭数据库连接,在结束任何数据库数据修改后调用,它将释放其存储的系统资源。
二、将sqlite3集成到项目,实现増删改查
#define DBNAME @"myDB.sqlite"
#define TABLENAME @"PERSONINFO"
#define NAME @"name"
@interface ViewController (){
sqlite3 *db;
}
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documents = [paths objectAtIndex:0];
NSString *database_path = [documents stringByAppendingPathComponent:DBNAME];
if (sqlite3_open([database_path UTF8String], &db)!= SQLITE_OK) {
sqlite3_close(db);
NSLog(@"打开数据库失败");
}
4. 新建一个sql语句操作数据库的函数
-(void)execSql:(NSString *)sql
{
char *err;
if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
sqlite3_close(db);
NSLog(@"数据库操作数据失败!");
}
}
5.创建数据表,表名为2中的宏定义PERSONINFO
NSString *sqlCreateTable = @"CREATE TABLE IF NOT EXISTS PERSONINFO(peopleInfoID integer primary key, firstname text, lastname text, age integer);";
[self execSql:sqlCreateTable];
6.插入一条名字为张三,年龄22岁的记录
NSString *sql = [NSString stringWithFormat:@"insert into peopleInfo values(null, '%@', '%@', %d)", @“张”,@“三”,22];
[self execSql:sql];
NSString *sqlQuery = @"SELECT * FROM PERSONINFO";
sqlite3_stmt * statement;
if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
char *firstName = (char*)sqlite3_column_text(statement, 1);
NSString *firstNameStr = [[NSString alloc]initWithUTF8String:firstName];
<pre name="code" class="objc"> char *lastName = (char*)sqlite3_column_text(statement, 2);
NSString *lastNameStr = [[NSString alloc]initWithUTF8String:lastName];
int age = sqlite3_column_int(statement, 3); NSLog(@"firstName:%@ lastName:%@ age:%d",
firstNameStr
,lastNameStr, age);
} } sqlite3_close(db);
三、封装DBManager
#import <Foundation/Foundation.h>
@interface DBManager : NSObject
@property (nonatomic, strong) NSMutableArray *arrColumnNames;//存储列名
@property (nonatomic) int affectedRows;//记录被改变的行数
@property (nonatomic) long long lastInsertedRowID;//记录最后插入行的id
-(NSArray *)loadDataFromDB:(NSString *)query;//查询
-(void)executeQuery:(NSString *)query;//插入、更新、删除
-(instancetype)initWithDatabaseFilename:(NSString *)dbFilename;//初始化方法
-(BOOL)createTableWithSql:(const char *)sql_stmt;//新建表
@end
(3).m文件
#import "DBManager.h"
#import <sqlite3.h> //导入sqlite3的头文件
@interface DBManager()
@property (nonatomic, strong) NSString *documentsDirectory;
@property (nonatomic, strong) NSString *databaseFilename;
@property (nonatomic, strong) NSMutableArray *arrResults;
@end
@implementation DBManager
-(instancetype)initWithDatabaseFilename:(NSString *)dbFilename{
self = [super init];
if (self) {
//获得存储路径
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
self.documentsDirectory = [paths objectAtIndex:0];
//数据库名
self.databaseFilename = dbFilename;
}
return self;
}
#pragma mark 建表
-(BOOL)createTableWithSql:(const char *)sql_stmt{
BOOL isSuccess = YES;
//检查数据库文件是否已经存在
NSString *destinationPath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];
NSLog(@"path:%@",destinationPath);
if (![[NSFileManager defaultManager] fileExistsAtPath:destinationPath]) {
sqlite3 *database = nil;
const char *dbpath = [destinationPath UTF8String];
if (sqlite3_open(dbpath, &database) == SQLITE_OK){
char *errMsg;
if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg)
!= SQLITE_OK)
{
isSuccess = NO;
NSLog(@"Failed to create table");
}
sqlite3_close(database);
}else{
isSuccess = NO;
NSLog(@"Failed to open/create table");
}
}
return isSuccess;
}
#pragma mark 执行sql语句
-(void)runQuery:(const char *)query isQueryExecutable:(BOOL)queryExecutable{
//创建一个sqlite3对象
sqlite3 *sqlite3Database;
//设置数据库路径
NSString *databasePath = [self.documentsDirectory stringByAppendingPathComponent:self.databaseFilename];
//初始化存储结果的array
if (self.arrResults != nil) {
[self.arrResults removeAllObjects];
self.arrResults = nil;
}
self.arrResults = [[NSMutableArray alloc] init];
//初始化存储列名的array
if (self.arrColumnNames != nil) {
[self.arrColumnNames removeAllObjects];
self.arrColumnNames = nil;
}
self.arrColumnNames = [[NSMutableArray alloc] init];
//打开数据库
BOOL openDatabaseResult = sqlite3_open([databasePath UTF8String], &sqlite3Database);
if(openDatabaseResult == SQLITE_OK) {
//声明一个sqlite3_stmt对象,存储查询结果
sqlite3_stmt *compiledStatement;
//将所有数据加载到内存
BOOL prepareStatementResult = sqlite3_prepare_v2(sqlite3Database, query, -1, &compiledStatement, NULL);
if(prepareStatementResult == SQLITE_OK) {
//是否是查询语句
if (!queryExecutable){
//用来保存每一行数据
NSMutableArray *arrDataRow;
//将结果一行行地加入到arrDataRow中
while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
//初始化arrDataRow
arrDataRow = [[NSMutableArray alloc] init];
//获得列数
int totalColumns = sqlite3_column_count(compiledStatement);
//读取和保存每一列数据
for (int i=0; i<totalColumns; i++){
//将数据转化为char
char *dbDataAsChars = (char *)sqlite3_column_text(compiledStatement, i);
//数据不为空则加到arrDataRow中
if (dbDataAsChars != NULL) {
//将char转化为string.
[arrDataRow addObject:[NSString stringWithUTF8String:dbDataAsChars]];
}
//保存列名(只保存一次)
if (self.arrColumnNames.count != totalColumns) {
dbDataAsChars = (char *)sqlite3_column_name(compiledStatement, i);
[self.arrColumnNames addObject:[NSString stringWithUTF8String:dbDataAsChars]];
}
}
//如果不为空,将每行的数据保存到
if (arrDataRow.count > 0) {
[self.arrResults addObject:arrDataRow];
}
}
}
else {
//插入、更新、删除等操作
if (sqlite3_step(compiledStatement) == SQLITE_DONE) {
// 被改变了多少行
self.affectedRows = sqlite3_changes(sqlite3Database);
// 最后插入的行id
self.lastInsertedRowID = sqlite3_last_insert_rowid(sqlite3Database);
}
else {
// 插入、更新、删除等错误
NSLog(@"DB Error: %s", sqlite3_errmsg(sqlite3Database));
}
}
}
else {
//打开错误
NSLog(@"%s", sqlite3_errmsg(sqlite3Database));
}
// 释放内存
sqlite3_finalize(compiledStatement);
}
// 关闭数据库
sqlite3_close(sqlite3Database);
}
-(NSArray *)loadDataFromDB:(NSString *)query{
// 执行查询
[self runQuery:[query UTF8String] isQueryExecutable:NO];
// 返回查询结果
return (NSArray *)self.arrResults;
}
-(void)executeQuery:(NSString *)query{
// 执行插入、更新、删除等
[self runQuery:[query UTF8String] isQueryExecutable:YES];
}
@end
四、使用三中DBManager完成一个Demo,实现增删改查,效果如下图,源代码:http://download.csdn.net/detail/dolacmeng/8816001