SqLite3 of iOS data storage

There are many ways to store data in iOS, and when you have a large amount of data, preferences, archiving and plist will not meet your needs.

You need to use SQLite or CoreData to store the data.

Here's how to use SQLite to store data

To use SQLite, you must introduce the Libsqlite3.dylib library

To use the first to have a handle handle (the handle handle, in the C language, commonly used to control a certain kind of thing called a handle, is actually a pointer. )

// Database handle    Sqlite3 *_db;

SQLite also stores data in a file, but this file format is customized, you can let SQLite quickly query to its required data

So if you want to use the SQLite database first to create a database file, so you have a file path

 //  a nsstring category -(NSString *) appenddocumentdir{NSString  *docdir = Nssearchpathfordirectoriesindomains (NSDocumentDirectory,    Nsuserdomainmask, YES) [0   return   [Docdir Stringbyappendingpathcomponent:self];}  

    // set the file path    in the sandbox // hint: In your own development, do not use the. db end of the SQLite database file name    NSString *dbpath = [@ "readme.db"  Appenddocumentdir];    NSLog (@ "%@", DbPath);

The above creates the path where we store the database files DBPath

Now we're going to start creating this storage file

 /*  * Sqlite3_open 1) If the database exists, open it directly 2) If the database does not exist, create a database file, and then open the  */  _db  =         if  (Sqlite_ok = = Sqlite3_open ([DbPath utf8string], &_db) {NSLog ( @ "  database open successfully   " );  else   {NSLog ( @ "  database open failed  "  ); }

Call the Sqlite3_open function will create or open a database, if the specified path already exists database will open this database, if the specified dbpath does not exist database will create a database then open, create or open the time is to pass in the path, the second is to pass the handle, Because the back of the database additions and deletions are to rely on the handle to work.

The SQLITE_OK in the above code is the return code of the SQLite operation result, which can refer to the header file or the Help document

At this point we have a database that can be operated, and the first thing I must do when we have a database is to build a table.

#pragmaMark creates a data table-(void) createtable{//the idea of avoiding repeating tables//1. Check the sandbox to determine if the database file exists, and if it exists, no longer build the table;//2. Database method: IF not EXISTS, put it before the table name//Defining SQL StatementsNSString *sql =@"CREATE TABLE IF not EXISTS T_person (id INTEGER not NULL PRIMARY KEY autoincrement, name TEXT, gender INTEGER, age INT EGER, height REAL)"; [Self execsql:sql message:@"Create a data table"];}#pragmaMark Stepping sql-(void) Execsql: (NSString *) SQL message: (NSString *) message{//Execute SQL statement    /** 1> database handle 2> The SQL statement 3> callback to execute: After the execution of the SQL instruction, the function called is called the callback function 4> the first parameter of the callback function 5> the error message executed by the SQL statement Interest*/    Char*errmsg =NULL; if(Sqlite_ok = = Sqlite3_exec (_db, [SQL Utf8string], NULL, NULL, &errmsg)) {NSLog (@"%@ Success!", message); } Else {        //the string output in the C language should be in%sNSLog (@"%@ Failure-%s", message, errmsg); }}

The first method is to create a good build statement, and then give the second I method to do the table operation

About the Build Table statement: CREATE TABLE if not exists is created without this table and will be found if there is a problem

Table names typically start with t_ to prevent conflicts with keyword or class name variable names

The SQLITE3_EXEC function can execute SQL statements to manipulate the database

The first parameter is the handle to the database, the second argument is the SQL statement executed, the 34th parameter is the callback-related parameter, and the last is the error message

If you print out the success information to build the table is successful.

and then adding and removing the function according to the above code to write a good SQL statement, directly call on it, see below the query how to write code

- (void) allpersons{//1. SQLNSString *sql =@"SELECT ID, name, age, gender, height from T_person"; //2. Query statements are usually spliced with strings.//therefore, before you can normally use a query statement, you need to check the syntax of the SQL statement correctly! Sqlite3_stmt *stmt =NULL; if(Sqlite_ok = = SQLITE3_PREPARE_V2 (_db, [SQL Utf8string],-1, &stmt, NULL)) {NSLog (@"grammatically correct"); //use the handle to query the eligible data one by one//Sqlite3_step each time you fetch a row of records for a query, repeat it and take it to the last record position .         while(Sqlite_row = =Sqlite3_step (stmt)) {            //fetch the row information and get the contents of each column individually//Icol corresponds to the order of the fields in the SQL statement, starting with 0//depending on the properties of the actual query field, using Sqlite3_column_xxx to get the corresponding content can be            intID = Sqlite3_column_int (stmt,0); ConstUnsignedChar*name = Sqlite3_column_text (stmt,1); intAge = Sqlite3_column_int (stmt,2); intgender = Sqlite3_column_int (stmt,3); CGFloat height= Sqlite3_column_double (stmt,4); //const unsigned char * Direct output does not show results and needs to be convertedNSString *nameutf8 = [NSString stringwithutf8string: (Const Char*) name]; person*p =[Person personwithid:id Name:nameutf8 age:age Gender:gender height:height]; NSLog (@"%@", p); }    } Else{NSLog (@"syntax error"); }}

The basic steps in querying are: 1. Create a query statement

2. Create a sqlite3_stmt result set

3. Use the SQLITE3_PREPARE_V2 function to establish the relationship between the result set and the action handle, and check the syntax

4.while (Sqlite_row = = Sqlite3_step (stmt)) loop to determine and extract data from

5.sqlite3_column_int (stmt, 0); Take out the data with a similar function, the second parameter is where

For more information about database primary keys, foreign keys, and other constraints, please refer to SQLite data

There is another sqlite third-party class library Fmdb, interested can go to GitHub to search

X