IOS(数据库的应用)

在iPhone的开发过程中常常会用到数据库,而SQLite3是iPhone中支持的数据库。下面简单介绍一下iPhone中SQLite3数据库的用法:

SQLite3简介

SQLite3是一个轻量级的数据库,完全使用C语言编写,使用简单方便。它是一个嵌入到程序进程的数据库,和其他一些数据库(MySQL,MS SQL)不同,它没有独立的进程。

1、打开数据库

首先声明一个数据库变量

sqlite3 *db = NULL;

每一个SQLite3都是一个文件,打开一个数据库只需要调用一次sqlite3_open函数:

SQLITE_API int sqlite3_open(

const char *filename, /* Database filename (UTF-8) */

sqlite3 **ppDb /* OUT: SQLite db handle */

);

filename是数据库的路径,ppDb是指向返回的sqlite3数据库,返回SQLITE3_OK则表示正常。比如我们打开Document目录下的“Test.db”数据库:

int result = sqlite3_open([dbPath UTF8String], &db);

NSAssert(result==SQLITE_OK, @”Can’t open database %@”, dbPath);

注意:如果dbPath路径下的数据库不存在,那么打开这个数据库将会创建一个空的数据库。

2、执行SQL语句

SQLite3有3种执行SQL语句的方法:

  • 通过sqlite3_exec()函数,适用于执行(非查询)语句:如CREATE TABLE、INSERT,REPLACE,DELETE等。

SQLITE_API int sqlite3_exec(

sqlite3*, /* An open database */

const char *sql, /* SQL to be evaluated */

int (*callback)(void*,int,char**,char**), /* Callback function */

void *, /* 1st argument to callback */

char **errmsg /* Error msg written here */

);

比如我们要创建一个表示个人(person)的表,就可以使用sqlite3_exec()函数。person表中包括名字(name)、性别(gender)、年龄(age)和生日(birthday)这几个字段。代码如下:

char *errmsg = NULL;

result = sqlite3_exec(db, “CREATE TABLE person (”

“name TEXT,”

“gender INTEGER,”

“age INTEGER,”

“birthday DATE)”, NULL, NULL, &errmsg);

NSAssert(result==SQLITE_OK, @”Can’t CREATE TABLE: %s”, errmsg);


  • 通过sqlite3_get_table()函数,适用于查询语句SELECT。由于此函数返回一个字符串数组,因此对于要求结果为二进制数据(如图像数据)的不能用此函数返回。

SQLITE_API int sqlite3_get_table(

sqlite3 *db, /* An open database */

const char *zSql, /* SQL to be evaluated */

char ***pazResult, /* Results of the query */

int *pnRow, /* Number of result rows written here */

int *pnColumn, /* Number of result columns written here */

char **pzErrmsg /* Error msg written here */

);

例如我们要查询person中的所有数据:

char **table = NULL;

int row,col;

result = sqlite3_get_table(db, “SELECT name,age,birthday from person”, &table, &row, &col, &errmsg);

NSAssert(result==SQLITE_OK, @”SQL ERROR: %s”, errmsg);

那么字符串数组table中就包含所有的表信息,本例中每行3个元素(name、age和birthday),table第一行元素是字段名字,如table[0]是”name”,table[1]是”age”,table[2]是”birthday”。table的第二行才是实际的数据,如table[3]是”张三”,table[4]是”24″,table[5]是”1981-10-21 12:00:01″。

  • 通过sqlite3_prepare()、sqlite3_step()、sqlite3_bind_xxx()以及sqlite3_finalize()等一系列函数,可以逐行获取数据,并可以获取二进制数据。

sqlite3_prepare()用于编译一个SQL语句,返回一个sqlite3_stmt结构指针。sqlite3_stmt结构指针用于sqlite3_step()、sqlite3_bind_xxx()以及sqlite3_finalize()等函数中。sqlite3_step()用于将当前记录指向下一个。sqlite3_bind_xxx()等一系列函数会获取当前记录中各个字段对应的值。最后调用一下sqlite3_finalize()用于释放一些资源。

 

下面是高级的应用

 

 

 

H文件

#import <Foundation/Foundation.h>

#import "sqlite3.h"

@interface DatabaseOperation : NSObject {

sqlite3 *m_sql;

NSString *m_dbName;

}

@property(nonatomic)sqlite3* m_sql;

@property(nonatomic,retain)NSString* m_dbName;

-(id)initWithDbName:(NSString*)dbname;

-(BOOL)openOrCreateDatabase:(NSString*)DbName;

-(BOOL)createTable:(NSString*)sqlCreateTable;

-(void)closeDatabase;

-(BOOL)InsertTable:(NSString*)sqlInsert;

-(BOOL)UpdataTable:(NSString*)sqlUpdata;

-(NSArray*)querryTable:(NSString*)sqlQuerry;

-(NSArray*)querryTableByCallBack:(NSString*)sqlQuerry;

@end

M文件

#import "DatabaseOperation.h"

@implementation DatabaseOperation

@synthesize m_sql;

@synthesize m_dbName;

- (id) initWithDbName:(NSString*)dbname

