Friday, March 26, 2010

Android join tables

In my application, I have many tables and for a reason, I had to do a query using "join".
I'll not explain how to access a database, but just so you know, check on Google for
object SQLiteDatabase.

So I was checking at the method query , here is the information:

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

table :The table name to compile the query against.

For example:

Cursor cursor = myDataBase.query(
"myTableA" ,
new String[] { "_id", "name"},
null,null, null, null, null);

That's nice, but what if I want to a "join" ? I need more than 1 table in my query.
Well, at beginning I found CursorJoiner. Nice, I can join 2 cursors. But what if I have 3 or more joins to do ??

In fact, the parameter "table " (function query) can contains many tables (not just one).
For example:

Cursor cursor = myDataBase.query(
"myTableA a, myTableB b",
new String[] { "a._id", "", "b._id", ""},
"a.foreignKey = b._id",
null, null, null, null);

That was not difficult, but the definition of the parameter "table " was not so good.

Another solution would be to use SQLiteQueryBuilder.

SQLiteQueryBuilder myQuery = new SQLiteQueryBuilder();
myQuery.setTables("myTableA, myTableB");
myQuery.appendWhere("a.FK = b._id");
Cursor cursor = myQuery.query(myDataBase, null, null, null, null, null, null);

No comments: