Basic use of sqlite3 and encapsulation use

1 Basic use

1. Open the DatabaseintSqlite3_open (Const Char*filename,//the file path of the databaseSqlite3 **ppdb//DB Instance);2. Execute any SQL statementintsqlite3_exec (Sqlite3*,//an open DB instance    Const Char*sql,//SQL statements that need to be executed    int(*callback) (void*,int,Char**,Char**),//callback after the SQL statement has completed execution    void*,//the 1th parameter of a callback function    Char**errmsg//error Message);3. Check the legality of SQL statements (preparation before query)intSqlite3_prepare_v2 (Sqlite3*db,//DB Instance    Const Char*zsql,//SQL statements that need to be checked    intNbyte,//maximum byte length for SQL statementsSqlite3_stmt **ppstmt,//sqlite3_stmt instances to obtain database data    Const Char**pztail);4. Querying a row of dataintSqlite3_step (sqlite3_stmt*);//if a row of data is queried, the Sqlite_row is returned5. Use stmt to get the value of a field (the subscript of the field starts with 0)DoubleSqlite3_column_double (sqlite3_stmt*,intICOL);//floating point DataintSqlite3_column_int (sqlite3_stmt*,intICOL);//Integral type DataSqlite3_int64 Sqlite3_column_int64 (sqlite3_stmt*,intICOL);//long-integer dataConst void*sqlite3_column_blob (sqlite3_stmt*,intICOL);//Binary Text dataConstUnsignedChar*sqlite3_column_text (sqlite3_stmt*,intICOL);//String Data

1 Create a model class,. h file#import<Foundation/Foundation.h>@interfaceIwstudent:nsobject@property (nonatomic, assign)intID; @property (nonatomic, copy) NSString*Name: @property (nonatomic, assign)intAge ;@end. m file#import "IWStudent.h"@implementationiwstudent@end2 Creating a tool class .... Encapsulation method. h file#import<Foundation/Foundation.h>@classiwstudent;@interfaceIwstudenttool:nsobject/** * Add Students * * @param student need to add students*/+ (BOOL) addstudent: (Iwstudent *) student;/** * Get all the students * * @return arrays are loaded with Iwstudent models*/+ (Nsarray *) students;/** * * @param condition search criteria based on the search criteria for the student * **/+ (Nsarray *) Studentswithcondition: (NSString *) condition;@end. m file#import "IWStudentTool.h"#import "IWStudent.h"#import<sqlite3.h>@implementationIwstudenttool//the function of static: To ensure that the _db variable is only accessed directly by IWSTUDENTTOOL.MStaticSqlite3 *_db;+ (void) initialize{//0. Get the database file name in the sandboxNSString *filename = [[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) LastObject] stringByAppendingPathComponent:@"Student.sqlite"]; //1. Create (Open) database (automatically created if the database file does not exist)    intresult = Sqlite3_open (filename. Utf8string, &_db); if(Result = =SQLITE_OK) {NSLog (@"successfully opened database"); //2. Create a watch        Const Char*sql ="CREATE table if not exists t_student (ID integer primary key autoincrement, name text, age integer);"; Char*ERRORMESG =NULL; intresult = Sqlite3_exec (_db, SQL, NULL, NULL, &ERRORMESG); if(Result = =SQLITE_OK) {NSLog (@"successfully created T_student table"); } Else{NSLog (@"failed to create t_student table:%s", ERRORMESG); }    } Else{NSLog (@"failed to open database"); }}+ (BOOL) addstudent: (Iwstudent *) student{NSString*sql = [NSString stringWithFormat:@"INSERT into T_student (name, age) VALUES ('%@ ',%d);", Student.name, Student.age]; Char*ERRORMESG =NULL; intresult = Sqlite3_exec (_db, SQL. Utf8string, NULL, NULL, &ERRORMESG); returnresult = =Sqlite_ok;}+ (Nsarray *) students{//0. Defining ArraysNsmutablearray *students =Nil; //1. Defining SQL statements    Const Char*sql ="Select ID, name, age from T_student;"; //2. Define a stmt to hold the result setSqlite3_stmt *stmt =NULL; //3. Detecting the legality of SQL statements    intresult = SQLITE3_PREPARE_V2 (_db, SQL,-1, &stmt, NULL); if(Result = =SQLITE_OK) {NSLog (@"The query statement is legal"); Students=[Nsmutablearray array]; //4. Execute the SQL statement to remove the data from the result set         while(Sqlite3_step (stmt) = = Sqlite_row) {//really query a row of data//get the data for this lineiwstudent*student =[[Iwstudent alloc] init]; //get the ID of the No. 0 columnStudent.id = Sqlite3_column_int (stmt,0); //get the name of column 1th            ConstUnsignedChar*sname = Sqlite3_column_text (stmt,1); Student.name= [NSString stringwithutf8string: (Const Char*) sname]; //get the age of the 2nd columnStudent.age = Sqlite3_column_int (stmt,2); //Add to Array[students addobject:student]; }    } Else{NSLog (@"Query statement is not legal"); }        returnstudents;}+ (Nsarray *) Studentswithcondition: (NSString *) condition{//0. Defining ArraysNsmutablearray *students =Nil; //1. Defining SQL statements    Const Char*sql ="Select ID, name, age from T_student where name?;"; //2. Define a stmt to hold the result setSqlite3_stmt *stmt =NULL; //3. Detecting the legality of SQL statements    intresult = SQLITE3_PREPARE_V2 (_db, SQL,-1, &stmt, NULL); if(Result = =SQLITE_OK) {NSLog (@"The query statement is legal"); Students=[Nsmutablearray array]; //fill in the contents of the placeholderNSString *newcondition = [NSString stringWithFormat:@"%%%@%%", condition];//NSLog (@ "%@", newcondition);Sqlite3_bind_text (stmt,1, Newcondition.utf8string,-1, NULL); //4. Execute the SQL statement to remove the data from the result set         while(Sqlite3_step (stmt) = = Sqlite_row) {//really query a row of data//get the data for this lineiwstudent*student =[[Iwstudent alloc] init]; //get the ID of the No. 0 columnStudent.id = Sqlite3_column_int (stmt,0); //get the name of column 1th            ConstUnsignedChar*sname = Sqlite3_column_text (stmt,1); Student.name= [NSString stringwithutf8string: (Const Char*) sname]; //get the age of the 2nd columnStudent.age = Sqlite3_column_int (stmt,2); //Add to Array[students addobject:student]; }    } Else{NSLog (@"Query statement is not legal"); }        returnstudents;}@end

2. Encapsulation as a tool class

Basic use of sqlite3 and encapsulation use