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

SQLite (http://www.sqlite.org/docs.html) is a lightweight relational database. SQLite was originally designed for embedded systems. It occupies a very small amount of resources. In embedded devices, only a few hundred KB of memory is required, it is currently applicable to Android, IOS, Windows Phone, and other smartphones. When using SQLite in iOS, you only need to add libsqlite3.dylib dependency and introduce the sqlite3.h header file.

Database Operations include opening databases, creating tables, adding, deleting, modifying, and querying tables. The following code provides database operations.

Create and open a database:

-(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 );}}

The command for creating a database is not provided in iOS. When sqlite3_open is used, if the database file does not exist, the database will be created on its own. If yes, the database will be opened. After opening the database, you can create a table and operate the table content. sqlite3 in iOS uses sqlite3_exec to create a table, insert the table content, modify the table content, and delete the table content. sqlite3_prepare_v2 is used to query the table. The encapsulation of sqlite3_exec is given below:

-(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 );}}

Create a table:

NSString *sqlCreateTable =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' INTEGER PRIMARY KEY AUTOINCREMENT, '%@' TEXT, '%@' INTEGER, '%@' TEXT)",TABLENAME,ID,NAME,AGE,ADDRESS];    [self execSql:sqlCreateTable];

Insert data:

-(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];}

Modify Table:

-(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 );}

Okay, that's all. The classes for database operations in iOS are not very useful. I hope you can encapsulate your classes as much as possible ,.

Download Demo

/**

* @ Author Zhang xingye * http://blog.csdn.net/xyz_lmn* iOS entry group: 83702688

* Android Development Group: 241395671

* My Sina Weibo:@ Zhang xingye TBOW*/
Http://www.cnblogs.com/xiaobaizhu/archive/2012/12/07/2808170.html