Ios_ Database 3_sqlite3 Basic Operations

write SQL statements directly 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 the Library, 2. Add the primary header file #import <sqlite3.h> @interface Viewcontroller ( ) {//DB represents the entire database, DB is the DB instance Sqlite3 *_db;}    @end @implementation viewcontroller-(void) viewdidload{[Super Viewdidload]; 0. Obtain the database file name in the sandbox nsstring *filename = [[Nssearchpathfordirectoriesindomains (NSDocumentDirectory, Nsuserdomainmask,    YES) Lastobject] stringbyappendingpathcomponent:@ "Student.sqlite"];    NSLog (@ "filepath:%@", filename); /* 2014-11-02 18:32:38.895 1.sqlite3 basic operation [85356:245858] filepath:/users/juns/library/developer/coresimulator/ devices/3198e002-e3c9-4523-983e-ac3e1283a654/data/containers/data/application/ E1150608-3eb8-4b9d-87af-33edf9fb6ff3/documents/student.sqlite 2014-11-02 18:32:38.896 1.sqlite3 basic operation [85,356:245,858] Successfully opened database 2014-11-02 18:32:38.897 1.sqlite3 basic operation [85,356:245,858] successfully created t_student table *//1. Create (Open) database (if it is first opened, the dataThe library file does not exist, then it is automatically created)//OC string, directly into the C language string, via the utf8string method int result = Sqlite3_open (filename.    Utf8string, &_db);                if (result = = Sqlite_ok) {NSLog (@ "Successfully open database"); 2. CREATE Table const char *SQL = "CREATE table if not exists t_student (ID integer primary key autoincrement, name text, a        GE integer); ";        Generally, it is best to clear the address, to prevent the emergence of wild pointers, C-language hollow is null char *ERRORMESG = NULL;        Parameters 3 and 4 are callbacks with int result = SQLITE3_EXEC (_db, SQL, NULL, NULL, &AMP;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"); }}//fully patterned createtable operation-(Ibaction) insertbtnclicked: (UIButton *) sender{for (int i = 0; i<30; i++) {NSString        *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, &AMP;ERRORMESG);        if (result = = Sqlite_ok) {NSLog (@ "Add data successfully");        } else {NSLog (@ "Add Data failed:%s", ERRORMESG); }}}//Method Ibid-(ibaction) deletebtnclicked: (UIButton *) sender{}//Method ditto-(Ibaction) updatebtnclicked: (UIButton *) sender{}/  /-(Ibaction) querybtnclicked: (UIButton *) sender{//SQL injection Vulnerability/** login function 1. User input account number and password * account number: 123 ' or 1 = 1 or ' = ' * Password: 456654679 2. Get the user input account number and password to the database query (query there is no user name and password) SELECT * from t_user where username = '               123 ' and password = ' 456 ';     SELECT * from t_user where username = ' 123 ' and password = ' 456 ';        *///1. Define the SQL statement const char *SQL = "SELECT ID, Name, age from t_student where name =?;";        2. Define a stmt to hold the result set to execute the static SQL statement and return the object to which it produces results sqlite3_stmt *stmt = NULL;    3. Detect the legality of the SQL statement, Parameter 3 is the length of the SQL statement, as long as write-1, will be automatically calculated, parameter 4 is statement storage result setint result = SQLITE3_PREPARE_V2 (_db, SQL,-1, &stmt, NULL);                if (result = = Sqlite_ok) {NSLog (@ "query statement is legal");                Set the contents of the placeholder, parameter 2 refers to the first few placeholder symbols, note that starting from 1, the parameter 4 is the length of the placeholder, as long as write-1, will be automatically calculated, Sqlite3_bind_text (stmt, 1, "Beyond", 1, NULL);                4.step Execute SQL statement, remove data from result set//int stepresult = Sqlite3_step (stmt); The execution result of step equals Sqlite_row, which indicates that a row of data is really queried while (sqlite3_step (stmt) = = Sqlite_row) {//get the corresponding data of this line, the result is stored                        In statement//obtains the NO. 0 column ID int sid = Sqlite3_column_int (stmt, 0);                        Gets the 1th column of the name const unsigned char *sname = Sqlite3_column_text (stmt, 1);                        Get age int sage = Sqlite3_column_int (stmt, 2) of the 2nd column;        NSLog (@ "%d%s%d", Sid, Sname, Sage);    }} else {NSLog (@ "query statement is not legal"); }} @end

Ios_ Database 3_sqlite3 Basic Operations