Use of the iOS development database SQLite

The iOS system comes with Core Data persistence, and Core Data can use a graphical interface to create objects, but Core Data not relational databases, and is Core Data better at managing data persisted on devices created by user-created objects. However, in order to process large amounts of data, it is preferable to choose SQL relational databases to store the data.
Core DataSQLite is also used to store data in the background, but developers cannot access the data directly, only through Core Data the API provided, and if the data is accessed manually by the SQLite user, an Core Data error will occur.

SQLite Library

SQLite is an C open source library written in a language that implements a self-contained SQL关系型数据库引擎 , SQLite large amount of data that can be manipulated using storage, and as a relational database we can create multiple tables in a database to solve a large number of data duplication problems. And the SQLite library is optimized for use on mobile devices.
Because SQLite the interface uses C write, and is a superset, it Objective-C C can be used directly in the project SQLite .

Design a Database

Before you start, think about what data you need to store, and then how to design the database.
First we design a database to store personnel information as follows:

ID	name	age	Email	region
1	jhon	[	] [email protected]	bei Jing
2	Peter		[email protected]	sha Nghai
3	July		[email protected]	bei Jing
4	elev		[email protected]	Shen Zhen
5	ribet		[email protected]	bei Jing

The above is all the personnel information, the actual may be much more than this. But we've found region that there's a lot of data in this row that repeats itself. Many people may come from the same place, in order to avoid this situation we should re-create a table to store region This column of information separately and then reference the information in the table in this table region . Of course, we can also region add more information such as detailed address in the table. Now create two tables as people region shown below

• People table

Age Region

ID	name		Email
1	Jhon	20	[Email protected]	1
2	Peter	20	[Email protected]	2
3	July	20	[Email protected]	1
4	Elev	20	[Email protected]	3
5	Ribet	20	[Email protected]	1

• Region table

RegionID	RegionInfo	Address
1	Beijing	Fengtai
2	Shanghai	Jingan
3	Shenzhen	Futian

Create a database using SQLite

To familiarize yourself with SQLite the statement, open the shell SQLite command line to create a database

• Open CREATE Database
  Open shell switch to specified directory input

sqlite3 database.db

This line of command is sqlite to start the command line and create a new database database.db and append the data to the command line
You have entered the sqlite command line by entering to .help display which commands you can use, enter .databases to see which databases are currently attached to the current command-line tool. Enter .quit or .exit exit the current command-line tool

• Create a table

create table "main"."people" ("id" integer primary key autoincrement not null, "name" text,"age" integer,"email" text,"region" integer);

This command creates a table and sets the people id field as the primary key primary key to designate it as an autoincrement autogrow field. Indicates that id the database is automatically generated without providing a value. The following represents the fields that are contained in the table.

Because you want to design two tables, you also need to create a region table

create table "main"."region" ("regionid" integer primary key autoincrement not null, "regioninfo" text,"address" text not null);

• Add data
  Two tables have been created successfully at this time. We're going to add data in.

insert into "main"."people" ("name","age","email","region") values (‘jhon‘,‘20‘,‘[email protected]‘,‘1‘);

This succeeded people in inserting a data into the table successfully. This writing efficiency is lower. Only one piece of data can be inserted at a time don't worry about SQLite importing files directly into the database. It can be an ordinary file file or an Excel file. Below we create a people.txt file format as follows:

1  Jhon 20  jhon @mail  1  2  Peter 20  peter @mail  2  3  July 20  july @mail  1  4  elev 20  elev @mail  3  5  ribet 20
      ribet @mail  1   

Note that the gap between each field is separated by a tab character \t , that is, each field is split with a key to create the file tab . The order of the fields must be the same as the order in the table and then people.txt import the file into the people table

.separator"\t"

\tdivide the field according to, and then enter

.import"people.txt" people

Importing the People.txt file into the People table will now prompt the following error message

INSERT failed: UNIQUE constraint failed: people.id

Don't worry. This means that there is already a data ID of 1, so this data insertion failed because we had previously inserted a piece of data manually. The inserted data can be checked by the following command

select * from people;

You then create a file in the same way region.txt and import it into the region table.

Note
Using SQLite the command line may appear ...> This indicates an instruction input error, press to ctrl+d exit

• Query data above has been added data through select instructions can query the data

select * from people;

Querying all data in a popple table

Linked table Data

select name,regioninfo from people,region where people.region=region.regionid;

Output results

jhon    beijingpeter   shanghaijuly    beijingelev    shenzhenribet   beijing

peopleFind the region name result that matches the field in the and table and regioninfo query People.region=region.regionid only, if there is no such condition then the 5*3=15 data will appear

If you want to find people in a region use where to filter criteria

select name,regioninfo from people,region where people.region=region.regionid and region.regioninfo="beijing";

Output results

jhon    beijingjuly    beijingribet   beijing

Use of SQLite in iOS

You should refer to the SQLite library in your project before you begin. TARGETS->General->Linked Frameworks and Librariesas shown

Import the previously created database.db file into the project and introduce the sqlite3.h header file

#import <sqlite3.h>

Using SQLite takes a few steps

• Declaring a class variable sqlite3 to hold a reference to the database
• Using the sqlite3_open Open database
• Create SQLite statement
• To create a SQLite statement objectsqlite3_stmt
• Prepare the SQLite statementsqlite3_prepare_v2
• Start traversing resultssqlite3_step

Initialize Open database

sqlite3 * database;-(void)initDatabase{    NSString *path = [[NSBundle mainBundle] pathForResource:@"database" ofType:@"db"];       if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {        NSLog(@"open database");    }    else{        sqlite3_close(database);        NSLog(@"error %s",sqlite3_errmsg(database));    }}

