-SQLite database using persistent iOS data

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The use of SQLite database

Create database

3 steps to creating a database:

1,Uses the sqlite3_open function to open the database,

2,Uses the sqlite3_exec function to execute the Create  Table statement, create database tables;,

3,Use the sqlite3_close function to release resources.


The use of 3 SQLite3 function in the process, they are pure C language function, through the Objective-C to call the C function of course is not a problem, but also pay attention to types and C data type compatibility of Objective-C data.

We use SQLite technology to realize the following memo case, and the attribute list file to achieve, we only need to modify the persistence layer Engineering (PersistenceLayer) in the NoteDAO class can be. First, we need to add the SQLite3 library to engineering environment, there are 3 projects need to be added to what? Should be added to the project can run that layer of Engineering PresentationLayer. Select the TARGETS&rarr project PresentationLayer; PresentationLayer→ Link  Binary  With  Libraries, click on the lower left-hand corner of the “ +”, the pop-up dialog box to select libsqlite3.dylib or libsqlite3.0.dylib, add a click Add in the pop-up dialog box.

1

Modified 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;

 

//All data query

-(NSMutableArray*) findAll;

 

//Data query method according to the primary key

-(Note*) findById:(Note*)model;

 

@end

We need to use the #import  statement; ” sqlite3.h” the SQLite3 header files, but also need to 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,@”Open the database failed. ”);

} 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, @”Create table failed, %s”, err);  ⑥

}

sqlite3_close(db);  ⑦

}

}

The createEditableCopyOfDatabaseIfNeeded method is used to create database, The first step to open data base, The line of code, The statement is sqlite3_open([writableDBPath UTF8String], &db), The first parameter of the sqlite3_open function is the path to the database file complete, But the need to pay attention to is accepted in the SQLite3 function of the UTF-8 data type char*, To convert NSString* to UTF-8, Using the UTF8String method of NSString* can be converted, The sqlite3_open function of the second parameters of the SQLite3 pointer variable DB address. The function's return value is of type int, a lot of constants defined in the SQLite3, the return value is equal to the constant SQLITE_OK shows successful operation.

Create table statement executed in second steps, the first line of code, the statement sqlite3_exec (DB, [createSQL  UTF8String], NULL, NULL, & ERR) perform built table SQL. The first parameter is the SQLite3 pointer variable DB address, the second parameter is the SQL statement to be executed, the third parameter to the callback function, the fourth parameter is the parameter to the callback function, the fifth parameter is the string error executing. Create table SQL statement is, if there is no create table Note.

CREATE TABLE IF NOT EXISTS Note (cdate TEXT PRIMARY KEY, content TEXT)

The third step uses the sqlite3_close function to release resources, code the, the, the line is shown, failed to open, in the database Create  Table execution failed and successful implementation of complete call. In principle both in normal or abnormal end end must use the sqlite3_close function to release resources.

Query data

Data query to a general query conditions by using the SQL statement, the where clause is very easy to implement, but requires dynamic binding parameters to the where clause in the program. Execute the query data is as follows:

1,Uses the sqlite3_open function to open the database,

2,The use of pre process a SQL statement sqlite3_prepare_v2 function,

3,Uses the sqlite3_bind_text function to bind the parameter,

4,Uses the sqlite3_step function to execute the SQL statement, traverse the result set,

5,Use the sqlite3_column_text function to extract the field data,

6,The use of sqlite3_finalize and sqlite3_close function to release resources.

According to the primary key in NoteDAO.m data query method:

-(Note*) findById:(Note*)model

{

NSString *path = [self applicationDocumentsDirectoryFile];

if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) { ①

sqlite3_close(db);  ②

NSAssert(NO,@”Open the database failed. ”);

} else {

 

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

 

sqlite3_stmt *statement;

//The pretreatment process

if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statement, NULL) == SQLITE_OK) { ③

//Preparation parameters

NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; ④

[dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

NSString *nsdate = [dateFormatter stringFromDate:model.date];

//Bind the parameter to

sqlite3_bind_text(statement, 1, [nsdate UTF8String], -1, NULL); ⑤

//Execution

if (sqlite3_step(statement) == SQLITE_ROW) { ⑥

char *cdate = (char *) sqlite3_column_text(statement, 0); ⑦

NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate];

 

char *content = (char *) sqlite3_column_text(statement, 1);

NSString * nscontent = [[NSString alloc] initWithUTF8String: 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);  ⑨

 

}

return nil;

}

The method performs the 6 steps, including first steps, the first line of code is shown, it is the same with creating a database of first steps, not introduced.

The second step, the lines of code shown, the statement sqlite3_prepare_v2 (DB,   [qsql  UTF8String],   -1,   & statement,   NULL) is the pre-processing of SQL statements, preprocessing is to compile SQL into binary code, improve the SQL statement execution speed. The third parameter of the sqlite3_prepare_v2 function -1 represents all SQL the length of the string, the fourth parameter & statement is a sqlite3_stmt pointer address, it is a statement object, to execute the SQL statement by statement object, the fifth parameter is the SQL statement is not part of sentence to be executed.

