The basic package of SQLite

when we use SQLite, we need to create a database to create a data table, and then execute the corresponding SQL statement, which is not conducive to the unified management of database operations, nor conform to the idea of object-oriented, when our needs change, such as the database table name changed, Or to add a few fields, this time there will be a look around the database operation code situation, if it is a place two good, but if it is hundreds of places, it will be a lot of work. So we can define a tool class Sqlmanager, and unify the management of database operations. Tool class Sqlmanager is generally defined as a singleton mode, under normal circumstances, Sqlmanager objects in the system only need to save one copy,. h file provides an interface to obtain a single instance

/** gets a singleton object */+ (instancetype) sharemanage;

implementing the Singleton method in a. m file

Static Sqlmanager *_instance;+ (instancetype) sharemanage{    static dispatch_once_t Oncetoken;    Dispatch_once (&oncetoken, ^{        _instance = [[Sqlmanager alloc] init];    });    return _instance;}

since it is a database management tool class, to operate on the database, we need to create a database and database tables, create only one time, so you can write the creation code in the Initialize method, The Initialize method is called when the class is first used, and is called only once in the app's lifetime

+ (void) initialize{    //Splicing database address    nsstring *path = [Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastobject];    NSString *sqlfile = [path stringbyappendingpathcomponent:@ "Student.sqlite"];    Open Data    int result = Sqlite3_open (sqlfile.utf8string, &_db);//[Self db]    //Determine if success is open if    (result = = SQLITE _OK) {        NSLog (@ "open successfully");        3.1 CREATE TABLE        nsstring *sql = @ "CREATE table IF not EXISTS t_student (id INTEGER PRIMARY KEY autoincrement, name TEXT, AG e INTEGER, score REAL); ";        result = Sqlite3_exec (_db, SQL. Utf8string, NULL, NULL, NULL);}    }

now we have a tool class that provides a way to insert a student data into the database

/** Insert student Data */-(BOOL) insertstudent: (hmstudent *) student;

Of course we first need to define a student model to store the data, the student class in the field root database table in the field one by one corresponding

@property (nonatomic, copy) NSString *name; @property (nonatomic, assign) int age; @property (nonatomic, assign) double Scor e; @property (nonatomic, assign) int ID;

Insertstudent: The implementation of the method is as follows

-(BOOL) Insertstudent: (hmstudent *) student{    nsstring *sql = [NSString stringWithFormat: @ "INSERT into t_student (age , score, name) VALUES (%d,%f, '%@ '); ", Student.age, Student.score, Student.name];    int result =  sqlite3_exec (_db, SQL. Utf8string, NULL, NULL, NULL);    if (result = = SQLITE_OK) {        return YES;    }    return NO;}

Thus, the method encapsulates an insert operation. again, in the class where we need to use the database operation, just import the header file of the tool class, and you can insert a piece of data into the database in an object-oriented way .

    Student *stu = [[Student alloc] init];    Stu.name = @ "LNJ";    Stu.age = +;    Stu.score = 100.0;    if ([[[Sqlmanager Sharemanage]insertstudent:stu]) {        NSLog (@ "Insert succeeded");    }

and so on, we can encapsulate the other delete modification operation, if there are any database requirements changes, we just use the focus on the modification of the tool class on the line, do not go around to find the database related to the code of operation, and compared to the way the database operation between, the above code is more readable. the way to query is very special, now it is easy to implement the following. also need to provide an excuse for external

-(Nsarray *) query;

This method is used to query all data and return an array of student models

NSString *sql = @ "SELECT * from T_student;";    sqlite3_stmt *stemt = NULL;    SQLITE3_PREPARE_V2 (_db, SQL. Utf8string,-1, &STEMT, NULL);    Determine if there is a query result    nsmutablearray *ARRM = [Nsmutablearray array];    while (Sqlite3_step (stemt) = = Sqlite_row) {        //Remove query to result        Const unsigned char *name = Sqlite3_column_text (stemt, 1) ;        int age = Sqlite3_column_int (STEMT, 2);        Double score = sqlite3_column_double (STEMT, 3);        Hmstudent *stu = [[Hmstudent alloc] init];        Stu.name = [NSString stringwithutf8string:name];        Stu.age = age;        Stu.score = score;        [Arrm Addobject:stu];    }    return ARRM;

when we need to get the data in the database in the controller, as long as the following lines of code

Nsarray *arr = [[Sqlmanager sharemanage] query];    For (Student *stu in arr) {        NSLog (@ "%@", Stu);    }

So easy! You will find that the controller needs to focus on less code, the code will be more concise, this is the charm of encapsulation!

The basic package of SQLite