iOS uses Sqlite3 native syntax for additional pruning and Fmdb use

Reprinted from: http://www.cnblogs.com/Steak/p/3802508.html

First of all to import Libsqlite3.dylib and join the header file #import <sqlite3.h>,, before the increase and deletion of the database before the first to get in.

One way is to copy from the outside into the program: http://www.cnblogs.com/Steak/p/3764395.html

Another way is to create a table directly, since it is manipulating the database, then there must be a database object, SQLite is the C library, so a C variable is required:

1 @interface Dbhandler () {2     sqlite3 *db;3}

Create a database file in the Sandbox Documents folder, and if the database does not exist, the Sqlite3_open function creates a file:

1     nsstring *documentpath = [Nssearchpathfordirectoriesindomains (nsdocumentdirectory, NSUserDomainMask, YES) Objectatindex:0];2     NSString *databasepath = [[NSString alloc] Initwithstring:[documentpath stringbyappendingpathcomponent:@ "Sqlite.sqlite"]];3     int result = Sqlite3_open ([DatabasePath utf8string], & DB);

The return value is a macro definition, and if success is SQLITE_OK, it is important to note that open is required every time a database operation is performed, and that the corresponding close is called after it is finished.

For the timing of opening and closing, there are two statements, one is always open, the other is that with the switch, the individual prefers the latter.

To create a table:

1     Char *sql = "CREATE table if not exists for_persons (ID integer primary key autoincrement, name text, age integer);" ; 2     char *error;3     int result = SQLITE3_EXEC (db, SQL, NULL, NULL, &ERROR);

Change and delete:

1     Char *sql = "INSERT into for_persons (name, age) VALUES (?,?)"; 2      3     sqlite3_stmt *stmt; 4     int result = Sq LITE3_PREPARE_V2 (DB, SQL,-1, &stmt, NULL); 5     if (result = = SQLITE_OK) {6         Sqlite3_bind_text (stmt, 1, "Xiaoming",-1, NULL); 7         Sqlite3_bind_int (stmt, 2, 1 0); 8  9         if (Sqlite3_step (stmt)  = = Sqlite_done) {Ten             //Success         }12         else {             //failure         }15     }16     Else {         //grammar check failed     }19     sqlite3_finalize (stmt);

Here to increase as an example, add and remove changes to define a sqlite3_stmt, and at the end of the release, and then through the SQLITE3_PREPARE_V2 to check SQL, check the success of the Sqlite3_bind_xxx method for data binding, The SQL statement is then executed through Sqlite3_step. Additions and deletions are the same format.

Query data:

1     Char *sql = "SELECT * from For_persons"; 2     sqlite3_stmt *stmt; 3     int result = SQLITE3_PREPARE_V2 (db, SQL, 1 , &stmt, NULL); 4     if (result = = SQLITE_OK) {5         while (Sqlite3_step (stmt) = = Sqlite_row) {6             int ID = Sqlite3_column_int (stmt, 0); 7             Char *name = (char *) Sqlite3_column_text (stmt, 1); 8             int age = Sqlite3_column_int (stmt, 2); 9             //To this place has Complete read out a record             //If there is more than one data, you can define an array in the outside save         }13     }

Queries are similar, and need to use while (Sqlite3_step (stmt) = = Sqlite_row) to determine if there is another one to take out.

The above is the basic use of native sqlite3, but this C-style code looks like nausea, copy and paste have no appetite.

Fortunately, someone encapsulated a very handy third-party library called Fmdb. The following is the use of Fmdb, first download Fmdb:https://github.com/ccgus/fmdb, the same import libsqlite3.dylib, and #import "FMDatabase.h".

The code that creates the library file and opens it is similar, but it's all OC code and looks much more comfortable:

1 @interface Dbhandler () {2     fmdatabase *db;3}

1     nsstring *documentpath = [Nssearchpathfordirectoriesindomains (nsdocumentdirectory, NSUserDomainMask, YES) Objectatindex:0];2     NSString *databasepath = [[NSString alloc] Initwithstring:[documentpath stringbyappendingpathcomponent:@ "Sqlite.sqlite"]];3     db = [fmdatabase databasewithpath:databasepath];4     [db] Open];

To create a table:

1     nsstring *sql = @ "CREATE table if not exists for_persons (ID integer primary key autoincrement, name text, age Integ ER); "; 2     [db executeupdate:sql];

The code is simple, EXECUTEUPDATE returns a bool value.

Change and delete:

1     [db executeupdate:@ "insert into for_persons (name, age) VALUES (?,?)" @ "Xiaowang", @20];

The same is true with the Executeupdate method, where the question mark is equivalent to the @ in NSString and is a placeholder for the OC object.

Execution queries are also OC-style:

1     fmresultset *rs = [db executequery:@ "select name, age from for_persons where name =?", @ "Xiaowang"];2 while     ([R S next]) {3         nsstring *name = [rs stringforcolumn:@ ' name '];4         int age = [rs intforcolumn:@ ' age '];5         NSLog (@ "%@% D ", name, age); 6     }7     [rs Close];