IOS SQLite database Additions and deletions

1. IntroductionSimple Package SQLite database operation class Basedb is used to complete the additions and deletions of SQLite, first import Libsqlite3.0.dylib library before use 2.basedb.h

  basedb.h//  sqlitedemo////  Created by Carrie on 14-8-26.//  Copyright (c) 2014 Carrie. All rights reserved.//#import <Foundation/Foundation.h> #import "sqlite3.h" @interface basedb:nsobject/** *  Create a table *  sql: Executed SQL statement *  dataname: Database name */-(void) CreateTable: (nsstring*) SQL DataBaseName: (nsstring*) Dataname /** *  Execute SQL statement, mainly complete add, modify, delete *  sql: Execute SQL statement *  PARAMS:SQL statement parameters *  dataname: Database name */-(BOOL) Execsql: ( nsstring*) SQL Parmas: (nsarray*) params dataBaseName: (nsstring*) dataname;/** *  SELECT data *  sql: Query SQL statement *  Params: Querying the parameters in the SQL statement *  dataname: Querying the database name */-(nsmutablearray*) Selectsql: (nsstring*) SQL Parmas: (nsarray*) params DataBaseName: (nsstring*) dataname; @end

The database file that you created is located in/users/zhaochao/library/application Support/iphone simulator/7.1/applications/ 07d17328-b63c-4d87-9b6c-03aa5cd681ea/documents/zhaochao.sqlite,is NSString *filename=[nshomedirectory () stringbyappendingformat:@ "/documents/%@", name]; This directory. The file can be opened directly with the Sqlitemanager software, or the Sqlitemanager plugin can be installed in the Firefox browser, as
3.basedb.m

