ORMLite with CursorAdapter in Android

Question

I am modifying my Android app to work with ORMLite, and it currently uses a number of CursorAdapters, which I quite want to keep in an attempt to minimise coding.

I'm not 100% sure but t appears that when ORMLite creates an id field in the db, it always uses id, whereas CursorAdapter needs _id.

It is possible to get round this using a query like the following:

select id as _id ......

but the Dao.queryRaw() method returns a list, not a Cursor, so the way I have done it is to open another SQLiteOpenHelper database connection and use rawQuery().

This works, but are there any better ways of doing it at all? It seems overkill to have two separate database connections, and perhaps storing up trouble later.

Answer 1

It turns out I did need a raw SQL query with ORMLite after all as I needed to do a Left Join, (i.e. not as a result of having to rename the id column in a query, that is not necessary as per Gray's answer above)

The way I did it is below:

public class DatabaseHelper extends OrmLiteSqliteOpenHelper{
   public void myRawQueryMethod() {
        SQLiteDatabase database = this.getReadableDatabase();
        String SqlQuery = "Write raw SQL query here"; 
        Cursor cursor = database.rawQuery(SqlQuery, null);
    }
}

Thanks for advice

Answer 2

Your comments indicate that you've already answered you problem. You can certainly create a column named "_id" using ORMLite:

@DatabaseField(generatedId = true)
private int _id;

or

@DatabaseField(generatedId = true, columnName = "_id")
private int id;

If you are working with Cursors then you may want to take a look at the last() and moveAbsolute(...) methods on the DatabaseResults class. Also, the AndroidDatabaseResults (which you can cast to) also has a getRawCursor() method which returns the underlying Cursor object and has additional getCount() and getPosition() methods.

Here are some more information about ORMLite and Cursors:

Android Cursor with ORMLite to use in CursorAdapter

You can get access to the Cursor using something like the following:

// build your query
QueryBuilder<Foo, String> qb = fooDao.queryBuilder();
qb.where()...;
// when you are done, prepare your query and build an iterator
CloseableIterator<Foo> iterator = dao.iterator(qb.prepare());
try {
   // get the raw results which can be cast under Android
   AndroidDatabaseResults results =
       (AndroidDatabaseResults)iterator.getRawResults();
   Cursor cursor = results.getRawCursor();
   ...
} finally {
   iterator.closeQuietly();
}