IOS SQLite database Additions and deletions
Last Update:2017-04-15
Source: Internet
Author: User
<span id="Label3"></p>1. Brief Introduction<span style="white-space: pre"><span style="white-space: pre"></span></span>Simple Package SQLite database operation class Basedb used to complete the additions and deletions of sqlite. Import Libsqlite3.0.dylib Library before use 2.basedb.h<pre name="code" class="objc"><pre name="code" class="objc"> 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: Run SQL statement * dataname: database name */-(void) createtable: (nsstring*) SQL DataBaseName: (nsstring*) dataname ;/** * run the SQL Statement. Main finish add, change, Delete * sql: Run SQL statement * params:sql in statement * dataname: database name */-(BOOL) execsql: (nsstring*) SQL Parmas :(NSARRAY*) params dataBaseName: (nsstring*) dataname;/** * Select data * sql: query SQL statement * params: query for the number of references in the SQL statement * dataname: Query database name */-(nsmutablearray*) selectsql: (nsstring*) SQL parmas: (nsarray*) params dataBaseName: ( Nsstring*) dataname; @end</pre></pre><span style="white-space: pre"><span style="white-space: pre"> </span></span>The database file that you created is located in<span style="color: #ff0000"><span style="color: #ff0000">/users/zhaochao/library/application Support/iphone simulator/7.1/applications/ 07d17328-b63c-4d87-9b6c-03aa5cd681ea/documents/zhaochao.sqlite,</span></span>is NSString *filename=[nshomedirectory () stringbyappendingformat:@ "/documents/%@", name]; This folder. The file can be opened directly with the Sqlitemanager software, or it can be installed in the Firefox browser with the Sqlitemanager plugin open, for example<br>3.basedb.m<pre name="code" class="objc">Basedb.m//sqlitedemo////Created by Carrie on 14-8-26.//Copyright (c) 2014 carrie. All rights reserved.//#import "BaseDB.h" @implementation basedb/* * Get sandbox folder * name: appended folder AA * */-(nsstring*) DataBaseName :(nsstring *) name {nsstring *filename=[nshomedirectory () stringbyappendingformat:@ "/documents/%@", name]; Return fileName;} /** * Select data * sql: Query SQL statement * params: query 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); Bind parameters for (int i=0; i<params.count; i++) {nsstring *param=[params objectatindex:i]; SqliTe3_bind_text (stmt, i+1, [param utf8string],-1, NULL); }//run 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;} /** * Run the SQL Statement. Main finish add, change, delete * sql: Run SQL statement * Params:sql in statement * dataname: database name */-(BOOL) execsql: (nsstring *) sql parmas: (nsarray *) par AMS 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); Bind parameters for (int i=0; i<params.count; i++) {nsstring *parm=[params objectatindex:i]; Sqlite3_bind_text (stmt, i+1, [parm utf8string],-1, NULL); }//run SQL Result=sqlite3_step (stmt); If (result==sqlite_error | | result==sqlite_misuse) {NSLog (@ "run SQL statement failed"); Sqlite3_close (sqlite); Return NO; }//close Database handle Sqlite3_finalize (stmt); Close the database Sqlite3_close (sqlite); NSLog (@ "ran Successfully.) "); Return YES;} /** * Create a table * sql: run 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; Run SQL int result=sqlite3_exec (sqlite, sqlch, null, null, &error); If (result!=sqlite_ok) {NSLog (@ "create failed"); NSLog (@ "%s", error); Sqlite3_close (sqlite); return; }//close Database Sqlite3_close (sqlite); NSLog (@ "create success"); }} @end</pre><br>4. Call Format<pre name="code" class="objc">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 (?<p><p></p></p><p><p>,?)"; Nsarray *[email protected][@ "acasdfaa", @ "bb"]; [db execsql:inserttable parmas:insertparmas databasename:@ "zhaochao.sqlite"]; Change 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=?</p></p><p><p>"; Nsarray *[email protected][@ "aa"]; [db execsql:deletetable parmas:deleteparams databasename:@ "zhaochao.sqlite"]; Query 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); }</p></p></pre><br><br><br> <p><p>IOS SQLite database Additions and deletions</p></p></span>