SQLite 簡介

SQLite是一個輕量級的關聯式資料庫，運算速度快，佔用資源少，適合在行動裝置上使用， 使用標準SQL語法，且無需帳號即可使用

SQLite支援五種數據類型：NULL、INTEGER、REAL(浮點數)、TEXT(字串)和BLOB(二進制對象)

SQlite透過文件來保存資料庫，一個文件就是一個資料庫，資料庫中又包含多個表格

下列為幾個在SQLite資料表時會用到的三個類別：

• SQLiteOpenHelper ：抽像類別，透過繼承該類別，重寫資料庫建立以及更新的方法，並透過該類別取得資料庫實例，或者關閉資料庫
• SQLiteDatabase 資料庫訪問類別，可以透過該類別來對資料庫做一些增修刪查的操作
• Cursor 游標，類似於JDBC裡的ResultSet

使用SQLiteOpenHelper類別建立資料庫與版本管理

對於使用資料庫的app，需要在第一次啟用的時候就建立好資料庫表格，而當我們的App進行升級需要修改資料表的結構時，這時就需要對資料庫表進行更新。對於這兩個操作，Android在SQLiteOpenHelper中提供了兩個方法：onCreate( )與onUpgrade( )來實現

• onCreate(database) 首次使用App時生成建立資料表用
• onUpgrade(database,oldVersion,newVersion) 在資料庫的版本號發生變化時會被呼叫，而資料庫的版本號是由開發人員控制

SQLiteOpenHelper程式範例

public class DBHelper extends SQLiteOpenHelper {

    // 資料庫名稱，依專案設定資料庫名稱
    public static final String DATABASE_NAME = "ds.sampleapp.db";

    // 資料庫版本，資料結構改變的時候要更改這個數字，通常是加一
    public static final int VERSION = 1;

    // 資料庫物件，固定的欄位變數
    private static SQLiteDatabase database;

