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) {

    }
  }

}

 

I didn’t include any validation checks for entered values for not to make the code heavy. It’s already rather big, but there is nothing difficult with it.

Here we have three arrays name, people and region which determines countrie’s names, population and regions they are located in. We will fill the table with this data.

We determine views within onCreate method, set listeners, create dbHelper object for database management, connect to database and get db object for interaction with it. Then we check if the table is empty and fill it with data, close connection and programmatically click “All data” button to display all the list in logs.

Within onClick method we connect to database, wright values from editText fields to variables, determine Cursor object, variables for query method and check which button was clicked.

btnAll is for all data displaying. Here we call query method with table name for the first parameter and null for others. We’ve already done it the previous lesson.

btnFunc is for displaying aggregate function value (or any other attribute). Here we use columns parameter, which must be filled with attributes we want to get from the table which are usually written after SELECT tag in SQL request. columns is a String array, so we initialize it as an array including one string was read from etFunc field and launch query.

btnPeople is for displaying countries with population bigger than the entered value. We use select to determine conditions and use ? mark as an argument which value we get from selectionArgs (it is sPeople variable initialized with etPeople field content). Launch query.

btnGroup is for countries grouping and displaying their total population. We use columns to determine types of attributes we want to get, they are region and total population. Within groupBy we set grouping by region and launch query.

btnHaving is for displaying regions with population bigger than the entered value. This case is similar to grouping, but one more condition is added with having parameter - region population summ must be lower than sRegionPeople (it’s etRegionPeople field content value).

btnSort is for country sorting. Here we check which RadioButton is selected and specify data sort attribute within orderBy accordingly. Launch query.

In the cases described earlier we launched query and got Cursor object. Now we check if it is not null and has rows (moveToFirst). If it’s alright, we launch data search in do .. while cycle (c.moveToNext()). For each row we get columns names (getColumnNames) and their values calling getString with column number as an argument. We initialize str with chain of column names and their values and display str in logs. After that we close the connection.

At the end of the code there is a description of the DBHelper class, nothing has changed here since the previous lesson, except columns names for table creation.  

 

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;

 


Присоединяйтесь к нам в Telegram:

- в канале StartAndroid публикуются ссылки на новые статьи с сайта startandroid.ru и интересные материалы с хабра, medium.com и т.п.

- в чатах решаем возникающие вопросы и проблемы по различным темам: Android, Kotlin, RxJava, Dagger, Тестирование 

- ну и если просто хочется поговорить с коллегами по разработке, то есть чат Флудильня

- новый чат Performance для обсуждения проблем производительности и для ваших пожеланий по содержанию курса по этой теме 




Language