ORMLite union operator

Question

I have three tables that I have to present in one Android ListView. To get the data I use the SQL UNION operator to "merge" all three tables together, so that in my ViewBinder I can make each timeline item look distinct.

These items need to be sorted in chronological order. These three tables do not have a common base class.

Here is the SQL that I have in mind:

SELECT * FROM (
        SELECT id, startTime as time, username, comment, "CustomerInteraction" FROM CustomerInteraction  
        UNION 
        SELECT id, date as time, "" as username, "" as comment, "Sale" FROM Sale
        UNION 
        SELECT id, claimDate as time, username, comment,  "TravelClaim" FROM TravelClaim) 
    ORDER BY time DESC LIMIT 100

How can I express the above query in ORMLite?

I know I can use Dao.executeRaw, but I don't want to populate my entire list in one go. I would much rather use the trick to get the underlying cursor from ORMLite, and then just pass that to my Adapter. (Lazy loading, makes initial display of long lists much faster.)

Is there a way I can do something like Dao.getPreparedQueryFromRaw(String statement) ? Or better yet QueryBuilder.union(QueryBuilder qb)?

"I don't want to populate my entire list in one go [...] Lazy loading" I am not sure you can achieve this easily. If you use ListView and CursorAdapter, the entire list is queried. There is no pagination/limit. The recycling stuff of ListView is purely on the views, not on the underlying data. — Simon, Jan 26 '15 at 12:57

score 2 · Answer 1 · answered Mar 27 '13 at 08:58

2

You can get a Cursor by calling rawQuery on the SQLiteDatabase. I do something like this:

final SQLiteDatabase db = getHelper().getReadableDatabase();

Cursor cursor = db.rawQuery(MY_SQL_QUERY, null);

You don't need to do anything much more than that.

answered Mar 27 '13 at 08:58

Jonathan Caryl

1,330
3
12
30

That would work, yes, but I would prefer an ORMLite centric solution. Nowhere else in our code do we use raw SQL, so to put it here, would be nasty. – Diederik Mar 27 '13 at 09:05

score 1 · Accepted Answer · answered Mar 27 '13 at 13:25

Is there a way I can do something like Dao.getPreparedQueryFromRaw(String statement) ? Or better yet QueryBuilder.union(QueryBuilder qb)?

The best way to do this with ORMLite is with one of the the queryRaw(...) methods. They return a GenericRawResults class which you can iterate across. The iterator gives you a number of different methods to help with moving around the list.

The problem is that the generic results are not of a certain type so I'm not sure if you can map it into the Android ListView. You can provide a RawRowMapper to queryRaw(...). You can get the mapper for a particular type by using the dao.getRawRowMapper() method.

Hope something here is helpful.

I'll see if it will work to create a Cursor that wraps a `RawRowMapper` to make it work with a `ListView`. — Diederik, Mar 28 '13 at 07:23

ORMLite union operator

2 Answers2