Урок 36. SQLite. Подробнее про метод query. Условие, сортировка, группировка
В этом уроке:
- подробно разбираем метод чтения данных query
- используем сортировку, группировку, условия, having
На прошлых уроках мы использовали метод query для чтения всех данных из таблицы. Мы использовали только имя таблицы в качестве входного параметра и получали все записи. Но у query есть и другие параметры:
columns – список полей, которые мы хотим получить
selection – строка условия WHERE
selectionArgs – массив аргументов для selection. В selection можно использовать знаки ? , а которые будут заменены этими значениями.
groupBy - группировка
having – использование условий для агрегатных функций
orderBy - сортировка
Попробуем на примере их использовать. Создадим приложение – справочник стран. Возьмем десять стран и сохраним в БД их наименование, количество населения и регион. Реализуем в приложении следующие функции:
- вывод всех записей
- вывод значения агрегатной функции (SUM, MIN, MAX, COUNT)
- вывод стран с населением, больше чем указано
- группировка стран по региону
- вывод регионов с населением больше, чем указано
- сортировка стран по наименованию, населению или региону
Выводить все данные снова будем в лог.
Создадим проект:
Project name: P0361_SQLiteQuery
Build Target: Android 2.3.3
Application name: SQLiteQuery
Package name: ru.startandroid.develop.p0361sqlitequery
Create Activity: MainActivity
Открываем layout-файл 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>
6 кнопок – 6 функций, которые мы планируем реализовать. Поля для ввода значений, где это необходимо. Для сортировки используем RadioGroup.
Код для 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) {
}
}
}
Никаких проверок на вводимые с экрана значения я не делал, чтобы не перегружать код. Он и так немаленький получился, но ничего сложного в нем нет.
Три массива данных name, people, region. Это наименования стран, их население (в тысячах) и регионы, к которым страны относятся. По этим данным мы будем заполнять таблицу.
В методе onCreate мы определяем и находим экранные элементы, присваиваем обработчики, создаем объект dbHelper для управления БД, подключаемся к базе и получаем объект db для работы с БД, проверяем наличие записей в таблице, если нет ничего – заполняем ее данными, закрываем соединение и эмулируем нажатие кнопки Все записи - чтобы сразу вывести весь список.
В методе onClick – подключаемся к базе, читаем данные с экранных полей в переменные, описываем переменные, которые будем использовать в методе query, и курсор, и смотрим, какая кнопка была нажата.
btnAll – вывод всех записей. Вызываем метод query с именем таблицы и null для остальных параметров. Это уже знакомо, делали на прошлом уроке.
btnFunc – вывод значения агрегатной функции (или любого поля). Использую параметр columns, в который надо записать поля, которые я хотел бы получить из таблицы, т.е. то, что обычно перечисляется после слова SELECT в SQL-запросе. columns имеет тип String[] – массив строк. Создаем массив из одного значения, которое считано с поля etFunc на экране. Запускаем query.
btnPeople – вывод стран с населением больше введенного на экране количества. Используем selection для формирования условия. При этом используем один аргумент - ?. Значение аргумента задаем в selectionArgs – это sPeople – содержимое поля etPeople. Запускаем query.
btnGroup – группировка стран по регионам и вывод общее количество населения. Используем columns для указания столбцов, которые хотели бы получить – регион и сумма населения. В groupBy указываем, что группировка будет по региону. Запускаем query.
btnHaving – вывод регионов с населением больше указанного числа. Полностью аналогично случаю с группировкой, но добавляется условие в параметре having – сумма населения региона должна быть меньше sRegionPeople (значение etRegionPeople с экрана).
btnSort – сортировка стран. Определяем какой RadioButton включен и соответственно указываем в orderBy поле для сортировки данных. Запускаем query.
В выше описанных случаях мы запускали query и получали объект c класса Cursor. Далее мы проверяем, что он существует и в нем есть записи (moveToFirst). Если все ок, то мы запускаем перебор записей в цикле do … while (c.moveToNext()). Для каждой записи перебираем названия полей (getColumnNames), получаем по каждому полю его номер и извлекаем данные методом getString. Формируем список полей и значений в переменную str, которую потом выводим в лог. После всего этого закрываем соединение.
Ну и в конце кода идет описание вложенного класса DBHelper. Тут ничего не изменилось с прошлых уроков. Только при создании таблицы используются другие поля.
Сохраняем все и запускаем приложение.
В лог при запуске вывелись все записи, как если бы мы нажали кнопку «Все записи».
--- Все записи ---
id = 1; name = Китай; people = 1400; region = Азия;
id = 2; name = США; people = 311; region = Америка;
id = 3; name = Бразилия; people = 195; region = Америка;
id = 4; name = Россия; people = 142; region = Европа;
id = 5; name = Япония; people = 128; region = Азия;
id = 6; name = Германия; people = 82; region = Европа;
id = 7; name = Египет; people = 80; region = Африка;
id = 8; name = Италия; people = 60; region = Европа;
id = 9; name = Франция; people = 66; region = Европа;
id = 10; name = Канада; people = 35; region = Америка;
Т.е. таблица заполнена данными, можно работать.
Попробуем использовать агрегатную функцию. Например – получим кол-во записей. Вводим значение:
жмем кнопку Функция. Смотрим лог:
--- Функция count(*) as Count ---
Count = 10;
Все верно, 10 записей в таблице.
Покажем страны с населением больше 100 млн. Вводим 100 и жмем Население >
Лог:
--- Население больше 100 ---
id = 1; name = Китай; people = 1400; region = Азия;
id = 2; name = США; people = 311; region = Америка;
id = 3; name = Бразилия; people = 195; region = Америка;
id = 4; name = Россия; people = 142; region = Европа;
id = 5; name = Япония; people = 128; region = Азия;
Сгруппируем страны по региону и покажем население регионов. Нажмем кнопку Население по региону
Лог:
--- Население по региону ---
region = Азия; people = 1528;
region = Америка; people = 541;
region = Африка; people = 80;
region = Европа; people = 350;
Теперь отобразим только те регионы, в которых население выше 500 млн.чел. Вводим 500 и жмем Население по региону >
Лог:
--- Регионы с населением больше 500 ---
region = Азия; people = 1528;
region = Америка; people = 541;
Осталась сортировка. Выберем, например, сортировку по населению и жмем кнопку Сортировка
Лог:
--- Сортировка по населению ---
id = 10; name = Канада; people = 35; region = Америка;
id = 8; name = Италия; people = 60; region = Европа;
id = 9; name = Франция; people = 66; region = Европа;
id = 7; name = Египет; people = 80; region = Африка;
id = 6; name = Германия; people = 82; region = Европа;
id = 5; name = Япония; people = 128; region = Азия;
id = 4; name = Россия; people = 142; region = Европа;
id = 3; name = Бразилия; people = 195; region = Америка;
id = 2; name = США; people = 311; region = Америка;
id = 1; name = Китай; people = 1400; region = Азия;
По умолчанию сортировка идет по возрастанию.
Все работает так, как и должно. На этих примерах мы использовали все основные параметры метода query. Кроме описанных параметров, у метода query есть также реализации с использованием параметров limit и distinct. Я не стал их здесь отдельно показывать. Расскажу на словах:
limit – строковый параметр, указывается в формате [offset], rows. Т.е. если в query в качестве limit передать строку "5" - то запрос выдаст только пять первых записей. Если же передать "3,5", то запрос выдаст пять записей, начиная с четвертой (НЕ с третьей).
distinct – это boolean-параметр, удаление дубликатов. Может быть true или false.
Надеюсь, что метод query, который сначала казался большим скоплением параметров, стал понятен и прост.
На следующем уроке:
- читаем данные из связанных таблиц
- используем rawQuery