SQLite database developed for IOS (94)

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:

[Cpp]
<Span style = "font-family: Comic Sans MS; font-size: 18px;"> # 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, and press the home Key for execution (scheduling in the notification center)
-(Void) applicationWillResignActive :( NSNotification *) notification;
// After entering the field in UITextField through the keyboard, click the blank area on the screen to close the keyboard.
-(IBAction) backgroundTapped :( id) sender;
@ End </span>

 

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:

[Cpp]
# Import "ViewController. h"
# Import "sqlite3.h"
// Database file name
# Define kDatabaseName @ "database. sqlite3"
 
@ InterfaceViewController ()
 
@ End
 
@ Implementation ViewController
 
@ Synthesize databaseFilePath;
 
-(Void) viewDidLoad
{
[SuperviewDidLoad];

NSArray * path = NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES );
NSString * documentDirectory = [path objectAtIndex: 0];
// Specify the path of the database file
Self. databaseFilePath = [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, @ "failed to open database ");
}

// 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 included in the string.
Char * errorMsg;

// The sqlite3_exec method can be used to execute operations that do not return results, such as creation, insertion, and deletion. This function contains the sqlite3_prepare function operation, the purpose is to convert an SQL statement in UTF-8 format to a 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 a 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 (statament) = SQLITE_ROW ){
// Return the value of an int type field in the current record. sqlite3_column_text below returns a string type value, and the following numbers correspond to each column
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) in Android)
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 );

// Register the notification. Execute the applicationWillResignActive method (defined in. h) when the program is about to return to the background)
UIApplication * application = [UIApplicationsharedApplication];
[[Nsnotifcencenterdefacenter] addObserver: selfselector: @ 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, @ "Database Error opened ");
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 update is complete.
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 field in UITextField through the keyboard, click the blank area on the screen to close the keyboard.
The setup step is to open. 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 in the hfile
*/
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.