How to use the SQLite database in iOS development

First, using SQLite to store data, you need to add libsqlite3.dylib this dynamic library, and then add the dynamic Library's main header file #import <sqlite3.h>

DB is the symbol of the database, if you want to do crud (increase and deletion), you have to operate DB this instance
@property (nonatomic, assign) Sqlite3 *db;

First step: Open the database. When the system first uses the database, we first create a database file and create the table, just in this step.

//Get the path to the database file, that is, sandbox
nsstring *doc = [Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask, YES) Lastobject];
NSString *filename = [Doc stringbyappendingpathcomponent:@ "Students.sqlite"];
//Convert OC String to C language string
const char *cfilename = filename. utf8string;
//1. Open the database (if the database file does not exist, the Sqlite3_open function automatically creates the database file)
int result = Sqlite3_open (cFileName, &_db);    / /This open method opens the database, passing a C-language string and DB
if (result = = SQLITE_OK) {//Open successfully
NSLog (@ "Open database successfully");

//2. CREATE TABLE
const char *sql = "CREATE table IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, Name text Not NULL, the age integer is not null); ";
Char *erromsg = NULL;
result = sqlite3_exec (self.db, SQL, NULL, NULL, &ERROMSG);
if (result = = SQLITE_OK) {
NSLog (@ "Success creation table");
} else {

NSLog (@ "Genesis failure--%s--%@-%d", erromsg, [NSString stringwithutf8string:__file__], __line__); This will tell you the first line of the file in which the error
}
} else {
NSLog (@ "Failed to open database");
}

Part Two: inserting/adding data

The following is an example of inserting/adding 20 data:

for (int i = 0; i<20; i++) {
1. Splicing SQL statements
NSString *name = [NSString stringwithformat:@ "jack-%d", Arc4random_uniform (100)];
int age = Arc4random_uniform (20) + 30;
NSString *sql = [NSString stringwithformat:@ "INSERT into T_student (name, age) VALUES ('%@ ',%d);", name, age]; See the method INSERT into

2. Execute SQL statements
char *erromsg = NULL;
Sqlite3_exec (self.db, SQL.  Utf8string, NULL, NULL, &ERROMSG); The SQLite method is to insert database data
if (erromsg) {//If there is an error
NSLog (@ "Insert data failed--%s", erromsg);
} else {
NSLog (@ "Insert data successfully");
}
}

Delete the data and insert the data, just replace the SQL statement.

Query data:

const char *sql = "SELECT ID, Name, age from T_student WHERE age <= 30;"; SQL statements, query criteria
Preparation before querying
-1 means the system automatically calculates the length of the SQL statement
SQLITE3_STMT: Used to fetch data

sqlite3_stmt *stmt = NULL;
if (SQLITE3_PREPARE_V2 (self.db, SQL,-1, &stmt, NULL) = = SQLITE_OK) {//SQL statement No problem
NSLog (@ "query Statement no problem");

Each time the Sqlite3_step function is adjusted, stmt points to the next record
while (Sqlite3_step (stmt) = = Sqlite_row) {//Find a record
Remove data

Remove the value of the No. 0 column field (the value of type int)
int ID = sqlite3_column_int (stmt, 0);

Remove the value of the 1th column field (the value of Tex type)
Const unsigned char *name = Sqlite3_column_text (stmt, 1);

Remove the value of the 2nd column field (the value of type int)
int age = Sqlite3_column_int (stmt, 2);

NSLog (@ "%d%s%d", ID, name, age);
}
} else {
NSLog (@ "query statement has a problem");
}

How to use the SQLite database in iOS development