Lesson 36. SQLite. Learn more about the query method. Condition, sorting, grouping

Lesson 36. SQLite. Learn more about the query method. Condition, sorting, grouping


In this lesson:

– we thoroughly examine the method of reading query data
– we use sorting, grouping, conditions, having

In the past lessons, we have used the method query to read all the data in the table. We used only the table name as the input parameter and received all entries. But query has other options:

columns is the list of fields we want to get
selection – WHERE string
selectionArgs – an array of arguments for selection. You can use characters in the selection ?To be replaced by these values.
groupBy – grouping
having – use of conditions for aggregate functions
orderBy – sorting

Let’s try to use them as an example. Let’s create an application – country directory. let’s take ten countries and store them in the database name, number population and region. We implement the following features in the application:

– output all records
– output of aggregate function value (SUM, MIN, MAX, COUNT)
– the conclusion of countries with population more than indicated
– grouping of countries by region
– Conclusion of regions with population more than indicated
– sort countries by name, population or region

We will display all the data again in a log.

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

Open the layout file main.xml and we write:



    
    
    
    
        
        
            
            
        
    
    
        
        
        
    
    
    
        
        
        
    
    
        
        
            
            
            
            
            
            
        
    

6 buttons – 6 functions that we plan to implement. Fields for entering values ​​where necessary. for sorting we use RadioGroup.

