This lesson:

- learn query method in details
- use sorting, grouping, conditions, having

We use query to read all table data last lessons. We only used the table name as an input parameter and got all rows. But query has more parameters:

columns determines list of attributes we want to get;
selection is a string of WHERE condition;
selectionArgs is a array of arguments for selection. You can use ? marks within selection, which will be replaced with this values;
groupBy is for grouping;
having is for using conditions of aggregate functions;
orderBy is for sorting.

Now, let’s try to use them within an example. We will create a “country guide” application. We will take 10 countries and write to database their names, population and regions. The next functions we will implement in the application:
- displaying all rows;
- displaying the value of aggregate function (SUM, MIN, MAX, COUNT);
- displaying countries with more population than a number which was entered by user;
- grouping countries by region;
- displaying regions with more population than a number which was entered by user;
- sorting countries by name, population and region;

Data will be displayed in logs.

Let’s create a project:

Project name: P0361_SQLiteQuery
Build Target: Android 2.3.3
Application name: SQLiteQuery
Package name: ru.startandroid.develop.p0361sqlitequery
Create Activity: MainActivity

Let’s code main.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical">
    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Справочник стран"
        android:textSize="14sp"
        android:gravity="center_horizontal"
        android:layout_marginBottom="5dp"
        android:layout_marginTop="5dp">
    </TextView>
    <Button
        android:id="@+id/btnAll"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Все записи"
        android:layout_marginTop="5dp">
    </Button>
    <LinearLayout
        android:id="@+id/linearLayout1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp">
        <Button
            android:id="@+id/btnFunc"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Функция">
        </Button>
        <EditText
            android:id="@+id/etFunc"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1">
            <requestFocus>
            </requestFocus>
        </EditText>
    </LinearLayout>
    <LinearLayout
        android:id="@+id/linearLayout2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp">
        <Button
            android:id="@+id/btnPeople"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Население">
        </Button>
        <EditText
            android:id="@+id/etPeople"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:inputType="number">
        </EditText>
    </LinearLayout>
    <Button
        android:id="@+id/btnGroup"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Население по региону"
        android:layout_marginTop="5dp">
    </Button>
    <LinearLayout
        android:id="@+id/linearLayout4"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp">
        <Button
            android:id="@+id/btnHaving"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Население по региону >">
        </Button>
        <EditText
            android:id="@+id/etRegionPeople"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:inputType="number">
        </EditText>
    </LinearLayout>
    <LinearLayout
        android:id="@+id/linearLayout3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp">
        <Button
            android:id="@+id/btnSort"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Сортировка">
        </Button>
        <RadioGroup
            android:id="@+id/rgSort"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content">
            <RadioButton
                android:id="@+id/rName"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:checked="true"
                android:text="Наименование">
            </RadioButton>
            <RadioButton
                android:id="@+id/rPeople"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Население">
            </RadioButton>
            <RadioButton
                android:id="@+id/rRegion"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="Регион">
            </RadioButton>
        </RadioGroup>
    </LinearLayout>
</LinearLayout>

Here are 6 buttons for 6 functions we are going to implement and editText fields for data entering where it needs. RadioGroup will be used for sorting.

MainActivity.java code:

package ru.startandroid.develop.p0361sqlitequery;

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;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioGroup;

public class MainActivity extends Activity implements OnClickListener {

  final String LOG_TAG = "myLogs";

  String name[] = { "China", "USA", "Brazil", "Russia", "Japan",
      "Germany", "Egypt", "Italy", "France", "Canada" };
  int people[] = { 1400, 311, 195, 142, 128, 82, 80, 60, 66, 35 };
  String region[] = { "Asia", "America", "America", "Europa", "Asia",
      "Europe", "Africa", "Europe", "Europe", "America" };

  Button btnAll, btnFunc, btnPeople, btnSort, btnGroup, btnHaving;
  EditText etFunc, etPeople, etRegionPeople;
  RadioGroup rgSort;

