輕量級資料庫sqlite的使用

標籤：

本文不涉及一些概念性的東西，請大家多多原諒

這個就是Android sqlite的簡單架構。

使用sqlite 大概分為3步

第一步：建立自己的sqliteopenhelper類

第二步：建立資料庫中的dao層 ，其中分裝了對資料庫的操作

第三步：在activity 中使用dao層的操作了（多線程的形式，防止卡介面）

第一步：建立 sqliteopenhelper

/** * @author skyfin *@time 2015/6/4 */public class MyDatabase extends SQLiteOpenHelper {/** * 資料庫的名字 */public final static String DB_NAME = "ClassaateInfo";/** * 資料庫的版本號碼 */public final static int VERSION = 1;/** * @param 預設構造的函數 */public MyDatabase(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);// TODO 自動產生的建構函式存根}public MyDatabase(Context context, String name, CursorFactory factory,int version, DatabaseErrorHandler errorHandler) {super(context, name, factory, version, errorHandler);// TODO}/** * @param 為了每次不用傳入資料庫名和版本資訊 */public MyDatabase(Context context) {this(context, DB_NAME, null, VERSION);}/** * @param 為了更新資料庫名和版本資訊 */public MyDatabase(Context context, int version) {this(context, DB_NAME, null, version);}/* * （非 Javadoc） *  * @see * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite * .SQLiteDatabase) * 建立了資料庫 主鍵 id name 和phone */@Overridepublic void onCreate(SQLiteDatabase db) {// TODO 建立資料庫對資料庫的操作String sql = "create table IF NOT EXISTS student"+"("+ "id int primary key autoincrement," + "name varchar(20),"+ "phone int)";db.execSQL(sql);}/* （非 Javadoc） * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int) *  * 更改資料庫版本的操作 */@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO 更改資料庫版本的操作//String sql = "updete student ";//db.execSQL(sql);}@Overridepublic void onOpen(SQLiteDatabase db) {// TODO 開啟資料庫super.onOpen(db);}}

第二步：建立StudentDao 封裝了 相關的操作

public class StudentDao {MyDatabase myDatabase = null;public StudentDao(Context context) {myDatabase = new MyDatabase(context);}public StudentDao(Context context, int version) {myDatabase = new MyDatabase(context, version);}/** *  * @param 實現資料的插入 */public void insertData(Student stu) {/* * 方法一 使用了(?)的預留位置 ，重載後的execSQL(String sql, Object[] bindArgs)方法 */// try {// Log.i("skyfin", "insert datebase" + stu.id);// String sql = "insert into student(id,name,phone)values(?,?,?)";// SQLiteDatabase db = myDatabase.getWritableDatabase();// db.execSQL(sql, new Object[] { stu.id, stu.name, stu.phone });// db.close();// } catch (Exception e) {// e.printStackTrace();// // TODO: handle exception// }/* * 方法二 使用了 ContentValues欄位存放的形式 */SQLiteDatabase db = myDatabase.getWritableDatabase();ContentValues values = new ContentValues();values.put("id", stu.id);values.put("name", stu.name);values.put("phone", stu.phone);long rowid = db.insert("student", null, values);// 返回新添記錄的行號，與主鍵id無關db.close();}// 測試public void insert() {Log.i("skyfin", "test");}public void seleteAll() {/* *  * 方法一  query 形式 */ try { Log.i("skyfin", "select  datebase"); String sql = "select  * from student"; SQLiteDatabase db = myDatabase.getWritableDatabase(); // 產生一個新的遊標，遊標課向前和向後 Cursor cursor = db.rawQuery(sql,null); while(cursor.moveToNext()){ int id = cursor.getInt(cursor.getColumnIndex("id")); String name = cursor.getString(cursor.getColumnIndex("name")); int phone = cursor.getInt(cursor.getColumnIndex("phone")); //日誌列印輸出 Log.i("skyfin","query-->"+"id: "+id+"name: "+name+"phone: "+phone); } db.close(); } catch (Exception e) { // TODO 自動產生的 catch 塊 e.printStackTrace(); }/* *  * 方法二  query 形式 *///try {//SQLiteDatabase db = myDatabase.getWritableDatabase();//Cursor cursor = db.query("student",//new String[] { "id","name","phone" }, null,//null, null, null, null, null);////while (cursor.moveToNext()) {//Log.i("skyfin", "cursor");//int id = cursor.getInt(cursor.getColumnIndex("id"));//String name = cursor.getString(cursor.getColumnIndex("name"));//int phone = cursor.getInt(cursor.getColumnIndex("phone"));//// 日誌列印輸出//Log.i("skyfin", "query-->" + "id: " + id + "name: " + name//+ "phone: " + phone);////}//cursor.close();//db.close();//} catch (Exception e) {//// TODO 自動產生的 catch 塊//e.printStackTrace();//}}public void update() {try {SQLiteDatabase db = myDatabase.getWritableDatabase();//ContentValues 是一種 key -value 的形式 ，類似於mapContentValues values = new ContentValues();values.put("id", 1020);//key為欄位名，value為值values.put("name","doubi");values.put("phone", 1234);//update 函數後面表示 條件db.update("student", values, "id=?", new String[]{"123"}); db.close();} catch (Exception e) {// TODO 自動產生的 catch 塊e.printStackTrace();}}public void delete(){SQLiteDatabase db = myDatabase.getWritableDatabase();//和update類似db.delete("student", "id<?", new String[]{"2000"});db.close();}}

最後在 Activity中使用

public class MainActivity extends Activity implements OnClickListener {public StudentDao studentDao = null;public EditText edit_Id= null;public EditText edit_Name= null;public EditText edit_Phone= null;public Button ok_btn = null;public Button show_btn = null;public Button update_btn = null;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        studentDao = new StudentDao(getApplicationContext());        edit_Id = (EditText)findViewById(R.id.id);        edit_Name = (EditText)findViewById(R.id.name);        edit_Phone = (EditText)findViewById(R.id.phonenum);        ok_btn = (Button)findViewById(R.id.ok);        show_btn = (Button)findViewById(R.id.show);        update_btn = (Button)findViewById(R.id.update);        ok_btn.setOnClickListener(this);        show_btn.setOnClickListener(this);        update_btn.setOnClickListener(this);    }    @Override    public boolean onCreateOptionsMenu(Menu menu) {        // Inflate the menu; this adds items to the action bar if it is present.        getMenuInflater().inflate(R.menu.main, menu);        return true;    }    @Override    public boolean onOptionsItemSelected(MenuItem item) {        // Handle action bar item clicks here. The action bar will        // automatically handle clicks on the Home/Up button, so long        // as you specify a parent activity in AndroidManifest.xml.        int id = item.getItemId();        if (id == R.id.action_settings) {            return true;        }        return super.onOptionsItemSelected(item);    }@Overridepublic void onClick(View v) {// TODO 自動產生的方法存根switch (v.getId()) {case R.id.ok:Toast.makeText(getApplicationContext(), "點擊了確認按鈕", Toast.LENGTH_SHORT).show();  Log.i("skyfin", "insert into datebase");final Student student = new Student();student.setId(Integer.parseInt(edit_Id.getText().toString()));student.setName(edit_Name.getText().toString());student.setPhone(Integer.parseInt(edit_Phone.getText().toString()));Log.i("skyfin", student.toString());    new Thread(new Runnable() {@Overridepublic void run() {Log.i("skyfin", "insert thread is running");studentDao.insertData(student);}}).start();break;case R.id.show: new Thread(new Runnable() {@Overridepublic void run() {Log.i("skyfin", "select  thread is running");studentDao.seleteAll();}}).start();break;case R.id.update: new Thread(new Runnable() {@Overridepublic void run() {Log.i("skyfin", "update  thread is running");studentDao.update();//studentDao.delete();}}).start();break;default:break;}}}

dome

輕量級資料庫sqlite的使用