Objective-c Visit SQLite

Database related knowledge I will not explain, after all, as long as the SQL language people are the same.

This case is a single view application created in the Xcode7.2 environment to perform the demonstration operation.

First 1th, why use Sqlite3?

In iOS programming, there is no doubt that the most contact is the interface of the code layout and design, data parsing and placement, algorithm of various scratching problems ... In the data parsing and placement of this piece, will involve the operation of the database cache, we all know, iOS mobile phone programming is running on the phone, you can not always go to the server to read the data ah, good, even if you are not bored, mobile phone also run up, traffic not too ah, for mobile phone control or application users, traffic is life, Also, if you always go to the server to read the data, the speed of the mobile phone will also have an impact, after all, there is a network transmission process, so, in order to let users have a better experience, the general recommendation is to download the server data, and then cache it through the database. This eliminates the need to request server data every time, if there is data in the database, directly through the database query can be obtained, and save a time or even multiple server request operation. The sqlite3 is a form of a C-language Access database. Then our code will see, in fact, sqlite3 grammar may be more difficult to understand, but I will give you to introduce.

So 2nd, what are the benefits of using sqlite3?

The advantage of using sqlite3, except the syntax of the various egg pain, the speed of access will be faster than Fmdb. And, with a step-by-step summary and summary, you can clearly know the function of each sentence sqlite3, instead of using the encapsulated statement, as long as the use of it, but do not know why. I personally think that learning to learn it, or to know it, but also know the reason why they can learn the practical.

The first step is to introduce the LIBSQLITE3.TBD static library

The second step is to import the library master header files where you want to use SQLite #import <sqlite3.h>

The third step is to create the SQLite database file

* Sqlite3 provides a form of access manipulation database, if you want to use Sqlite3 to access the manipulation of the database, we first have to have a database, the following is the use of code to generate a database file.

SQLite file name static NSString * Const FILENAME = @ "Sqlite.sqlite";//Get the database file address, this is a concatenation of the address string, is the sandbox path under the Shop.sqlite file// This file name can be arbitrarily named, suffix preferably db ah or sqlite this, to indicate that this is a database file nsstring *filepath = [[Nssearchpathfordirectoriesindomains ( NSDocumentDirectory, Nsuserdomainmask, YES) lastobject] stringbyappendingpathcomponent:filename];

Open the database connection and, if there is one, re-create the connection without it.

* Sqlite3_open is the first sqlite3 statement that we are exposed to, it functions when the database connection is opened, if there is a database file to open it, no re-create the database, this method has an enumerated return value indicates whether the database is open correctly, I use the variable status of int goes to receive this value, and it is judged later. 　　

* Sqlite3_open has two parameters, the first parameter is the path of the database file, the second parameter is a reference to a database, that is, a sqlite3 * type of reference ;

* The filePath.UTF8String:filename itself is a string type of OC , but the sqlite3_open requires that a type C string be passed, so use the utf8string Converts a string type of OC to a string of type C

@interface Viewcontroller () {    sqlite3 *_sqlite;} @end//Open database connection, if there is open, no re-create connection Nsinteger state = Sqlite3_open (filepath.utf8string, &_sqlite);//enumeration value Sqlite_ OK indicates a successful status if (state = = SQLITE_OK)    NSLog (@ "-----Open database successfully-----"), Else    NSLog (@ "-----Open database failed-----");

　　

Fourth step, create a database table

* When we open the database connection, it means that a sqlite. SQLite database file has been created under the program's sandbox path, and there is nothing in this database, we need to create a database table to hold the data.

* Sqlite3_exec is the second sqlite3 statement we are exposed to, which is used to execute statements other than query statements .

It has a return value of an enumeration type, and The enumeration value returned by Sqlite3_open is the same, and here I do not use the variable to save, because I have other forms to get the success of creating the table.

* sqlite3_exec need to pass 5 parameters, the first parameter is the database reference is sqlite3 *_shop, the second argument is the SQL statement to execute ,

The third parameter is the function to execute after the SQL statement is executed, the fourth parameter is the parameter of the function to execute after the SQL statement is executed, and the fifth parameter is the error message after the SQL statement is executed.

The CREATE TABLE statement, if not EXISTS prevents the creation of duplicate tables, AutoIncrement is the autogrow keyword, real is the numeric type const char *SQL = "CREATE table IF not EXISTS t_sqlite (id int Eger PRIMARY KEY autoincrement, Name text not NULL, price real) ";//variable that holds the error message char *errormessage = null;sqlite3_exec (_sqlite , SQL, NULL, NULL, &errormessage);//If there is an error message, it is simpler to represent the statement execution failure than to determine the enumeration value if (errormessage)    NSLog (@ "-----CREATE TABLE failure ErrorMessage:%s-----", errormessage); else    NSLog (@"-----CREATE TABLE successfully-----");

Fifth step, data table manipulation

The so-called data table manipulation is to the data table data additions and deletions, but also the most commonly used functions. That is, insert, UPDATE, DELETE statements are executed.

(1) Increase

* Note: Here I dragged two input boxes (Uitextfield) and a button (UIButton) in the Main.storyboard, and established an association

