Android SQLite資料庫使用樣本

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> converCursorToList(Cursor cursor) {        ArrayList> result = new ArrayList>();    //遍曆結果集    while (cursor.moveToNext()) {Map map = new HashMap();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

 

 

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> list = (List>) 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