This lesson:

- get data from joined tables;
- use rawQuery;

We have parsed query method close enough. But we haven’t took a look at how to make queries from joined tables with it. Let’s create a simple application which will make a query from two tables and display the result in logs. Tables’ names will be people and position. We will insert a list of people into the first one and a list of positions into the second. For every man in people table position id will be specified with value from position table.

Let’s create a project:

Project name: P0371_SQLiteInnerJoin
Build Target: Android 2.3.3
Application name: SQLiteInnerJoin
Package name: ru.startandroid.develop.p0371sqliteinnerjoin
Create Activity: MainActivity

We won’t use a layout so there is no need to change main.xml. Let’s open MainActivity.java:

package ru.startandroid.develop.p0371sqliteinnerjoin;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends Activity {

  final String LOG_TAG = "myLogs";

  // data for position table
  int[] position_id = { 1, 2, 3, 4 };
  String[] position_name = { "Директор", "Программер", "Бухгалтер", "Охранник" };
  int[] position_salary = { 15000, 13000, 10000, 8000 };

  // data for people table
  String[] people_name = { "Иван", "Марья", "Петр", "Антон", "Даша", "Борис", "Костя", "Игорь" };
  int[] people_posid = { 2, 3, 2, 2, 3, 1, 2, 4 };

  /** Called when the activity is first created. */
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    // connect to database
    DBHelper dbh = new DBHelper(this);
    SQLiteDatabase db = dbh.getWritableDatabase();

    // declare a cursor
    Cursor c;

    // display in log positions data
    Log.d(LOG_TAG, "--- Table position ---");
    c = db.query("position", null, null, null, null, null, null);
    logCursor(c);
    c.close();
    Log.d(LOG_TAG, "--- ---");

    // display in log people data
    Log.d(LOG_TAG, "--- Table people ---");
    c = db.query("people", null, null, null, null, null, null);
    logCursor(c);
    c.close();
    Log.d(LOG_TAG, "--- ---");

    // display the result of the update
    // use rawQuery
    Log.d(LOG_TAG, "--- INNER JOIN with rawQuery---");
    String sqlQuery = "select PL.name as Name, PS.name as Position, salary as Salary "
        + "from people as PL "
        + "inner join position as PS "
        + "on PL.posid = PS.id " 
        + "where salary > ?";
    c = db.rawQuery(sqlQuery, new String[] {"12000"});
    logCursor(c);
    c.close();
    Log.d(LOG_TAG, "--- ---");

    // display the joining result
    // use query
    Log.d(LOG_TAG, "--- INNER JOIN with query---");
    String table = "people as PL inner join position as PS on PL.posid = PS.id";
    String columns[] = { "PL.name as Name", "PS.name as Position", "salary as Salary" };
    String selection = "salary < ?";
    String[] selectionArgs = {"12000"};
    c = db.query(table, columns, selection, selectionArgs, null, null, null);
    logCursor(c);
    c.close();
    Log.d(LOG_TAG, "--- ---");
    
    // Close connection
    dbh.close();
  }

  // displaying data from cursor in logs
  void logCursor(Cursor c) {
    if (c != null) {
      if (c.moveToFirst()) {
        String str;
        do {
          str = "";
          for (String cn : c.getColumnNames()) {
            str = str.concat(cn + " = " + c.getString(c.getColumnIndex(cn)) + "; ");
          }
          Log.d(LOG_TAG, str);
        } while (c.moveToNext());
      }
    } else
      Log.d(LOG_TAG, "Cursor is null");
  }

  // class for database interaction
  class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
      super(context, "myDB", null, 1);
    }

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, "--- onCreate database ---");

      ContentValues cv = new ContentValues();

      // create positions table
      db.execSQL("create table position (" 
          + "id integer primary key,"
          + "name text," + "salary integer" 
          + ");");

      // fill it with data
      for (int i = 0; i < position_id.length; i++) {
        cv.clear();
        cv.put("id", position_id[i]);
        cv.put("name", position_name[i]);
        cv.put("salary", position_salary[i]);
        db.insert("position", null, cv);
      }

      // create people table
      db.execSQL("create table people ("
          + "id integer primary key autoincrement," 
          + "name text,"
          + "posid integer" 
          + ");");

      // fill it with data
      for (int i = 0; i < people_name.length; i++) {
        cv.clear();
        cv.put("name", people_name[i]);
        cv.put("posid", people_posid[i]);
        db.insert("people", null, cv);
      }
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
  }

}

Let’s go through the code. At the beginning we have arrays with data for the tables. Note, filling the position table we specify the ids so we can use them within people table to specify the position’s id.

Within activity’s onCreate method we create an object for database interaction and connect to database. Then we use familiar query method to display data from position and people tables in logs.

We use rawQuery to get data from joined tables. It’s a simple method which get an SQL query and a list of arguments for the WHERE condition (if necessary) as parameters. We create a query to join two tables and displaying man’s name, position and salary. The query condition is a salary more than 12000. We use arguments for query creation.

Then we again display joined tables but with simple query. We initialize table with tables data, their alias and JOIN condition. columns includes all field names we need with aliases. we initialize selection and selectionArgs with query condition (salary less than 12000).

logCursor gets Cursor as an argument and displays its content in the log.

Within DBHelper we write code for tables creation and this time here, they are filled with data.

Let’s save and launch the project:

--- onCreate database ---
--- Table position ---
id = 1; name = Director; salary = 15000;
id = 2; name = Developer; salary = 13000;
id = 3; name = Bookkeeper; salary = 10000;
id = 4; name = Security; salary = 8000;
--- ---
--- Table people ---
id = 1; name = Ivan; posid = 2;
id = 2; name = Maria; posid = 3;
id = 3; name = Petr; posid = 2;
id = 4; name = Anton; posid = 2;
id = 5; name = Dasha; posid = 3;
id = 6; name = Boris; posid = 1;
id = 7; name = Kostja; posid = 2;
id = 8; name = Igor; posid = 4;
--- ---

Displayed tables’ data separately.

--- INNER JOIN with rawQuery---
Name = Иван; Position = Coder; Salary = 13000;
Name = Петр; Position = Coder; Salary = 13000;
Name = Anton; Position = Coder; Salary = 13000;
Name = Boris; Position = Director; Salary = 15000;
Name = Kostja; Position = Coder; Salary = 13000;
--- ---

Displayed data with query. Salary is more than 12000.

--- INNER JOIN with query---
Name = Марья; Position = Bookkeeper; Salary = 10000;
Name = Dasha; Position = Bookkeeper; Salary = 10000;
Name = Igor; Position = Security; Salary = 8000;
--- ---

Displayed data with query. Salary is less than 12000.

As you see, queries from joined tables in SQLite are simple and they don’t differ from ordinary databases.

It’s up to you whether to use rawQuery or query, it depends on situation. Although, I can’t think up the advantages of one over another, but probably they are.

Data about salary and posts are fictitious, any coincidence is accidental. And, of course, I have nothing against bookkeepers and security officers)))

Next lesson:

- use transactions for database interaction.