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.

Nemertean

Sorry, it appears this question is spurious. I was wrong, it is possible to name columns _id with ORMLite, so I proposed to delete this question to avoid confusion unless anyone has anything further to add soon .. – Nemertean

Rather than delete it, I'd suggest you answer it with how you fixed it -- someone may find this helpful, especially code samples for how you created the cursor to work with ORM – Chuckwalla

have done below. You're right - no use deleting, I just worry about people stumbling across this type of misinformation and it putting people off the scent, but the answer is to read carefully! – Nemertean

Answer 1

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();
}

Antiquarian

I just figured out that the iterator must not be closed while the cursor is in use (I guessed so). How do you handle that? Keep it around and close it in onDestroy()? – Minda

If it is an eager fetched collection, it doesn't need to be closed. If it is a lazy collection then a connection will be kept open to the database and you will need to close it. Not sure how to do that if you are inside a an Android CursorAdapter @Sebastian. – Antiquarian

Sorry for a newbie question: is the a collection lazy per default? I had the same problem as @Sebastian, closing the iterator when the cursor is in use caused a crash. Right now I am passing the cursor from a Datamanager class to a list fragment and a custom cursorAdapter. It works but I am not sure where and when to release the iterator. – Maid

Lazy is the default. See here: ormlite.com/javadoc/ormlite-core/com/j256/ormlite/field/… – Antiquarian

Answer 2

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

Nemertean

FYI: We've added to version 4.22 LEFT JOIN capabilities to ORMLite. It was just released. – Antiquarian

Answer 3

Wrap your cursor with another one that treats "_id" as an alias to your real primary key column. Example:

cursor = new CursorWrapper(cursor) {
                @Override
                public int getColumnIndexOrThrow(String columnName) throws IllegalArgumentException {
                    if ("_id".equals(columnName)) columnName = "id";
                    return super.getColumnIndexOrThrow(columnName);
                }
            };

Encyst