The third step, the code shown in the fifth row, the statement sqlite3_bind_text (statement,   1,   [nsdate  UTF8String],   -1,   NULL) is the binding parameters in the SQL statement. With a question mark in the SQL statement, parameters of the question mark is to bind, mark is a placeholder.

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

The sqlite3_bind_text function is the binding parameters, first parameters are pointers to statement, the second parameter is the number (starting from 1), the third parameter is the string value, the fourth parameter is the length of the string, the fifth parameter is a pointer to a function.

Fourth steps of the sqlite3_step (statement) SQL statement execution, code No. 6 lines as shown, sqlite3_step return type int, equal to SQLITE_ROW but also shows other row is not ergodic.

The fifth step extraction field data, code article the lines shown below, using the sqlite3_column_text (statement,   0) function can read the string type field, the second parameter is specified in the select field index (since 0). The same char* into NSString* type, initWithUTF8String: construction method. Read field function using the relationship with the field type, SQLite3 similar to that of the commonly used functions and:

sqlite3_column_blob()

sqlite3_column_double()

sqlite3_column_int()

sqlite3_column_int64()

sqlite3_column_text()

sqlite3_column_text16()

On the other can refer to API .

The sixth step is to release the resources, creating a database, in addition to use the sqlite3_close function to close the database, the code shown in the line, but also uses the sqlite3_finalize function to release a statement object in the statement code shown on line.

All data in NoteDAO.m query method:

-(NSMutableArray*) findAll

{

NSString *path = [self applicationDocumentsDirectoryFile];

NSMutableArray *listData = [[NSMutableArray alloc] init];

if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) {

sqlite3_close(db);

NSAssert(NO,@”Open the database failed. ”);

} else {

 

NSString *qsql = @”SELECT cdate,content FROM Note”;

 

sqlite3_stmt *statement;

//The pretreatment 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"];

//Execution

while (sqlite3_step(statement) == SQLITE_ROW) {

char *cdate = (char *) sqlite3_column_text(statement, 0);

NSString *nscdate = [[NSString alloc] initWithUTF8String: cdate];

 

char *content = (char *) sqlite3_column_text(statement, 1);

NSString * nscontent = [[NSString alloc] initWithUTF8String: 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;

}

Query all data method similar to query data according to the key difference is that this method, no query condition need not bind parameters. Traversal of the while loop is not used, the if statement.

while (sqlite3_step(statement) == SQLITE_ROW) {

… …

}

Revised data 

Modify the data including: insert, update and delete statements. The 3 SQL statement can have parameters, a parameter binding and query the where clause to bind the same way. Implementation of data modification steps are as follows:

1,Uses the sqlite3_open function to open the database,

2,The use of pre process a SQL statement sqlite3_prepare_v2 function,

3,Uses the sqlite3_bind_text function to bind the parameter,

4,Uses the sqlite3_step function to execute the SQL statement,

5,The use of sqlite3_finalize and sqlite3_close function to release resources.

Modification steps and query data step less than one step extraction field data. Below we have a look the code. Other steps are the same.

Insert the Note 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,@”Open the database failed. ”);

} else {

 

NSString *sqlStr = @”INSERT OR REPLACE INTO note (cdate, content) VALUES (?,?)”;

 

sqlite3_stmt *statement;

//The pretreatment 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

sqlite3_bind_text(statement, 1, [nsdate UTF8String], -1, NULL);  ④

sqlite3_bind_text(statement, 2, [model.content UTF8String], -1, NULL);

 

//Insert

if (sqlite3_step(statement) != SQLITE_DONE) { ⑤

NSAssert(NO, @”Insert data failed. ”);

}

}

 

sqlite3_finalize(statement);  ⑥

sqlite3_close(db);  ⑦

}

return 0;

}

Sqlite3_step Article 5 lines of code (statement) performs an insert statement, the constant SQLITE_DONE execution completed.

Delete the Note 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,@”Open the database failed. ”);

} else {

 

NSString *sqlStr = @”DELETE  from note where cdate =?”;

 

sqlite3_stmt *statement;

//The pretreatment 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

sqlite3_bind_text(statement, 1, [nsdate UTF8String], -1, NULL);

//Insert

if (sqlite3_step(statement) != SQLITE_DONE) {

NSAssert(NO, @”Delete data failed. ”);

}

}

 

sqlite3_finalize(statement);

sqlite3_close(db);

}

return 0;

}

Modify the Note method in NoteDAO.m: 

-(int) modify:(Note*)model

{

NSString *path = [self applicationDocumentsDirectoryFile];

if (sqlite3_open([path UTF8String], &db) != SQLITE_OK) {

sqlite3_close(db);

NSAssert(NO,@”Open the database failed. ”);

} else {

 

NSString *sqlStr = @”UPDATE note set content=? where cdate =?”;

 

sqlite3_stmt *statement;

//The pretreatment 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

sqlite3_bind_text(statement, 1, [model.content UTF8String], -1, NULL);

sqlite3_bind_text(statement, 2, [nsdate UTF8String], -1, NULL);

//Insert

if (sqlite3_step(statement) != SQLITE_DONE) {

NSAssert(NO, @”Modify the data failed. ”);

}

}

 

sqlite3_finalize(statement);

sqlite3_close(db);

}

return 0;

}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by maker at November 21, 2013 - 1:47 AM