IPhone MySQL数据库操作代码例子

IPhone MySQL数据库操作代码例子

//database operation

打开数据库

-(BOOL) opendatabase{

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

NSString *documentsDirectory = [pathsobjectAtIndex:0];

NSString *path = [documentsDirectorystringByAppendingPathComponent:@"mydb.sql"];

NSFileManager*fileManager = [NSFileManagerdefaultManager];

BOOL find = [fileManagerfileExistsAtPath:path];

//找到数据库文件mydb.sql

if (find) {

NSLog(@"Database file have already existed.");

if(sqlite3_open([pathUTF8String], &database_) !=SQLITE_OK) {

sqlite3_close(database_);

NSLog(@"Error: open database file.");

return NO;

}

return YES;

}

if(sqlite3_open([path UTF8String], &database_) ==SQLITE_OK) {

//bFirstCreate_ = YES;

[selfcreateChannelsTable:database_];//在后面实现函数createChannelsTable

return YES;

}else {

sqlite3_close(database_);

NSLog(@"Error: open database file.");

return NO;

}

return NO;

}

创建表

- (BOOL) createChannelsTable:(sqlite3*)db{

char*sql = "CREATE TABLE reports (id integer primary key,stime text,stitle text,scal text,sruntime text)";

sqlite3_stmt *statement;

if(sqlite3_prepare_v2(db, sql, -1, &statement,nil) !=SQLITE_OK) {

NSLog(@"Error: failed to prepare statement:create reports table");

return NO;

}

int success =sqlite3_step(statement);

sqlite3_finalize(statement);

if ( success !=SQLITE_DONE) {

NSLog(@"Error: failed to dehydrate:CREATE TABLE reports");

return NO;

}

NSLog(@"Create table 'reports' successed.");

return YES;

}

插入表

- (BOOL)insertOneChannel:(NSString*)stime mytitle:(NSString*)stitle mycal:(NSString*)scal myruntime:(NSString*)sruntime

{

sqlite3_stmt *statement;

staticchar*sql = "INSERT INTO reports (id,stime,stitle,scal,sruntime) VALUES(NULL,?,?,?,?)";

//问号的个数要和(cid,title,imageData,imageLen)里面字段的个数匹配，代表未知的值，将在下面将值和字段关 联。

int success =sqlite3_prepare_v2(database_, sql, -1, &statement,NULL);

if (success !=SQLITE_OK) {

NSLog(@"Error: failed to insert:channels");

return NO;

}

//这里的数字1，2，3，4代表第几个问号

//sqlite3_bind_text(statement, 1, stime, -1, SQLITE_TRANSIENT);

char*p = [stime cStringUsingEncoding:1];

sqlite3_bind_text(statement,1, [stime cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

sqlite3_bind_text(statement,2, [stitle cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

sqlite3_bind_text(statement,3, [scal cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

sqlite3_bind_text(statement,4, [sruntime cStringUsingEncoding:1], -1,SQLITE_TRANSIENT);

success =sqlite3_step(statement);

sqlite3_finalize(statement);

if (success ==SQLITE_ERROR) {

NSLog(@"Error: failed to insert into the database with message.");

return NO;

}

NSLog(@"Insert One Channel#############:id = _");

return YES;

}

查询表

- (void) getChannels:(NSMutableArray*)fChannels{

sqlite3_stmt *statement =nil;

char*sql = "SELECT * FROM reports";

if (sqlite3_prepare_v2(database_, sql, -1, &statement,NULL) !=SQLITE_OK) {

NSLog(@"Error: failed to prepare statement with message:get channels.");

}

//查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。

while (sqlite3_step(statement) ==SQLITE_ROW) {

//char* cid = (char*)sqlite3_column_text(statement, 1);

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

char* stitle =(char*)sqlite3_column_text(statement,2);

char* scal = (char*)sqlite3_column_text(statement,3);

char* sruntime= (char*)sqlite3_column_text(statement,4);

//NSString *tmp = [NSString stringWithCString:stitle encoding:1];

myreportitem* ri = [[myreportitemalloc]init];

ri.mytime= [NSString stringWithCString:stime encoding:1];