Use SQLite3 to persistently save application data.

Use SQLite3 to persistently save application data.

Preface

SQL is a database query language used to access data and query, update, and manage relational database systems. It has become a standard language for mainstream databases because of its powerful query functions and simple syntax. SQLite3 is an embedded database that does not require server support. It embeds SQL statements into general programming languages. SQL statements are used to extract and operate data in the database, the extracted data is submitted to the program line by line, and other statements in the program are responsible for data processing. Sqlite3 program interface is based on the C language, it is very effective in the storage and retrieval of a large amount of data, and can aggregate complex data, faster processing of data to obtain results. Compared with object processing data, the biggest advantage is that you do not have to load all objects into the memory, but just extract objects that meet specific conditions.

SQLite3 development process in the project

1. Design and Production Databases

Step 1. download and install the SQLite Manager tool: First open Firefox, select "additional components" under the tool, enter "SQLite" in the search box in the upper right corner of the browser, and then select and install SQLite Manager.

 

Step 2. After the installation is complete, the system will prompt whether to restart the browser. After restarting, click the tool again and you will see that SQLite Manager is already in the options. Open it and you will see the database tool interface. Here we have completed the download and installation of the SQLite Manager tool.

Step 3: Create a SQLite3 Database: first, select the blank new icon in SQLite Manager to create a database and enter the database name NoteSQL. sqlite.

Step 4. Click the Create Table icon to Create a new Table and name it NoteSQL. Edit the table content. Finally, the database is created.

 

2. Create a project and import the database file to the project.

Step 1: add the database you just created to your project directory.

Step 2. Click project name-> TARGETS-> Build Phases, select Link Binary With Libraires, and add the libsqlite3.tbd file.

　　

3. Use a database to write and read data

Step 1: copy the database to the project database and copy it to the Documents file directory of our application.

// Database copy operation-(void) createDatabaseIfNeeded :( NSString *) filename {BOOL success; NSFileManager * fileManager = [NSFileManager defaultManager]; NSError * error; NSArray * paths = require (NSDocumentDirectory, NSUserDomainMask, YES); NSString * documentsDirectory = [paths objectAtIndex: 0]; NSString * writableDBPath = [documentsDirectory stringByAppendingPathComponent: filename]; NSLog (@ "% @", writableDBPath); success = [fileManager fileExistsAtPath: writableDBPath]; if (success) {return ;} NSString * defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent: filename]; success = [fileManager copyItemAtPath: defaultDBPath toPath: writableDBPath error: & error]; if (! Success) {NSAssert1 (0, @ "Failed to create writable database file with message '% @'.", [error localizedDescription]);}

Step 2. Open the database

// Open the database-(void) openDB {NSArray * paths = require (NSDocumentDirectory, NSUserDomainMask, YES); NSString * implements enthpath = [[paths objectAtIndex: 0] stringByAppendingPathComponent: @ "NoteSQL. sqlite "]; int returnValue = sqlite3_open ([documenthPath UTF8String], & database); if (returnValue! = SQLITE_ OK) {sqlite3_close (database); NSAssert1 (0, @ "Failed to open database with message '% s'.", sqlite3_errmsg (database ));}}

Step 3: Open the database and insert data

If (sqlite3_open ([self dataFilePath] UTF8String], & _ database )! = SQLITE_ OK) {sqlite3_close (_ database); NSAssert (0, @ "Failed to open database");} // insert NSString * insertSQL = [[NSString alloc] initWithFormat: @ "insert into NoteSQL (title, date, types, content) values ('% @', '% @')", titleStr, dateStr, typesStr, contentStr]; char * errorMsg2; if (sqlite3_exec (_ database, [insertSQL UTF8String], NULL, NULL, & errorMsg2 )! = SQLITE_ OK) {NSAssert1 (0, @ "Error updating tables: % s", errorMsg2); sqlite3_free (errorMsg2 );}
Sqlite3_close (_ database );

Step 4. Open the database and read data from it

If (sqlite3_open ([self dataFilePath] UTF8String], & database )! = SQLITE_ OK) {sqlite3_close (database); NSAssert (0, @ "Failed to open database");} NSString * query = @ "select * from NoteSQL order by date "; sqlite3_stmt * statement; if (sqlite3_prepare_v2 (database, [query UTF8String],-1, & statement, nil) = SQLITE_ OK) {while (sqlite3_step (statement) = SQLITE_ROW) {char * titleChar = (char *) sqlite3_column_text (statement, 0); char * dateChar = (char *) sqlite3_column_text (statement, 1); char * typesChar = (char *) sqlite3_column_text (statement, 2); char * contentChar = (char *) sqlite3_column_text (statement, 3); // convert the C string to NSString * title = [[NSString alloc] initwithuf8string: titleChar]; NSString * date = [[NSString alloc] initwithuf8string: dateChar]; NSString * types = [[NSString alloc] initwithuf8string: typesChar]; NSString * content = [[NSString alloc] initwithuf8string: contentChar]; recordInfo * record = [[recordInfo alloc] init]; record. title = [title copy]; record. types = [types copy]; record. date = [date copy]; record. content = [content copy]; [mutArray addObject: record];} sqlite3_finalize (statement );}