[Android] Getting Started with SQLite

<span id="Label3"></p>Overview<p><p>Android also offers several ways to save data so that the data is not lost even after the program is Finished. These methods are:</p></p> <ul> <ul> <li>Text file:<br>Can be saved in the Application's own directory, each installed app will create a folder in the/data/data/directory, the name and the application in the Androidmanifest.xml file in the same package.</li> <li><p>SDcard save:</p></li> <li><p>Preferences Save:<br>This is also a frequently used method of storing data because they are transparent to the user and exist when installed from the Application.</p></li> <li><p>Assets Save:<br>Used to store some read-only data, assets refers to those files in the assets directory that exist before you compile your app and can be accessed when the application is Running.</p><p>But sometimes we need to do some complex operations on the saved data, or a large amount of data, beyond the performance of the text file and preference, so need some more efficient way to manage, starting from Android1.5, Android has its own SQLite database.<br>SQLite It is a standalone, non-service process that supports transactional processing and can be used in SQL language Databases.</p></li> </ul> </ul>Features of SQLite<p><p>1. Acid Transaction<br>　　</p></p> <blockquote> <blockquote> <p>ACID:<br>The abbreviation for the four basic elements that a database transaction performs correctly. Contains: atomicity (atomicity), Consistency (consistency), isolation (isolation), Persistence (durability). A support transaction (Transaction) database, must have these four characteristics, otherwise in the transaction process (Transaction Processing) can not guarantee the correctness of the data, the transaction process is very likely not to reach the requirements of the Counterparty.</p> </blockquote> </blockquote><p><p>2, 0 Configuration – No installation and management configuration required</p></p><p><p>3. A complete database stored in a single disk file</p></p><p><p>4. Database files can be freely shared between machines in different byte order</p></p><p><p>5. Support Database size to 2TB</p></p><p><p>6, small enough, roughly 30,000 lines C code, 250K</p></p><p><p>7, more than some popular databases in most ordinary database operations to fast</p></p><p><p>8. simple, Easy API</p></p><p><p>9, including Tcl binding, while supporting other languages through wrapper binding</p></p> <blockquote> <blockquote> <p>Http://www.sqlite.org/tclsqlite.html</p> </blockquote> </blockquote><p><p>10, Good annotated source code, and has more than 90% of the test coverage</p></p><p><p>11. Independence: No additional Reliance</p></p><p><p>12. Source Full open, You can use it for any purpose, including selling it</p></p><p><p>13, support a variety of development languages, C,php,perl,java,asp.net,python</p></p>Using SQLite in Android<p><p>Activites can access a database through the Content Provider or Service.</p></p>Create a database<p><p>Android does not automatically provide a database. To use SQLite in an Android application, you must create your own database, and then create tables, indexes, and populate the Data. Android provides sqliteopenhelper to help you create a database, as long as you inherit the Sqliteopenhelper class based on the needs of the development application, encapsulate the logic to create and update the database Used.<br>　　 <br>sqliteopenhelper, you need to implement at least three methods:</p></p><pre class="prettyprint"><code class=" hljs java"><span class="hljs-keyword"><span class="hljs-keyword"></span> public</span> <span class="hljs-class"><span class="hljs-class"> <span class="hljs-keyword">class</span> <span class="hljs-title">databasehelper</span> <span class="hljs-keyword">extends</span> <span class="hljs-title">sqliteopenhelper</span> {</span></span> <span class="hljs-javadoc"><span class="hljs-javadoc">/** *<span class="hljs-javadoctag"> @param</span> context Contexts (for example, an Activity) *<span class="hljs-javadoctag"> @param</span> Name Database name *<span class="hljs-javadoctag"> @param</span> Fact Ory an optional cursor factory (usually Null) *<span class="hljs-javadoctag"> @param</span> The integer of version database model versions * * Calls the constructor of the parent class Sqliteopenhelper</span> * *</span> <span class="hljs-keyword"><span class="hljs-keyword"></span> public</span> <span class="hljs-title"><span class="hljs-title">Databasehelper</span></span>(context context, String name, cursorfactory factory,<span class="hljs-keyword"><span class="hljs-keyword">int</span></span>Version) {<span class="hljs-keyword"><span class="hljs-keyword">Super</span></span>(context, name, factory, version); }<span class="hljs-javadoc"><span class="hljs-javadoc">/** * This method is called when the database is first created * * Populate the table and initialize the data for incoming Sqlitedatabase objects as Needed. */</span></span> <span class="hljs-annotation"><span class="hljs-annotation">@Override</span></span> <span class="hljs-keyword"><span class="hljs-keyword"></span> public</span> <span class="hljs-keyword"><span class="hljs-keyword">void</span></span> <span class="hljs-title"><span class="hljs-title">onCreate</span></span>(sqlitedatabase Db) { }<span class="hljs-javadoc"><span class="hljs-javadoc">/** * When the database needs to be modified (two database versions are different), the Android system will invoke this method on its own Initiative. * Generally we delete the database table in this method and create a new database Table. */</span></span> <span class="hljs-annotation"><span class="hljs-annotation">@Override</span></span> <span class="hljs-keyword"><span class="hljs-keyword"></span> public</span> <span class="hljs-keyword"><span class="hljs-keyword">void</span></span> <span class="hljs-title"><span class="hljs-title">Onupgrade</span></span>(sqlitedatabase db,<span class="hljs-keyword"><span class="hljs-keyword">int</span></span>oldversion,<span class="hljs-keyword"><span class="hljs-keyword">int</span></span>Newversion) {<span class="hljs-comment"><span class="hljs-comment">//three parameters, One sqlitedatabase object, one old version number and one new version number</span></span>}<span class="hljs-annotation"><span class="hljs-annotation">@Override</span></span> <span class="hljs-keyword"><span class="hljs-keyword"></span> public</span> <span class="hljs-keyword"><span class="hljs-keyword">void</span></span> <span class="hljs-title"><span class="hljs-title">OnOpen</span></span>(sqlitedatabase Db) {<span class="hljs-comment"><span class="hljs-comment">//each Successful opening of the database is executed first</span></span> <span class="hljs-keyword"><span class="hljs-keyword">Super</span></span>. OnOpen (db); }}</code></pre><p><p>After inheriting sqliteopenhelper, you have the following two methods:</p></p> <ul> <ul> <li><p>Getreadabledatabase () Create or open a query database</p></li> <li><p>Getwritabledatabase () Create or open a writable database</p></li> </ul> </ul><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs cs"><span class="hljs-keyword">new</span> DatabaseHelper(context);<span class="hljs-comment">//传入一个上下文参数</span><span class="hljs-keyword">null</span>;db = database.getWritableDatabase();</code></pre></pre><p><p>The above code will return an instance of the Sqlitedatabase class, using which you can query or modify the Database.</p></p><p><p>The Sqlitedatabase class provides us with many methods, and the more common methods are as Follows:</p></p> <blockquote> <blockquote> <p>(int) Delete (String table,string whereclause,string[] Whereargs)</p> </blockquote> </blockquote><p><p>Delete data rows</p></p> <blockquote> <blockquote> <p>(long) Insert (String table,string nullcolumnhack,contentvalues values)</p> </blockquote> </blockquote><p><p>Adding data rows</p></p> <blockquote> <blockquote> <p>(int) update (string table, contentvalues values, string whereclause, string[] Whereargs)</p> </blockquote> </blockquote><p><p>Update Data rows</p></p> <blockquote> <blockquote> <p>(void) Execsql (String sql)</p> </blockquote> </blockquote><p><p>Executes an SQL statement that can be a select or other SQL statement</p></p> <blockquote> <blockquote> <p>(void) Close ()</p> </blockquote> </blockquote><p><p>Close the database</p></p> <blockquote> <blockquote> <p>(Cursor) query (string table, string[] columns, string selection, string[] selectionargs, string groupBy, string having, St ring, String Limit)</p> </blockquote> </blockquote><p><p>Queries the specified data table to return a data set with a Cursor.</p></p><p><p>Description of each Parameter:<br>Table: Tables Name<br>Colums: Array of column names<br>Selection: conditional clauses, equivalent to where<br>Selectionargs: parameter array for conditional statements<br>GroupBy: Grouping<br>Having: Grouping conditions<br>Order By: Sort Class<br>Limit: Limits for paged queries<br>Cursor: the return value, equivalent to the result set resultset</p></p> <blockquote> <blockquote> <p>(Cursor) rawquery (String sql, string[] Selectionargs)</p> </blockquote> </blockquote><p><p>Run a pre-built SQL statement that returns a data set with a cursor (the biggest difference from the above statement is that it prevents SQL Injection)</p></p><p><p>When you have completed the operation of the database (for example, your Activity has been closed), you need to call Sqlitedatabase's close () method to release the database Connection.</p></p>Creating Tables and Indexes<p><p>In order to create tables and indexes, you need to call Sqlitedatabase's Execsql () method to execute the DDL statement. If there is no exception, this method does not return a Value.<br>For example, you can execute the following code:<br>　　</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs sql"> db.execSQL("<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">user</span>(_id <span class="hljs-keyword">INTEGER</span> <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> AUTOINCREMENT, username TEXT, password TEXT);</span>");</code></pre></pre><p><p>This statement creates a table named user, with a column named _id and a primary key, and the value of this column is an integer that will grow Automatically. In addition, there are two columns: username (character) and password (character). SQLite automatically creates an index for the primary key Column.<br>typically, The first time you create a database, you create tables and Indexes. To delete tables and indexes, you need to call the drop Index and drop TABLE statements using the Execsql () method.</p></p>Add data<p><p>There are two ways to add data to a Table.</p></p><p><p>① can use the Execsql () method to update the Table's data by executing statements such as INSERT, update, DELETE, and so On. The Execsql () method applies to all SQL statements that do not return a Result. For example:</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs sql">String sql = "<span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">user</span>(username,password) <span class="hljs-keyword">values</span> (<span class="hljs-string">‘finch‘</span>,<span class="hljs-string">‘123456‘</span>);</span>//插入操作的SQL语句db.execSQL(sql);//执行SQL语句</code></pre></pre><p><p>② uses the Insert () of the Sqlitedatabase Object.</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs cs"><span class="hljs-keyword">new</span> ContentValues();cv.put(<span class="hljs-string">"username"</span>,<span class="hljs-string">"finch"</span>);<span class="hljs-comment">//添加用户名</span>cv.put(<span class="hljs-string">"password"</span>,<span class="hljs-string">"123456"</span><span class="hljs-comment">//添加密码</span>db.insert(<span class="hljs-string">"user"</span>,<span class="hljs-keyword">null</span>,cv);<span class="hljs-comment">//执行插入操作</span></code></pre></pre>Update Data (modify)<p><p>① uses the update () method of the Sqlitedatabase Object.</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs javascript"><span class="hljs-keyword">new</span> ContentValues();cv.put(<span class="hljs-string">"password"</span>,<span class="hljs-string">"654321"</span>);<span class="hljs-comment">//添加要更改的字段及内容</span><span class="hljs-built_in">String</span><span class="hljs-string">"username=?"</span>;<span class="hljs-comment">//修改条件</span><span class="hljs-built_in">String</span>[] whereArgs = {<span class="hljs-string">"finch"</span>};<span class="hljs-comment">//修改条件的参数</span>db.update(<span class="hljs-string">"user"</span>,cv,whereClause,whereArgs);<span class="hljs-comment">//执行修改</span></code></pre></pre><p><p>The method has four parameters:<br>Table name;<br>The Contentvalues object of the column name and value;<br>The optional where condition;<br>The optional string that fills the where statement, which replaces the "?" in the where Condition. "tag, Update () Updates the value of the specified column according to the CRITERIA.</p></p><p><p>Implementation of ② using Execsql method</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs sql">String sql = "<span class="hljs-operator"><span class="hljs-keyword">update</span> [<span class="hljs-keyword">user</span>] <span class="hljs-keyword">set</span> password = <span class="hljs-string">‘654321‘</span> <span class="hljs-keyword">where</span> username=<span class="hljs-string">"finch"</span>;</span>//修改的SQL语句db.execSQL(sql);//执行修改</code></pre></pre>Delete data<p><p>① uses the Delete () method of the Sqlitedatabase Object.</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs javascript"><span class="hljs-built_in">String</span><span class="hljs-string">"username=?"</span>;<span class="hljs-comment">//删除的条件</span><span class="hljs-built_in">String</span>[] whereArgs = {<span class="hljs-string">"finch"</span>};<span class="hljs-comment">//删除的条件参数</span>db.delete(<span class="hljs-string">"user"</span>,whereClause,whereArgs);<span class="hljs-comment">//执行删除</span></code></pre></pre><p><p>Implementation of ② using Execsql method</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs sql">String sql = "<span class="hljs-operator"><span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> username=<span class="hljs-string">"finch"</span>;</span>//删除操作的SQL语句db.execSQL(sql);//执行删除操作</code></pre></pre>Querying data<p><p>① call the SELECT statement directly using Rawquery ()</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs avrasm">Cursor c = db<span class="hljs-preprocessor">.rawQuery</span>(<span class="hljs-string">"select * from user where username=?"</span>,new Stirng[]{<span class="hljs-string">"finch"</span>})<span class="hljs-comment">;</span>if(cursor<span class="hljs-preprocessor">.moveToFirst</span>()) { String password = c<span class="hljs-preprocessor">.getString</span>(c<span class="hljs-preprocessor">.getColumnIndex</span>(<span class="hljs-string">"password"</span>))<span class="hljs-comment">;</span>}</code></pre></pre><p><p>The return value is a cursor object, and the method of the object can iterate over the query Results.<br>If the query is dynamic, using this method can be very complex. For example, using the query () method can be a lot easier when the column you are querying is not deterministic when the program is Compiled.</p></p><p><p>② Querying with Query implementation</p></p><p><p>The query () method constructs queries with a SELECT statement Segment.<br>The contents of the SELECT statement are the parameters of the query () method, such as the name of the table to be queried, the name of the field to get, the where condition, which contains the optional positional parameters, to replace the value of the positional parameter in the where condition, the GROUP by condition, and the having Condition.<br>In addition to the table name, other parameters can be Null. So the code can be written as:</p></p><pre class="prettyprint"><pre class="prettyprint"><code class=" hljs javascript">Cursor c = db.query(<span class="hljs-string">"user"</span>,<span class="hljs-literal">null</span>,<span class="hljs-literal">null</span>,<span class="hljs-literal">null</span>,<span class="hljs-literal">null</span>,<span class="hljs-literal">null</span>,<span class="hljs-literal">null</span>);<span class="hljs-comment">//查询并获得游标</span><span class="hljs-keyword">if</span>(c.moveToFirst()){<span class="hljs-comment">//判断游标是否为空</span> <span class="hljs-keyword">for</span>(int i=<span class="hljs-number">0</span>;i<c.getCount();i++){　c.move(i);<span class="hljs-comment">//移动到指定记录</span><span class="hljs-built_in">String</span> username = c.getString(c.getColumnIndex(<span class="hljs-string">"username"</span>);<span class="hljs-built_in">String</span> password = c.getString(c.getColumnIndex(<span class="hljs-string">"password"</span>)); }}</code></pre></pre>Using cursors<p><p>No matter how you execute the query, it will return a cursor, which is an Android SQLite database cursor, using a cursor, you can:</p></p> <ul> <ul> <li><p>How many records in the result set are obtained by using the GetCount () method;</p></li> <li><p>Traverse all records by Movetofirst (), MoveToNext (), and Isafterlast () methods;</p></li> <li><p>The field name is obtained by Getcolumnnames ();</p></li> <li><p>Convert to field number by Getcolumnindex ();</p></li> <li><p>The value of the current record in a given field is obtained by getString (), getInt () and other METHODS.</p></li> <li><p>Re-execute the query through the Requery () method to get the cursor;</p></li> <li><p>Releasing cursor resources through the close () method;</p></li> </ul> </ul><p><p>For example, The following code iterates through the user table:</p></p><pre class="prettyprint"><code class=" hljs avrasm">Cursor result=db<span class="hljs-preprocessor"><span class="hljs-preprocessor">. Rawquery</span></span>(<span class="hljs-string"><span class="hljs-string">"select _id, username, password from user"</span></span>)<span class="hljs-comment"><span class="hljs-comment">; </span></span>Result<span class="hljs-preprocessor"><span class="hljs-preprocessor">. Movetofirst</span></span>()<span class="hljs-comment"><span class="hljs-comment">; </span></span>While (!result<span class="hljs-preprocessor"><span class="hljs-preprocessor">. Isafterlast</span></span>()) {int Id=result<span class="hljs-preprocessor"><span class="hljs-preprocessor">. GetInt</span></span>(<span class="hljs-number"><span class="hljs-number">0</span></span>)<span class="hljs-comment"><span class="hljs-comment">; </span></span>String Name=result<span class="hljs-preprocessor"><span class="hljs-preprocessor">. getString</span></span>(<span class="hljs-number"><span class="hljs-number">1</span></span>)<span class="hljs-comment"><span class="hljs-comment">; </span></span>String Password =result<span class="hljs-preprocessor"><span class="hljs-preprocessor">. getString</span></span>(<span class="hljs-number"><span class="hljs-number">2</span></span>)<span class="hljs-comment"><span class="hljs-comment">; </span></span>Do something useful with these result<span class="hljs-preprocessor"><span class="hljs-preprocessor">. MoveToNext</span></span>()<span class="hljs-comment"><span class="hljs-comment">; </span></span>} result<span class="hljs-preprocessor"><span class="hljs-preprocessor">. Close</span></span>()<span class="hljs-comment"><span class="hljs-comment">;</span></span></code></pre><p><p>Reference: http://www.ibm.com/developerworks/cn/opensource/os-cn-sqlite/</p></p> <p><p>[Android] Getting Started with SQLite</p></p></span>