code for MainActivity.java:

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[] = { "Китай", "США", "Бразилия", "Россия", "Япония",
      "Германия", "Египет", "Италия", "Франция", "Канада" };
  int people[] = { 1400, 311, 195, 142, 128, 82, 80, 60, 66, 35 };
  String region[] = { "Азия", "Америка", "Америка", "Европа", "Азия",
      "Европа", "Африка", "Европа", "Европа", "Америка" };

  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);
    // подключаемся к базе
    db = dbHelper.getWritableDatabase();

    // проверка существования записей
    Cursor c = db.query("mytable", null, null, null, null, null, null);
    if (c.getCount() == 0) {
      ContentValues cv = new ContentValues();
      // заполним таблицу
      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();
    // эмулируем нажатие кнопки btnAll
    onClick(btnAll);

  }

  public void onClick(View v) {

    // подключаемся к базе
    db = dbHelper.getWritableDatabase();

    // данные с экрана
    String sFunc = etFunc.getText().toString();
    String sPeople = etPeople.getText().toString();
    String sRegionPeople = etRegionPeople.getText().toString();

    // переменные для query
    String[] columns = null;
    String selection = null;
    String[] selectionArgs = null;
    String groupBy = null;
    String having = null;
    String orderBy = null;

    // курсор
    Cursor c = null;

    // определяем нажатую кнопку
    switch (v.getId()) {
    // Все записи
    case R.id.btnAll:
      Log.d(LOG_TAG, "--- Все записи ---");
      c = db.query("mytable", null, null, null, null, null, null);
      break;
    // Функция
    case R.id.btnFunc:
      Log.d(LOG_TAG, "--- Функция " + sFunc + " ---");
      columns = new String[] { sFunc };
      c = db.query("mytable", columns, null, null, null, null, null);
      break;
    // Население больше, чем
    case R.id.btnPeople:
      Log.d(LOG_TAG, "--- Население больше " + sPeople + " ---");
      selection = "people > ?";
      selectionArgs = new String[] { sPeople };
      c = db.query("mytable", null, selection, selectionArgs, null, null,
          null);
      break;
    // Население по региону
    case R.id.btnGroup:
      Log.d(LOG_TAG, "--- Население по региону ---");
      columns = new String[] { "region", "sum(people) as people" };
      groupBy = "region";
      c = db.query("mytable", columns, null, null, groupBy, null, null);
      break;
    // Население по региону больше чем
    case R.id.btnHaving:
      Log.d(LOG_TAG, "--- Регионы с населением больше " + 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;
    // Сортировка
    case R.id.btnSort:
      // сортировка по
      switch (rgSort.getCheckedRadioButtonId()) {
      // наименование
      case R.id.rName:
        Log.d(LOG_TAG, "--- Сортировка по наименованию ---");
        orderBy = "name";
        break;
      // население
      case R.id.rPeople:
        Log.d(LOG_TAG, "--- Сортировка по населению ---");
        orderBy = "people";
        break;
      // регион
      case R.id.rRegion:
        Log.d(LOG_TAG, "--- Сортировка по региону ---");
        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) {
      // конструктор суперкласса
      super(context, "myDB", null, 1);
    }

    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, "--- onCreate database ---");
      // создаем таблицу с полями
      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 did not make any checks on the values ​​entered from the screen, not to overload the code. He came out quite a lot, but there is nothing complicated about it.

Three arrays of data name, people, region. it name countries, theirs people (Million) And regionsTo which countries they belong. According to this data, we will fill the table.

In the method onCreate we identify and find screen elements, assign handlers, create an object dbHelper to manage the database, connect to the database and get the object db to work with the database, check the presence of records in the table, if there is nothing – fill it with data, close the connection with the emulation of a click of a button all records – to display the entire list immediately.

In the method onClick – connect to the database, read data from the screen fields into variables, describe the variables that we will use in the method query, And the cursor and see which button was pressed.

btnAll – output all records. Call the query method with by name tables and null for other parameters. This is already familiar, did in the previous lesson.

btnFunc – output value of the aggregate function (or any field). I use the parameter columnsThat should be written down fieldsWhich I would like to get from a spreadsheet, that is, what is usually listed after a word SELECT in a SQL query. columns has type String[] – array of rows. We create an array of one value that is read from the field etFunc on the screen. We launch query.

btnPeople – the conclusion of the countries with population more than the number entered on the screen. we use selection to form a condition. We use one argument here – ?. The value of the argument is set in selectionArgs is it sPeople – field content etPeople. We launch query.

btnGroup – country grouping by region and conclusion total population. we use columns to specify the columns you would like to receive – region and sum people. IN groupBy we point out that grouping will be on region. We launch query.

btnHaving – the conclusion of regions with population more than the specified number. Quite similar to the occasion with the grouping, but the condition in the parameter is added having – the population of the region should be less sRegionPeople (value etRegionPeople screen).

btnSort – sorting countries. determine which RadioButton is included and accordingly specified in orderBy data sort box. We launch query.

In the cases described above, we ran query and received a class c object Cursor. Next, we check that it exists and has records (moveToFirst). If everything is ok then we run a loop of records in the loop do … while (c.moveToNext ()). For each entry, we sort through the field names (getColumnNames), We obtain for each field its number and extract the data by method getString. Form a list of fields and values ​​into a variable pWhich is then logged. After all this we close the connection.

Well, at the end of the code is a description of the nested class DBHelper. Nothing has changed here in the past. only at creating a table others are used fields.

We save everything and launch the application.

All entries were displayed in the log at startup, as if we had clicked the “All records” button.

— All records —
id = 1; name = China; people = 1400; region = Asia;
id = 2; name = US; 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;

That is, the table is filled with data, you can work.

Let’s try to use the aggregate function. For example – we get number of records. Enter the value:

press the button function. We look at the log:

— Count function (*) as Count —
Count = 10;

That’s right, 10 entries in the table.

Let’s show countries with a population of more than 100 million 100 and click population>

log:

— Population over 100 —
id = 1; name = China; people = 1400; region = Asia;
id = 2; name = US; 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;

We group the countries by region and show the population of the regions. press the button Population of the region

log:

— Population of the region —
region = Asia; people = 1528;
region = America; people = 541;
region = Africa; people = 80;
region = Europe; people = 350;

Now we will only reflect those regions where the population is above 500 million. we introduce 500 and click Population of the region>

log:

— Regions with population over 500 —
region = Asia; people = 1528;
region = America; people = 541;

Remaining sort. For example, select sort by population and push the button Sorting

log:

— Population sorting —
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 = US; people = 311; region = America;
id = 1; name = China; people = 1400; region = Asia;

Default sorting goes on growth.

Everything works as it should. In these examples, we used all the basic ones parameters method query. In addition to the described parameters, the query method also has implementations using the parameters limit and distinct. I didn’t show them here separately. Let’s say in words:

limit urgent the parameter is specified in the format [offset], rows. That is, if in query as limit pass row “5“- the request will only be issued five first records. If you pass “3.5“Then the request will be issued five records starting with the fourth (NOT from the third).

distinct is it boolean-parameter, removal of duplicates. May be true or false.

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

In the next lesson:

– read data from linked tables
– we use rawQuery




Discuss in the forum [205 replies]

Leave a Comment