how to join two tables in android using content provider query method?

Question

Im new to android I'm using content provider in my application, In my application I want to join two tables but I don't know how to do it, please help me find out solution

my tables:

CREATE TABLE Bank_customers (customer_id varchar PRIMARY KEY ,
    customer_name varchar,
    customer_date_of_birth date,
    address varchar,
    mobile integer,
    email varchar);

CREATE TABLE Bank_accounts (account_number integer(11) PRIMARY KEY,
    customer_id varchar ,
    account_type text,
    account_open_date date,
    account_balance real,FOREIGN KEY(customer_id) REFERENCES Bank_customers(customer_id));

my query: **SELECT mobile,Bank_accounts.customer_id from Bank_accounts,Bank_customers WHERE Bank_customers.customer_id = Bank_accounts.customer_id and Bank_accounts.account_number = 13323;**

How I can implement above query using content provider class "query method"

Answer 1

This is easy to do. You need to think of the join as an abstract table (sorry, code is in Java...)

First, create a new content: URL and use a URIMatcher to parse it:

private static final int JOIN_DIR_TYPE = 10;
private static final UriMatcher MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
static {
    // ...
    MATCHER.addURI(
        MyContract.AUTHORITY,
        MyContract.Join.TABLE,
        JOIN_DIR_TYPE);
}

Now, in your query method, delegate queries with the matching URL to a separate method:

@Override
public Cursor query(Uri uri, String[] proj, String sel, String[] selArgs, String sort) {
    switch (MATCHER.match(uri)) {
        case JOIN_DIR_TYPE:
            return queryJoin(uri, proj, sel, selArgs, sort)
            break;
            // ...
        default:
            throw new IllegalArgumentException("Unexpected uri: " + uri);
    }
    // ...
}

Now, all you have to do is query the join, in the new method:

private Cursor queryJoin(Uri uri, String[] proj, String sel, String[] selArgs, String sort) {
     SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
     qb.setTables("Bank_accounts LEFT OUTER JOIN Bank_customers ON (Bank_customers.customer_id = Bank_accounts.customer_id)")");

You may want to use the QueryBuilder's ColumnMap to map abstract aliases onto column names.

Answer 2

Content providers are an abstraction on top of SQL, so you do not have direct access to the tables to join them.

The content provider implementation must already provide the joined tables.

If you do not need to make the data accessible for other apps, you should probably avoid using a content provider, and use the database more directly.