Go An explanation of the SQLite development of Android

<span id="Label3"></p>About SQLite<p><p>Google for Andriod's larger data processing provides sqlite, he is in the storage, management, maintenance and other aspects of the excellent, the function is very strong. SQLite has the following features:</p></p><p><p><strong>1. Lightweight</strong></p></p><p><p>With SQLite you only need to bring a dynamic library, you can enjoy its full functionality, and the size of the dynamic library want to be Small.</p></p><p><p><strong>2. Independence</strong></p></p><p><p>The core engine of the SQLite database does not need to rely on Third-party software, nor does it require the so-called "install".</p></p><p><p><strong>3. Isolation</strong></p></p><p><p>All information in the SQLite database (such as tables, views, triggers, Etc.) is contained within a folder for easy administration and MAINTENANCE.</p></p><p><p><strong>4. Cross-platform</strong></p></p><p><p>SQLite currently supports most of the operating systems, not the computer operating system more in the many mobile phone systems can also be run, such as: Android.</p></p><p><p><strong>5. Multi-lingual Interface</strong></p></p><p><p>The SQLite database supports multiple language programming Interfaces.</p></p><p><p><strong>6. Security</strong></p></p><p><p>The SQLite database implements independent transaction processing through exclusive and shared locks at the database level. This means that multiple processes can read data from the same database at the same time, but only one can write Data.</p></p>SQLite usage in Android<p><p>First create the Database class</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="code"><code class="java keyword"><code class="java keyword">public</code></code> <code class="java keyword"><code class="java keyword">class</code></code> <code class="java plain"><code class="java plain">DatabaseHelper </code></code><code class="java keyword"><code class="java keyword">extends</code></code> <code class="java plain"><code class="java plain">SQLiteOpenHelper {</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java keyword"><code class="java keyword">private</code></code> <code class="java keyword"><code class="java keyword">static</code></code> <code class="java keyword"><code class="java keyword">final</code></code> <code class="java plain"><code class="java plain">String DB_NAME = </code></code><code class="java string"><code class="java string">"mydata.db"</code></code><code class="java plain"><code class="java plain">; </code></code><code class="java comments"><code class="java comments">//数据库名称</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java keyword"><code class="java keyword">private</code></code> <code class="java keyword"><code class="java keyword">static</code></code> <code class="java keyword"><code class="java keyword">final</code></code> <code class="java keyword"><code class="java keyword">int</code></code> <code class="java plain"><code class="java plain">version = </code></code><code class="java value"><code class="java value">1</code></code><code class="java plain"><code class="java plain">; </code></code><code class="java comments"><code class="java comments">//数据库版本</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java keyword"><code class="java keyword">public</code></code> <code class="java plain"><code class="java plain">DatabaseHelper(Context context) {</code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java keyword"><code class="java keyword">super</code></code><code class="java plain"><code class="java plain">(context, DB_NAME, </code></code><code class="java keyword"><code class="java keyword">null</code></code><code class="java plain"><code class="java plain">, version);</code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java comments"><code class="java comments">// TODO Auto-generated constructor stub</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java plain"><code class="java plain">}</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java color1"><code class="java color1">@Override</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java keyword"><code class="java keyword">public</code></code> <code class="java keyword"><code class="java keyword">void</code></code> <code class="java plain"><code class="java plain">onCreate(SQLiteDatabase db) {</code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java plain"><code class="java plain">String sql = </code></code><code class="java string"><code class="java string">"create table user(username varchar(20) not null , password varchar(60) not null );"</code></code><code class="java plain"><code class="java plain">;          </code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java plain"><code class="java plain">db.execSQL(sql);</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java plain"><code class="java plain">}</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java color1"><code class="java color1">@Override</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java keyword"><code class="java keyword">public</code></code> <code class="java keyword"><code class="java keyword">void</code></code> <code class="java plain"><code class="java plain">onUpgrade(SQLiteDatabase db, </code></code><code class="java keyword"><code class="java keyword">int</code></code> <code class="java plain"><code class="java plain">oldVersion, </code></code><code class="java keyword"><code class="java keyword">int</code></code> <code class="java plain"><code class="java plain">newVersion) {</code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java comments"><code class="java comments">// TODO Auto-generated method stub</code></code> <code class="java spaces"><code class="java spaces">    </code></code><code class="java plain"><code class="java plain">}</code></code><code class="java plain"><code class="java plain">}</code></code></td> </tr> </tbody> </table><p><p><strong>Sqliteopenhelper Class Introduction</strong></p></p><p><p>Sqliteopenhelper is a helper class for sqlitedatabase that manages the creation of databases and the updating of versions. It is generally established that a class inherits it and implements its OnCreate and Onupgrade methods.</p></p> <table style="width: 7px;" border="2" cellspacing="1" cellpadding="1"> <tbody> <tr> <th valign="top" width="65%">Method Name</th> <th valign="top" width="35%">Method Description</th> </tr> <tr> <td valign="top">Sqliteopenhelper (Context context,string name,sqlitedatabase.cursorfactory factory,int Version)</td> <td valign="top">Constructs a method, typically passing a database name to be created so the parameter</td> </tr> <tr> <td>OnCreate (sqlitedatabase Db)</td> <td>Called when a database is created</td> </tr> <tr> <td valign="top">Onupgrade (sqlitedatabase db,int oldversion, int Newversion)</td> <td valign="top">Called when the version is updated</td> </tr> <tr> <td valign="top">Getreadabledatabase ()</td> <td valign="top">Create or open a read-only database</td> </tr> <tr> <td valign="top">Getwritabledatabase ()</td> <td valign="top">Create or open a Read-write database</td> </tr> </tbody> </table><p><p>Here's How to call this</p></p><p><p><strong>Create a database</strong></p></p><p><p>The special place here is to implement the creation of a database by invoking the Getreadabledatabase () method of the Sqliteopenhelper class.</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">123</td> <td class="code"><code class="java plain">DatabaseHelper database = </code><code class="java keyword">new</code><code class="java plain">DatabaseHelper(</code><code class="java keyword">this</code><code class="java plain">);</code><code class="java comments">//这段代码放到Activity类中才用this</code><code class="java plain">SQLiteDatabase db = </code><code class="java keyword">null</code><code class="java plain">;</code><code class="java plain">db = database.getReadalbeDatabase();</code></td> </tr> </tbody> </table><p><p>The Sqlitedatabase class provides us with many methods, and the more common methods are as follows</p></p> <table style="width: 90%;" border="2" cellspacing="1" cellpadding="1"> <tbody> <tr> <th valign="top" width="65%">(return Value) Method name</th> <th valign="top" width="35%">Method Description</th> </tr> <tr> <td valign="top">(int) Delete (String table,string whereclause,string[] Whereargs)</td> <td valign="top">A convenient way to delete data rows</td> </tr> <tr> <td>(long) Insert (String table,string nullcolumnhack,contentvalues values)</td> <td>A convenient way to add data rows</td> </tr> <tr> <td valign="top">(int) update (string table, contentvalues values, string whereclause, string[] Whereargs)</td> <td valign="top">A convenient way to update data rows</td> </tr> <tr> <td valign="top">(void) Execsql (String sql)</td> <td valign="top">Executes an SQL statement that can be a select or other SQL statement</td> </tr> <tr> <td valign="top">(void) Close ()</td> <td valign="top">Close the database</td> </tr> <tr> <td valign="top">(Cursor) query (string table, string[] columns, string selection, string[] selectionargs, string groupBy, string having, St ring, String Limit)</td> <td valign="top">Queries the specified data table to return a data set with a cursor</td> </tr> <tr> <td valign="top">(Cursor) rawquery (String sql, string[] Selectionargs)</td> <td valign="top">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)</td> </tr> </tbody> </table><p><p>The addition and deletion of data can be realized by 2 ways.</p></p><p><p><strong>The addition of data</strong></p></p><p><p>1. Using the Insert method</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">123</td> <td class="code"><code class="java plain">ContentValues cv = </code><code class="java keyword">new</code><code class="java plain">ContentValues();</code><code class="java comments">//实例化一个ContentValues用来装载待插入的数据cv.put("username","Jack Johnson");//添加用户名</code><code class="java plain">cv.put(</code><code class="java string">"password"</code><code class="java plain">,</code><code class="java string">"iLovePopMusic"</code><code class="java plain">); </code><code class="java comments">//添加密码</code><code class="java plain">db.insert(</code><code class="java string">"user"</code><code class="java plain">,</code><code class="java keyword">null</code><code class="java plain">,cv);</code><code class="java comments">//执行插入操作</code></td> </tr> </tbody> </table><p><p>2. Use the Execsql method to achieve</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">12</td> <td class="code"><code class="java plain">String sql = "insert into user(username,password) values (</code><code class="java string">‘Jack Johnson‘</code><code class="java plain">,</code><code class="java string">‘iLovePopMuisc‘</code><code class="java plain">);</code><code class="java comments">//插入操作的SQL语句</code><code class="java plain">db.execSQL(sql);</code><code class="java comments">//执行SQL语句</code></td> </tr> </tbody> </table><p><p><strong>Deletion of data</strong></p></p><p><p>There are also 2 ways to achieve</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">123</td> <td class="code"><code class="java plain">String whereClause = </code><code class="java string">"username=?"</code><code class="java plain">;</code><code class="java comments">//删除的条件</code><code class="java plain">String[] whereArgs = {</code><code class="java string">"Jack Johnson"</code><code class="java plain">};</code><code class="java comments">//删除的条件参数</code><code class="java plain">db.delete(</code><code class="java string">"user"</code><code class="java plain">,whereClause,whereArgs);</code><code class="java comments">//执行删除</code></td> </tr> </tbody> </table><p><p>Implementation using the Execsql method</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">12</td> <td class="code"><code class="java plain">String sql = </code><code class="java string">"delete from user where username=‘Jack Johnson‘"</code><code class="java plain">;</code><code class="java comments">//删除操作的SQL语句</code><code class="java plain">db.execSQL(sql);</code><code class="java comments">//执行删除操作</code></td> </tr> </tbody> </table><p><p><strong>Data modification</strong></p></p><p><p>ditto, still 2 different ways</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">12345</td> <td class="code"><code class="java plain">contentvalues cv = </code> <code class="java keyword">new </code> <code class="java plain">contentvalues (); </code> <code class="java comments">//instantiation contentvalues </code> <code class="java plain">cv.put (</code> <code class="java string"> "password" </code> <code class="java plain" ), < code> <code class="java string"> "ihatepopmusic" </code> <code class="java plain" ); < code> <code class="java comments">//add The fields and contents you want to change </code> <code class="java plain">string whereclause = </code> <code class="java string"> "username=?" </code> <code class="java plain" ; < code> <code class="java comments">//modify condition </code> <code class="java plain">string[] whereargs = {</code> <code class="java String" "jack johnson" < code> <code class=" Java Plain ">}; </code> <code class="java comments">//modify The parameters of the condition </code> <code class="java plain">db.update (</code> <code class="java string" "user" < code> <code class="java plain">,cv, whereclause,whereargs); </code> <code class="java comments">//perform Modifications </code> </code></code></code></code></code></td> </tr> </tbody> </table><p><p>Implementation using the Execsql method</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">12</td> <td class="code"><code class="java plain">String sql = </code><code class="java string">"update [user] set password = ‘iHatePopMusic‘ where username=‘Jack Johnson‘"</code><code class="java plain">;</code><code class="java comments">//修改的SQL语句</code><code class="java plain">db.execSQL(sql);</code><code class="java comments">//执行修改</code></td> </tr> </tbody> </table><p><p><strong>Data query</strong></p></p><p><p>The data query is more complex than the previous methods because the query comes with many conditions</p></p><p><p>Queries are implemented using query</p></p><p><p>Public Cursor Query (string table, string[] columns, string selection, string[] selectionargs, string groupBy, string havin g, string by-clause, string Limit)</p></p><p><p>Description of each Parameter:</p></p> <ul> <ul> <li>Table: Tables Name</li> <li>Colums: Array of column names</li> <li>Selection: conditional clauses, equivalent to where</li> <li>Selectionargs: parameter array for conditional statements</li> <li>GroupBy: Grouping</li> <li>Having: Grouping conditions</li> <li>Order By: Sort Class</li> <li>Limit: Limits for paged queries</li> <li>Cursor: the return value, equivalent to the result set resultset</li> </ul> </ul><p><p>There are also a number of ways to target cursors (cursor)</p></p> <table style="width: 7px;" border="2" cellspacing="1" cellpadding="1"> <tbody> <tr> <th valign="top" width="35%"> method Name </th> <th valign="top" width="65%"> method description </th> </tr> <tr> <td valign="top">getcount () </td> <td valign="top"> total Number of records </td> </tr> <tr> <td> isFirst () </td> <td> determine whether the first record </td> </tr> <tr> <td valign="top">islast () </td> TD valign= "top" to determine whether the last record </tr> <tr> <td valign="top">movetofirst () </td> <td valign="top"> Move to the first record </td> </tr> <tr> <td valign="top">movetolast () </td> <td valign="top"> move to last record </td> </tr> <tr> <td valign="top">m Ove (int offset) </td> <td valign="top"> Move to the specified record </td> </tr> <tr> <td valign="top">movetonext () </td> <td valign="top"> Move to scare a record </td> </tr> <tr> <td valign="top">movetoprevious () </td> <td valign="top"> Move to previous record </td> </tr> tr> <tr> <td valign="top">getcolumnindex (String columnName) </td> <td valign="top" to get the int type value of specified column index < td> </td> </tr> </tbody> /table> </table><p><p>Implementation code</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">12345678</td> <td class="code"><code class="java plain"><code class="java plain">Cursor c = db.query(</code></code><code class="java string"><code class="java string">"user"</code></code><code class="java plain"><code class="java plain">,</code></code><code class="java keyword"><code class="java keyword">null</code></code><code class="java plain"><code class="java plain">,</code></code><code class="java keyword"><code class="java keyword">null</code></code><code class="java plain"><code class="java plain">,</code></code><code class="java keyword"><code class="java keyword">null</code></code><code class="java plain"><code class="java plain">,</code></code><code class="java keyword"><code class="java keyword">null</code></code><code class="java plain"><code class="java plain">,</code></code><code class="java keyword"><code class="java keyword">null</code></code><code class="java plain"><code class="java plain">,</code></code><code class="java keyword"><code class="java keyword">null</code></code><code class="java plain"><code class="java plain">);</code></code><code class="java comments"><code class="java comments">//查询并获得游标</code></code><code class="java keyword"><code class="java keyword">if</code></code><code class="java plain"><code class="java plain">(c.moveToFirst()){</code></code><code class="java comments"><code class="java comments">//判断游标是否为空</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java keyword"><code class="java keyword">for</code></code><code class="java plain"><code class="java plain">(</code></code><code class="java keyword"><code class="java keyword">int</code></code> <code class="java plain"><code class="java plain">i=</code></code><code class="java value"><code class="java value">0</code></code><code class="java plain"><code class="java plain">;i<c.getCount();i++){</code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java plain"><code class="java plain">c.move(i);</code></code><code class="java comments"><code class="java comments">//移动到指定记录</code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java plain"><code class="java plain">String username = c.getString(c.getColumnIndex(</code></code><code class="java string"><code class="java string">"username"</code></code><code class="java plain"><code class="java plain">);</code></code><code class="java spaces"><code class="java spaces">        </code></code><code class="java plain"><code class="java plain">String password = c.getString(c.getColumnIndex(</code></code><code class="java string"><code class="java string">"password"</code></code><code class="java plain"><code class="java plain">));</code></code><code class="java spaces"><code class="java spaces">    </code></code><code class="java plain"><code class="java plain">}</code></code><code class="java plain"><code class="java plain">}</code></code></td> </tr> </tbody> </table><p><p>Query with parameters implemented by Rawquery</p></p> <table border="0" cellspacing="0" cellpadding="0"> <tbody> <tr> <td class="gutter">1234</td> <td class="code"><code class="java plain">Cursor c = db.rawQuery(</code><code class="java string">"select * from user where username=?"</code><code class="java plain">,</code><code class="java keyword">new</code><code class="java plain">Stirng[]{</code><code class="java string">"Jack Johnson"</code><code class="java plain">});</code><code class="java keyword">if</code><code class="java plain">(cursor.moveToFirst()) {</code><code class="java spaces">    </code><code class="java plain">String password = c.getString(c.getColumnIndex(</code><code class="java string">"password"</code><code class="java plain">));</code><code class="java plain">}</code></td> </tr> </tbody> </table><p><p>Go An explanation of the SQLite development of Android</p></p></span>