Android SQLite資料庫使用樣本

標籤：

簡單介紹一下，現在的主流行動裝置像Android、iPhone等都使用SQLite作為複雜資料的儲存引擎，在我們為行動裝置開發應用程式時，也許就要使用到SQLite來儲存我們大量的資料，所以我們就需要掌握行動裝置上的SQLite開發技巧。對於Android平台來說，系統內建了豐富的API來供開發人員操作SQLite，我們可以輕鬆的完成對資料的存取。

下面我們用SQLite來開發一個英語詞典。是項目結構……

MySQLite.java

package sn.qdj.sqlitedemo;import android.content.Context;import android.database.DatabaseErrorHandler;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;/** * SQLite資料庫操作 * @author qingdujun * */public class MySQLite extends SQLiteOpenHelper {/** * 構造SQL語句建立表 */final String CREATE_TABLE_SQL = "CREATE TABLE dict(uid integer primary key autoincrement," +"word interge," +"detail varchar)";public MySQLite(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);// TODO Auto-generated constructor stub}public MySQLite(Context context, String name, CursorFactory factory,int version, DatabaseErrorHandler errorHandler) {super(context, name, factory, version, errorHandler);// TODO Auto-generated constructor stub}@Overridepublic void onCreate(SQLiteDatabase db) {// 第一次使用資料庫時自動建表db.execSQL(CREATE_TABLE_SQL);Log.i("create", "ok");}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stub}}

MainActicity.java

package sn.qdj.sqlitedemo;import java.util.ArrayList;import java.util.HashMap;import java.util.Map;import android.app.Activity;import android.content.Intent;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.util.Log;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.EditText;import android.widget.Toast;public class MainActivity extends Activity {MySQLite dbHelpher;Button insert = null;Button search = null;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);                /**         * 建立MySQLite對象，指定資料庫版本為1         */        dbHelpher = new MySQLite(this, "myDict.db3", null,1);        insert = (Button)findViewById(R.id.btn_insert);        search = (Button)findViewById(R.id.btn_search);        /**         * 插入事件         */        insert.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubLog.i("insert", "front");String word = ((EditText)findViewById(R.id.et_word)).getText().toString();String detail = ((EditText)findViewById(R.id.et_detail)).getText().toString();//插入語句myInsert(dbHelpher.getReadableDatabase(), word, detail);Log.i("insert", "after");Toast.makeText(getApplicationContext(), "插入成功", 800).show();}});        /**         * 查詢事件         */        search.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {// TODO Auto-generated method stubString key = ((EditText)findViewById(R.id.et_word)).getText().toString();String sql = "SELECT * FROM dict WHERE word LIKE ? OR detail LIKE ?";String selectionArgs[] = {"%"+key+"%","%"+key+"%"};Cursor cursor = dbHelpher.getReadableDatabase().rawQuery(sql, selectionArgs);//建立一個Bundle對象Bundle data = new Bundle();data.putSerializable("data", converCursorToList(cursor));//建立一個IntentIntent intent = new Intent(MainActivity.this, ResultActivity.class);intent.putExtras(data);//啟動ActivitystartActivity(intent);}});            }    protected ArrayList<Map<String, String>> converCursorToList(Cursor cursor) {        ArrayList<Map<String, String>> result = new ArrayList<Map<String,String>>();    //遍曆結果集    while (cursor.moveToNext()) {Map<String, String> map = new HashMap<String, String>();map.put("word", cursor.getString(1));map.put("detail", cursor.getString(2));result.add(map);}return result;}    /**     *      * @param db     * @param word  單詞     * @param detail  解釋     */    private void myInsert(SQLiteDatabase db, String word, String detail){        db.execSQL("INSERT INTO dict values(null,?,?)",    new String[]{word, detail});    }        @Override    public void onDestroy(){    super.onDestroy();    /**     * 退出程式時，關閉SQLiteDatabase     */    if (dbHelpher != null) {dbHelpher.close();}    }}

activity_main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:paddingBottom="@dimen/activity_vertical_margin"    android:paddingLeft="@dimen/activity_horizontal_margin"    android:paddingRight="@dimen/activity_horizontal_margin"    android:paddingTop="@dimen/activity_vertical_margin"    tools:context="sn.qdj.sqlitedemo.MainActivity" >    <EditText        android:id="@+id/et_word"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:hint="word" />    <EditText        android:id="@+id/et_detail"        android:layout_width="fill_parent"        android:layout_height="wrap_content"        android:layout_below="@id/et_word"        android:hint="detail" />    <Button        android:id="@+id/btn_search"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentRight="true"        android:layout_alignParentBottom="true"        android:text="查詢" />    <Button        android:id="@+id/btn_insert"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentLeft="true"        android:layout_alignParentBottom="true"        android:text="插入" /></RelativeLayout>

ResultActivity.java

package sn.qdj.sqlitedemo;import java.util.List;import java.util.Map;import android.app.Activity;import android.content.Intent;import android.os.Bundle;import android.widget.ListView;import android.widget.SimpleAdapter;public class ResultActivity extends Activity {@Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.result);                ListView listView = (ListView)findViewById(R.id.show);                Intent intent = getIntent();        //擷取intent攜帶的資料        Bundle data = intent.getExtras();        //從Bundle中取出資料        List<Map<String, String>> list = (List<Map<String,String>>) data.getSerializable("data");        //將List封裝成SimpleAdapter        SimpleAdapter adapter = new SimpleAdapter(ResultActivity.this, list, R.layout.line,        new String[]{"word","detail"},new int[]{R.id.word,R.id.detail} );        //填充ListView        listView.setAdapter(adapter);}}

result.xml

<?xml version="1.0" encoding="utf-8"?><RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:layout_width="match_parent"    android:layout_height="match_parent" >    <ListView        android:id="@+id/show"        android:layout_width="match_parent"        android:layout_height="wrap_content" >    </ListView></RelativeLayout>

line.xml

<?xml version="1.0" encoding="utf-8"?><RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    android:layout_width="fill_parent"    android:layout_height="fill_parent" >    <TextView        android:id="@+id/find"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_alignParentTop="true"        android:text="找到的單詞" />    <TextView        android:id="@+id/word"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_below="@id/find"        android:text="TextView" />    <TextView        android:id="@+id/explain"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_below="@id/word"        android:text="解釋" />    <TextView        android:id="@+id/detail"        android:layout_width="wrap_content"        android:layout_height="wrap_content"        android:layout_below="@id/explain"        android:text="" /></RelativeLayout>

Mainifest.xml

<?xml version="1.0" encoding="utf-8"?><manifest xmlns:android="http://schemas.android.com/apk/res/android"    package="sn.qdj.sqlitedemo"    android:versionCode="1"    android:versionName="1.0" >    <uses-sdk        android:minSdkVersion="15"        android:targetSdkVersion="15" />    <application        android:allowBackup="true"        android:icon="@drawable/ic_launcher"        android:label="@string/app_name"        android:theme="@style/AppTheme" >        <activity            android:name=".MainActivity"            android:label="@string/app_name" >            <intent-filter>                <action android:name="android.intent.action.MAIN" />                <category android:name="android.intent.category.LAUNCHER" />            </intent-filter>        </activity>        <activity            android:name=".ResultActivity"            android:label="@string/app_name" >        </activity>    </application></manifest>

下載原始碼，請點擊這裡！

參考文獻：《瘋狂Android講義（第2版）》  李剛 編著

Android SQLite資料庫使用樣本