SQLite: how to join tables and present a cursor using SimpleCursorAdapter

Question

I used SQLite on Android for a long time, but for the first time I performed the join-table operation. I am completely upset about this because I have been working on it all day.

Now I have 2 tables, FTSProfile and FTSCell, and I want to join them using the shared key A with LEFT JOIN. So I implemented a series of ContentProvider and Database code to accomplish what I want. In the database, I used SQLiteQueryBuilder to build the query and therefore get the cursor that I want. I used:

SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); builder.setTables(FTS_VIRTUAL_TABLE_PROFILE+" LEFT JOIN "+FTS_VIRTUAL_TABLE_CELL+" ON "+FTS_VIRTUAL_TABLE_CELL+"."+Database.KEY_CID+"="+FTS_VIRTUAL_TABLE_PROFILE+"."+Database.KEY_CELL_ID); builder.setProjectionMap(mColumnMap_CombinedTable); 

and my hashmap looks like:

 HashMap<String,String> map = new HashMap<String,String>(); for (String key: KEYS_PROFILE) map.put(key, FTS_VIRTUAL_TABLE_PROFILE+"."+key); for (String key: KEYS_CELL) map.put(key, FTS_VIRTUAL_TABLE_CELL+"."+key); map.put(BaseColumns._ID, BaseColumns._ID); map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " + SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID); map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " + SearchManager.SUGGEST_COLUMN_SHORTCUT_ID); 

And then, when I run the program and try to open the list for the result, I get SQLiteException: no such column: _id...

I have no idea how to solve it, but I really want to know how I can get rid of it. Can anyone help?

Answer 1

Ha funny question. I just looked at the same thing 30 minutes ago;). You can try to follow an example, as in the one used in the application to connect to the Android community.

http://www.google.com/codesearch/p?hl=en#cbQwy62oRIQ/src/com/android/providers/contacts/ContactsProvider2.java&q=ContactsProvider.java&d=5

They are declaring an interface.

 private interface DataContactsQuery { public static final String TABLE = "data " + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) " + "JOIN contacts ON (raw_contacts.contact_id = contacts._id)"; public static final String[] PROJECTION = new String[] { RawContactsColumns.CONCRETE_ID, DataColumns.CONCRETE_ID, ContactsColumns.CONCRETE_ID }; public static final int RAW_CONTACT_ID = 0; public static final int DATA_ID = 1; public static final int CONTACT_ID = 2; } 

Then, when trying to query the results, they use this piece of code.

 Cursor cursor = null; try { cursor = mDb.query(DataContactsQuery.TABLE, DataContactsQuery.PROJECTION, mSb.toString(), mSelectionArgs.toArray(EMPTY_STRING_ARRAY), null, null, Contacts.IN_VISIBLE_GROUP + " DESC, " + Data.RAW_CONTACT_ID); if (cursor.moveToFirst()) { dataId = cursor.getLong(DataContactsQuery.DATA_ID); rawContactId = cursor.getLong(DataContactsQuery.RAW_CONTACT_ID); contactId = cursor.getLong(DataContactsQuery.CONTACT_ID); } else { // No contact found, return a null URI return -1; } } finally { if (cursor != null) { cursor.close(); } } 

This is useful?

Answer 2

How about creating a VIEW in your database? If this is a frequent request, draw it as a view, and you can avoid many problems with setting up the connection and just worry about the selection criteria. If you can write a join as direct SQL, then you can create a view on it and then use it.

The problem that I see with your code is that you are trying to select a column named _id (which is needed for the CursorAdapter ), but this column does not exist. You map BaseColumns._ID to BaseColumns._ID , but these are just constants, and if your database does not have a _id column, you will get the error message that you see.

 CREATE VIEW FooBar AS SELECT Foo.field1 AS FooField1, Foo.field2 AS FooField2, Bar.field1 AS BarField1, Bar.field2 AS BarField2, (Foo._id * 1000000000) + Bar._id AS _id FROM Foo LEFT JOIN Bar ON Foo.Field1 = Bar.Field2; 

This (Foo._id * 1000000000) + Bar._id AS _id is a quick, dirty, and probably painfully wrong way to synthesize a unique _id for the string returned by the view you will need if you use this as an adapter. It also has the disadvantage that it is read-only. You cannot update VIEW .

Once you have VIEW , you can SELECT against it, as if it were the table itself. those. SELECT * FROM FooBar WHERE BarField1 > 10;