{

self = [super init];

if (self != nil) {

if ([self openOrCreateDatabase:dbname]) {

[self closeDatabase];

}

}

return self;

}

- (id) init

{

NSAssert(0,@"Never Use this.Please Call Use initWithDbName:(NSString*)");

return nil;

}

- (void) dealloc

{

self.m_sql = nil;

self.m_dbName =nil;

[super dealloc];

}

//-------------------创建数据库-------------------------

-(BOOL)openOrCreateDatabase:(NSString*)dbName

{

self.m_dbName = dbName;

NSArray *path =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);

NSString *documentsDirectory = [path objectAtIndex:0];

if(sqlite3_open([[documentsDirectorystringByAppendingPathComponent:dbName]UTF8String],&m_sql) !=SQLITE_OK)

{

NSLog(@"创建数据库失败");

return NO;

}

return YES;

}

//------------------创建表----------------------

-(BOOL)createTable:(NSString*)sqlCreateTable

{

if (![selfopenOrCreateDatabase:self.m_dbName]) {

return NO;

}

char *errorMsg;

if (sqlite3_exec (self.m_sql, [sqlCreateTable UTF8String],NULL,NULL, &errorMsg) != SQLITE_OK)

{

NSLog(@"创建数据表失败:%s",errorMsg);

return NO;

}

[selfcloseDatabase];

return YES;

}

//----------------------关闭数据库-----------------

-(void)closeDatabase

{

sqlite3_close(self.m_sql);

}

//------------------insert-------------------

-(BOOL)InsertTable:(NSString*)sqlInsert

{

if (![selfopenOrCreateDatabase:self.m_dbName]) {

return NO;

}

char* errorMsg = NULL;

if(sqlite3_exec(self.m_sql, [sqlInsertUTF8String],0,NULL, &errorMsg) ==SQLITE_OK)

{ [selfcloseDatabase];

returnYES;}

else {

printf("更新表失败:%s",errorMsg);

[selfcloseDatabase];

return NO;

}

return YES;

}

//--------------updata-------------

-(BOOL)UpdataTable:(NSString*)sqlUpdata{

if (![selfopenOrCreateDatabase:self.m_dbName]) {

return NO;

}

char *errorMsg;

if (sqlite3_exec (self.m_sql, [sqlUpdata UTF8String],0,NULL, &errorMsg) !=SQLITE_OK)

{

[selfcloseDatabase];

returnYES;

}else {

returnNO;

}

return YES;

}

//--------------select---------------------

-(NSArray*)querryTable:(NSString*)sqlQuerry

{

if (![selfopenOrCreateDatabase:self.m_dbName]) {

return nil;

}

int row = 0;

int column = 0;

char* errorMsg = NULL;

char** dbResult = NULL;

NSMutableArray* array = [[NSMutableArrayalloc]init];

if(sqlite3_get_table(m_sql, [sqlQuerryUTF8String], &dbResult, &row,&column,&errorMsg ) ==SQLITE_OK)

{

if (0 == row) {

[self closeDatabase];

return nil;

}

int index = column;

for(int i =0; i < row ; i++ ) {

NSMutableDictionary* dic = [[NSMutableDictionaryalloc]init];

for(int j =0 ; j < column; j++ ) {

if (dbResult[index]) {

NSString* value = [[NSStringalloc]initWithUTF8String:dbResult[index]];

NSString* key = [[NSStringalloc]initWithUTF8String:dbResult[j]];

[dic setObject:value forKey:key];

[value release];

[key release];

}

index ++;

}

[array addObject:dic];

[dic release];

}

}else {

printf("%s",errorMsg);

[selfcloseDatabase];

return nil;

}

[selfcloseDatabase];

return [array autorelease];

}

//----------------------select--------------------

int processData(void* arrayResult,int columnCount,char** columnValue,char** columnName)

{

int i;

NSMutableDictionary* dic = [[NSMutableDictionaryalloc]init];

for( i = 0 ; i < columnCount; i ++ )

{

if (columnValue[i]) {

NSString* key = [[NSStringalloc]initWithUTF8String:columnName[i]];

NSString* value = [[NSStringalloc]initWithUTF8String:columnValue[i]];

[dic setObject:value forKey:key];

}

}

[(NSMutableArray*)arrayResult addObject:dic];

[dic release];

return 0;

}

//---------------------select-----------------------

-(NSArray*)querryTableByCallBack:(NSString*)sqlQuerry

{

if (![selfopenOrCreateDatabase:self.m_dbName]) {

return nil;

}

char* errorMsg = NULL;

NSMutableArray* arrayResult = [[NSMutableArrayalloc]init];

if (sqlite3_exec(self.m_sql,[sqlQuerryUTF8String],processData,(void*)arrayResult,&errorMsg) !=SQLITE_OK) {

printf("查询出错:%s",errorMsg);

}

[selfcloseDatabase];

return [arrayResult autorelease];

}

@end

摘自:http://blog.csdn.net/pengruikeji/article/details/6681260

上一篇:阿里云数据库添加ECS云服务器白名单的方法


下一篇:一些值得关注的开源SDN项目