IOS Study Notes (15th)-Database Operations (SQLite)

Http://www.sqlite.org/docs.html

 

 

-(BOOL) openDB {// obtain the database path NSArray * paths = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES); NSString * documents = [paths objectAtIndex: 0]; NSString * database_path = [documents stringByAppendingPathComponent: DBNAME]; // if the database exists, use sqlite3_open to open it directly (do not worry, if the database does not exist sqlite3_open will automatically create) // open the database, here, [path UTF8String] converts NSString to a C string, because SQLite3 is written in portable C (instead of // Objective-C) and does not know what NSString is. if (sqlite3_open ([database_path UTF8String], & db) = SQLITE_ OK) {return YES;} else {return NO; NSLog (@ "failed to open database "); sqlite3_close (db );}}

 

 

-(Void) execSql :( NSString *) SQL {if ([self openDB]) {char * err; if (sqlite3_exec (db, [SQL UTF8String], NULL, NULL, & err )! = SQLITE_ OK) {NSLog (@ "database operation data failed! ") ;}Else {NSLog (@" % @ ", SQL) ;}sqlite3_close (db );}}

 

 

-(Void) insertData {NSString * insertSql1 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @') ", TABLENAME, NAME, AGE, ADDRESS, @" Zhang San ", @" 13 ", @ "Jinan"]; [self execSql: insertSql1]; NSString * insertSql2 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @') ", TABLENAME, NAME, AGE, ADDRESS, @" ", @ "12", @ "Jinan"]; [self execSql: insertSql2];}

 

 

 

-(void) updateData{    NSString *updateSql = [NSString stringWithFormat:                      @"UPDATE '%@' SET '%@' = '%@' WHERE '%@' = '%@'",                      TABLENAME,   AGE,  @"15" ,AGE,  @"13"];    [self execSql:updateSql];}

Delete table content:

 

 

-(Void) deleteData {NSString * sdeleteSql = [NSString stringWithFormat: @ "delete from % @ where % @ = '% @'", TABLENAME, NAME, @ "Zhang San"]; [self execSql: sdeleteSql];}

The table content is added, modified, and deleted. The following table Content Query is implemented.

 

 

-(Void) selectData {[self openDB]; NSString * sqlQuery = [NSString stringWithFormat: @ "SELECT * FROM % @", TABLENAME]; sqlite3_stmt * statement; if (sqlite3_prepare_v2 (db, [sqlQuery UTF8String],-1, & statement, nil) = SQLITE_ OK) {// traverse all records one by one in the query result set, the numbers here correspond to column values. Note that the column values here are while (sqlite3_step (statement) = SQLITE_ROW) {char * name = (char *) sqlite3_column_text (statement, 1 ); NSString * nsNameStr = [[NSString alloc] initwithuf8string: name]; int age = sqlite3_column_int (statement, 2); char * address = (char *) sqlite3_column_text (statement, 3 ); NSString * nsAddressStr = [[NSString alloc] initwithuf8string: address]; NSLog (@ "name: % @ age: % d address: % @", nsNameStr, age, nsAddressStr) ;}} else {NSLog (@ "select error: % @", sqlQuery);} sqlite3_close (db );}

 

 

 

 

@ Zhang xingye TBOW