IOS SQLite Detailed

This week is busy, a few days ago to 11 o'clock, the basic is home is about 12 points (a little complaining about, free overtime, what is free, is any compensation, such as take some, overtime pay, meal supplement and other benefits, is not a bit) because the app to go online! App on-line, you can also spare some time to organize their own things, ready to spend a week to tell about the iOS data storage way. This article is mainly about the use and encapsulation of SQLite, after reading about 10-15 minutes.

First, the concept of SQLite

SQLite is a lightweight relational database (the concept of a relational database will be extended below); It is an untyped database (meaning that all types of data can be saved)----B-Tree provides SQLite with an O (logn) query, insert, and delete operations, An O (1) Traversal of the record is also provided.

Development ""

1. Relational database and non-relational database

The relational database is a database based on the relational model, and the relational database establishes the relationship between the table and the table by the foreign key, and the non-relational database is in the form of the object (the relationship between the objects is determined by the property of the object itself).

To give an example:

For example, there is a student's data:

Name: Zhang Four, gender: male, School Number: 12345678, Class: College class

Data for a class:

Class: University class, class teacher: John Doe

For this example: the relational database requires the creation of student and class tables to store the two data, and the class store in the student table should be the primary key for the class table. For non-relational databases, we need to create two objects, one for the student object, one for the class object, and the Java representation as follows:

class Student {    String id;    String name;    String sex;    String number;    String ClassID;} class Grade {    String id;    String name;    String teacher;}

Second, the basic use of SQLite

1. Build the Table command:

CREATE TABLE table name (Field name 1 field type 1, Field Name 2 field type 2, etc...) ；

CREATE table if not EXISTS (Field name 1 field type 1, Field Name 2 field type 2, etc...);

(1) The figure is as follows:

(2) The code example is as follows:

