ORMLite

參考文件

範例情境

這邊的範例使用情境是建立一個一對多(one to many)的結構關係，一位使用者可以對到多個群組。

如何使用

1 建立資料物件

Tip

1. 資料類別需要有空的建構子
2. @DatabaseTable(tableName = "User") 定義資料表名稱，可省略不寫同類別名稱
3. @DatabaseField 定義資料欄位名稱，可省略不寫同變數名稱
   • generatedId = true 設定為 id 並自動增量
   • foreign = true 外來鍵
   • canBeNull = true 可為空
   • columnName 欄位名稱可不寫
   • foreignAutoRefresh 自動更新外來鍵
4. @ForeignCollectionField 關聯的資料集合
   • 須使用ForeignCollection

使用者 User

一位使用者可以對應到多個群組

@DatabaseTable(tableName = "User")
public class User {
  @DatabaseField(generatedId = true) private int id;
  @DatabaseField private String name;
  @DatabaseField private int sort;
  @ForeignCollectionField private ForeignCollection<Group> groups;

  public User() {}
}

群組 Group

一個群組可以對應到一位使用者的id

@DatabaseTable(tableName = "Group")
public class Group {

  @DatabaseField(generatedId = true)
  private int id;
  @DatabaseField
  private String name;
  @DatabaseField
  private String description;
  @DatabaseField(foreign = true, foreignAutoRefresh = true, canBeNull = false, columnName = "user_id")
  private User user;

  public Group() {}
}

2 建立 DatabaseHelper

Tip

1. 使用 Singleton 避免產生多個實例(instance)，要注意 thread safe 這邊使用雙重鎖定(Double-checked locking)
2. 使用 TableUtils 類別建立及刪除表格

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "Demo";
    private static DatabaseHelper instance;


    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static synchronized DatabaseHelper getHelper(Context context) {
        context = context.getApplicationContext();
        if (instance == null) {
            synchronized (DatabaseHelper.class) {
                if (instance == null)
                    instance = new DatabaseHelper(context);
            }
        }
        return instance;
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion) {
        try {
            TableUtils.dropTable(connectionSource, User.class, true);
            TableUtils.dropTable(connectionSource, Group.class, true);
            onCreate(database, connectionSource);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onCreate(SQLiteDatabase database, ConnectionSource connectionSource) {
        try {
            TableUtils.createTable(connectionSource, User.class);
            TableUtils.createTable(connectionSource, Group.class);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3 建立基礎 Dao 類別

這邊我會先建立泛型 Dao 抽象類別，定義基礎的資料庫操作行為。如：新增、刪除、修改、查詢等...

public abstract class BaseDao<T, ID> {

    private Dao<T, ID> dao;
    private DatabaseHelper helper;

    public BaseDao(Context context, Class<T> clazz) {
        try {
            helper = DatabaseHelper.getHelper(context);
            dao = helper.getDao(clazz);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Dao<T, ID> getDao() {
        return dao;
    }

    public int insert(T item) {
        int id;
        try {
            id = dao.create(item);
        } catch (SQLException e) {
            e.printStackTrace();
            id = -1;
        }
        return id;
    }

    public int deleteById(ID primaryKey) {
        int id;
        try {
            id = dao.deleteById(primaryKey);
        } catch (SQLException e) {
            e.printStackTrace();
            id = -1;
        }
        return id;
    }

    public int update(T item) {
        int id;
        try {
            id = dao.update(item);
        } catch (SQLException e) {
            e.printStackTrace();
            id = -1;
        }
        return id;
    }

    public T getById(ID id) {
        try {
            return dao.queryForId(id);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<T> getAll() {
        try {
            return dao.queryBuilder().orderBy("id", false).query();
        } catch (SQLException e) {
            e.printStackTrace();
            return Collections.emptyList();
        }
    }

    public T maxOfFieldItem(String field) throws Exception {
        return dao.queryBuilder().orderBy(field, false).limit(1L).query().get(0);
    }

    public T minOfFieldItem(String field) throws Exception {
        return dao.queryBuilder().orderBy(field, true).limit(1L).query().get(0);
    }

    /**
     * get all data
     *
     * @param isAsc true asc, false desc
     * @return List<T> sort by id
     */
    public List<T> getAll(boolean isAsc) {
        try {
            return dao.queryBuilder().orderBy("id", isAsc).query();
        } catch (SQLException e) {
            e.printStackTrace();
            return Collections.emptyList();
        }
    }

    /**
     * get all data
     *
     * @param field sort by field
     * @param isAsc true asc, false desc
     * @return List<T> sort by field
     */
    public List<T> getAll(String field, boolean isAsc) {
        try {
            return dao.queryBuilder().orderBy(field, isAsc).query();
        } catch (SQLException e) {
            e.printStackTrace();
            return Collections.emptyList();
        }
    }

    public QueryBuilder<T, ID> getQueryBuilder() {
        return dao.queryBuilder();
    }

    public void insertWithTransaction(final List<T> items) {
        try {
            TransactionManager.callInTransaction(helper.getConnectionSource(), new Callable<Void>() {
                @Override
                public Void call() throws Exception {
                    for (T t : items) {
                        dao.create(t);
                    }
                    return null;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void createWithTransaction(final List<T> items) {
        try {
            TransactionManager.callInTransaction(helper.getConnectionSource(), new Callable<Void>() {
                @Override
                public Void call() throws Exception {
                    for (T t : items) {
                        dao.update(t);
                    }
                    return null;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateWithTransaction(final List<ID> items) {
        try {
            TransactionManager.callInTransaction(helper.getConnectionSource(), new Callable<Void>() {
                @Override
                public Void call() throws Exception {
                    dao.deleteIds(items);
                    return null;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4 定義使用者及群組 Dao 類別

用於覆寫基礎行為及定義新方法

public class UserDao extends BaseDao<User, Integer>{

    public UserDao(Context context) {
        super(context, User.class);
    }

    public void swap(int sourceId, int targetId) {
        int sourceSort = getById(sourceId).getSort();
        int targetSort = getById(targetId).getSort();
        update(getById(sourceId).setSort(targetSort));
        update(getById(targetId).setSort(sourceSort));
    }

    @Override
    public int insert(User item) {
        try {
            item.setSort(maxOfFieldItem("sort").getSort() + 1);
        }catch (Exception e){
            item.setSort(1);
            e.printStackTrace();
        }
        return super.insert(item);
    }
}

public class GroupDao extends BaseDao<Group, Integer>{

    public GroupDao(Context context) {
        super(context, Group.class);
    }

    public List<Group> getByUserId(int userId){
        try {
            return getDao().queryBuilder().where().eq("user_id", userId).query();
        }catch (SQLException e){
            e.printStackTrace();
            return Collections.emptyList();
        }
    }
}