IOS study notes 16-database SQLite

IOS study notes 16-database SQLite
I. Databases

In project development, data is usually cached offline, for example, offline cache of news data. Offline cache generally stores data in the project sandbox. There are several methods:
1. Archiving:NSKeyedArchiver
2. preference settings:NSUserDefaults
3. plist storage:writeToFile

For the above usage, refer to iOS Study Notes 15-serialization, preference setting, and archiving. However, the above three methods all have a fatal drawback, that is, they cannot store large volumes of data, there is a performance problem, at this time it is time to use the database.

A Database is a warehouse that organizes, stores, and manages data according to the data structure. It is used to store and manage a large amount of data. It can efficiently store large volumes of data or efficiently read large volumes of data, powerful functions.

The storage structure of the database is similar to that of excel, in the unit of table. A table consists of multiple fields (columns, attributes, and columns). Each row in the table is a record, but not a simple table. It also has a relationship between the table and the table, that is, the current mainstream relational databases.

II. Introduction to SQLite

SQLite is a lightweight embedded database, which is developed and used by Android and iOS.
Its features:
1. It occupies very low resources. In embedded devices, it may only need several hundred KB of memory.
2. It processes faster than MySQL and PostgreSQL, two famous databases.
3. It is a C language framework with strong cross-platform performance.

We are now in the SQLite3 era, and later 3 is the version. Now let's start using it.

Iii. SQLite3 Common Database Operations: [This chapter describes these five steps] Create a database, create a table, insert data to a table, read data from the table, and close the database.

To use SQLite3 in iOS, You need to import libsqlite3 library in Xcode

Either of the above two can be used. then we also need to add a header file and define a database handle in the project. This database handle controls all database operations.

1. The C language functions used to open the database are as follows:

/* Open the database */int sqlite3_open (const char * filename,/* database path (UTF-8) */sqlite3 ** pDb/* Returned Database handle */);

The following is an example:

