iphone数据库增删改查

数据库的创建需要sqlite3，所以需要在mac下装sqlite3数据库，这样就可以创建sqlite3数据库了，当然用别的方法也行，如在windows下有很多软件可以创建sqlite3数据库 (注意，数据库的格式为：dbname.sql)

数据库的浏览软件在mac下可用MesaSQLite

1，将创建好的数据库拖到Xcode的Resources文件夹里

2，在程序的AppDelegate.m里，写一个如下函数：

- (void) checkAndCreateDatabase{ BOOL success; NSString *databaseName = @"FirstAid.sql";

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentDir = [documentPaths objectAtIndex:0]; NSString *databasePath = [documentDir stringByAppendingPathComponent:databaseName];

NSFileManager *fileManager = [NSFileManager defaultManager]; success = [fileManager fileExistsAtPath:databasePath]; if (success) return;

NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName]; [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

}

此函数先检查手机里是否已有此数据库，若没有，则将数据库拷到手机里

在didFinishLaunchingWithOptions函数里，调用此函数 [self checkAndCreateDatabase]; 至此，数据库就到你的手机里了，此后，就可以在iphone程序里对这个数据库进行增，删，改，查了

3，对数据库的增，删，改，查

增，请看如下函数：（假设FirstAid.sql里有一个call表，要插入一条含name,number这两个字段的记录）

- (void) insertIntoCallWithName:(NSString *)name num:(NSString *)number{

sqlite3 *database;

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentDir = [documentPaths objectAtIndex:0]; NSString *databasePath = [documentDir stringByAppendingPathComponent:@"FirstAid.sql"];

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

const char *sqlStatement = "INSERT INTO call(name,number) VALUES(?,?)";

sqlite3_stmt *compiledStatement; if (sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

sqlite3_bind_text(compiledStatement, 1, [name UTF8String], -1, SQLITE_STATIC); sqlite3_bind_text(compiledStatement, 2, [number UTF8String], -1, SQLITE_STATIC);

if (sqlite3_step(compiledStatement) == SQLITE_ERROR) {

NSLog(@"Error:failed in kit update database"); } } sqlite3_finalize(compiledStatement); } sqlite3_close(database); }

删，请看如下函数：（以id来删除call表的一条记录）

- (void) deleteACallFromDbWithId:(int)callId{

sqlite3 *database;

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentDir = [documentPaths objectAtIndex:0]; NSString *databasePath = [documentDir stringByAppendingPathComponent:@"FirstAid.sql"];

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

const char *sqlStatement = "DELETE FROM call WHERE rowid = ?";

sqlite3_stmt *compiledStatement; if (sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

sqlite3_bind_int(compiledStatement, 1, callId);

if (sqlite3_step(compiledStatement) == SQLITE_ERROR) {

NSLog(@"Error:failed in kit update database"); } } sqlite3_finalize(compiledStatement); } sqlite3_close(database); }

改，请看如下函数：（根据id来修改kit表的kit_num字段）

- (void) updateKitNum:(int)num withId:(int)kitId{

sqlite3 *database;

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentDir = [documentPaths objectAtIndex:0]; NSString *databasePath = [documentDir stringByAppendingPathComponent:@"FirstAid.sql"];

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

const char *sqlStatement = "UPDATE kit SET kit_num = ? WHERE rowid = ?";

sqlite3_stmt *compiledStatement; if (sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

sqlite3_bind_int(compiledStatement, 1, num); sqlite3_bind_int(compiledStatement, 2, kitId); if (sqlite3_step(compiledStatement) == SQLITE_ERROR) {

NSLog(@"Error:failed in kit update database"); } } sqlite3_finalize(compiledStatement); } sqlite3_close(database); }

查，请看如下函数：（kitRowIdArray kitNameArray kitNumArray 为在.h中申明的NSMutableArray,从kit中查出rowid,kit_name,kit_num字段)

- (void) selectKitFormDb{

sqlite3 *database;

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentDir = [documentPaths objectAtIndex:0]; NSString *databasePath = [documentDir stringByAppendingPathComponent:@"FirstAid.sql"];

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

const char *sqlStatement = "select rowid,kit_name,kit_num from kit";

sqlite3_stmt *compiledStatement; if (sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

while (sqlite3_step(compiledStatement) == SQLITE_ROW) {

int aKitId = sqlite3_column_int(compiledStatement, 0); NSString *aKitName = [[NSString alloc] initWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)]; int aKitNumber = sqlite3_column_int(compiledStatement, 2);

NSNumber *aKitIdN = [[NSNumber alloc] initWithInt:aKitId]; NSNumber *aKitNum = [[NSNumber alloc] initWithInt:aKitNumber];

[kitRowIdArray addObject:aKitIdN]; [kitNameArray addObject:aKitName]; [kitNumArray addObject:aKitNum];

[aKitName release]; [aKitIdN release]; [aKitNum release]; } } sqlite3_finalize(compiledStatement); } sqlite3_close(database); }

自此，数据库的操作就完成了，应该可以对iphone下的数据库灵活操作了

本文来自：http://blog.163.com/zhunshiqichuang@126/blog/static/168065443201062911503015/