    // 建構子，一般都不需要修改
    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
                    int version) {
        super(context, name, factory, version);
    }


    // 取得資料庫操作實例，需要資料庫的元件呼叫這個方法，一般都不需要修改
    public static SQLiteDatabase getDatabase(Context context) {

        // 單例，避免重覆建立物件
        SQLiteDatabase inst = database;
        if (inst == null || !inst.isOpen()) {
            synchronized (SQLiteDatabase.class) {
                inst = database;
                if (inst == null || !inst.isOpen()) {
                    inst = new DBHelper(context, DATABASE_NAME,
                            null, VERSION).getWritableDatabase();;
                    database = inst;
                }
            }
        }
        return inst;
    }


    /**
     * 資料庫初始化時呼叫
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

        // 建立所需資料表
        db.execSQL(UserPermissionDAO.CREATE_TABLE);

    }

    /**
     * 在資料庫版本號碼更新時自動呼叫
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


        // 視需求刪除表格或作其他操作
        // :: 刪除表格
        db.execSQL("DROP TABLE IF EXISTS " + UserPermissionDAO.TABLE_NAME); 

        // :: 新增欄位
        db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + NEW_COL_NAME_1 + " TEXT");
        db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + NEW_COL_NAME_2 + " INTEGER DEFAULT 0");


        // 呼叫onCreate建立新版的表格
        onCreate(db);

    }

}

資料表操作

public class UserPermissionDAO  extends MyBaseDAO<UserPermissionModel> {

    // 表格名稱
    public static final String TABLE_NAME = "ds_user_permission";

    // 其它表格欄位名稱
    public static final String USER_ID_COLUMN = "_user_id";

    public static final String APP_ID_COLUMN = "_app_id";

    public static final String FUNCTION_ID_COLUMN = "_function_id";

    public static final String IS_OPEN_COLUMN = "_is_open";

    public static final String CAN_INQUIRY_COLUMN = "_can_inquiry";

    public static final String CAN_CREATE_COLUMN = "_can_create";

    public static final String CAN_EDIT_COLUMN = "_can_edit";

    public static final String CAN_DELETE_COLUMN = "_can_delete";

    public static final String UPDATEDATE_COLUMN = "_updatedate";


    // 使用上面宣告的變數建立表格的SQL指令
    public static final String CREATE_TABLE =
            "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                    USER_ID_COLUMN + " TEXT NOT NULL, " +
                    APP_ID_COLUMN + " TEXT NOT NULL, " +
                    FUNCTION_ID_COLUMN + " INTEGER NOT NULL, " +
                    IS_OPEN_COLUMN + " INTEGER NOT NULL DEFAULT 0 , " +
                    CAN_INQUIRY_COLUMN + " INTEGER NOT NULL DEFAULT 0 , " +
                    CAN_CREATE_COLUMN + " INTEGER NOT NULL DEFAULT 0 , " +
                    CAN_EDIT_COLUMN + " INTEGER NOT NULL DEFAULT 0 , " +
                    CAN_DELETE_COLUMN + " INTEGER NOT NULL DEFAULT 0 , " +
                    UPDATEDATE_COLUMN + " INTEGER NOT NULL ," +
                    "PRIMARY KEY ( " + USER_ID_COLUMN + ", " + APP_ID_COLUMN + ", " + FUNCTION_ID_COLUMN + ")" +
                    " )";



    // 資料庫物件
    protected SQLiteDatabase mDB;



    // 建構子
    public UserPermissionDAO(Context context) {
        mDB = DBHelper.getDatabase(context);
    }



    @Override
    public String getTableName() {
        return TABLE_NAME;
    }


    @Override
    public void close() {
        // 關閉資料庫
        mDB.close();
    }

    @Override
    public void execSQL(String sql) {

        mDB.execSQL(sql);
    }

    @Override
    public Cursor rawQuery(String sql, String[] selectionArgs) {

        return mDB.rawQuery(sql, selectionArgs);
    }


    @Override
    public UserPermissionModel insert(UserPermissionModel item) {

        // 建立準備新增資料的ContentValues物件
        ContentValues cv = new ContentValues();


        // 加入ContentValues物件包裝的新增資料
        // 第一個參數是欄位名稱， 第二個參數是欄位的資料
        cv.put(USER_ID_COLUMN, item.mUserID);
        cv.put(APP_ID_COLUMN, item.mAppID);
        cv.put(FUNCTION_ID_COLUMN, item.mFunctionID);
        cv.put(IS_OPEN_COLUMN, item.mIsOpen ? 1 : 0);
        cv.put(CAN_INQUIRY_COLUMN, item.mCanInquiry ? 1 : 0);
        cv.put(CAN_CREATE_COLUMN, item.mCanCreate ? 1 : 0);
        cv.put(CAN_EDIT_COLUMN, item.mCanEdit ? 1 : 0);
        cv.put(CAN_DELETE_COLUMN, item.mCanDelete ? 1 : 0);
        cv.put(UPDATEDATE_COLUMN, 0);


        // 新增一筆資料並取得編號
        // 第一個參數是表格名稱
        // 第二個參數是沒有指定欄位值的預設值
        // 第三個參數是包裝新增資料的ContentValues物件
        long id = mDB.insert(TABLE_NAME, null, cv);

        item.mKid = id;

        // 回傳結果
        return item;
    }



    public void insert(ArrayList<UserPermissionModel> items) {

        try {

            String sql = "INSERT INTO " + TABLE_NAME
                    + " ( "
                    + USER_ID_COLUMN + ", "
                    + APP_ID_COLUMN + ", "
                    + FUNCTION_ID_COLUMN + ", "
                    + IS_OPEN_COLUMN + ", "
                    + CAN_INQUIRY_COLUMN + ", "
                    + CAN_CREATE_COLUMN + ", "
                    + CAN_EDIT_COLUMN + ", "
                    + CAN_DELETE_COLUMN + ", "
                    + UPDATEDATE_COLUMN+ " "
                    + " ) "
                    + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";


            mDB.beginTransactionNonExclusive();

            SQLiteStatement stmt = mDB.compileStatement(sql);

            for (int x = 0; x < items.size(); x++) {


                stmt.bindString(1, items.get(x).mUserID);
                stmt.bindString(2, items.get(x).mAppID);
                stmt.bindLong(3, items.get(x).mFunctionID);
                stmt.bindLong(4, items.get(x).mIsOpen);
                stmt.bindLong(5, items.get(x).mCanInquiry);
                stmt.bindLong(6, items.get(x).mCanCreate);
                stmt.bindLong(7, items.get(x).mCanEdit );
                stmt.bindLong(8, items.get(x).mCanDelete );
                stmt.bindLong(9, 0);

                stmt.execute();
                stmt.clearBindings();

            }

            mDB.setTransactionSuccessful();


        } catch (Exception e) {


        } finally {
            mDB.endTransaction();
        }
    }



    public ArrayList<UserPermissionModel> get(String userID, String appID) {


        ArrayList<UserPermissionModel> result = new ArrayList<UserPermissionModel>();

        String where = USER_ID_COLUMN + "='" + userID + "' AND " + APP_ID_COLUMN + "='" + appID + "'";

        // 執行查詢
        Cursor cursor = mDB.query(
                TABLE_NAME, null, where, null, null, null, null, null);


        // 查詢結果
        while (cursor.moveToNext()) {
            result.add(getRecord(cursor));
        }

        // 關閉Cursor物件
        cursor.close();

        // 回傳結果
        return result;
    }

    /**
     * 刪除全資料
     * @return 是否有刪除資料
     */
    @Override
    public boolean deleteAll() {

        return mDB.delete(TABLE_NAME, null, null) > 0;

    }


    /**
     * 把Cursor目前的資料包裝為物件
     * @param cursor cursor
     * @return BloodPressure 資料
     */
    @Override
    public UserPermissionModel getRecord(Cursor cursor) {

        // 準備回傳結果用的物件
        UserPermissionModel result = new UserPermissionModel();

        result.mAppID = cursor.getString(cursor.getColumnIndex(APP_ID_COLUMN));
        result.mFunctionID = cursor.getInt(cursor.getColumnIndex(FUNCTION_ID_COLUMN));
        result.mIsOpen = cursor.getInt(cursor.getColumnIndex(IS_OPEN_COLUMN)) == 1;
        result.mCanInquiry = cursor.getInt(cursor.getColumnIndex(CAN_INQUIRY_COLUMN)) == 1;
        result.mCanCreate = cursor.getInt(cursor.getColumnIndex(CAN_CREATE_COLUMN)) == 1;
        result.mCanEdit = cursor.getInt(cursor.getColumnIndex(CAN_EDIT_COLUMN)) == 1;
        result.mCanDelete = cursor.getInt(cursor.getColumnIndex(CAN_DELETE_COLUMN)) == 1;

        // 回傳結果
        return result;
    }


}