IOS Study Notes 15 -- SQLite Database

SQLite is a simplified version of MySQL. It is used on mobile devices or small devices. The advantage of SQLite is its portability. It can run without a server. It also has some defects. First, it does not provide a simple database creation method and must be created manually, secondly, SQLite does not have object-oriented interfaces and must use APIs that depend on C language code. Compared with OC, this API is neither so elegant nor difficult to use. We recommend that you use SQLite for data storage compared to using files for storage.

Next let's take a look at how to use SQLite

The project directory is as follows:

Create a single view application project named sqlite3test and open the viewcontroller. XIB file. The layout is as follows. Set the tags of the three uitextfields to 1, 2, and 3, respectively.

Set the tag in the property selector (to the right of xcode)

Then declare the following in the viewcontroller. h file:

# Import <uikit/uikit. h> @ interface viewcontroller: uiviewcontroller @ property (copy, nonatomic) nsstring * databasefilepath; // This method defines the method to be executed when the application returns to the background, press the Home Key to run the task (scheduling in the notification center)-(void) applicationwillresignactive :( nsnotification *) notification; // after entering the task in uitextfield on the keyboard, click the blank area on the screen to close the keyboard operation-(ibaction) backgroundtapped :( ID) sender; @ end

Next, let's take a look at the specific implementation of the. M file. I will not discuss it one by one. There will be comments in the code.

Before writing the code, import the SQLite support package libsqlite3.dylib (the specific practices will not be detailed)

Code:

# Import "viewcontroller. H "# import" sqlite3.h "// database file name # define kdatabasename @" database. sqlite3 "@ interfaceviewcontroller () @ end @ implementation viewcontroller @ synthesize databasefilepath;-(void) viewdidload {[superviewdidload]; nsarray * Path = require (nsdocumentdirectory, nsuserdomainmask, yes ); nsstring * documentdirectory = [path objectatindex: 0]; // specify the path of the database file self. datab Asefilepath = [documentdirectory stringbyappendingpathcomponent: kdatabasename]; // open the database sqlite3 * database; // [self. databasefilepath utf8string]; replace the OC string with the C string if (sqlite3_open ([self. databasefilepathutf8string], & database )! = Sqlite_ OK) {// close the database sqlite3_close (database); nsassert (0, @ "database failed to open ");} // create a table nsstring * createsql = @ "create table if not exists student (TAG integer primary key, field_data text);"; // if an error occurs, the error message is char * errormsg in the string. // sqlite3_exec can be used to perform operations without returned results, such as creation, insertion, and deletion, this function contains sqlite3_prepare the operation of this function, the purpose is to convert the SQL statement in UTF-8 format to the compiled statement if (sqlite3_exec (Database, [createsql utf8string], null, null, & errormsg )! = Sqlite_ OK) {sqlite3_close (database); nsassert (0, @ "Table creation error: % s", errormsg );} // query the database nsstring * querysql = @ "select * from student order by Tag"; // statement handle sqlite3_stmt * statament; // sqlite3_prepare_v2 converts an SQL statement in UTF-8 format to a compiled statement and returns the pointer to the statement if (sqlite3_prepare_v2 (Database, [querysql utf8string],-1, & statament, nil) = sqlite_ OK) {// sqlite3_step moves a record forward in the compiled statement. sqlite_row indicates a row while (sqlite3_step (s) Tatament) = sqlite_row) {// return the value of an int type field in the current record. sqlite3_column_text below returns a string type value, the numbers that follow correspond to the int tag = sqlite3_column_int (statament, 1); char * rowdata = (char *) sqlite3_column_text (statament, 2 ); // to obtain an nsstring, use the following method // nsstring * STR = [nsstring stringwithuf8string :( char *) sqlite3_column_text (statament, 1)]; // obtain the UI control through tag, similar to findviewbyid (ID) uitextfield * textfield = (uitextfield *) [Self. viewviewwithtag: Tag]; // [[nsstring alloc] initwithuf8string: rowdata] converts a C string to an OC string textfield. TEXT = [[nsstringalloc] initwithuf8string: rowdata];} // Delete the compiled statement sqlite3_finalize (statament);} sqlite3_close (database); // registration notification, execute the applicationwillresignactive method (in. (defined in H) uiapplication * Application = [uiapplicationsharedapplication]; [[nsnotificationcenterdefacenter] addobserver: selfsele Ctor: @ selector (applicationwillresignactive :) name: uiapplicationwillresignactivenotificationobject: Application];}-(void) viewdidunload {[superviewdidunload]; // release any retained subviews of the main view .} -(bool) shouldautorotatetointerfaceorientation :( uiinterfaceorientation) interfaceorientation {return (interfaceorientation! = Uiinterfaceorientationportraitupsidedown);} // press the Home Key to save the data in uitextfield before entering the background-(void) applicationwillresignactive :( nsnotification *) Notification {sqlite3 * database; if (sqlite3_open ([self. databasefilepathutf8string], & database )! = Sqlite_ OK) {nsassert (0, @ "Open Database Error"); sqlite3_close (database) ;}for (INT I = 1; I <= 3; I ++) {uitextfield * textfield = (uitextfield *) [self. viewviewwithtag: I]; // insert data char * update = "insert or replace into student values (?,?) "; Sqlite3_stmt * Statement; If (sqlite3_prepare_v2 (Database, update,-1, & statement, nil) = sqlite_ OK) {// Save the value to the specified column sqlite3_bind_int (statement, 1, I); // The fourth parameter represents the length to be passed in the third parameter. For a C string,-1 indicates passing all strings. The fifth parameter is a callback function, for example, to clear the memory after execution. Sqlite3_bind_text (statement, 2, [textfield. textutf8string],-1, null);} Char * errormsg = NULL; // sqlite_done indicates whether the database is updated if (sqlite3_step (statement )! = Sqlite_done) {nsassert (0, @ "data update error: % s", errormsg);} sqlite3_finalize (statement);} sqlite3_close (database);}-(ibaction) backgroundtapped :( ID) sender {/* after entering the information in uitextfield on the keyboard, click the blank area to close the keyboard. select the entire view from the XIB file, change the class in the attribute selector from uiview to uicontrol, select the touch down event in the event selector, and connect it. the backgroundtapped Method */For (INT I = 1; I <= 3; I ++) {uitextfield * textfield = (uitextfield *) [self. viewviewwithtag: I]; [textfield resignfirstresponder] ;}}@ end

The final running effect is as follows: Enter the information and press the Home key, and then enter the application. You can see that the data is saved before every exit, go to the SQLite database again to query the saved data and display it on the interface.

To join our QQ group or public account, see: Ryan's
Zone public account and QQ Group