IOS data persistence-detailed description of SQLite database usage

Use SQLite DatabaseCreate a database

Three steps are required to create a database:

1. Use the sqlite3_open function to open the database;

2. Use the sqlite3_exec function to execute the Create Table statement and Create a database Table;

3. Use the sqlite3_close function to release resources.

 

Three SQLite3 functions are used in this process. They are pure C functions. It is not a problem to call C functions through Objective-C, however, you should also pay attention to the compatibility between the Objective-C data type and the C data type.

Below we use SQLite technology to implement the memorandum case. Just like the implementation of the attribute list file, we only need to modify the NoteDAO class in the PersistenceLayer project. First, we need to add the SQLite3 Library to the project environment. Which of the three projects need to be added? It should be added to a running project, that is, the presentation layer project PresentationLayer. Select TARGETS → PresentationLayer → Link Binary With Libraries in the project PresentationLayer, click "+" in the lower left corner, select libsqlite3.dylib or libsqlite3.0.dylib in the pop-up dialog box, and click Add.

Modify NoteDAO. h file:

# Import "Note. h "# import" sqlite3.h "# define DBFILE_NAME @" NotesList. sqlite3 "@ interface NoteDAO: NSObject {sqlite3 * db;} + (NoteDAO *) sharedManager;-(NSString *) applicationDocumentsDirectoryFile;-(void) createEditableCopyOfDatabaseIfNeeded; // Insert the Note method-(int) create :( Note *) model; // Delete the Note method-(int) remove :( Note *) model; // modify the Note method-(int) modify :( Note *) model; // query all data methods-(NSMutableArray *) findAll; // Method for querying data by primary key-(Note *) findById :( Note *) model; @ end

We need to use the statement # import "sqlite3.h" to introduce the sqlite3 header file and define the sqlite3 * member variable db. The createEditableCopyOfDatabaseIfNeeded method in NoteDAO. m:

-(Void) createEditableCopyOfDatabaseIfNeeded {NSString * writableDBPath = [self applicationDocumentsDirectoryFile]; if (sqlite3_open ([writableDBPath UTF8String], & db )! = SQLITE_ OK) {① sqlite3_close (db); ② NSAssert (NO, @ "failed to open the database .");} Else {char * err; NSString * createSQL = [NSString stringWithFormat: @ "create table if not exists Note (cdate text primary key, content TEXT);"]; ③ if (sqlite3_exec (db, [createSQL UTF8String], NULL, NULL, & err )! = SQLITE_ OK) {④ sqlite3_close (db); ⑤ NSAssert1 (NO, @ "table creation failed, % s", err); ⑥} sqlite3_close (db); 7 }}

The createEditableCopyOfDatabaseIfNeeded method is used to create a database. The first step is to open the database, line 1 of the Code, and the statement is sqlite3_open ([writableDBPath UTF8String], & db ), sqlite3_open function's 1st parameters are the full path of the database file, but note that the SQLite3 function accepts the UTF-8 type data of char *, The NSString * needs to be converted to the UTF-8, you can use the UTF8String method of NSString * to convert the address of the sqlite3_open function's 2nd sqlite3 pointer variable db. The return value of this function is of the int type. Many constants are defined in SQLite3. If the return value is equal to the constant SQLITE_ OK, the operation is successful.

Step 4: Execute the table creation statement, line 4 of the Code, and SQL statement sqlite3_exec (db, [createSQL UTF8String], NULL, NULL, & err) to execute the table creation SQL. 1st parameters are the address of the sqlite3 pointer variable db, 2nd parameters are the SQL statement to be executed, 3rd parameters are the callback function, and 4th parameters are the parameters of the callback function, the 5th parameters are execution error strings. The SQL statement used to create a table is yes. If the table Note exists, you do not need to create it.

Create table if not exists Note (cdate text primary key, content TEXT)

Step 2 use the sqlite3_close function to release resources. As shown in lines ②, ⑤, and VII, it is called when the database fails to be opened, the Create Table fails to be executed, and the execution is successful. In principle, the sqlite3_close function must be used to release resources no matter whether the resource ends normally or abnormally.

Query data

Data Query generally has query conditions, which can be easily implemented using the SQL statement where clause. However, you need to dynamically bind parameters to the where clause in the program. To query data, follow these steps:

1. Use the sqlite3_open function to open the database;

2. Use the sqlite3_prepare_v2 function to pre-process SQL statements;

3. Use the sqlite3_bind_text function to bind parameters;

4. Use the sqlite3_step function to execute an SQL statement and traverse the result set;

5. Use functions such as sqlite3_column_text to extract field data;

6. Use the sqlite3_finalize and sqlite3_close functions to release resources.