- (void) createtable{//1. Designing SQL statements that create tables  Const Char*sql = "CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement,name text not NULL, score real DEFAULT 0 , sex text DEFAULT ' unknown '); "//2. Execute SQL statements    intRET =sqlite3_exec (_db, SQL, NULL, NULL, NULL); //3. Judging the result of execution    if(ret = =SQLITE_OK) {NSLog (@"CREATE TABLE succeeded"); }Else{NSLog (@"Failed to create TABLE"); }  }

2. Insert command

Insert into table name (Field 1, Field 2, and so on ...) VALUES (Value of field 1, Value of field 2, etc...);

(1) The figure is as follows:

(2) The code is as follows:

- (void) insertdata{//1. Create an SQL statement that inserts data//=========== inserting a single data =========    Const Char*sql ="INSERT into T_student (name,score,sex) VALUES (' Xiaoming ', 65, ' Male ');";//========== inserting multiple data at the same time =======nsmutablestring * MSTR = [nsmutablestringstring];  for(inti =0; I < -; i++) {NSString* name = [NSString stringWithFormat:@"name%d", I]; CGFloat score= Arc4random ()%101*1.0; NSString* sex = arc4random ()%2==0?@"male":@"female"; NSString* TSQL = [NSString stringWithFormat:@"INSERT into T_student (name,score,sex) VALUES ('%@ ',%f, '%@ ');", name, score, sex];    [Mstr Appendstring:tsql]; }      //converting OC strings to C-language stringssql =MSTR.      utf8string; //2. Execute SQL statements   intRET =sqlite3_exec (_db, SQL, NULL, NULL, NULL); //3. Judging the result of execution   if(ret==SQLITE_OK) {NSLog (@"Insert Successful"); }Else{NSLog (@"Insert Failed"); }    }   

3. Update command

Update table name set field 1 = new value for field 1, field 2 = new value for field 2, and so on ...;

4. Delete command

Delete from table name; Note: All records in the table are deleted

Expand: You can add some additional conditions to delete

Operation of conditional statements: can be selectively deleted

where if // cannot be used for two x = where  is // is equals = where  is // is isn't equivalent to! = where field > a value; where // and equivalent to the && in C language where // or equivalent to the C language | |

(1) The figure is as follows:

(2) The code is as follows:

- (void) deletedata{//create an SQL statement that deletes data    Const Char*sql ="DELETE from T_student WHERE score <;"; //Execute SQL statement    intRET =sqlite3_exec (_db, SQL, NULL, NULL, NULL); //Judging Execution Results    if(ret = =SQLITE_OK) {NSLog (@"Delete succeeded"); }Else{NSLog (@"Delete Failed"); }}

5. Querying data

SELECT Field 1, Field 2 from is indicated;

(1) The figure is as follows:

(2) The code is as follows:

- (void) selectdata{//Execute DATABASE statement    Const Char*sql ="SELECT name,score from T_student;"; //result set (used to collect results)SQLITE3_STMT *stmt; intret = SQLITE3_PREPARE_V2 (_db, SQL,-1, &stmt, NULL); if(ret = =SQLITE_OK) {NSLog (@"Query Successful"); //traverse the result set to get the data you have queried//Sqlite3_step getting data from a result set         while(Sqlite3_step (stmt) = =Sqlite_row) {            //parameter 1: Result set//Parameter 2: Number of columns            ConstUnsignedChar* Name = Sqlite3_column_text (stmt,0); DoubleScore = sqlite3_column_double (stmt,1); NSLog (@"%s%.2LF", name, score); }    }Else{NSLog (@"Query failed"); }}

Third, the use of SQLite in the project

For SQLite in the project, define a singleton, provide a class method that can be used globally, initialize the class at compile time, and then keep in memory, the whole program will maintain an instance, when the app exits, the system will automatically release the memory.

Because we define a singleton object for SQLite

1. New Sqlitemanager

. h file

. m implementation

@interfaceSqlitemanager () @property (nonatomic,assign) sqlite3*db;@end@implementationSqlitemanagerStaticSqlitemanager *instance;+(instancetype) shareinstance{Staticdispatch_once_t Oncetoken; Dispatch_once (&oncetoken, ^{instance=[[Self alloc] init];    }); returninstance;}//Open the database and return the Boolean value-(BOOL) opendb{//In -app database file storage path-typically stored in a sandboxNSString *documentpath =[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) lastobject]; NSString*dbpath = [Documentpath stringbyappendingpathcomponent:@"Appdb.sqlite"]; if(Sqlite3_open (dbpath.utf8string, &_db)! =SQLITE_OK) {        //Database open Failed        returnNO; }Else{        //open a successfully created table//User TableNSString *creatusertable =@"CREATE TABLE IF not EXISTS ' t_user ' (' ID ' integer not NULL PRIMARY KEY autoincrement, ' name ' TEXT, ' age ' integer, ' icon ' TEXT);"; //Car WatchNSString *creatcartable =@"CREATE TABLE IF not EXISTS ' T_car ' (' ID ' INTEGER not NULL PRIMARY KEY autoincrement, ' type ' TEXT, ' output ' REAL, ' master ' TEXT)"; //there is generally not only one table in a projectNsarray *sql_arr =[Nsarray arraywithobjects:creatusertable,creatcartable, Nil]; return[self Creattableexecsql:sql_arr]; }}-(BOOL) Creattableexecsql: (Nsarray *) sql_arr{ for(NSString *sqlinchSql_arr) {        //parameter One: database object parameter two: SQL statement needs to be executed the remaining parameters do not need to be processed        if(![self execusql:sql]) {            returnNO; }    }    returnYES;}#pragmaExecute SQL statement-(BOOL) Execusql: (NSString *) sql{Char*error; if(Sqlite3_exec (Self.db, SQL. Utf8string, nil, nil, &error) = =SQLITE_OK) {        returnYES; }Else{NSLog (@"sqlitemanager Executing SQL statement error:%s", error); returnNO; }}#pragmaMark-Querying data in the database-(Nsarray *) Querysql: (NSString *) sql{//Prepare Query//1> parameter One: database objects//2> parameter two: query statement//3> parameter Three: Length of query statement: -1//4> parameter four: handle (Cursor object)sqlite3_stmt*STMT =Nil; if(Sqlite3_prepare_v2 (Self.db, SQL. Utf8string,-1, &stmt, nil)! =SQLITE_OK) {NSLog (@"Prepare query failed!"); returnNULL; }    //prepare for success, start querying data//defines a mutable array that holds the data dictionaryNsmutablearray *DICTARRM =[[Nsmutablearray alloc] init];  while(Sqlite3_step (stmt) = =Sqlite_row) {        //get total number of columns in table (number of fields)        intColumnCount =Sqlite3_column_count (stmt); //define a dictionary that holds field dataNsmutabledictionary *dict =[[Nsmutabledictionary alloc] init];  for(inti =0; i < ColumnCount; i++) {            //Remove the field name of the I position column as the key for the dictionary            Const Char*ckey =Sqlite3_column_name (stmt, i); NSString*key =[NSString Stringwithutf8string:ckey]; //Remove the value stored in the I position as the value of the dictionary            Const Char*cvalue = (Const Char*) Sqlite3_column_text (stmt, i); NSString*value =[NSString Stringwithutf8string:cvalue]; //wraps the key and value in this field in this row data into a dictionary[Dict setobject:value Forkey:key];    } [Dictarrm addobject:dict]; }    returndictarrm;}

Above is the SQLite tool class package. Comes with GitHub address: https://github.com/zxy1829760/SQLite-.git

The above describes SQLite (contains the basic operation of the database SQL statement), you can drag Git's tool class directly into the project (if you want to package SQLite directly)--of course, there are more powerful Fmdb (the package is better-currently used more).

The article starts by saying that SQLite occupies very little resources (red callout), the next one will tell why SQLite occupies very little resources?

IOS SQLite Detailed