iOS Development SQLite3

SQLite3 is an open-source, embedded relational database with good portability, ease of use, and low memory overhead
SQLite3 is untyped, meaning that you can save any type of data to any field in any table. For example, the following statement of the creation is legal:
CREATE TABLE T_person (name, age);
To ensure readability, it is recommended that you add the field type:
CREATE TABLE T_person (Name text, age integer);

SQLite3 5 data types commonly used: text, integer,float, Boolean, blob
To use SQLite3 in iOS, you need to add a library file: Libsqlite3.dylib and import the primary header file, which is a C-language library

Create a database (SQLITE3_OPENDB) step action (sqlite3_exec) Create a data table data operation Insert Data Update data delete data query operation SQLITE3_PREPARE_V2 Check the legality of SQL sqlite3_ Step row by line gets the query result sqlite3_coloum_xxx gets the content of the corresponding type Sqlite3_finalize release stmt

Open Sqlite3 in Firefox (if not, select Tools, add-ons, added) new Sqlite3 database, Contacts, create a members table, field Id,integer, primary key, self-increment; Name,varchar; Email,varchar,null;birthday,datetime,null. Add some data to the table:

Second, create a new empty appliation, add a homeviewcontroller, and a component libsqlite3.dylib, to support the sqlite3 of the connection, closing, adding and deleting changes and other operations. 1. HomeViewController.h code: #import <UIKit/UIKit.h> #import "sqlite3.h" @interface Homeviewcontroller: uiviewcontroller{    //Declaring a sqlite3 database

}

the path to the//database file. Generally in the sandbox documents inside Operation -(NSString *) FilePath;

@end 2. HOMEVIEWCONTROLLER.M Code: #import "HomeViewController.h"@interface Homeviewcontroller () @end @implementation Homeviewcontroller//This method is used to return the full path information of the database in the Documents folder-(NSString *) filepath{Nsarray *paths = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, NSUserDomainMask, YE    S); NSString *documentsdir = [Paths objectatindex:0];return[Documentsdir stringbyappendingpathcomponent:@]Contacts.sqlite"];}//How to open a database- (void) opendb{if(Sqlite3_open ([[Self FilePath] utf8string], &db)! = SQLITE_OK)        {sqlite3_close (db); Nsassert (0, @ "database open failed. "); }}//Insert Data Method- (void) Insertrecordintotablename: (NSString *) tableName withField1: (NSString *) field1 field1value: (nsstring                        *) Field1value andField2: (NSString *) field2 field2value: (NSString *) field2value ANDFIELD3: (NSString *) field3 field3value: (NSString *) field3value{/ * Method 1: Classic method NSString *sql = [NSString stringwithformat:@ "INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES ('%@ ', '%@ ', '%@ ')    ) ", TableName, Field1, Field2, field3, Field1value, Field2value, Field3value];    Char *err;        if (sqlite3_exec (DB, [SQL Utf8string], NULL, NULL, &ERR)! = SQLITE_OK) {sqlite3_close (db); Nsassert (0, @ "Insert data Error!    "); }    */   //Method 2: Binding method of a variableInsert the table, what fields are in the table, and the given table field values NSString *sql = [NSString stringwithformat:@]INSERT into '%@ ' ('%@ ', '%@ ', '%@ ') VALUES (?,?,?)", TableName, Field1, Field2, field3]; Sqlite3_stmt *statement;if(SQLITE3_PREPARE_V2 (DB, [SQL Utf8string],-1, &statement, nil) = = SQLITE_OK)        {Sqlite3_bind_text (statement, 1, [Field1value utf8string], -1,null);        Sqlite3_bind_text (statement, 2, [Field2value utf8string], -1,null);    Sqlite3_bind_text (statement, 3, [Field3value utf8string], -1,null); }if(Sqlite3_step (statement)! = Sqlite_done) {Nsassert (0, @ "failed to insert data! ");    Sqlite3_finalize (statement); }                                }//Query data- (void) getallcontacts{NSString *sql = @ "SELECT * from"; Sqlite3_stmt *statement;if(SQLITE3_PREPARE_V2 (DB, [SQL Utf8string],-1, &statement, nil) = = SQLITE_OK) { while(Sqlite3_step (statement) = = Sqlite_row) {Char*name = (Char*) Sqlite3_column_text (statement, 0); NSString *namestr = [[NSString alloc] initwithutf8string:name];Char*email = (Char*) Sqlite3_column_text (statement, 1); NSString *emailstr = [[NSString alloc] initwithutf8string:email];Char*birthday = (Char*) Sqlite3_column_text (statement, 2);                        NSString *birthdaystr = [[NSString alloc] initwithutf8string:birthday]; NSString *info = [[NSString alloc] initwithformat:@ "%@ - %@ - %@", NameStr, Emailstr, Birthdaystr];                        NSLog (info);            [NAMESTR release];            [Emailstr release];            [Birthdaystr release];        [Info release];    } sqlite3_finalize (statement); }}- (void) viewdidload{[self opendb]; [Self insertrecordintotablename:@] Members"Withfield1:@"name"Field1value:@"Lee 1"Andfield2:@"Email"Field2value:@"[email protected]"Andfield3:@"Birthday"Field3value:@"12-45-78"]; [Self insertrecordintotablename:@] Members"Withfield1:@"name"Field1value:@"Lee 2"Andfield2:@"Email"Field2value:@"[email protected]"Andfield3:@"Birthday"Field3value:@"12-45-78"]; [Self insertrecordintotablename:@] Members"Withfield1:@"name"Field1value:@"Lee 3"Andfield2:@"Email"Field2value:@"[email protected]"Andfield3:@"Birthday"Field3value:@"12-45-78"];        [Self getallcontacts];        Sqlite3_close (DB); [Super Viewdidload];} @end the effect after inserting data:

The effect of the query:

Third, Summary:

1. Data query: The SQLITE3_EXEC () function executes the SQL statement, which is useful when there are no return values (such as creating a table, inserting a record, deleting a record, and so on).

The SQLITE3_STAT structure, the SQLITE3_PREPARE_V2 () function, the Sqlte3_step () function, and the sqlite3_finalize () function are also used.

The query is divided into three stages: preparation stage: Sqlite3_stat, SQLITE3_PREPARE_V2 ()

Execution stage: Sqlte3_step ()

Termination phase: Sqlite3_finalize ()

2. Schedule:

iOS Development SQLite3