basedb.m//sqlitedemo////Created by Carrie on 14-8-26.//Copyright (c) 2014 Carrie. All rights reserved.//#import "BaseDB.h" @implementation basedb/* * Get the Sandbox directory * Name: appended directory AA * */-(nsstring*) DataBaseName: (    NSString *) name {nsstring *filename=[nshomedirectory () stringbyappendingformat:@ "/documents/%@", name]; return fileName;} /** * Select data * sql: Query SQL statement * Params: Query for parameters in SQL statement * dataname: Query Database name */-(nsmutablearray*) Selectsql: (NSString *) SQL Parm    As: (Nsarray *) params dataBaseName: (NSString *) dataname{sqlite3 *sqlite=nil;    Sqlite3_stmt *stmt=nil;    Open database NSString *filename=[self databasename:dataname];    int result= Sqlite3_open ([FileName utf8string], &sqlite);        if (RESULT!=SQLITE_OK) {NSLog (@ "Open failed");    return nil;    } const char* Sqlch=[sql utf8string];    Compile SQL statement sqlite3_prepare_v2 (SQLite, Sqlch,-1, &stmt, NULL);        binding parameters for (int i=0; i<params.count; i++) {nsstring *param=[params objectatindex:i]; Sqlite3_bind_text (stmt, i+1, [param utf8string],-1, NULL);        }//Execute Query Statement result=sqlite3_step (stmt);    Nsmutablearray *resultdata=[nsmutablearray Array];        Traversal result while (Result==sqlite_row) {nsmutabledictionary *resultrow=[nsmutabledictionary dictionary];        Gets the number of fields int col_count = Sqlite3_column_count (stmt);            for (int i=0; i<col_count; i++) {//Get field name Const char*columname=sqlite3_column_name (stmt,i);            Gets the field value char* columvalue= (char*) sqlite3_column_text (stmt, i);            NSString *columkeystr=[nsstring Stringwithcstring:columname encoding:nsutf8stringencoding];            NSString *columvaluestr=[nsstring Stringwithcstring:columvalue encoding:nsutf8stringencoding];        [Resultrow setobject:columvaluestr forkey:columkeystr];        } [Resultdata Addobject:resultrow];    Result=sqlite3_step (stmt);    }//Close database handle sqlite3_finalize (stmt);    Close Database Sqlite3_close (SQLite); NSLOG (@ "Query finished!    "); return resultdata;} /** * Execute SQL statement, mainly complete add, modify, delete * sql: Executed SQL statement * PARAMS:SQL statement parameters * Dataname: Database name */-(BOOL) Execsql: (NSString *) SQL Parmas    :(Nsarray *) params dataBaseName: (NSString *) dataname{sqlite3 *sqlite=nil;    Sqlite3_stmt *stmt=nil;    Open database NSString *filename=[self databasename:dataname];    int result= Sqlite3_open ([FileName utf8string], &sqlite);        if (RESULT!=SQLITE_OK) {NSLog (@ "Open failed");    return NO;    } const char* Sqlch=[sql utf8string];    Compile SQL statement sqlite3_prepare_v2 (SQLite, Sqlch,-1, &stmt, NULL);        binding parameters for (int i=0; i<params.count; i++) {nsstring *parm=[params objectatindex:i];    Sqlite3_bind_text (stmt, i+1, [Parm utf8string],-1, NULL);    }//Execute SQL Result=sqlite3_step (stmt);        if (Result==sqlite_error | | result==sqlite_misuse) {NSLOG (@ "Execute SQL statement failed");        Sqlite3_close (SQLite);    return NO;    }//Close database handle sqlite3_finalize (stmt); Close Database Sqlite3_close (sqlite); NSLog (@ "executed successfully!    "); return YES;}    /** * Create a table * sql: Executed SQL statement * Dataname: Database name */-(void) CreateTable: (NSString *) SQL DataBaseName: (NSString *) dataname{    Sqlite3 *sqlite=nil;    NSString *filename=[self Databasename:dataname];    Open database int result= sqlite3_open ([FileName utf8string], &sqlite);    if (RESULT!=SQLITE_OK) {NSLog (@ "Open failed");        }else{Const char* Sqlch=[sql utf8string];        char* error;        Execute SQL int result=sqlite3_exec (sqlite, sqlch, NULL, NULL, &AMP;ERROR);            if (RESULT!=SQLITE_OK) {NSLog (@ "Create failed");            NSLog (@ "%s", error);            Sqlite3_close (SQLite);        return;        }//Close database Sqlite3_close (SQLite);    NSLog (@ "create success"); }} @end

4. Call format

        Basedb *db=[[basedb alloc] init];    CREATE TABLE NSString *[email protected] "CREATE TABLE Zhaochao (username text primary key,userpasswd test)";   NSString *[email protected] "zhaochao.sqlite";        [DB Createtable:dbcreate databasename:dbname];    Add Data nsstring *[email protected] "insert INTO Zhaochao (USERNAME,USERPASSWD) VALUES (?,?)";   Nsarray *[email protected][@ "Acasdfaa", @ "BB"];        [DB execsql:inserttable Parmas:insertparmas databasename:@ "Zhaochao.sqlite"]; Modify Data nsstring *[email protected] "update Zhaochao set username=?    where username=? ";   Nsarray *[email protected][@ "admin", @ "Zhaochao"];        [DB execsql:updatetable parmas:updateparams databasename:@ "Zhaochao.sqlite"];    Delete data nsstring *[email protected] "delete from Zhaochao where username=?";   Nsarray *[email protected][@ "AA"];        [DB execsql:deletetable parmas:deleteparams databasename:@ "Zhaochao.sqlite"]; Querying data NSString *[emaiL protected] "Select username,userpasswd from Zhaochao where userpasswd=?";    NSString *[email protected][@ "BB"];    Nsarray *result=[db selectsql:selecttable parmas:selectparam databasename:@ "Zhaochao.sqlite"];        for (int i=0; i<result.count; i++) {nsmutabledictionary *arr=[result objectatindex:i];    NSLog (@ "%@", arr);         }

IOS SQLite database Additions and deletions