"Go" iOS development 24: Use SQLite3 to store and read data

Transferred from: http://my.oschina.net/plumsoft/blog/57626

SQLite3 is a relational database embedded in iOS and is useful for storing large-scale data. SQLite3 makes it unnecessary to add each object to memory.

Basic operation:

(1) Open or create a database

Sqlite3 *database;int result = Sqlite3_open ("/path/databasefile", &database);

If the/path/databasefile does not exist, create it, or open it. If the value of result is SQLITE_OK, it indicates that our operation succeeded.

Note the address string for the database file in the preceding statement does not precede the @ character, which is a C string. The way to convert a nsstring string to a C string is:

const char *cstring = [NSString utf8string];

(2) Closing the database

Sqlite3_close (database);

(3) Create a table

Char *errormsg;const char *createsql = "CREATE TABLE IF not EXISTS people (ID INTEGER PRIMARY KEY AutoIncrement, Field_dat A TEXT) "; int result = Sqlite3_exec (database, createsql, NULL, NULL, &ERRORMSG);

After execution, if the value of result is SQLITE_OK, the execution succeeds; otherwise, the error information is stored in errormsg.

Sqlite3_exec This method can perform operations that do not return results, such as CREATE, insert, delete, and so on.

(4) Query operation

NSString *query = @ "Select ID, Field_data from fields ORDER by ROW"; sqlite3_stmt *statement;int result = Sqlite3_prepare_v 2 (database, [query utf8string],-1, &statement, nil);

If the value of result is SQLITE_OK, then the statement is ready and the query is executed:

while (sqlite3_step (statement) = = Sqlite_row) {    int rowNum = sqlite3_column_int (statement, 0);    Char *rowdata = (char *) sqlite3_column_text (statement, 1);    NSString *fieldvalue = [[NSString alloc] initwithutf8string:rowdata];    Do something with the data here}sqlite3_finalize (statement);

Using a different database should be a good way to understand this statement, which is to sequentially statement the data for each row, and then fetch the data based on the field in each row.

(5) Using constraint variables

The actual operation often uses something called a constraint variable to construct the SQL string, which can be inserted, queried, or deleted.

For example, to perform an insert operation with two constraint variables, the first variable is of type int and the second is a C string:

Char *sql = "INSERT into onetable values (?,?);"; Sqlite3_stmt *stmt;if (SQLITE3_PREPARE_V2 (Database, SQL, 1, &stmt, nil) = = SQLITE_OK) {    sqlite3_bind_int (stmt, 1, 235);    Sqlite3_bind_text (stmt, 2, "valuestring",-1, NULL);} if (Sqlite3_step (stmt)! = Sqlite_done)    NSLog (@ "Something is wrong!"); Sqlite3_finalize (stmt);

Here, Sqlite3_bind_int (stmt, 1, 235); There are three parameters:

The first is a variable of type sqlite3_stmt, used in the previous sqlite3_prepare_v2.

The second one is the tag index of the constrained variable.

The third parameter is the value to be added.

Some functions have two more variables, such as

Sqlite3_bind_text (stmt, 2, "valuestring",-1, NULL);

In this sentence, the fourth parameter represents the length to be passed in the third argument. For C strings, 1 means that all strings are passed.

The fifth parameter is a callback function, such as doing a memory cleanup after execution.

Next, make a small example!

1. Run Xcode 4.3 and create a new single View application with the name SQLite3 Test:

2. Connect SQLite3 Library:

Locate the plus sign in the order of the red numbers in:

Click the plus sign to open the window and enter Sqlite3 in the search field:

Select Libsqlite3.dylib, click Add to add to the project.

3, the interface design. To open Viewcontroller.xib, use the Interface Builder design interface as follows:

The tags for setting four text boxes are 1, 2, 3, 4, respectively.

4. Add properties and methods in ViewController.h:

@property (copy, nonatomic) NSString *databasefilepath;-(void) applicationwillresignactive: (Nsnotification *) Notification

5. Open VIEWCONTROLLER.M and add code to it:

5.1 Add code at the beginning:

#import "sqlite3.h" #define Kdatabasename @ "Database.sqlite3"

5.2 Add code after @implementation:

@synthesize Databasefilepath;

5.3 Add code in the Viewdidload method:

-(void) viewdidload{[Super Viewdidload];    Additional setup after loading the view, typically from a nib.    Get the database file path Nsarray *paths = Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES);    NSString *documentsdirectory = [Paths objectatindex:0];    Self.databasefilepath = [Documentsdirectory stringbyappendingpathcomponent:kdatabasename];    Open or create a database sqlite3 *database;        if (Sqlite3_open ([Self.databasefilepath utf8string], &database)! = SQLITE_OK) {sqlite3_close (database); Nsassert (0, @ "failed to open database!    ");    }//CREATE database table NSString *createsql = @ "CREATE table IF not EXISTS fields (TAG INTEGER PRIMARY KEY, Field_data TEXT);";    Char *errormsg; if (sqlite3_exec (database, [createsql utf8string], NULL, NULL, &ERRORMSG)! = SQLITE_OK) {sqlite3_close (databas        e);    Nsassert (0, @ "CREATE database table error:%s", errormsg);    }//Execute query NSString *query = @ "Select tag, Field_data from the fields ORDER by TAG"; Sqlite3_stmT *statement; if (SQLITE3_PREPARE_V2 (database, [query utf8string],-1, &statement, nil) = = SQLITE_OK) {//read each row in the database table fields sequentially Content and displayed in the corresponding TextField while (sqlite3_step (statement) = = Sqlite_row) {//get data int tag = sqlite3_            Column_int (statement, 0);            Char *rowdata = (char *) sqlite3_column_text (statement, 1);            Get TextField Uitextfield *textfield = (Uitextfield *) according to tag [Self.view Viewwithtag:tag];        Set text Textfield.text = [[NSString alloc] initwithutf8string:rowdata];    } sqlite3_finalize (statement);    }//Close database Sqlite3_close (db);    Write database operations when the program enters the background uiapplication *app = [UIApplication sharedapplication]; [[Nsnotificationcenter Defaultcenter] addobserver:self selector: @selector (applicationwillresignactive:) Name:U Iapplicationwillresignactivenotification Object:app];}

5.4 Implement the method before @end:

When the program enters the background, the implementation writes the currently displayed data to the database-(void) Applicationwillresignactive: (nsnotification *) Notification {//Open database Sqlite3 *da    Tabase;         if (Sqlite3_open ([Self.databasefilepath utf8string], &database)! = SQLITE_OK) {sqlite3_close (database); Nsassert (0, @ "failed to open database!    "); }//Insert four rows of data into the table for (int i = 1; I <= 4; i++) {//TextField Uitextfield *textfield = (Uitextfi        ELD *) [Self.view viewwithtag:i];        Inserting data using constraint variables char *update = "Insert OR REPLACE into fields (TAG, Field_data) VALUES (?,?);";        Sqlite3_stmt *stmt;            if (SQLITE3_PREPARE_V2 (database, update,-1, &stmt, nil) = = Sqlite_ok) {sqlite3_bind_int (stmt, 1, i);        Sqlite3_bind_text (stmt, 2, [Textfield.text utf8string],-1, NULL);        } char *errormsg = NULL;        if (Sqlite3_step (stmt)! = sqlite_done) Nsassert (0, @ "Update database table fields error:%s", errormsg);    Sqlite3_finalize (stmt); }//Close database Sqlite3_close;} 

6, the implementation of the keyboard off, refer to "iOS Development 4: Close the keyboard" in the 2nd step. Among them, the Backgroundtap method is as follows:

Off keyboard-(ibaction) Backgroundtap: (ID) Sender {for    (int i = 1; I <= 4; i++) {        Uitextfield *textfield = (uitextfie LD *) [Self.view viewwithtag:i];        [TextField Resignfirstresponder];}    }

7. Run the program:

At the beginning of the run, the following faces are displayed:

Enter the content in each text box, as shown above. Then press the home key, so that the operation to write the data is performed.

The first time you run the program, the database file Database.sqlite3 appears under the Documents directory in the sandbox:

When you exit the program and run again, the value displayed is the last time you exited.

"Go" iOS development 24: Use SQLite3 to store and read data