SQLite3 使用详解

iPhone中支持通过sqlite3来访问iPhone本地的数据库。

具体使用方法如下

1:添加开发包libsqlite3.0.dylib

首先是设置项目文件，在项目中添加iPhone版的sqlite3的数据库的开发包，在项目下的Frameworks点击右键，然后选择libsqlite3.0.dylib文件。

libsqlite3.0.dylib文件地址: 
/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.2.sdk/usr/lib/libsqlite3.0.dylib

2，代码中的操作：

那么接下来是代码了。

1 首先获取iPhone上sqlite3的数据库文件的地址

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"database_name"];

2 打开iPhone上的sqlite3的数据库文件

sqlite3 *database;
sqlite3_open([path UTF8String], &database);

3 准备sql文－－－sql语句

sqlite3_stmt *stmt;
const char *sql = " SELECT * FROM table_name WHERE pk=? and name=?";
sqlite3_prepare_v2(database,  sql, -1, &stmt, NULL);

4 邦定参数

// 邦定第一个int参数
sqlite3_bind_int(stmt, 1, 1);
// 邦定第二个字符串参数
sqlite3_bind_text(stmt, 2, [title UTF8String], -1, SQLITE_TRANSIENT);

5 执行sql文

sqlite3_step(stmt);

6 释放sql文资源

sqlite3_finalize(stmt);

7 关闭iPhone上的sqlite3的数据库

sqlite3_close(database);

- (BOOL)openSqlite

{

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

    NSString *thePath1 = [paths1 objectAtIndex:0];

    NSLog(@"%@",thePath1);

    

    NSArray *paths2 = NSSearchPathForDirectoriesInDomains(NSCachesDirectory,NSUserDomainMask, YES);

    NSString *cachesDirectory2 = [paths2 objectAtIndex:0];

    NSLog(@"%@",cachesDirectory2);

    

    NSString *paths = [[NSBundle mainBundle] resourcePath];

    NSLog(@"%@",paths);

    NSString *xmlFile = [paths stringByAppendingPathComponent:@"Provin.db"];

    if (sqlite3_open([xmlFile UTF8String], &database) != SQLITE_OK) {

        sqlite3_close(database);

        database = NULL;

        return NO;

    }

    return YES;

}

- (void)closeSqlite

{

    if (database!=nil) {

        sqlite3_close(database);

        database = NULL;

    }

}

-(void)searchPrivince

{

    [arrProvince removeAllObjects];

    NSString* selectSQL1 = [[NSString alloc] initWithFormat:@"select province,provinceID from province"];

    

    sqlite3_stmt* statement;

    if (sqlite3_prepare_v2(database, [selectSQL1 UTF8String], -1, &statement, nil) == SQLITE_OK) {

        while (sqlite3_step(statement)== SQLITE_ROW) {

            char* province = (char*)sqlite3_column_text(statement, 0);

            char* provinceid = (char*)sqlite3_column_text(statement, 1);

            NSString* provinceName = [[NSString alloc] initWithCString:province encoding:NSUTF8StringEncoding];

            NSString* provinceID = [[NSString alloc] initWithCString:provinceid encoding:NSUTF8StringEncoding];

            provinceData = [[PhysicalBuyProvinceData alloc]init];

            provinceData.strProvinceName = provinceName;

            provinceData.strProvinceID = provinceID;

            [arrProvince addObject:provinceData];

            [provinceID release];

            [provinceName release];

            [provinceData release];

        }

    }

    [selectSQL1 release];

    sqlite3_finalize(statement);

}