Ormlite Select Distinct Fields

I have a SQLite table (on Android) that has numerous fields, but certain fields are repeated/denormalized. I would like to select a distinct set of this data and use them as actua

Solution 1:

I would like to select a distinct set of this data and use them as actual objects.

ORMLite supports a distinct() method on the QueryBuilder that should do what you want. So your code would look something like:

List<Book> results = booksDao.queryBuilder()
    .distinct().selectColumns("author").query();

In this case, the resulting Book objects would only have the author field set and not the id field or anything else. If you just wanted the author names instead of objects then you could do:

GenericRawResults<String[]> rawResults =
    booksDao.queryRaw("SELECT DISTINCT author FROM books");
for (String[] resultColumns : rawResults) {
    String author = resultColumns[0];
    ...
}

Solution 2:

This is my application code

publicclassDbHelper<T> {
    private Class<T> c;
    private DatabaseHelper db;
    publicDbHelper(Class<T> c) {
        this.c = c;
        db = DatabaseHelper.getInstance();
    }

This is a good idea

publicList<T> queryForBuilderDistinct(int offset, int limit, String ColumnsName,
       String orderName, boolean isAsc) {
    try {
        Dao<T, Integer> dao = db.getDao(c);
        QueryBuilder<T, Integer> queryBuilder = dao.queryBuilder();
        if (offset != 0) {
            queryBuilder.offset((long) offset);
        }
        if (limit != 0) {
            queryBuilder.limit((long) limit);
        }
        if (orderName != null) {
            queryBuilder.orderBy(orderName, isAsc);
        }
        queryBuilder.distinct().selectColumns(ColumnsName);
        return dao.query(queryBuilder.prepare());
    } catch (SQLException e) {
        LogUtil.e(TAG, "queryForBuilderDistinct", e);
    }
    returnnew ArrayList<T>();
}