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
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// 第一次使用數據庫時自動建表
		db.execSQL(CREATE_TABLE_SQL);
		Log.i(create, ok);
	}

	@Override
	public 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() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				Log.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() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				String 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));
				//創建一個Intent
				Intent intent = new Intent(MainActivity.this, ResultActivity.class);
				intent.putExtras(data);
				//啟動Activity
				startActivity(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