IOS Development Guide 11th data Persistence SQLite Learning

1 SQLite is a database with no data type, or a field without a specified type. But from a programming specification, you should specify the data type in the CREATE TABLE statement: Interger signed integer type REAL floating-point type TEXT String type blob binary type

2 Create a database:

Prepare: Add SQLite3 Library to project Targets-link Binary with libraries-add Libsqlite3.dylib

Open a database using Sqlite3_open-sqlite3_exec execute creat table statement, create a database-use Sqlite3_close to free resources

- (void) createeditablecopyofdatabaseifneeded {nsstring*writabledbpath =[self applicationdocumentsdirectoryfile]; const char* CPath = [Writabledbpath utf8string]; convert path to C string     if(Sqlite3_open (CPath, &db)! =SQLITE_OK)        { sqlite3 *db;db is a database pointer sqlite3_close (DB); Nsassert (NO,@"database open failed. "); } Else {        Char*err;
        Building SQL statements NSString         *sql = [NSString stringwithformat:@ "CREATE TABLE IF not EXISTS Note (cdate text PRIMARY KEY, content TEXT);"        ]; Const Char* Csql =[SQL Utf8string]; The execution parameters are sqlite3 pointer variables to execute the SQL statement to callback the function to the callback function parameter execution error string if(sqlite3_exec (db, csql,null,null,&err)! = SQLITE_OK)            {The order is performed first in the Judgment Sqlite3_close (db); Nsassert (NO,@"failed to build table");    } sqlite3_close (db); }}

＊＊＊＊＊＊＊＊＊＊

Nsassert () is just a macro that is used for bugs in the development phase of the debugger, by passing conditional expressions for Nsassert () to determine if a bug is true, to satisfy a conditional return truth, to run a program, to throw an exception if a false value is returned, and to customize the description of the exception. Nsassert () is defined in this way:

#define NSASSERT (condition, desc)

condition is a conditional expression, with a value of Yes or No;desc as an exception, usually nsstring. When the program continues to run when Conditon is yes, exception information with desc description is thrown when no. Nsassert () can appear at any location in the program.

＊＊＊＊＊＊＊＊＊＊

3 Querying data

The query condition uses the WHERE statement, in which a dynamic binding parameter is required to the WHERE statement

Opening a database using Sqlite3_open-preprocessing SQL statements with SQLITE3_PREPARE_V2-using Sqlite3_bind_text binding parameters-using SQLITE3_STEP to execute SQL statements, traversing result sets-using sqlite3 _column_text extracting field data-releasing resources using Sqlite3_finalize and Sqlite3_close