In NoteDAO. m, the data query method based on the primary key is as follows:

-(Note *) findById :( Note *) model {NSString * path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open ([path UTF8String], & db )! = SQLITE_ OK) {① sqlite3_close (db); ② NSAssert (NO, @ "failed to open the database .");} Else {NSString * qsql = @ "SELECT cdate, content FROM Note where cdate = ?"; Sqlite3_stmt * statement; // pre-processing process if (sqlite3_prepare_v2 (db, [qsql UTF8String],-1, & statement, NULL) = SQLITE_ OK) {③ // prepare the parameter NSDateFormatter * dateFormatter = [[NSDateFormatter alloc] init]; ④ [dateFormatter setDateFormat: @ "yyyy-MM-dd HH: mm: ss"]; NSString * nsdate = [dateFormatter stringFromDate: model. date]; // bind the parameter to start sqlite3_bind_text (statement, 1, [nsdate UTF8String],-1, NULL); ⑤ // execute if (sqlite3_step (statement) = SQLITE_ROW) {⑥ char * cdate = (char *) sqlite3_column_text (statement, 0); 7nsstring * nscdate = [[NSString alloc] initwithuf8string: cdate]; char * content = (char *) sqlite3_column_text (statement, 1); NSString * nscontent = [[NSString alloc] initwithuf8string: content]; Note * note = [[Note alloc] init]; note. date = [dateFormatter dateFromString: nscdate]; note. content = nscontent; sqlite3_finalize (statement); sqlite3_close (db); return note ;}} sqlite3_finalize (statement); ⑧ sqlite3_close (db); Response} return nil ;}

This method performs six steps, of which 1st are shown in line ① Of the code. It is the same as the 1st steps for creating a database.

Step 3: As shown in line ③ of the Code, the SQL statement sqlite3_prepare_v2 (db, [qsql UTF8String],-1, & statement, NULL) is a pre-processing SQL statement, the purpose of preprocessing is to compile SQL statements into binary code and improve the speed of SQL statement execution. Sqlite3_prepare_v2 represents the length of all SQL strings. The 3rd parameter & statement is the address of the sqlite3_stmt pointer. It is a statement object and can execute SQL statements through statement objects, the 5th parameters are some of the SQL statements that are not executed.

In step 5, the SQL statement sqlite3_bind_text (statement, 1, [nsdate UTF8String],-1, NULL) is bound to the SQL statement parameters. In an SQL statement, the question mark is the parameter to be bound. The question mark is a placeholder.

NSString * qsql = @ "SELECT cdate, content FROM Note where cdate = ?";

The sqlite3_bind_text function is a binding parameter. The 1st parameters are statement pointers, the 2nd parameters are serial numbers (starting from 1), the 3rd parameters are string values, and the 4th parameters are string lengths, the 5th parameters are a function pointer.

In step 6, sqlite3_step (statement) executes the SQL statement. As shown in line 6 of the Code, sqlite3_step returns the int type, which is equal to SQLITE_ROW, indicating that other rows are not traversed.

The second step extracts field data, as shown in line 7 of the Code. The sqlite3_column_text (statement, 0) function can be used to read string-type fields, the 2nd parameter specifies the index of the select field (starting from 0 ). Similarly, if char * is converted to NSString * type, initwithuf8string is required: constructor. The function used to read fields is related to the field type. Similar common functions of SQLite3 include:

Sqlite3_column_blob ()

Sqlite3_column_double ()

Sqlite3_column_int ()

Sqlite3_column_int64 ()

Sqlite3_column_text ()

Sqlite3_column_text16 ()

For more information about other APIs, see http://www.sqlite.org/cintro.html.

The second step is to release resources. The database creation process is different. Besides using the sqlite3_close function to close the database, the statement object statement code is also shown in the statement row released by the sqlite3_finalize function.

To query all data in NoteDAO. m:

-(NSMutableArray *) findAll {NSString * path = [self applicationDocumentsDirectoryFile]; NSMutableArray * listData = [[NSMutableArray alloc] init]; if (sqlite3_open ([path UTF8String] & db )! = SQLITE_ OK) {sqlite3_close (db); NSAssert (NO, @ "failed to open the database .");} Else {NSString * qsql = @ "SELECT cdate, content FROM Note"; sqlite3_stmt * statement; // pre-processing process if (sqlite3_prepare_v2 (db, [qsql UTF8String],-1, & statement, NULL) = SQLITE_ OK) {NSDateFormatter * dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat: @ "yyyy-MM-dd HH: mm: ss "]; // execute while (sqlite3_step (statement) = SQLITE_ROW) {char * cdate = (char *) sqlite3_column_text (statement, 0 ); NSString * nscdate = [[NSString alloc] initwithuf8string: cdate]; char * content = (char *) sqlite3_column_text (statement, 1); NSString * nscontent = [[NSString alloc] initwithuf8string: content]; Note * note = [[Note alloc] init]; note. date = [dateFormatter dateFromString: nscdate]; note. content = nscontent; [listData addObject: note] ;}} sqlite3_finalize (statement); sqlite3_close (db);} return listData ;}

The method for querying all data is similar to the method for querying data by primary key. The difference is that this method does not have query conditions and does not require parameter binding. When traversing, use the while LOOP statement instead of the if statement.

While (sqlite3_step (statement) = SQLITE_ROW ){

... ...

}

Modify data

Data modification includes insert, update, and delete statements. These three SQL statements can contain parameters. The parameter binding method is the same as that of the query where clause. Perform the following steps to modify the data:

1. Use the sqlite3_open function to open the database;

2. Use the sqlite3_prepare_v2 function to pre-process SQL statements;

3. Use the sqlite3_bind_text function to bind parameters;

4. Use the sqlite3_step function to execute an SQL statement;

5. Use the sqlite3_finalize and sqlite3_close functions to release resources.

The data modification step is less than the data query step to extract field data. Let's take a look at the code section. The other steps are the same.

Note Insertion Method in NoteDAO. m:

-(Int) create :( Note *) model {NSString * path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open ([path UTF8String], & db )! = SQLITE_ OK) {① sqlite3_close (db); ② NSAssert (NO, @ "failed to open the database .");} Else {NSString * sqlStr = @ "insert or replace into note (cdate, content) VALUES (?,?)"; Sqlite3_stmt * statement; // pre-processing process if (sqlite3_prepare_v2 (db, [sqlStr UTF8String],-1, & statement, NULL) = SQLITE_ OK) {③ NSDateFormatter * dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat: @ "yyyy-MM-dd HH: mm: ss"]; NSString * nsdate = [dateFormatter stringFromDate: model. date]; // The binding parameter starts sqlite3_bind_text (statement, 1, [nsdate UTF8String],-1, NULL); ④ sqlite3_bind_text (statement, 2, [Model. content UTF8String],-1, NULL); // execute insert if (sqlite3_step (statement )! = SQLITE_DONE) {⑤ NSAssert (NO, @ "failed to insert data .");}} Sqlite3_finalize (statement); ⑥ sqlite3_close (db); 7} return 0 ;}

The fifth line of code sqlite3_step (statement) statement executes the insert statement. The constant SQLITE_DONE is executed completely.

Note deletion method in NoteDAO. m:

-(Int) remove :( Note *) model {NSString * path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open ([path UTF8String], & db )! = SQLITE_ OK) {sqlite3_close (db); NSAssert (NO, @ "failed to open the database .");} Else {NSString * sqlStr = @ "DELETE from note where cdate = ?"; Sqlite3_stmt * statement; // pre-processing process if (sqlite3_prepare_v2 (db, [sqlStr UTF8String],-1, & statement, NULL) = SQLITE_ OK) {NSDateFormatter * dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat: @ "yyyy-MM-dd HH: mm: ss"]; NSString * nsdate = [dateFormatter stringFromDate: model. date]; // bind the parameter to start sqlite3_bind_text (statement, 1, [nsdate UTF8String],-1, NULL); // execute insert if (sqlite3_step (statement )! = SQLITE_DONE) {NSAssert (NO, @ "failed to delete data .");}} Sqlite3_finalize (statement); sqlite3_close (db);} return 0;} NoteDAO. modify the Note method in m:-(int) modify :( Note *) model {NSString * path = [self applicationDocumentsDirectoryFile]; if (sqlite3_open ([path UTF8String], & db )! = SQLITE_ OK) {sqlite3_close (db); NSAssert (NO, @ "failed to open the database .");} Else {NSString * sqlStr = @ "UPDATE note set content =? Where cdate = ?"; Sqlite3_stmt * statement; // pre-processing process if (sqlite3_prepare_v2 (db, [sqlStr UTF8String],-1, & statement, NULL) = SQLITE_ OK) {NSDateFormatter * dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat: @ "yyyy-MM-dd HH: mm: ss"]; NSString * nsdate = [dateFormatter stringFromDate: model. date]; // start sqlite3_bind_text (statement, 1, [model. content UTF8String],-1, NULL); sqlite3_bind_text (statement, 2, [nsdate UTF8String],-1, NULL); // execute insert if (sqlite3_step (statement )! = SQLITE_DONE) {NSAssert (NO, @ "failed to modify data .");}} Sqlite3_finalize (statement); sqlite3_close (db);} return 0 ;}