  DBHelper dbHelper;
  SQLiteDatabase db;

  /** Called when the activity is first created. */

  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    btnAll = (Button) findViewById(R.id.btnAll);
    btnAll.setOnClickListener(this);

    btnFunc = (Button) findViewById(R.id.btnFunc);
    btnFunc.setOnClickListener(this);

    btnPeople = (Button) findViewById(R.id.btnPeople);
    btnPeople.setOnClickListener(this);

    btnSort = (Button) findViewById(R.id.btnSort);
    btnSort.setOnClickListener(this);

    btnGroup = (Button) findViewById(R.id.btnGroup);
    btnGroup.setOnClickListener(this);

    btnHaving = (Button) findViewById(R.id.btnHaving);
    btnHaving.setOnClickListener(this);

    etFunc = (EditText) findViewById(R.id.etFunc);
    etPeople = (EditText) findViewById(R.id.etPeople);
    etRegionPeople = (EditText) findViewById(R.id.etRegionPeople);

    rgSort = (RadioGroup) findViewById(R.id.rgSort);

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

    // check if the table is empty
    Cursor c = db.query("mytable", null, null, null, null, null, null);
    if (c.getCount() == 0) {
      ContentValues cv = new ContentValues();
      // filling the table
      for (int i = 0; i < 10; i++) {
        cv.put("name", name[i]);
        cv.put("people", people[i]);
        cv.put("region", region[i]);
        Log.d(LOG_TAG, "id = " + db.insert("mytable", null, cv));
      }
    }
    c.close();
    dbHelper.close();
    // emulate btnAll click
    onClick(btnAll);

  }

  public void onClick(View v) {

    // connect to database
    db = dbHelper.getWritableDatabase();

    // data from the screen
    String sFunc = etFunc.getText().toString();
    String sPeople = etPeople.getText().toString();
    String sRegionPeople = etRegionPeople.getText().toString();

    // arguments for query
    String[] columns = null;
    String selection = null;
    String[] selectionArgs = null;
    String groupBy = null;
    String having = null;
    String orderBy = null;

    // cursor
    Cursor c = null;

    // determine which button was clicked
    switch (v.getId()) {
    // All data
    case R.id.btnAll:
      Log.d(LOG_TAG, "--- Все записи ---");
      c = db.query("mytable", null, null, null, null, null, null);
      break;
    // Function
    case R.id.btnFunc:
      Log.d(LOG_TAG, "--- Function " + sFunc + " ---");
      columns = new String[] { sFunc };
      c = db.query("mytable", columns, null, null, null, null, null);
      break;
    // Population more than
    case R.id.btnPeople:
      Log.d(LOG_TAG, "--- Population more than " + sPeople + " ---");
      selection = "people > ?";
      selectionArgs = new String[] { sPeople };
      c = db.query("mytable", null, selection, selectionArgs, null, null,
          null);
      break;
    // Population by region
    case R.id.btnGroup:
      Log.d(LOG_TAG, "--- Population by region ---");
      columns = new String[] { "region", "sum(people) as people" };
      groupBy = "region";
      c = db.query("mytable", columns, null, null, groupBy, null, null);
      break;
    // Regions with population more than
    case R.id.btnHaving:
      Log.d(LOG_TAG, "--- Regions with population more than " + sRegionPeople
          + " ---");
      columns = new String[] { "region", "sum(people) as people" };
      groupBy = "region";
      having = "sum(people) > " + sRegionPeople;
      c = db.query("mytable", columns, null, null, groupBy, having, null);
      break;
    // Sorting
    case R.id.btnSort:
      // sorting by
      switch (rgSort.getCheckedRadioButtonId()) {
      // name
      case R.id.rName:
        Log.d(LOG_TAG, "--- Sorting by name ---");
        orderBy = "name";
        break;
      // population
      case R.id.rPeople:
        Log.d(LOG_TAG, "--- Sorting by population ---");
        orderBy = "people";
        break;
      // region
      case R.id.rRegion:
        Log.d(LOG_TAG, "--- Sorting by region ---");
        orderBy = "region";
        break;
      }
      c = db.query("mytable", null, null, null, null, null, orderBy);
      break;
    }

    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());
      }
      c.close();
    } else
      Log.d(LOG_TAG, "Cursor is null");

    dbHelper.close();
  }

  class DBHelper extends SQLiteOpenHelper {

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

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, "--- onCreate database ---");
      // create table with columns
      db.execSQL("create table mytable ("
          + "id integer primary key autoincrement," + "name text,"
          + "people integer," + "region text" + ");");
    }

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

    }
  }

}

