12th, Swift----use of SQLite

Import UIKitclassSqlitemanager:nsobject {Private StaticLet Manager:sqlitemanager =Sqlitemanager ()///Single Grain    classFunc Sharemanager ()Sqlitemanager {returnManager}//database Objects    private var db:copaquepointer = nil//Create a serial queue    PrivateLet Dbqueue = Dispatch_queue_create ("Com.520it.lnj", Dispatch_queue_serial) func Execqueuesql (action: (Manager:sqlitemanager)-())    {        //1. Turn on a sub-threadDispatch_async (Dbqueue) {() Voidinchprint (Nsthread.currentthread ())//2. Execute closuresaction (manager:self)}} /** Open Database:p aram:sqlitename database name*/func opendb (sqlitename:string) {//0. Get the path to the databaseLet path =Sqlitename.docdir () print (path) let CPath= Path.cstringusingencoding (nsutf8stringencoding)!//1. Open the database        /*1. The path of the database file that needs to be opened, C-language string 2. After the database object (pointer) is opened, all subsequent database operations must be given this pointer for the relevant operation*/        //Open Method Features: If the specified path corresponding to the database file already exists, it will be opened directly//If the database file that corresponds to the specified path does not exist, a new        ifSqlite3_open (CPath, &db)! =SQLITE_OK {print ("failed to open database")            return        }                //2. Create a table        ifcreattable () {print ("CREATE TABLE Success")        }Else{print ("failed to create table")        }    }        PrivateFunc creattable ()Bool {//1. Writing SQL statements//Recommendation: Write SQL statements in development, if the statement is too long, do not write in one line//Development tips: When doing database development, if you encounter errors, you can print the SQL, copy to the PC tool to verify before debuggingLet sql ="CREATE TABLE IF not EXISTS T_person (\ n"+"ID INTEGER PRIMARY KEY autoincrement, \ n"+"name TEXT, \ n"+"Age INTEGER \ n"+"); \ n"//print (SQL)//2. Execute SQL statements        returnexecsql (SQL)}/** Executes SQL statements other than queries:p aram:sql needs to execute SQL statement: Returns: True execution succeeded false execution failed*/func execsql (sql:string)-Bool {//0. Convert swift strings to C-language stringsLet Csql = sql.cstringusingencoding (nsutf8stringencoding)!//in SQLite3, the same function is used in addition to query unexpected (Create/delete/Add/update)        /*1. Database object already open 2. The SQL statement that needs to be executed, the C language string 3. The callback after the execution of the SQL statement, generally passing nil 4. Is the first parameter of the third parameter, the general pass nil 5. Error message, General pass nil*/        ifSqlite3_exec (db, csql, nil, nil, nil)! =SQLITE_OK {return false        }        return true    }        /** Query All data: Returns: Query to the dictionary array*/func execrecordsql (sql:string)-[[String:anyobject]] {//0. Convert swift strings to C-language stringsLet Csql = sql.cstringusingencoding (nsutf8stringencoding)!//1. Preparing the Data//Preparation: Understood as pre-compiled SQL statements, detection of errors inside, and so on, it can provide performance        /*1. Database objects that have been started 2. SQL statement required to execute 3. The length of the SQL statement that needs to be executed, the incoming-1 system automatically calculates 4. After the precompiled handle, the handle is needed to remove the data. 5. General Transmission Nil*/var stmt:copaquepointer=NilifSQLITE3_PREPARE_V2 (DB, Csql,-1, &stmt, nil)! =SQLITE_OK {print ("prepare to fail")        }                //ready to succeedvar records =[[String:anyobject]] ()//2. Querying Data//The Sqlite3_step representative takes out a piece of data and returns if the data is fetched Sqlite_row         whileSqlite3_step (stmt) = =Sqlite_row {//get the data for a recordLet record =recordwithstmt (stmt)//adds the currently acquired record to the arrayrecords.append (record)}//returns the data queried to        returnRecords}/** Gets the value of a record:p aram:stmt precompiled SQL statement: Returns: Dictionary*/    PrivateFunc recordwithstmt (Stmt:copaquepointer)[String:anyobject] {//2.1 Get all the columns for the current piece of dataLet count =Sqlite3_column_count (stmt)//print (count)//define the data that the dictionary store queries tovar record =[String:anyobject] () forIndexinch 0.. <Count {//2.2 Get the name of each columnLet CName =sqlite3_column_name (stmt, index) let name= String (Cstring:cname, encoding:nsutf8stringencoding)!//print (name)//2.3 Get the type of each column Sqlite_integerLet type =Sqlite3_column_type (stmt, index)//print ("name = \ (name), type = \ (type)")                        Switchtype { CaseSqlite_integer://Plastic SurgeryLet num =Sqlite3_column_int64 (stmt, index) record[name]=Int (num) Casesqlite_float://floating Point TypeLetDouble=sqlite3_column_double (stmt, index) record[name]= Double (Double)             CaseSqlite3_text://Text TypeLet Ctext = unsafepointer<int8>(Sqlite3_column_text (stmt, index)) let text= NSString (Cstring:ctext, encoding:nsutf8stringencoding)!Record[name]=text CaseSqlite_null://Empty TypeRecord[name] =NSNull ()default:                //Binary type Sqlite_blob//in general, binary data is not stored in the databasePrint"")            }        }        returnRecord}}

To insert data using a transaction:

Let manager =Sqlitemanager.sharemanager ()//Open Transactionmanager.begintransaction () forIinch 0.. <10000{let P= Person (dict: ["name":"Zs + \ (i)"," Age":3+i]) P.insertperson ()ifi = = +{manager.rollbacktransaction ()//Note: Be sure to jump out of the loop and stop the update after rolling back.                 Break            }        }                //Commit a transactionManager.committransaction ()

Precompiled bindings:

    //1. Open the databaseSqlite3 *db =[self sqlite3_open_database]; //2. Build the precompiled statement object and compile itCharchar *sql ="INSERT into student (name,age) VALUES (?,?)";//? As a placeholder representing the future substitution of variable values//2.1. Prepare the precompiled statement objectSqlite3_stmt *stmt =NULL; //2.2. Precompiled SQL Statements    intresult = SQLITE3_PREPARE_V2 (db, SQL,-1, &stmt, NULL); //2.3, precompiled success, bound value    if(Result = =SQLITE_OK) {          //binding interface data into precompiled SQL statementsSqlite3_bind_text (stmt,1, [Self.txtName.text utf8string],-1, NULL); Sqlite3_bind_int (stmt,2, [Self.txtAge.text intvalue]); //3. Execute Precompiled Statement object, 4, use resultresult =Sqlite3_step (stmt); if(Result = =Sqlite_done) {[Self Myalert:@"Insert data Successfully! "]; } Else{[Self Myalert:@"failed to insert data! "]; }      }      //5. Destroy the precompiled statement object and close the databasesqlite3_finalize (stmt); Sqlite3_close (DB); 

12th, Swift----use of SQLite