Basic iOS _ database 3_sqlite3 operations, ios3_sqlite3

Basic iOS _ database 3_sqlite3 operations, ios3_sqlite3
Finally:
Sqlite3 function Summary

1. open the database int sqlite3_open (const char * filename, // The database file path sqlite3 ** ppDb // database instance); 2. execute any SQL statement int sqlite3_exec (sqlite3 *, // an open database instance const char * SQL, // The SQL statement int (* callback) (void *, int, char **, char **), // callback void * after the SQL statement is executed, // The 1st parameter char ** errmsg // error message of the callback function ); 3. check the validity of SQL statements (preparations before query) int sqlite3_prepare_v2 (sqlite3 * db, // database instance const char * zSql, // The SQL statement int nByte to be checked, // SQL statement maximum byte length sqlite3_stmt ** ppStmt, // sqlite3_stmt instance, used to obtain database data const char ** pzTail); 4. query a row of Data int sqlite3_step (sqlite3_stmt *); // If a row of data is queried, SQLITE_ROW5 is returned. use stmt to obtain the value of a certain field (the subscript of the field starts from 0) double sqlite3_column_double (sqlite3_stmt *, int iCol); // floating point data int sqlite3_column_int (sqlite3_stmt *, int I ); // sqlite3_int64 sqlite3_column_int64 (sqlite3_stmt *, int iCol); // const void * sqlite3_column_blob (sqlite3_stmt *, int iCol ); // const unsigned char * sqlite3_column_text (sqlite3_stmt *, int iCol); // string data

Directly write SQL statements in the controller, not encapsulated

//// ViewController. m // 1. sqlite3 basic operations // Created by xss on 14-11-2. // Copyright (c) 2014 beyond. all rights reserved. // # import "ViewController. h "// 1. import to database, 2. add the primary header file # import <sqlite3.h> @ interface ViewController () {// db represents the entire database, and db is the database instance sqlite3 * _ db ;} @ end @ implementation ViewController-(void) viewDidLoad {[super viewDidLoad]; // 0. obtain the database file name NSString * filename = [[NSSearchPathForDirectoriesInDomain S (NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent: @ "student. sqlite "]; NSLog (@" filePath: % @ ", filename);/* 18:32:38. 895 1. sqlite3 basic operations [85356: 245858] filePath:/Users/juns/Library/Developer/CoreSimulator/Devices/connectors/data/Containers/Data/Application/E1150608-3EB8-4B9D-87AF-33EDF9FB6FF3/Documents/student. sqlite 2014-11-02 18:32:38. 896 1. sqlite3 basic operation [85356: 245858] successfully opened the database 18:32:38. 897 1. sqlite3 basic operation [85356: 245858] successfully created the t_student table * // 1. create (open) a database (if the database file does not exist for the first time, it is automatically created) // OC string, which is directly converted into a C string, the UTF8String method int result = sqlite3_open (filename. UTF8String, & _ db); if (result = SQLITE_ OK) {NSLog (@ "database opened successfully"); // 2. create table const char * SQL = "create table if not exists t_student (id integer primary key autoincrement, Name text, age integer); "; // If You Want To transmit the address, it is best to clear it first to prevent the occurrence of a wild pointer. The C language is NULL char * errorMesg = NULL; // when parameters 3 and 4 are called back, use int result = 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") ;}// create createTable operation completely-(IBAction) insertBtnClicked :( UIButton *) sender {for (int I = 0; I <30; I ++) {NSStri Ng * name = [NSString stringWithFormat: @ "beyond-% d", arc4random () % 100]; int age = arc4random () % 100; NSString * SQL = [NSString stringWithFormat: @ "insert into t_student (name, age) values ('% @', % d);", name, age]; char * errorMesg = NULL; int result = sqlite3_exec (_ db, SQL. UTF8String, NULL, NULL, & errorMesg); if (result = SQLITE_ OK) {NSLog (@ "added data");} else {NSLog (@ "failed to add data: % s ", errorMesg) ;}}// party Same method as above-(IBAction) deleteBtnClicked :( UIButton *) sender {} // method same as above-(IBAction) updateBtnClicked :( UIButton *) sender {} //-(IBAction) queryBtnClicked :( UIButton *) sender {// SQL injection vulnerability/** logon function 1. user input account and password * account: 123 'or 1 = 1 or ''=' * password: 456654679 2. obtain the account and password entered by the user to go to the database for query (check whether the user name and password are available) select * from t_user where username = '000000' and password = '000000 '; select * from t_user where username = '000000' and password = '000000'; * // 1. Define the SQL statement const char * SQL = "select id, name, age from t_student where name = ?; "; // 2. defines an stmt storage result set, which is used to execute a static SQL statement and return the object sqlite3_stmt * stmt = NULL; // 3. check the validity of the SQL statement. Parameter 3 is the length of the SQL statement. If you write-1, it is automatically calculated. Parameter 4 is the result set int result = sqlite3_prepare_v2 (_ db, SQL, -1, & stmt, NULL); if (result = SQLITE_ OK) {NSLog (@ "the query statement is valid"); // sets the placeholder content, parameter 2 refers to the placeholder symbols. Note that it starts from 1. Parameter 4 is the length of the placeholder. If you write-1, it is automatically calculated. sqlite3_bind_text (stmt, 1, "beyond",-1, NULL); // 4. step: Execute the SQL statement and retrieve data from the result set // int stepResult = sqlite3_step (stmt); // The execution result of step is equal to SQLITE_ROW, it indicates that a row of data is queried while (sqlite3_step (stmt) = SQLITE_ROW) {// you can obtain the corresponding data of this row, result stored in statement // obtain the id int sid of column 0th = sqlite3_column_int (stmt, 0); // obtain the name const unsigned char * sname = sqlite3_column_text (stmt, 1); // get the age int sage = sqlite3_column_int (stmt, 2); NSLog (@ "% d % s % d", sid, sname, sage) ;}} else {NSLog (@ "the query statement is invalid") ;}}@ end

Use tool class encapsulation model

//// Student. h // 2_database tool package /// Created by xss on 14-11-2. // Copyright (c) 2014 beyond. all rights reserved. // # import <Foundation/Foundation. h> @ interface Student: NSObject // Student ID @ property (nonatomic, assign) int ID; // name @ property (nonatomic, copy) NSString * name; // age @ property (nonatomic, assign) int age; @ end

Tool class StudentDAO

//// StudentDAO. h // 2_database tool package /// Created by xss on 14-11-2. // Copyright (c) 2014 beyond. all rights reserved. // CRUD (add, delete, modify, and query) of student data # import <Foundation/Foundation. h> @ class Student; @ interface StudentDAO: NSObject/*** add student ** @ param Student student to be added */+ (BOOL) addStudent :( Student *) student; /*** get all students ** @ return the array is loaded with the IWStudent model */+ (NSArray *) students; /*** obtain the corresponding student based on the Search condition ** @ param condition Search condition */+ (NSArray *) studentsWithCondition :( NSString *) condition; @ end

Key encapsulated code

//// StudentDAO. m // 2 _ database tool package /// Created by xss on 14-11-2. // Copyright (c) 2014 beyond. all rights reserved. // # import "StudentDAO. h "# import" Student. h "# import <sqlite3.h> @ implementation StudentDAO // key ~~~~ With static, the variable _ db is only accessed by StudentDAO. m. static sqlite3 * _ db. // key ~~~~ Call the initialize method + (void) initialize {// 0 when the tool class is loaded for the first time. obtain the database file name NSString * filename = [[NSSearchPathForDirectoriesInDomains (NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent: @ "student. sqlite "]; // 1. create (open) The database (if the database file does not exist, it will be automatically created) int result = sqlite3_open (filename. UTF8String, & _ db); if (result = SQLITE_ OK) {NSLog (@ "database opened successfully"); // 2. create table const char * SQL = "create table If not exists t_student (id integer primary key autoincrement, name text, age integer); "; char * errorMesg = NULL; int result = 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") ;}for (int I = 0; I <30; I ++) {NSString * name = [NSString stringWithFormat: @ "beyond-% d", arc 4 random () % 100]; int age = arc4random () % 100; NSString * SQL = [NSString stringWithFormat: @ "insert into t_student (name, age) values ('% @', % d); ", name, age]; char * errorMesg = NULL; int result = sqlite3_exec (_ db, SQL. UTF8String, NULL, NULL, & errorMesg); if (result = SQLITE_ OK) {NSLog (@ "added data");} else {NSLog (@ "failed to add data: % s ", errorMesg) ;}}+ (BOOL) addStudent :( Student *) student {// All strings in Sqlite3 must NSString * SQL = [NSString stringWithFormat: @ "insert into t_student (name, age) values ('% @', % d);", student. name, student. age]; char * errorMesg = NULL; // two NULL values indicate the callback method. int result = sqlite3_exec (_ db, SQL. UTF8String, NULL, NULL, & errorMesg); return result = SQLITE_ OK;} // returns an array of all student objects + (NSArray *) students {// 0. define the array NSMutableArray * students = nil; // 1. define SQL statement const char * SQL = "select Id, name, age from t_student; "; // 2. define a stmt to store the result set sqlite3_stmt * stmt = NULL; // 3. check SQL statement validity int result = sqlite3_prepare_v2 (_ db, SQL,-1, & stmt, NULL); if (result = SQLITE_ OK) {NSLog (@ "the query statement is valid"); students = [NSMutableArray array]; // 4. execute the SQL statement and retrieve the data from the result set while (sqlite3_step (stmt) = SQLITE_ROW) {// a row of data is queried. // obtain the corresponding row of data. Student * student = [[Student alloc] init]; // obtain the id student in column 0th. ID = sq Lite3_column_int (stmt, 0); // obtain the name const unsigned char * sname = sqlite3_column_text (stmt, 1) of column 1st; student. name = [NSString stringwithuf8string :( const char *) sname]; // you can get the age student in Column 2nd. age = sqlite3_column_int (stmt, 2); // Add it to the array [students addObject: student] ;}} else {NSLog (@ "the query statement is invalid ");} return students;} // fuzzy query + (NSArray *) studentsWithCondition :( NSString *) condition {// 0. define the NSMutableArra Array Y * students = nil; // 1. Define an SQL statement with fuzzy query '% Lin %' const char * SQL = "select id, name, age from t_student where name like ?; "; // 2. define a stmt to store the result set sqlite3_stmt * stmt = NULL; // 3. check SQL statement validity int result = sqlite3_prepare_v2 (_ db, SQL,-1, & stmt, NULL); if (result = SQLITE_ OK) {NSLog (@ "the query statement is valid"); students = [NSMutableArray array]; // fill the placeholder content, % is a keyword in OC, therefore, escape NSString * newCondition = [NSString stringWithFormat: @ "% @ %", condition]; sqlite3_bind_text (stmt, 1, newCondition. UTF8String,-1, NULL); // 4. execute the SQL statement and retrieve the data from the result set while (sqlite3_step (stmt) = SQLITE_ROW) {// a row of data is queried. // obtain the corresponding row of data. Student * student = [[Student alloc] init]; // obtain the id student in column 0th. ID = sqlite3_column_int (stmt, 0); // obtain the name const unsigned char * sname = sqlite3_column_text (stmt, 1) In column 1st; student. name = [NSString stringwithuf8string :( const char *) sname]; // you can get the age student in Column 2nd. age = sqlite3_column_int (stmt, 2); // Add it to the array [students addObject: student] ;}} else {NSLog (@ "invalid query statement");} return students ;} @ end

The tool class in the Controller provides data sources for tableView and listens to fuzzy queries in the search box.

//// ViewController. m // 2 _ database tool package /// Created by xss on 14-11-2. // Copyright (c) 2014 beyond. all rights reserved. // # import "ViewController. h "# import" Student. h "# import" StudentDAO. h "@ interface ViewController () <UISearchBarDelegate> // an array of encapsulated objects returned from the database, providing the data source @ property (nonatomic, strong) NSArray * students for tableView; @ end @ implementation ViewController # pragma mark-lazy loading-(NSArray *) students {if (_ students = nil) {_ students = [StudentDAO students];} return _ students;}-(void) viewDidLoad {[super viewDidLoad]; // Add a search box and set the proxy UISearchBar * searchBar = [[UISearchBar alloc] initWithFrame: CGRectMake (0, 0,320, 44)]; searchBar. delegate = self; // headView self. tableView. tableHeaderView = searchBar;} # pragma mark-search box proxy-(void) searchBar :( UISearchBar *) searchBar textDidChange :( NSString *) searchText {// directly overwrite the original object array self. students = [StudentDAO studentsWithCondition: searchText]; // refresh the table [self. tableView reloadData] ;}# pragma mark-tableView proxy method-(NSInteger) tableView :( UITableView *) tableView numberOfRowsInSection :( NSInteger) section {return self. students. count;}-(UITableViewCell *) tableView :( UITableView *) tableView cellForRowAtIndexPath :( NSIndexPath *) indexPath {// 1. create cell static NSString * ID = @ "student"; UITableViewCell * cell = [tableView dequeueReusableCellWithIdentifier: ID]; if (cell = nil) {cell = [[UITableViewCell alloc] initWithStyle: UITableViewCellStyleSubtitle reuseIdentifier: ID];} // 2. set cell data Student * stu = self. students [indexPath. row]; cell. textLabel. text = stu. name; cell. detailTextLabel. text = [NSString stringWithFormat: @ "% d", stu. age]; return cell;}-(void) tableView :( UITableView *) tableView didSelectRowAtIndexPath :( NSIndexPath *) indexPath {[self. view endEditing: YES];} @ end

How to operate the sqlite3 database in ios

Use the Open Source library FMDB! Sqlite3 is encapsulated and easy to use!

Create ios program for accessing SQlite Database

Now, if SQLite is used, coreData is used. Your function is to create a page, which has nothing to do with database storage. Do not store Data in the database. This will make the database very large and inconvenient to read. Store the address or URL and display it.