How do I combine data from two separate tables int

问题:

Currently, I have a table of posts and a table of users. Naturally, each user can be associated with multiple posts. Each row in the post table stores the user ID of the user that created the post. Here are example rows:

Post row: post_id headline user_id

User row: user_id user_name

I want to return a Cursor containing both the post row and its corresponding user row by matching the user ID in the post table to the user ID in the user table. What type of query would I use to achieve this? The result should be:

Combined row: post_id headline user_id user_name

More generally: How do I combine data from two separate tables based on a shared piece of data into a single Cursor?

回答1:

You can also use raw SQLite statements in your Android code like such:

SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT serials.id, serials.cabinet_number, serials.serial_number, " +
            "configuration.frequency, configuration.ag, configuration.number_alarms, configuration.failed_rapper, configuration.max_mv, configuration.max_nav " +
            "FROM serials JOIN configuration ON serials.id = configuration.serial_id WHERE serials.id = '" + current_id + "'", null);
    cursor.moveToFirst();

In the SELECT the format is table_name.column_name. The ON is where you would combine the data based on a shared piece of data.

回答2:

You can use a CursorJoiner to get something similar to merging two Cursors into one. The CursorJoiner doesn't actually perform a merge. As you iterate over it, it moves the original two Cursors such that their rows will match up on the specified column(s). This is why it's necessary that both Cursors be sorted on the columns that are to be used in the join.

Link to documentation: http://developer.android.com/reference/android/database/CursorJoiner.html

Code example:

CursorJoiner joiner = new CursorJoiner(userCursor, new String[]{ "user_id" }, postCursor, new String[] {"user_id"});

while (joiner.hasNext()) {
    CursorJoiner.Result result = joiner.next();
        switch (result) {
            case LEFT:
                // don't care about this case
                break;

            case RIGHT:
                // nor this case
                break;

            case BOTH:
                // here both original Cursors are pointing at rows that have the same user_id, so we can extract values
                int postId = postCursor.getInt(...);
                String headline = postCursor.getString(...);
                int userId = userCursor.getInt(...);        
                String userName = userCursor.getString(...);

                // do something with above values

                break;

        }
}