@interface Viewcontroller () {    sqlite3 *_sqlite;} @property (Weak, nonatomic) Iboutlet Uitextfield *namefield; @property (weak, nonatomic) Iboutlet Uitextfield *pricefield ; @end #pragma mark Increase-(ibaction) Adddataevent: (UIButton *) Sender {    NSString *name = [Self.nameField.text stringbyreplacingoccurrencesofstring:@ "" withstring:@ "";    CGFloat price = Self.priceField.text.floatValue;    Splicing SQL statements inserting data    nsstring *sql = [NSString stringwithformat:@ "INSERT into T_sqlite (name,price) VALUES ('%@ ',%f) ', Name, price];    char *errormessage = NULL;    Sqlite3_exec (_sqlite, SQL. Utf8string, NULL, NULL, &errormessage);    if (errormessage)        NSLog (@ "-----failed to insert data, ErrorMessage:%s-----", errormessage);    else        NSLog (@ "-----Insert data successfully-----");}

　　

(2) by deleting

* Note: Here I drag and drop an input box (Uitextfield) and a Delete button (UIButton) below the new button in Main.storyboard, and create an association

　　　　　　This is to locate the data with the ID key, so the value of this ID must be the ID value of the existing data

@interface Viewcontroller () {    sqlite3 *_sqlite;} @property (Weak, nonatomic) Iboutlet Uitextfield *namefield; @property (weak, nonatomic) Iboutlet Uitextfield *pricefield ; @property (weak, nonatomic) Iboutlet Uitextfield *lbl; @end #pragma mark-delete-(ibaction) Deletedataevent: (UIButton *) Sender {    NSString *lbl = [Self.lbl.text stringbyreplacingoccurrencesofstring:@ "" withstring:@ ""];    Splicing SQL DELETE statement    nsstring *sql = [NSString stringwithformat:@ "Delete from T_sqlite where id=%ld", Lbl.integervalue];    char *error = NULL;    Sqlite3_exec (_sqlite, SQL. Utf8string, NULL, NULL, &ERROR);    if (error)        NSLog (@ "-----Delete data failed with error:%s-----", error);    else         NSLog (@ "-----Delete data successfully-----");}

(3) Change

* Note: Here I have a new Change button (UIButton) in the Main.storyboard, and establish an association

　　　　　　This is to locate the data with the ID key, so the value of this ID must be the ID value of the existing data

#pragma mark-Change-(Ibaction) Updatedataevent: (UIButton *) Sender {    NSString *name = [Self.nameField.text stringbyreplacingoccurrencesofstring:@ "" withstring:@ "";    NSString *price = [Self.priceField.text stringbyreplacingoccurrencesofstring:@ "" withstring:@ ""];    NSString *LBL = [Self.lbl.text stringbyreplacingoccurrencesofstring:@ "" withstring:@ ""];    Stitching SQL statements to update data    nsstring *sql = [nsstring stringwithformat:@ "Update t_sqlite set name= '%@ ', price= '%@ ' where id=%@", n Ame, Price, LBL];    char *error = NULL;    Sqlite3_exec (_sqlite, SQL. Utf8string, NULL, NULL, &ERROR);    if (error)        NSLog (@ "-----failed to update data, error:%s-----", error);     else        NSLog (@ "-----Update data successfully-----");}

　　　　You can observe the above three methods, you will find that in addition to the SQL statement splicing is not the same, the other place is almost identical, so, adding and deleting the operation is relatively simple.

(4) Check

　　　　* The SQLITE3_PREPARE_V2 function is a function that prepares a SQL query for execution, and can be used as a preparation for a SQL query, and it is also the result of returning an enumeration as a preparatory work. SQLITE_OK stands for Ready work OK

Span class= "S1" >* sqlite3_prepare_v2 need to pass in 5sqlite3* _shop , The second parameter is the statement, the third parameter is sql statement length , The fourth parameter is a reference to the query result stmtnull It's OK.

/span> * sqlite3_step (stmt) The function will execute the query and deposit the current record to stmt (sqlite3_stmt *

　　　　* The first execution of Sqlite3_step (stmt) will deposit the first data in the table into stmt, the second execution sqlite3_step (stmt) The second record in the table will be credited to the stmt .

　　　　* that is,while (Sqlite3_step (stmt) ==sqlite_row) will read the records in the table one after the other, and Sqlite_row Enumeration is determined by the fact that there is a read to the data row

#pragma mark-check-(void) Inquirydata {//Query SQL statement const char *SQL = "SELECT * from T_sqlite";    stmt used to remove the query result sqlite3_stmt *stmt = NULL;    Nsinteger state = SQLITE3_PREPARE_V2 (_sqlite, SQL,-1, &stmt, NULL);            Prepare successfully, the SQL statement is correct if (state = = SQLITE_OK)//successfully points to a record while (Sqlite3_step (stmt) = = Sqlite_row) {            The encapsulated entity Shop *shop = [[Shop alloc] init]; sqlite3_column_xxxx function: It is used to read the data rows of different types of data, the function's return value is read to the data content, the function requires 2 parameters,
The first parameter is the stmt that holds the data, the second parameter is the data column subscript//reads the NO. 0 column of data stored in stmt shop.pid= sqlite3_column_int (stmt, 0);            Reads the 1th column of data stored in stmt const char *name = (const char*) sqlite3_column_text (stmt, 1);            Reads the 2nd column of data stored in stmt const char *price = (const char*) sqlite3_column_text (stmt, 2);            Converts a string of type C to a string of OC type and stores shop.name = [NSString stringwithutf8string:name];        Converts a string of type C to a string of OC type and stores shop.price = [NSString Stringwithutf8string:price]; } else NSLog (@ "-----Read data failed-----");

The basic use of sqlite3 is roughly these, usually we want to use only these operations, other SQL operations normally will not be involved.

The next step is to apply the above code repeatedly to your project!

Add Source Address: Source Link password: 5xgm

Objective-c Visit SQLite