小賴的實戰記錄

接著SQLite(1)，已實作完建立Database與建立資料表，現在我們要來使用Dao來取得與新增資料的部分

我們以MemberDao來修改，如下

package com.sample.db.dao;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.sample.db.model.Member;

import java.util.ArrayList;

public class MemberDao extends BaseDao{
    private static MemberDao sInst;

    public static class Schema {
        public static final String TableName = "member";
        /**
         * Define table schema
         */
        public static class Columns {
            public static final String Id = "id";
            public static final String Name = "name";
        };
    }

    public MemberDao(Context context) {
        super(context);
    }

    public synchronized static MemberDao getInstance(Context context) {
        if (sInst == null) {
            sInst = new MemberDao(context);
        }
        return sInst;
    }

    public static String getCraeteTableSQL() {
        return "CREATE TABLE IF NOT EXISTS `"
                + Schema.TableName + "`(" + "`"
                + Schema.Columns.Id + "` TEXT KEY UNIQUE," + "`"
                + Schema.Columns.Name + "` TEXT)";
    }

    /**
         * 取得所有資料
         */
    public synchronized ArrayList<Member> getAll() {
        ArrayList<Member> list = new ArrayList<Member>();

        StringBuilder sbSelection = new StringBuilder();
        ArrayList<String> strArgs = new ArrayList<String>();
        SQLiteDatabase db = mSQLite.getReadableDatabase();

        String[] columns = new String[]{Schema.Columns.Id,  Schema.Columns.Name};
        String[] selectionArgs = (String[]) strArgs.toArray(new String[strArgs.size()]);
        String selection = sbSelection.toString();
        String groupBy = null;
        String having = null;
        String orderBy = Schema.Columns.Id + " DESC";
        Cursor cursor = db.query(Schema.TableName,columns, selection, selectionArgs, groupBy, having, orderBy);

        while (cursor.moveToNext()) {
            Member obj = new Member();
            obj.id = getString(cursor,Schema.Columns.Id);
            obj.name =  getString(cursor,Schema.Columns.Name);
            list.add(obj);
        }

        cursor.close();
        db.close();
        return list;
    }

    /**
     *   新增資料
     */
    public synchronized void insert(Member obj) {
        SQLiteDatabase db = mSQLite.getWritableDatabase();
        db.beginTransaction();
        try {
            ContentValues cv = new ContentValues();
            cv.clear();
            cv.put(Schema.Columns.Id, obj.id);
            cv.put(Schema.Columns.Name,  obj.name);
            db.replace(Schema.TableName, null, cv);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        db.close();
    }

    /**
        *   新增資料
        */
    public synchronized void insert(ArrayList<Member> list) {
        if (list == null || list.size() == 0)
            return;

        SQLiteDatabase db = mSQLite.getWritableDatabase();
        db.beginTransaction();
        try {
            ContentValues cv = new ContentValues();
            for (Member obj : list) {
                cv.clear();
                cv.put(Schema.Columns.Id, obj.id);
                cv.put(Schema.Columns.Name,  obj.name);
                db.replace(Schema.TableName, null, cv);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        db.close();
    }
}

新增了getAll、insert(Member)及insert(ArrayList<Member>)的方法。

先談新增的使用方法

MemberDao dao  = MemberDao.getInstance(this);
Member obj = new Member();
obj.id = this.mEditId.getText().toString();
obj.name = this.mEditName.getText().toString();
dao.insert(obj);

再來談取得資料的使用方法

MemberDao dao = MemberDao.getInstance(this);
List<Member> list = dao.getAll();
mAdapter.addAll(list);
mAdapter.notifyDataSetChanged();

至於mAdapter，是我繼承 ArrayAdapter<Member>實作出來的，的MemberListAdapter，但此文章只講SQLited的部分，並討論ListView、ArrayAdapter、ViewHolder等實作方式。