iOS Learning note 16-database SQLite

First, the database

In project development, it is usually necessary to deal with the offline cache of data, such as offline caching of news data. Offline caching typically saves data to the project's sandbox. There are several ways to do this:
1. Archive: NSKeyedArchiver
2. Preferences: NSUserDefaults
3. plist Storage:writeToFile

The use of the above can refer to iOS Learning Note 15-serialization, preferences and archiving, but the above three methods have a fatal disadvantage, that is, the inability to store large amounts of data, performance problems, at this time is the time to use the database.

A database is a warehouse that organizes, stores, and manages data according to its structure, and is used to store and manage large amounts of data, to efficiently store large quantities of data, and to efficiently read large volumes of data and be powerful.

The storage structure of the database is similar to Excel, in tables (table). A table consists of multiple fields (columns, attributes, column), each row of data in the table is called a record, but not a simple table, and the relationship between tables and tables, which is now the mainstream relational database.

Second, the introduction of SQLite

SQLite is a lightweight, embedded database that is used by both Android and iOS developers in the SQLite database.
It is characterized by:
1. It occupies very low resources, in the embedded device, may only need hundreds of K of memory is enough.
2. It is faster than the two well-known databases of MySQL and PostgreSQL.
3. It is a C language framework, strong cross-platform.

We are now in the SQLite3 era, the next 3 is the version, now we start to use it.

Third, the use of SQLite3 commonly used database operations are: "This chapter on the 5 steps to explain"

1. Create a database
2. Create a table
3. Inserting data into a table
4. Reading data from a table
5. Close the database

To use SQLite3 in iOS, you need to import the Libsqlite3 library in Xcode

One of the two above is possible, and then we need to add a header file to the project and define a database handle that controls all the operations of the database.

1. Open the database using the C language function as follows:

/* 打开数据库 */int sqlite3_open(  constchar *filename,   /* 数据库路径(UTF-8) */  sqlite3 **pDb           /* 返回的数据库句柄 */);

Here is an example:

/ * Open Database * /- (void) OpenDatabase: (NSString*) dbname{//Generate sandbox file path    NSString*directory = [Nssearchpathfordirectoriesindomains (Nsdocumentdirectory,nsuserdomainmask,YES) Lastobject];NSString*filepath = [Directory Stringbyappendingpathcomponent:dbname];//Open database, if database exists directly open, if database does not exist, create and open    intresult = Sqlite3_open (FilePath. Utf8string, &_database);if(Result = = SQLITE_OK) {NSLog(@"Open Database Success"); }Else{NSLog(@"Open Database Fail"); }}

2. Execution does not return a C-language function that is worth using for SQL statements:

/* 执行没有返回的SQL语句 */int sqlite3_exec(  sqlite3 *db,                               /* 数据库句柄 */  constchar *sql,                           /* SQL语句(UTF-8) */  int (*callback)(void*,int,char**,char**),  /* 回调的C函数指针 */  void *arg,                                 /* 回调函数的第一个参数 */  char **errmsg                              /* 返回的错误信息 */);

Usage examples:

/* Execute SQL statement with no return value */-(void ) ExecuteNonQuery: (nsstring  *) sql{if  (!_database) {return ; } char  *error; //Execute SQL statement with no return value  int  result = Sqlite3_exec (_database, Sql, null , null , &error); if  (Result = = SQLITE_OK) {nslog  (@ "Execute SQL Query Success" ); } else  {nslog  (@, error); }}

3. Execute the C-language function used by the SQL statement with the return value:

/* 执行有返回结果的SQL语句 */int sqlite3_prepare_v2(  sqlite3 *db,            /* 数据库句柄 */  constchar *zSql,       /* SQL语句(UTF-8) */  int nByte,              /* SQL语句最大长度，-1表示SQL支持的最大长度 */  sqlite3_stmt **ppStmt,  /* 返回的查询结果 */  constchar **pzTail     /* 返回的失败信息*/);

Since there is a return result, there are some C-language functions for handling the returned results:

#Pragma mark-method for locating records/ * Locate a record in the query results * /intSqlite3_step (sqlite3_stmt *stmt);/ * Gets the number of field names for the current location record * /intSqlite3_column_count (sqlite3_stmt *stmt);/ * Gets the first field name of the current location record * /Const Char* Sqlite3_column_name (sqlite3_stmt *stmt,intICOL);# Pragma mark-method to get field values/ * Get binary data * /Const void* SQLITE3_COLUMN_BLOB (sqlite3_stmt *stmt,intICOL);/ * Get floating-point data * /DoubleSqlite3_column_double (sqlite3_stmt *stmt,intICOL);/ * Get Integer data * /intSqlite3_column_int (sqlite3_stmt *stmt,intICOL);/ * Get Text data * /ConstUnsignedChar* Sqlite3_column_text (sqlite3_stmt *stmt,intICOL);

Usage examples:

/ * Execute SQL statement with return value * /- (Nsarray*) ExecuteQuery: (NSString*) sql{if(!_database) {return Nil; }Nsmutablearray*array = [NsmutablearrayArray]; Sqlite3_stmt *stmt;//Save query Results    //Execute SQL statement, return result saved in stmt    intresult = SQLITE3_PREPARE_V2 (_database, SQL. Utf8string, -1, &stmt,NULL);if(Result = = SQLITE_OK) {//Each time a record is fetched from the stmt, the Sqlite_row is returned successfully, and the Sqlite_done is returned until all gets completed.         while(Sqlite_row = = Sqlite3_step (stmt)) {//Get a record of how many columns            intColumnCount = Sqlite3_column_count (stmt);//Save a record as a dictionary            nsmutabledictionary*dict = [nsmutabledictionaryDictionary]; for(inti =0; i < ColumnCount; i++) {//Get the field name of column I                Const Char*name = Sqlite3_column_name (stmt, i);//Get the field value of column I                Const unsigned Char*value = Sqlite3_column_text (stmt, i);//Save in Dictionary                NSString*NAMESTR = [NSStringStringwithutf8string:name];NSString*VALUESTR = [NSStringStringwithutf8string: (Const Char*) value];            DICT[NAMESTR] = valuestr; } [array addobject:dict];//Add a dictionary store for the current record} sqlite3_finalize (stmt);//stmt need to manually free up memorystmt =NULL;NSLog(@"Query Stmt Success");returnArray }NSLog(@"Query Stmt Fail");return Nil;}

4. Close the C-language function used by the database:

/* 关闭数据库 */int sqlite3_close(sqlite3 *db);

Usage examples:

/* 关闭数据库 */- (void)closeDatabase{    //关闭数据库    int result = sqlite3_close(_database);    if (result == SQLITE_OK) {        NSLog(@"Close Database Success");        NULL;    else {        NSLog(@"Close Database Fail");    }}

Four, SQLite combined with SQL statements

In addition to using the C language function in the Libsqlite library, it is not possible to complete the operation of the database, but also to use the SQL statement, is a control of the database language "a big knowledge."

Here is a simple list of some commonly used SQL statements:

1. To create a table:
   create table 表名称(字段1,字段2,……,字段n,[表级约束])[TYPE=表类型];
2. Insert Record:
   insert into 表名(字段1,……,字段n) values (值1,……,值n);
3. To delete a record:
   delete from 表名 where 条件表达式;
4. To modify a record:
   update 表名 set 字段名1=值1,……,字段名n=值n where 条件表达式;
5. To view records:
   select 字段1,……,字段n from 表名 where 条件表达式;

The following is a combination of SQL statements to complete database operations, using the method defined above:

/ * Combine SQL statements to manipulate the database * /- (void) sqlite3test{//Open SQLite database[ Selfopendatabase:@"Sqlite3_database.db"];//create a table in the database[ Selfexecutenonquery:@"CREATE TABLE mytable (num varchar (7), name varchar (7), sex char (1), primary key (num));"];//Insert a record in a table[ Selfexecutenonquery:@"INSERT INTO MyTable (num,name,sex) VALUES (0, ' liuting ', ' m ');"]; [ Selfexecutenonquery:@"INSERT INTO MyTable (num,name,sex) VALUES (1, ' Zhangsan ', ' f ');"]; [ Selfexecutenonquery:@"INSERT INTO MyTable (Num,name,sex) VALUES (2, ' Lisi ', ' m ');"]; [ Selfexecutenonquery:@"INSERT INTO MyTable (Num,name,sex) VALUES (3, ' Wangwu ', ' f ');"]; [ Selfexecutenonquery:@"INSERT INTO MyTable (num,name,sex) VALUES (4, ' xiaoming ', ' m ');"];//Read data from tables in the database    Nsarray* result = [ Selfexecutequery:@"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); }    }    [ SelfCloseDatabase];}/ * Modify, delete the original database file, and then create and open the database * /- (void) OpenDatabase: (NSString*) dbname{//Generate sandbox file path    NSString*directory = [Nssearchpathfordirectoriesindomains (Nsdocumentdirectory,nsuserdomainmask,YES) Lastobject];NSString*filepath = [Directory Stringbyappendingpathcomponent:dbname];//Determine if the file is not in    Nsfilemanager*manager = [NsfilemanagerDefaultmanager];if([manager Fileexistsatpath:filepath]) {//file exists, it is deleted[Manager Removeitematpath:filepath Error:NULL]; }//Open database, keep Sqlite3 database object _database, return value discriminant whether open successfully    intresult = Sqlite3_open (FilePath. Utf8string, &_database);if(Result = = SQLITE_OK) {NSLog(@"Open Database Success"); }Else{NSLog(@"Open Database Fail"); }}

If your C language base is better, not feel that use is not so difficult, but this use lacks some object-oriented ideas, we will still a bit unaccustomed, okay, next I will speak SQLite a lightweight package third-party open Source Library Fmdb, Make it easy to operate SQLite database, please look forward to!

Seek attention, ask local tyrants to reward, if there is a problem can be put forward in the comment area below, O (∩_∩) o ha!

iOS Learning note 16-database SQLite