Let’s save and launch the application.

All rows were displayed in logs, as if we click “All data” button.

--- All data ---
id = 1; name = China; people = 1400; region = Asia;
id = 2; name = USA; people = 311; region = America;
id = 3; name = Brazil; people = 195; region = America;
id = 4; name = Russia; people = 142; region = Europe;
id = 5; name = Japan; people = 128; region = Asia;
id = 6; name = Germany; people = 82; region = Europe;
id = 7; name = Egypt; people = 80; region = Africa;
id = 8; name = Italy; people = 60; region = Europe;
id = 9; name = France; people = 66; region = Europe;
id = 10; name = Canada; people = 35; region = America;

I.e the table is filled with data and we can interact with it.

Let’s try to use aggregate function. We will get rows’ count for example.

Enter the value:

click “Function” button and look in logs:

--- Function count(*) as Count ---
Count = 10;

It’s alright, there are 10 rows in the table

Now let’s show countries with population over 100 million. Enter 100 and click “Population” button.

Logs:

--- Population more than 100 ---
id = 1; name = China; people = 1400; region = Asia;
id = 2; name = USA; people = 311; region = America;
id = 3; name = Brazil; people = 195; region = America;
id = 4; name = Russia; people = 142; region = Europe;
id = 5; name = Japan; people = 128; region = Asia;

Now let’s group countries by region and display regions’ population. Click “Population by region” button.

Logs:

--- Population by region ---
region = Asia; people = 1528;
region = America; people = 541;
region = Africa; people = 80;
region = Europe; people = 350;

Now let’s only display regions with population over 500 million. Enter 500 and click “Population by region” button.

Logs:

--- Regions with population more than 500 ---
region = Asia; people = 1528;
region = America; people = 541;

 The sorting only remained. Let’s for example choose sort by population and click “Sort” button.

Лог:

--- Sorting by population ---
id = 10; name = Canada; people = 35; region = America;
id = 8; name = Italy; people = 60; region = Europe;
id = 9; name = France; people = 66; region = Europe;
id = 7; name = Egypt; people = 80; region = Africa;
id = 6; name = Germany; people = 82; region = Europe;
id = 5; name = Japan; people = 128; region = Asia;
id = 4; name = Russia; people = 142; region = Europe;
id = 3; name = Brazil; people = 195; region = America;
id = 2; name = USA; people = 311; region = America;
id = 1; name = China; people = 1400; region = Asia;

By default sorting is produced in an ascending order.

Everything works the way it should. Within these examples we used all basic parameters of the query method. In addition to the described parameters, the query method also has implementations with limit and distinct parameters. I did not show them here, but I'll tell you about them in words:

limit is a string parameter, which is used to be determined in [offset], rows format. I.e if “5” will be passed to query as a limit, the query will return only five first rows. If we pass “3.5”, query will return 5 rows, beginning from the fourth one (NOT THIRD).

distinct is a boolean parameter for duplicates removing. It may be true or false.

I hope that the query method, which at first seemed like a large group of parameters, became clear and simple.

The next lesson:
- read data from joined tables;
- use rawQuery;