/* Open the database */-(void) openDatabase :( NSString *) dbname {// generate the sandbox file path NSString * directory = [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; NSString * filePath = [directory stringByAppendingPathComponent: dbname]; // open the database if it exists. If the database does not exist, create and open int result = sqlite3_open (filePath. UTF8String, & _ database); if (result = SQLITE_ OK) {NSLog (@ "Open Database Success");} else {NSLog (@ "Open Database Fail ");}}

2. The execution does not return the C language functions worth the SQL statement:

/* Execute the SQL statement not returned */int sqlite3_exec (sqlite3 * db,/* database handle */const char * SQL,/* SQL statement (UTF-8) */int (* callback) (void *, int, char **, char **),/* callback C function pointer */void * arg, /* The first parameter of the callback function */char ** errmsg/* returned error message */);

Instance used:

/* Execute the SQL statement without return values */-(void) executeNonQuery :( NSString *) SQL {if (! _ Database) {return;} char * error; // execute the SQL statement int result = sqlite3_exec (_ database, SQL. UTF8String, NULL, NULL, & error); if (result = SQLITE_ OK) {NSLog (@ "Execute SQL Query Success ");} else {NSLog (@ "Execute SQL Query Fail error: % s", error );}}

3. C language functions used to execute SQL statements with returned values:

/* Execute the SQL statement with returned results */int sqlite3_prepare_v2 (sqlite3 * db,/* database handle */const char * zSql,/* SQL statement (UTF-8) */int nByte,/* maximum SQL statement length.-1 indicates the maximum length supported by SQL */sqlite3_stmt ** ppStmt, /* returned query result */const char ** pzTail/* returned Failure Information */);

Since there are returned results, there are also some C language functions for how to handle the returned results:

# Pragma mark-Method for locating a record/* locate a record in the query result */int sqlite3_step (sqlite3_stmt * stmt ); /* obtain the number of fields in the current location record */int sqlite3_column_count (sqlite3_stmt * stmt ); /* obtain the names of the fields in the current location record */const char * sqlite3_column_name (sqlite3_stmt * stmt, int iCol ); # pragma mark-method for obtaining Field Values/* for obtaining binary data */const void * sqlite3_column_blob (sqlite3_stmt * stmt, int iCol ); /* obtain floating point data */double sqlite3_column_double (sqlite3_stmt * stmt, int iCol);/* obtain Integer Data */int sqlite3_column_int (sqlite3_stmt * stmt, int iCol ); /* Get Text Data */const unsigned char * sqlite3_column_text (sqlite3_stmt * stmt, int iCol );

Instance used:

/* Execute the SQL statement with returned values */-(NSArray *) executeQuery :( NSString *) SQL {if (! _ Database) {return nil;} NSMutableArray * array = [NSMutableArray array]; sqlite3_stmt * stmt; // Save the query result // execute the SQL statement, int result = sqlite3_prepare_v2 (_ database, SQL. UTF8String,-1, & stmt, NULL); if (result = SQLITE_ OK) {// each time a record is obtained from stmt, SQLITE_ROW is returned successfully until all records are obtained, SQLITE_DONE while (SQLITE_ROW = sqlite3_step (stmt) will be returned {// obtain the number of columns of a record int columnCount = sqlite3_column_count (stmt ); // save a record as a dictionary NSMutableDictionary * dict = [NSMutableDictionary dictionary]; for (int I = 0; I <columnCount; I ++) {// obtain the field name const char * name = sqlite3_column_name (stmt, I) in column I ); // obtain the field value const unsigned char * value = sqlite3_column_text (stmt, I) in column I; // save it to the dictionary NSString * nameStr = [NSString stringwithuf8string: name]; NSString * valueStr = [NSString stringwithuf8string :( const char *) value]; dict [nameStr] = valueStr;} [array addObject: dict]; // Add dictionary storage for the current record} sqlite3_finalize (stmt); // stmt needs to manually release the memory stmt = NULL; NSLog (@ "Query Stmt Success"); return array ;} NSLog (@ "Query Stmt Fail"); return nil ;}

4. Disable the C language functions used by the database:

/* Close the database */int sqlite3_close (sqlite3 * db );

Instance used:

/* Close the database */-(void) closeDatabase {// close the database int result = sqlite3_close (_ database); if (result = SQLITE_ OK) {NSLog (@ "Close Database Success"); _ database = NULL;} else {NSLog (@ "Close Database Fail ");}}

4. SQLite combined with SQL statements

In addition to using C functions in the libsqlite library, you cannot perform database operations. You also need to use SQL statements, which is a language for controlling databases ].

Here we will briefly list some common SQL statements: Create a table:
Create table Name (Field 1, Field 2 ,......, Field n, [Table-level constraints]) [TYPE = table type];Insert record:
Insert into Table Name (Field 1 ,......, Field n) values (value 1 ,......, Value n );Delete record:
Delete from table name where condition expression;Modification record:
Update table name set field name 1 = value 1 ,......, Field name n = value n where condition expression;View records:
Select Field 1 ,......, Field n from table name where condition expression;The following describes how to complete database operations using SQL statements:

/* Use SQL statements to operate the database */-(void) sqlite3Test {// open the SQlite database [self openDatabase: @ "sqlite3_database.db"]; // create a table in the database [self executeNonQuery: @ "create table mytable (num varchar (7), name varchar (7), sex char (1 ), primary key (num); "]; // insert a record in the table [self executeNonQuery: @" insert into mytable (num, name, sex) values (0, 'liuting ', 'M'); "]; [self executeNonQuery: @" insert into mytable (num, name, sex) values (1, 'hangsan ', 'F'); "]; [self executeNonQuery: @ "insert into mytable (num, name, sex) values (2, 'lisi', 'M');"]; [self executeNonQuery: @ "insert into mytable (num, name, sex) values (3, 'hangzhou', 'F');"]; [self executeNonQuery: @ "insert into mytable (num, name, sex) values (4, 'xiaoming', 'M'); "]; // read data from the database table NSArray * result = [self executeQuery: @" select num, name, sex from mytable; "]; if (result) {for (NSDictionary * row in result) {NSString * num = row [@" num "]; NSString * name = row [@ "name"]; NSString * sex = row [@ "sex"]; NSLog (@ "Read Database: num = % @, name = % @, sex = % @ ", num, name, sex) ;}} [self closeDatabase];}/* modify it to delete the existing database file, create and open the database */-(void) openDatabase :( NSString *) dbname {// generate the sandbox file path NSString * directory = [NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; NSString * filePath = [directory stringByAppendingPathComponent: dbname]; // determines whether the file is not in NSFileManager * manager = [NSFileManager defaultManager]; if ([manager fileExistsAtPath: filePath]) {// if the file exists, delete [manager removeItemAtPath: filePath error: NULL];} // open the database and keep the sqlite3 database object _ database, whether the returned value is successfully opened. int result = sqlite3_open (filePath. UTF8String, & _ database); if (result = SQLITE_ OK) {NSLog (@ "Open Database Success");} else {NSLog (@ "Open Database Fail ");}}