Open database If the returned status code is not SQLITE_OK so open failed to close the database and output an error message

Querying data

-(void) operatedatabase{Const Char* sql ="Select Name,regioninfo from People,region where People.region=region.regionid"; Sqlite3_stmt *statement;//Create SQL statement object    intSqlresult = SQLITE3_PREPARE_V2 (database, SQL,-1, &statement,NULL);//Prepare SQL statements    if(sqlresult== SQLITE_OK)//Are you ready to finish{ while(Sqlite3_step (statement) = = Sqlite_row)//Start traversing query results{NSLog(@"Name%s, region%s", Sqlite3_column_text (statement,0), Sqlite3_column_text (statement,1)); }    }}

Output Result:

name jhon, region beijingname peter, region shanghainame july, region beijingname elev, region shenzhenname ribet, region beijing

sqlite3_prepare_v2The first parameter is the database connection, the second is the SQL statement, the third is the length of the statement passed in-1 for the first null terminator, the fourth is to return a statement object, and fifth is to return a pointer to the first byte of the SQL statement.
Begins the traversal of the result when the sqlite3_prepare_v2 status code SQLITE_OK is returned.
sqlite3_stepUsed to traverse the result if returned to SQLITE_ROW indicate that the next line is ready to start the query. So here's one while for convenience so the results of the query
In the process of traversal to get the result through a function to obtain the traversal results

Sqlite_apiConst void*sqlite_stdcall Sqlite3_column_blob (sqlite3_stmt*,intICOL); Sqlite_apiintSqlite_stdcall Sqlite3_column_bytes (sqlite3_stmt*,intICOL); Sqlite_apiintSqlite_stdcall SQLITE3_COLUMN_BYTES16 (sqlite3_stmt*,intICOL); Sqlite_apiDoubleSqlite_stdcall sqlite3_column_double (sqlite3_stmt*,intICOL); Sqlite_apiintSqlite_stdcall Sqlite3_column_int (sqlite3_stmt*,intICOL); Sqlite_api sqlite3_int64 sqlite_stdcall Sqlite3_column_int64 (sqlite3_stmt*,intICOL); Sqlite_apiConst unsigned Char*sqlite_stdcall Sqlite3_column_text (sqlite3_stmt*,intICOL); Sqlite_apiConst void*sqlite_stdcall sqlite3_column_text16 (sqlite3_stmt*,intICOL); Sqlite_apiintSqlite_stdcall Sqlite3_column_type (sqlite3_stmt*,intICOL); Sqlite_api sqlite3_value *sqlite_stdcall sqlite3_column_value (sqlite3_stmt*,intICOL);

The above is the supported result type, the first parameter is the SQL statement object, and the second is the information for which column to get.

parameterized queries

The above situation every time the SQL statement is dead, if you want to change a condition you need to write a statement, fortunately, SQLite support parameterized query, each time only need to change the query condition can not change the entire SQL statement, if now only want to query the Beijing area of the population information using parameterized query as follows:

-(void) operatedatabase{Const Char* sql ="Select Name,regioninfo from People,region where People.region=region.regionid and regioninfo=?"; Sqlite3_stmt *statement;//Create SQL statement object    intSqlresult = SQLITE3_PREPARE_V2 (database, SQL,-1, &statement,NULL);//Prepare SQL statementsSqlite3_bind_text (statement,1,"Beijing", -1, sqlite_transient);//Binding parameters    if(sqlresult== SQLITE_OK)//Are you ready to finish{ while(Sqlite3_step (statement) = = Sqlite_row)//Start traversing query results{NSLog(@"Name%s, region%s", Sqlite3_column_text (statement,0), Sqlite3_column_text (statement,1)); }    }}

Output Result:

name jhon, regionbeijingname july, regionbeijingname ribet, regionbeijing

Visible in the conditional SQL that needs to be changed ? instead, and then use the sqlite3_bind_text function to bind the parameters. Functions that are bound differently by type are also different

Sqlite_apiintSqlite_stdcall Sqlite3_bind_blob (sqlite3_stmt*,int,Const void*,intNvoid(*) (void*)); Sqlite_apiintSqlite_stdcall sqlite3_bind_blob64 (sqlite3_stmt*,int,Const void*, Sqlite3_uint64,void(*) (void*)); Sqlite_apiintSqlite_stdcall sqlite3_bind_double (sqlite3_stmt*,int,Double); Sqlite_apiintSqlite_stdcall Sqlite3_bind_int (sqlite3_stmt*,int,int); Sqlite_apiintSqlite_stdcall Sqlite3_bind_int64 (sqlite3_stmt*,int, Sqlite3_int64); Sqlite_apiintSqlite_stdcall Sqlite3_bind_null (sqlite3_stmt*,int); Sqlite_apiintSqlite_stdcall Sqlite3_bind_text (sqlite3_stmt*,int,Const Char*,int,void(*) (void*)); Sqlite_apiintSqlite_stdcall sqlite3_bind_text16 (sqlite3_stmt*,int,Const void*,int,void(*) (void*)); Sqlite_apiintSqlite_stdcall Sqlite3_bind_text64 (sqlite3_stmt*,int,Const Char*, Sqlite3_uint64,void(*) (void*),unsigned Charencoding); Sqlite_apiintSqlite_stdcall Sqlite3_bind_value (sqlite3_stmt*,int,Constsqlite3_value*); Sqlite_apiintSqlite_stdcall Sqlite3_bind_zeroblob (sqlite3_stmt*,int,intn);

All functions that support binding types are listed above.

End

This article only lists the SQLite common basic methods, the actual development of the database may be much more complex, but also to consider the problem of data competition thread security. In particular, they should be in the development of their own practice.

Use of the iOS development database SQLite