How to create databases, tables, and insert and view data for sqlite in iOS

IOS sqlite database operations. Steps:

First join the sqlite Development Library libsqlite3.dylib,

Create or open a database,

Create a data table,

Insert data,

Query and print data

1. Create a project sqliteDemo and add the library libsqlite3.dylib that uses sqlite.

2. sqlite Method

Sqlite3 * db, database handle, similar to FILE handle

Sqlite3_stmt * stmt, which is equivalent to the Command object of ODBC, used to save compiled SQL statements
Sqlite3_open () to open the database. It is created when no database exists.
Sqlite3_exec (): executes non-query SQL statements.
Sqlite3_step (): After sqlite3_prepare is called, use this function to move in the record set.
Sqlite3_close () to close database files
There are also a series of functions used to obtain data from record set fields, such
Sqlite3_column_text (), which is text-type data.
Sqlite3_column_blob (), fetch blob-type data
Sqlite3_column_int (), which is an int-type data.

3. Obtain the sandbox directory and create or open a database.

Add a member variable to the viewController. h header file and include the header file sqlite3.h

#import <UIKit/UIKit.h>#import <sqlite3.h>@interface ViewController : UIViewController{    sqlite3 *db;}@end

In the. m file definition macro, which will be used later

#define DBNAME    @"personinfo.sqlite"#define NAME      @"name"#define AGE       @"age"#define ADDRESS   @"address"#define TABLENAME @"PERSONINFO"

NSArray * paths = require (NSDocumentDirectory, NSUserDomainMask, YES); NSString * documents = [paths objectAtIndex: 0]; NSString * database_path = [documents stringByAppendingPathComponent: DBNAME]; if (sqlite3_open ([database_path UTF8String], & db )! = SQLITE_ OK) {sqlite3_close (db); NSLog (@ "database failed to open ");}

Sqlite3_open. If the data does not exist, it is created. Run. This shows the database files in the sandbox directory. (For details about how to open the sandbox directory of the simulator, refer to iOS sandbox and file operations (I ))

4. Create a data table

Create an independent method to execute SQL statements. Pass in SQL statements to execute SQL statements.

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

Create a data table PERSONINFO statement

NSString *sqlCreateTable = @"CREATE TABLE IF NOT EXISTS PERSONINFO (ID INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, address TEXT)";    [self execSql:sqlCreateTable];

Run the program and the data table is created. How can I know that a data table has been created? Let's use the Sqlite Manager plug-in tool of Firefox to open the database file. You can install this plug-in Firefox. Open

All four fields are in the table.

5. Insert data:

NSString * sql1 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @') ", TABLENAME, NAME, AGE, ADDRESS, @" Zhang San ", @" 23 ", @" Xicheng district "]; NSString * sql2 = [NSString stringWithFormat: @ "insert into '% @' ('% @', '% @', '% @') VALUES ('% @', '% @', '% @') ", TABLENAME, NAME, AGE, ADDRESS, @" la ", @" 20 ", @" Dongcheng District "]; [self execSql: sql1]; [self execSql: sql2];

Run the program, insert two pieces of data, and use the sqlite tool of Firefox to view the data.

6. query the database and print data

 NSString *sqlQuery = @"SELECT * FROM PERSONINFO";    sqlite3_stmt * statement;        if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {        while (sqlite3_step(statement) == SQLITE_ROW) {            char *name = (char*)sqlite3_column_text(statement, 1);            NSString *nsNameStr = [[NSString alloc]initWithUTF8String:name];                        int age = sqlite3_column_int(statement, 2);                        char *address = (char*)sqlite3_column_text(statement, 3);            NSString *nsAddressStr = [[NSString alloc]initWithUTF8String:address];                        NSLog(@"name:%@  age:%d  address:%@",nsNameStr,age, nsAddressStr);        }    }    sqlite3_close(db);

Print result:

13:25:32. 205 sqlitDemo [3587: f803] name: Zhang San age: 23 address: Xicheng district 13:25:32. 206 sqlitDemo [3587: f803] name: la age: 20 address: Dongcheng District

Close the database.

Example code: http://download.csdn.net/detail/totogo2010/4400911

Copyright Disclaimer: This article will be published at http://blog.csdn.net/totogo2010/, and you will be welcomed to enjoy the transfer and sharing. Please respect the work of the author. Keep this note and the author's blog link when reprinting. Thank you!