IOS Database SQLite
Last Update:2016-04-13
Source: Internet
Author: User
<span id="Label3"></p>SQLite Introduction SQLite is a lightweight embedded database, both Android and iOS development using SQLite database. It is characterized by: <ul> <ul> <li>It occupies very low resources, in the embedded device, may only need hundreds of K of memory is Enough.</li> <li>Its processing speed is faster than the two famous databases of MySQL and Postgresql.</li> <li>It is a C language framework, strong cross-platform.</li> </ul> </ul>To use SQLite3 in ios, you need to import the Libsqlite3 library in Xcode<p><p></p></p>The following is a combination of SQL statements to complete database operations, using the method defined Above:<pre class="prettyprint"><code class=" hljs sql">To create a table:<span class="hljs-operator"><span class="hljs-operator"> <span class="hljs-keyword">Create</span> <span class="hljs-keyword"></span> table table name (field <span class="hljs-number">1</span>, field <span class="hljs-number">2</span>,......, field n,[table-level constraint]) [type= table type];</span></span>Insert Record:<span class="hljs-operator"><span class="hljs-operator"> <span class="hljs-keyword">Insert</span> <span class="hljs-keyword"></span> into table name (field <span class="hljs-number">1</span>,......, field n) <span class="hljs-keyword"></span> values (value <span class="hljs-number">1</span>,......, value n);</span></span>To delete a record:<span class="hljs-operator"><span class="hljs-operator"> <span class="hljs-keyword">Delete</span> <span class="hljs-keyword"></span> from table name <span class="hljs-keyword">where</span> conditional expression;</span></span>To modify a record:<span class="hljs-operator"><span class="hljs-operator"> <span class="hljs-keyword">update</span> table name <span class="hljs-keyword">set</span> field name <span class="hljs-number">1</span>= value <span class="hljs-number">1</span>,......, field name n= value n <span class="hljs-keyword">where</span> condition expression;</span></span>To view Records:<span class="hljs-operator"><span class="hljs-operator"> <span class="hljs-keyword">Select</span> field <span class="hljs-number">1</span>,......, field n <span class="hljs-keyword"></span> from table name <span class="hljs-keyword">where</span> conditional expression;</span></span></code></pre><p><p>1. Open the Database<br>The C language functions used are as follows:</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs cs"><span class="hljs-comment">/* 打开数据库 */</span><span class="hljs-keyword">int</span> sqlite3_open( <span class="hljs-keyword">const</span><span class="hljs-keyword">char</span> *filename, <span class="hljs-comment">/* 数据库路径(UTF-8) */</span> sqlite3 **pDb <span class="hljs-comment">/* 返回的数据库句柄 */</span>);</code></pre></pre><p><p>2, Querying the database<br>C-language functions Used:</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs cs"><span class="hljs-comment">/* 执行有返回结果的SQL语句 */</span><span class="hljs-keyword">int</span> sqlite3_prepare_v2( sqlite3 *db, <span class="hljs-comment">/* 数据库句柄 */</span> <span class="hljs-keyword">const</span><span class="hljs-keyword">char</span> *zSql, <span class="hljs-comment">/* SQL语句(UTF-8) */</span> <span class="hljs-keyword">int</span> nByte, <span class="hljs-comment">/* SQL语句最大长度，-1表示SQL支持的最大长度 */</span> sqlite3_stmt **ppStmt, <span class="hljs-comment">/* 返回的查询结果 */</span> <span class="hljs-keyword">const</span><span class="hljs-keyword">char</span> **pzTail <span class="hljs-comment">/* 返回的失败信息*/</span>);</code></pre></pre><p><p>3, Inserting data<br>C-language functions Used:</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs cs"><span class="hljs-comment">/* 执行有返回结果的SQL语句 */</span><span class="hljs-keyword">int</span> sqlite3_prepare( sqlite3 *db, <span class="hljs-comment">/* 数据库句柄 */</span> <span class="hljs-keyword">const</span><span class="hljs-keyword">char</span> *zSql, <span class="hljs-comment">/* SQL语句(UTF-8) */</span> <span class="hljs-keyword">int</span> nByte, <span class="hljs-comment">/* SQL语句最大长度，-1表示SQL支持的最大长度 */</span> sqlite3_stmt **ppStmt, <span class="hljs-comment">/* 返回的查询结果 */</span> <span class="hljs-keyword">const</span><span class="hljs-keyword">char</span> **pzTail <span class="hljs-comment">/* 返回的失败信息*/</span>);</code></pre></pre><p><p>4. Deleting a database<br>C-language functions Used:</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs cs"><span class="hljs-comment">/* 执行没有返回的SQL语句 */</span><span class="hljs-keyword">int</span> sqlite3_exec( sqlite3 *db, <span class="hljs-comment">/* 数据库句柄 */</span> <span class="hljs-keyword">const</span><span class="hljs-keyword">char</span> *sql, <span class="hljs-comment">/* SQL语句(UTF-8) */</span> <span class="hljs-keyword">int</span> (*callback)(<span class="hljs-keyword">void</span>*,<span class="hljs-keyword">int</span>,<span class="hljs-keyword">char</span>**,<span class="hljs-keyword">char</span>**), <span class="hljs-comment">/* 回调的C函数指针 */</span> <span class="hljs-keyword">void</span> *arg, <span class="hljs-comment">/* 回调函数的第一个参数 */</span> <span class="hljs-keyword">char</span> **errmsg <span class="hljs-comment">/* 返回的错误信息 */</span>);</code></pre></pre><p><p>5. Close the database<br>C-language functions Used:</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs cs"><span class="hljs-comment">/* 关闭数据库 */</span><span class="hljs-keyword">int</span> sqlite3_close(sqlite3 *db);</code></pre></pre><p><p>Here's a small example:</p></p><p><p>Here the request data is the user name and ID of the microblog to define a model</p></p><p><p>DataModel.h</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs objectivec"><span class="hljs-preprocessor">#import <span class="hljs-title"><Foundation/Foundation.h></span></span><span class="hljs-class"><span class="hljs-keyword">@interface</span> <span class="hljs-title">DataModel</span> : <span class="hljs-title">NSObject</span></span><span class="hljs-keyword">@property</span> (<span class="hljs-keyword">nonatomic</span><span class="hljs-built_in">NSString</span> *screen_name;<span class="hljs-keyword">@property</span> (<span class="hljs-keyword">nonatomic</span><span class="hljs-built_in">NSString</span> *avatar_hd;- (instancetype)initWithDictionary:(<span class="hljs-built_in">NSArray</span> *)dictionary;<span class="hljs-keyword">@end</span></code></pre></pre><p><p>datamodel.m</p></p><pre class="prettyprint"><pre class="prettyprint"><code class="hljs objectivec"><span class="hljs-preprocessor"> #import <span class="hljs-title"> "DataModel.h" </span> </span> <span class="hljs-class"><span class="hljs-keyword"> @implementation </span> <span class="hljs-title">datamodel </span> </span>-(instancetype) initwithdictionary: (<span class="hljs-built_in"> Nsdictionary </span> *) dictionary {<span class="hljs-keyword">if </span> (<span class="hljs-keyword">self </span> = [ Span class= "hljs-keyword" >super init]) {[<span class=" Hljs-keyword ">self </span> setvaluesforkeyswithdiction ary:dictionary]; } <span class="hljs-keyword">return </span> <span class="hljs-keyword">self </span>;} -(<span class="hljs-keyword">void </span>) setValue: (<span class="hljs-keyword">id </span>) value forundefinedkey: ( <span class="hljs-built_in">nsstring </span> *) key {<span class="hljs-built_in">nslog </span> (@<span class=" Hljs-string ">"%@ "</span>, key);} <span class="hljs-keyword"> @end </span> </code> </pre></pre><p><p>When defining a class that processes a database</p></p><p><p>DataBase.h</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs java">#<span class="hljs-keyword">import</span> <Foundation/Foundation.h><span class="hljs-javadoc">/** 导入DataModel*/</span>#<span class="hljs-keyword">import</span><span class="hljs-string">"DataModel.h"</span><span class="hljs-annotation">@interface</span> DataBase : NSObject<span class="hljs-javadoc">/** 打开数据库*/</span>+ (<span class="hljs-keyword">void</span>)openDB;<span class="hljs-javadoc">/** 查*/</span>+ (NSArray *)find;<span class="hljs-javadoc">/** 插*/</span><span class="hljs-javadoc">/** 删除*/</span>+ (BOOL)deleteModel:(DataModel *)dataModel;<span class="hljs-annotation">@end</span></code></pre></pre><p><p>Database.m</p></p><pre class="prettyprint"><code class=" hljs objectivec"><span class="hljs-preprocessor"><span class="hljs-preprocessor">#import <span class="hljs-title">"DataBase.h"</span> </span></span><span class="hljs-preprocessor"><span class="hljs-preprocessor">#import <span class="hljs-title"><sqlite3.h></span> </span></span><span class="hljs-class"><span class="hljs-class"> <span class="hljs-keyword">@implementation</span> <span class="hljs-title">DataBase</span> </span></span><span class="hljs-comment"><span class="hljs-comment">//create a Database object</span></span><span class="hljs-keyword"><span class="hljs-keyword">Static</span></span>Sqlite3 *db;<span class="hljs-preprocessor"><span class="hljs-preprocessor">#pragma mark-open Database</span></span>+ (<span class="hljs-keyword"><span class="hljs-keyword">void</span></span>) Opendb {<span class="hljs-comment"><span class="hljs-comment">//move The imported database to the Documents folder</span></span> <span class="hljs-comment"><span class="hljs-comment">//first Create a database outside to import the database into the project "this step must be done"</span></span> <span class="hljs-comment"><span class="hljs-comment">//get the path to the database</span></span> <span class="hljs-comment"><span class="hljs-comment">//here WeiBo is the name of the database created (do not mistake)</span></span> <span class="hljs-built_in"><span class="hljs-built_in">NSString</span></span>*originpath = [[<span class="hljs-built_in"><span class="hljs-built_in">NSBundle</span></span>mainbundle] pathforresource:@<span class="hljs-string"><span class="hljs-string">"WeiBo"</span></span>oftype:@<span class="hljs-string"><span class="hljs-string">"sqlite"</span></span>];<span class="hljs-comment"><span class="hljs-comment">//Documents folder and then stitch the path</span></span> <span class="hljs-built_in"><span class="hljs-built_in">NSString</span></span>*targetpath = [[nssearchpathfordirectoriesindomains (nsdocumentdirectory, nsuserdomainmask,<span class="hljs-literal"><span class="hljs-literal">YES</span></span>) objectatindex:<span class="hljs-number"><span class="hljs-number">0</span></span>] stringbyappendingpathcomponent:@<span class="hljs-string"><span class="hljs-string">"collection.sqlite"</span></span>];<span class="hljs-built_in"><span class="hljs-built_in">Nsfilemanager</span></span>*manager = [<span class="hljs-built_in"><span class="hljs-built_in">Nsfilemanager</span></span>defaultmanager];<span class="hljs-keyword"><span class="hljs-keyword">if</span></span>(! [manager Fileexistsatpath:targetpath]) {[manager Moveitematpath:originpath Topath:targetpath error:<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>]; }<span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*filename = (<span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*) [targetpath utf8string];<span class="hljs-comment"><span class="hljs-comment">//open Database</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span>result = Sqlite3_open (filename, &db);<span class="hljs-keyword"><span class="hljs-keyword">if</span></span>(result = = Sqlite_ok) {<span class="hljs-built_in"><span class="hljs-built_in">NSLog</span></span>(@<span class="hljs-string"><span class="hljs-string">"database Open successfully"</span></span>); }<span class="hljs-keyword"><span class="hljs-keyword">Else</span></span>{<span class="hljs-built_in"><span class="hljs-built_in">NSLog</span></span>(@<span class="hljs-string"><span class="hljs-string">"database Open failed"</span></span>); }}<span class="hljs-preprocessor"><span class="hljs-preprocessor">#pragma mark-check</span></span>+ (<span class="hljs-built_in"><span class="hljs-built_in">Nsarray</span></span>*) Find {<span class="hljs-built_in"><span class="hljs-built_in">Nsmutablearray</span></span>*marray = [<span class="hljs-built_in"><span class="hljs-built_in">Nsmutablearray</span></span>array];<span class="hljs-comment"><span class="hljs-comment">//open Database</span></span>[<span class="hljs-keyword"><span class="hljs-keyword"></span> self</span>opendb];<span class="hljs-comment"><span class="hljs-comment">//statement of a</span> statement</span>Sqlite3_stmt *stmt =<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>;<span class="hljs-comment"><span class="hljs-comment">//check Table</span></span> <span class="hljs-comment"><span class="hljs-comment">//here WeiBo is the name of the table under CREATE database (do not Mistake)</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Const</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*sql =<span class="hljs-string"><span class="hljs-string">"select *from weiBo"</span></span>;<span class="hljs-comment"><span class="hljs-comment">//translate SQL text into a statement object</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span>result = Sqlite3_prepare_v2 (db, sql,-<span class="hljs-number"><span class="hljs-number">1</span></span>, &stmt,<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>);<span class="hljs-keyword"><span class="hljs-keyword">if</span></span>(result = = Sqlite_ok) {<span class="hljs-built_in"><span class="hljs-built_in">NSLog</span></span>(@<span class="hljs-string"><span class="hljs-string">"prepare for Query success"</span></span>);<span class="hljs-keyword"><span class="hljs-keyword"></span> while</span>(sqlite3_step (stmt) = = Sqlite_row) {<span class="hljs-comment"><span class="hljs-comment">//extract Data</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Const</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*screen_name = (<span class="hljs-keyword"><span class="hljs-keyword">Const</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*) Sqlite3_column_text (stmt,<span class="hljs-number"><span class="hljs-number">0</span></span>);<span class="hljs-keyword"><span class="hljs-keyword">Const</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*AVATAR_HD = (<span class="hljs-keyword"><span class="hljs-keyword">Const</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*) Sqlite3_column_text (stmt,<span class="hljs-number"><span class="hljs-number">1</span></span>); Datamodel *datamodel = [[datamodel alloc] init]; Datamodel<span class="hljs-variable"><span class="hljs-variable">. Screen_name</span></span>= [<span class="hljs-built_in"><span class="hljs-built_in">NSString</span></span>Stringwithcstring:screen_name encoding:<span class="hljs-number"><span class="hljs-number">4</span></span>]; Datamodel<span class="hljs-variable"><span class="hljs-variable">. AVATAR_HD</span></span>= [<span class="hljs-built_in"><span class="hljs-built_in">NSString</span></span>STRINGWITHCSTRING:AVATAR_HD encoding:<span class="hljs-number"><span class="hljs-number">4</span></span>];<span class="hljs-comment"><span class="hljs-comment">//"nsnumber type" [nsnumber numberwithinteger:[[nsstring stringwithcstring:deal_id encoding:4] integerValue]];</span></span>[marray addobject:datamodel]; } }<span class="hljs-keyword"><span class="hljs-keyword">return</span></span>marray;}<span class="hljs-preprocessor"><span class="hljs-preprocessor">#pragma mark-plug</span></span>+ (<span class="hljs-built_in"><span class="hljs-built_in">BOOL</span></span>) Insertmodel: (datamodel *) Datamodel {<span class="hljs-comment"><span class="hljs-comment">//open Database</span></span>[<span class="hljs-keyword"><span class="hljs-keyword"></span> self</span>opendb]; Sqlite3_stmt *stmt =<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>;<span class="hljs-comment"><span class="hljs-comment">//here WeiBo is the name of the table under CREATE database (do not Mistake)</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Const</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Char</span></span>*sql =<span class="hljs-string"><span class="hljs-string">"insert into WeiBo (screen_name, avatar_hd) values (?,?)"</span></span>;<span class="hljs-comment"><span class="hljs-comment">//convert SQL text to a statement object</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span>result = Sqlite3_prepare (db, sql,-<span class="hljs-number"><span class="hljs-number">1</span></span>, &stmt,<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>);<span class="hljs-keyword"><span class="hljs-keyword">if</span></span>(result = = Sqlite_ok) {<span class="hljs-comment"><span class="hljs-comment">//insert the data into the database</span></span>Sqlite3_bind_text (stmt,<span class="hljs-number"><span class="hljs-number">1</span></span>, [datamodel<span class="hljs-variable"><span class="hljs-variable">. Screen_name</span></span>utf8string],-<span class="hljs-number"><span class="hljs-number">1</span></span>,<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>); Sqlite3_bind_text (stmt,<span class="hljs-number"><span class="hljs-number">2</span></span>, [datamodel<span class="hljs-variable"><span class="hljs-variable">. AVATAR_HD</span></span>utf8string],-<span class="hljs-number"><span class="hljs-number">1</span></span>,<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>);<span class="hljs-comment"><span class="hljs-comment">//[nsnumber] sqlite3_bind_text (stmt, 1, [[nsstring stringwithformat:@ "%@", model.comment_num] utf8string],-1, Nil );</span></span> <span class="hljs-keyword"><span class="hljs-keyword">if</span></span>(sqlite3_step (stmt) = = Sqlite_done) {flag =<span class="hljs-literal"><span class="hljs-literal">YES</span></span>; } }<span class="hljs-keyword"><span class="hljs-keyword">return</span></span>flag;}<span class="hljs-preprocessor"><span class="hljs-preprocessor">#pragma mark-delete</span></span>+ (<span class="hljs-built_in"><span class="hljs-built_in">BOOL</span></span>) Deletemodel: (datamodel *) Datamodel {<span class="hljs-built_in"><span class="hljs-built_in">BOOL</span></span>Flag =<span class="hljs-literal"><span class="hljs-literal">NO</span></span>;<span class="hljs-comment"><span class="hljs-comment">//open Database</span></span>[<span class="hljs-keyword"><span class="hljs-keyword"></span> self</span>opendb];<span class="hljs-comment"><span class="hljs-comment">//here WeiBo is the name of the table under CREATE database (do not Mistake)</span></span> <span class="hljs-built_in"><span class="hljs-built_in">NSString</span></span>*sql = [<span class="hljs-built_in"><span class="hljs-built_in">NSString</span></span>stringwithformat:@<span class="hljs-string"><span class="hljs-string">"delete from WeiBo where Screen_name =%@"</span></span>, Datamodel<span class="hljs-variable"><span class="hljs-variable">. Screen_name</span></span>];<span class="hljs-keyword"><span class="hljs-keyword">int</span></span>result = sqlite3_exec (db, sql<span class="hljs-variable"><span class="hljs-variable">. Utf8string</span></span>,<span class="hljs-literal"><span class="hljs-literal">NULL</span></span>,<span class="hljs-literal"><span class="hljs-literal">NULL</span></span>,<span class="hljs-literal"><span class="hljs-literal">Nil</span></span>);<span class="hljs-keyword"><span class="hljs-keyword">if</span></span>(result = = Sqlite_ok) {<span class="hljs-built_in"><span class="hljs-built_in">NSLog</span></span>(@<span class="hljs-string"><span class="hljs-string">"delete succeeded"</span></span>); Flag =<span class="hljs-literal"><span class="hljs-literal">YES</span></span>; }<span class="hljs-keyword"><span class="hljs-keyword">Else</span></span>{<span class="hljs-built_in"><span class="hljs-built_in">NSLog</span></span>(@<span class="hljs-string"><span class="hljs-string">"delete failed"</span></span>); }<span class="hljs-keyword"><span class="hljs-keyword">return</span></span>flag;}<span class="hljs-keyword"><span class="hljs-keyword">@end</span></span></code></pre>Create a database<p><p>(in Firefox browser)<br>1,sqlite Manager Download</p></p><p><p></p></p><p><p>2, There are several ways to create a database (this does not explain it all) I have a database of Firefox browser<br>Open Browser –> tools –> Find SQLite Manager<br></p></p><p><p>3, click: The SQLite Manager page is like this<br></p></p><p><p>4, Create a database<br></p></p><p><p>5, the desktop dragged it to the project<br></p></p><p><p>6, Create a table of the database (the table inside the Create field can be understood as a property)<br><br></p></p><p><p>7, after the table is created successfully<br></p></p><p><p>The picture is a little blurry, please Forgive Me.<br>Please leave a message if you have Questions.<br>Where is the wrong write please guide the guide thank you .....</p></p> <p><p>IOS Database SQLite</p></p></span>
