ORMLite Select Distinct Fields

Question

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 actual objects.

Example

books table

title            summary        author
Little Johnny    A funny kid    Johnny Himself
Big Johnny       A funny adult  Johnny Himself

I would like to extract one author from this list ("Johnny Himself") and would expect I should be able to do this with ORMLite instead of manually with Java.

Answer 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];
    ...
}