//querying data methods by primary key-(note*) FindByID: (note*) model{NSString*path =[self applicationdocumentsdirectoryfile]; Const Char* CPath =[path utf8string]; if(Sqlite3_open (CPath, &db)! =SQLITE_OK)        {sqlite3_close (db); Nsassert (NO,@"database open failed. "); } Else{nsstring*sql =@" SELECT cdate,content  from Note where CDate =? "; ? Representing dynamic parameters Const Char* Csql =[SQL Utf8string]; Sqlite3_stmt*statement; //The purpose of the preprocessing process is to compile SQL statements into binary code to improve the execution speed of SQL statements.
The third argument begins by representing the entire SQL string length statement object: The SQL statement that can be executed by the statement object the part of the SQL statement that was not executed
if(SQLITE3_PREPARE_V2 (DB, Csql,-1, &statement, NULL) = = SQLITE_OK) {            //Prepare ParametersNSDateFormatter *dateformatter =[[NSDateFormatter alloc] init]; [Dateformatter Setdateformat:@"YYYY-MM-DD HH:mm:ss"]; NSString*strdate =[Dateformatter stringFromDate:model.date]; const char* cDate = [Strdate utf8string]; //binding parameter Start
Parameters: Pointer ordinal: Starting from 1 string (parameter) string length function pointer
Sqlite3_bind_text (statement,1, CDate,-1, NULL); //Execution            if(sqlite3_step (statement)==Sqlite_row) { If the function return value equals Sqlite_row, there are other rows that do not traverse Char*bufdate =(char *) Sqlite3_column_text(Statement,0);                The second parameter specifies the index of a select field NSString*strdate =[[NSString alloc] initwithutf8string:bufdate]; NSDate*date =[Dateformatter datefromstring:strdate]; Char*bufcontent = (Char*) Sqlite3_column_text (statement,1); NSString* Strcontent =[[NSString alloc] initwithutf8string:bufcontent]; Note* Note =[Note alloc] initwithdate:date content:strcontent];                sqlite3_finalize (statement);                                Sqlite3_close (DB); returnNote;        }} sqlite3_finalize (statement);            Sqlite3_close (DB); }    returnNil;}

4 Modifying data

The SQL statement involved has an INSERT update delete

Opening a database using Sqlite3_open-preprocessing SQL statements with SQLITE3_PREPARE_V2-using Sqlite3_bind_text binding parameters-using SQLITE3_STEP to execute SQL statements-using sqlite3_ Finalize and Sqlite3_close close the database

//Insert Note Method-(int) Create: (note*) model{NSString*path =[self applicationdocumentsdirectoryfile]; Const Char* CPath =[path utf8string]; if(Sqlite3_open (CPath, &db)! =SQLITE_OK)        {sqlite3_close (db); Nsassert (NO,@"database open failed. "); } Else{nsstring*sql =@"INSERT OR REPLACE into note (cdate, content) VALUES (?,?) ";Const Char* Csql =[SQL Utf8string]; Sqlite3_stmt*statement; //preprocessing Process        if(SQLITE3_PREPARE_V2 (DB, Csql,-1, &statement, NULL) = =SQLITE_OK) {NSDateFormatter*dateformatter =[[NSDateFormatter alloc] init]; [Dateformatter Setdateformat:@"YYYY-MM-DD HH:mm:ss"]; NSString*strdate =[Dateformatter stringFromDate:model.date]; Const Char* CDate =[Strdate utf8string]; Const Char* Ccontent =[Model.content utf8string]; //binding parameter StartSqlite3_bind_text (statement,1, CDate,-1, NULL); Sqlite3_bind_text (statement,2, Ccontent,-1, NULL); //Perform an Insert            if(sqlite3_step (statement)! = Sqlite_done) {Nsassert (NO,@"failed to insert data. ");        }} sqlite3_finalize (statement);    Sqlite3_close (DB); }        return 0;}//Delete Note Method-(int) Remove: (note*) model{NSString*path =[self applicationdocumentsdirectoryfile]; Const Char* CPath =[path utf8string]; if(Sqlite3_open (CPath, &db)! =SQLITE_OK)        {sqlite3_close (db); Nsassert (NO,@"database open failed. "); } Else{nsstring*sql =@"DELETE from note where CDate =?"; Const Char* Csql =[SQL Utf8string]; Sqlite3_stmt*statement; //preprocessing Process        if(SQLITE3_PREPARE_V2 (DB, Csql,-1, &statement, NULL) = =SQLITE_OK) {NSDateFormatter*dateformatter =[[NSDateFormatter alloc] init]; [Dateformatter Setdateformat:@"YYYY-MM-DD HH:mm:ss"]; NSString*strdate =[Dateformatter stringFromDate:model.date]; Const Char* CDate =[Strdate utf8string]; //binding parameter StartSqlite3_bind_text (statement,1, CDate,-1, NULL); //Execution            if(Sqlite3_step (statement)! =Sqlite_done) {Nsassert (NO,@"failed to delete data. ");        }} sqlite3_finalize (statement);    Sqlite3_close (DB); }        return 0;}//Modify the Note method-(int) Modify: (note*) model{NSString*path =[self applicationdocumentsdirectoryfile]; Const Char* CPath =[path utf8string]; if(Sqlite3_open (CPath, &db)! =SQLITE_OK)        {sqlite3_close (db); Nsassert (NO,@"database open failed. "); } Else{nsstring*sql =@"UPDATE Note Set content=? where CDate =?"; Const Char* Csql =[SQL Utf8string]; Sqlite3_stmt*statement; //preprocessing Process        if(SQLITE3_PREPARE_V2 (DB, Csql,-1, &statement, NULL) = =SQLITE_OK) {NSDateFormatter*dateformatter =[[NSDateFormatter alloc] init]; [Dateformatter Setdateformat:@"YYYY-MM-DD HH:mm:ss"]; NSString*strdate =[Dateformatter stringFromDate:model.date]; Const Char* CDate =[Strdate utf8string]; Const Char* Ccontent =[Model.content utf8string]; //binding parameter StartSqlite3_bind_text (statement,1, Ccontent,-1, NULL); Sqlite3_bind_text (statement,2, CDate,-1, NULL); //Execution            if(Sqlite3_step (statement)! =Sqlite_done) {Nsassert (NO,@"failed to modify data. ");        }} sqlite3_finalize (statement);    Sqlite3_close (DB); }    return 0;}

The 11th IOS Development Guide, data Persistence, SQLite learning