Database sqlite3 using-ios in the reference method

A simple explanation

To use SQLite3 in iOS, first add the library file Libsqlite3.dylib and import the primary header file.

　　

Import header file, you can use the function in the library (pure C language)

Ii. Specific Instructions

Create a new project and place four buttons (add, delete, modify, query) in the main interface of the project.

1.sqlite3_open (< #const char *filename#>, < #sqlite3 **ppdb#>) function for some description:

(1) Function: Pass a file name to him, it will automatically detect whether the file exists, if not, it will automatically create the corresponding file (here is the database file, just created as empty).

(2) Parameter: its first parameter is the name of the file (need to convert to C language), the second parameter is the database instance, Sqlite3 *db;

Description: Sqlite3 is a type, DB is the database of the handle, is the symbol of the database, if you want to make the deletion and check, you have to operate DB this instance.

(3) Return value: Its return value is of type int, depending on the return value of the function, whether the open database file succeeds or fails, if the return value is SQLITE_OK, it is successful, otherwise it fails.

2. Open the Database

Implement code and display:

To view the database files created in the sandbox:

　　

Double-click Open to view the Open database connection name students, which defaults to the filename prefix, and the database was created successfully.

　　

3. Create a table

Function Description:

　　

Parameters: The first parameter is the database handle (DB), the second parameter is the SQL statement, the third parameter is the callback parameter, is a pointer to the function, if the callback front of the * change to ^ is a block code snippet, the fourth parameter can be written null, the fifth parameter is an error message, Used for code debugging.

1     //1. Open the database file (if the database file does not exist, the function automatically creates the database file) 2     int result = Sqlite3_open (cFileName, &db); 3     if (result== SQLITE_OK) {        //Open successfully 4         NSLog (@ "Open database Successfully"); 5          6     //2. CREATE TABLE 7         const char  *sql= "CREATE table IF not EXISTS t_students (id integer PRIMARY KEY autoincrement,name text not null,age integer not NULL); "; 8         Char *errmsg=null; 9         result = sqlite3_exec (db, SQL, NULL, NULL, &ERRMSG),         if (RESULT==SQLITE_OK) {             NSLog (@ "creation success"),         }else13         {             NSLog (@ "failure----%s", errmsg);         }16     }else17     {         NSLog (@ "Failed to open database");     

After execution, the CREATE table is opened to view:

Debugging tips:

1    if (RESULT==SQLITE_OK) {2             NSLog (@ "creation success"), 3         }else4         {5//            NSLog (@ "failure----%s", errmsg); 6             printf ("Failure of the table---%s----%s---%d", errmsg,__file__,__line__); 7         }

__FILE__ macro Print file name,

__LINE__ a macro to print the line number.

　　

4. Inserting data

Implementation code:

1-(ibaction) Insert {2 for     (int i=0; i<20; i++) {3         //1. Splicing SQL statements 4         nsstring *name=[nsstring stringwithform at:@ "Wen Xiao--%d", Arc4random_uniform (100)]; 5         int Age=arc4random_uniform (+10), 6         nsstring *sql=[nsstring stringwithformat:@ "INSERT into T_students ( Name,age) VALUES ('%@ ',%d); ", Name,age]; 7          8         //2. Execute SQL statement 9         char *errmsg=null;10         sqlite3_exec (self.db, SQL. Utf8string, NULL, NULL, &ERRMSG),         one if (errmsg) {//If there is an error message             (@ "Insert data failed--%s", errmsg);         Else14         {             NSLog (@ "Insert data success");         }17     }18}

Print View:

View the data in the T_students table in the database:

5. Choose (select) query operation

Function Description:

The select operation can also be implemented using the SQLITE3_EXEC function, but typically the following function is used.

　　

Parameters: The first parameter is the database handle, the second argument is the SQL statement, the third parameter is the length of SQL (if set to-1, the system will automatically calculate the length of the SQL statement), the fourth parameter is used to fetch the data, the fifth parameter is the tail is generally not used to directly write null.

Example code:

1-(ibaction) Select {2     const char *sql= "Select Id,name,age from t_students WHERE age<20;"; 3     sqlite3_stmt *stmt=null; 4      5     //Pre-query Preparation 6     if (SQLITE3_PREPARE_V2 (self.db, SQL, 1, &stmt, NULL ) {==SQLITE_OK) {//sql Statement no problem 7         NSLog (@ "query Statement no Problem"); 8          9         //each time the Sqlite3_step function is called, stmt points to the next record ten while         ( Sqlite3_step (stmt) ==sqlite_row) {//Find a record             //Take Out data (             1) Remove the value of the No. 0 column field (value of type int)             int id=sqlite3_ Column_int (stmt, 0); +             //(2) Take out the value of the 1th column field (the value of text type)-             const unsigned char *name=sqlite3_column_text (stmt, 1); +             //(3) Remove the value of the 2nd column field (the value of type int)             Age=sqlite3_column_int int (stmt, 2);//            NSLog (@ "%d%s%d", Id,name,age ),             printf ("%d%s%d\n", id,name,age),         }21     }else22     {         NSLog (@ "query statement is problematic")     ;     26}

Print to view query results:

Third, supplementary

　Full code:

YYVIEWCONTROLLER.M file

  1//2//YYVIEWCONTROLLER.M 3//02-sqlite Application 4//5 6 #import "YYViewController.h" 7 #import <sqlite3.h&gt  ; 8 9 @interface Yyviewcontroller ()//db is the database of the handle, is the symbol of the database, to the database to be increased and censored, you have to operate this example @property (nonatomic,assign) sqlite3 *db; -(ibaction) insert; -(ibaction) Delete; -(ibaction) update; -(ibaction) Select;  @end @implementation Yyviewcontroller-(void) viewdidload [[Super Viewdidload]; 24 25 Sqlite3 *db; 26 27//Get the path to the database file NSString *doc=[nssearchpathfordirectoriesindomains (nsdocumentdirectory, Nsuserdomainmas K, YES) Lastobject]; NSString *filename=[doc stringbyappendingpathcomponent:@ "Students.sqlite"]; 30//Converts the OC string to the C-language string of the const char *cfilename=filename.utf8string; 32 33//1. Open the database file (if the database file does not exist, the function automatically creates the database file). int result = Sqlite3_open (cFileName, &_db); if (RESULT==SQLITE_OK) {//Open successfully NSLog (@ "Open database successfully"); 37 38//2. Create a table The *sql= const char "CREATE TABLE t_students (id integer PRIMARY KEY autoincrement,name text not null,age intege R not NULL); "; *errmsg=null Char; The result = Sqlite3_exec (self.db, SQL, NULL, NULL, &AMP;ERRMSG); if (RESULT==SQLITE_OK) {NSLog (@ "success of the Record"),}else (+//NSLog (@) Failed to create table----%s ", errmsg); printf ("Record failed---%s----%s---%d", errmsg,__file__,__line__);  }else {NSLog (@ "failed to open the database"), and the "(Ibaction)" (int) i=0; i<20;         i++) {57//1. Splicing SQL statements NSString *name=[nsstring stringwithformat:@ "Wen Xiao--%d", Arc4random_uniform (100)]; 59 int Age=arc4random_uniform (20) +10; NSString *sql=[nsstring stringwithformat:@ "INSERT into T_students (name,age) VALUES ('%@ ',%d);", Name,age]; 61 62//2. Execute SQL statement *errmsg=null char; Sqlite3_exec (self.db, SQL. Utf8string, NULL, NULL, &AMP;ERRMSG);             if (errmsg) {//If there is an error message NSLog (@ "Insert data failed--%s", errmsg);}else 68 {69 NSLog (@ "Insert data success"); +} (ibaction) Delete {ibaction}-(Updata)-(ibaction) Select {8} () 1 const char *sql= "Select Id,name,age from t_students WHERE age<20;"; Sqlite3_stmt *stmt=null;         83 84//Pre-query Preparation (SQLITE3_PREPARE_V2 (self.db, SQL, 1, &stmt, NULL) ==SQLITE_OK) {//sql Statement no problem 86 NSLog (@ "query Statement no problem");             87 88//each time the Sqlite3_step function is called, stmt will point to the next record, the Sqlite3_step (stmt) ==sqlite_row) {//Find a record 90 Take out data 91//(1) Take out the value of the No. 0 column field (value of type int) Id=sqlite3_column_int int (stmt, 0); 93//(2) Remove the value of the 1th column field (value of text type) 94 const unsigned char *name=sqlite3_column_text (stmt, 1); 95//(3) Remove the value of the 2nd column field (the value of type int) Age=sqlite3_column_int int (stmt, 2); //NSLog (@ "%d%s%d ", id,name,age); 98 printf ("%d%s%d\n", id,name,age); }100}else101 {102 NSLog (@ "query statement has a problem"); 103}104 @end

Referencing methods in-ios using the database sqlite3