Урок 36. SQLite. Подробнее про метод query. Условие, сортировка, группировка
На прошлых уроках мы использовали метод 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 и пишем:
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. Тут ничего не изменилось с прошлых уроков. Только при создании таблицы используются другие поля.
Сохраняем все и запускаем приложение.
В лог при запуске вывелись все записи, как если бы мы нажали кнопку «Все записи».
— Все записи —
name = Китай; people = 1400; region = Азия;
name = США; people = 311; region = Америка;
name = Бразилия; people = 195; region = Америка;
name = Россия; people = 142; region = Европа;
name = Япония; people = 128; region = Азия;
name = Германия; people = 82; region = Европа;
name = Египет; people = 80; region = Африка;
name = Италия; people = 60; region = Европа;
name = Франция; people = 66; region = Европа;
name = Канада; people = 35; region = Америка;
Т.е. таблица заполнена данными, можно работать.
Попробуем использовать агрегатную функцию. Например – получим кол-во записей. Вводим значение:
жмем кнопку Функция. Смотрим лог:
— Функция count(*) as Count —
Count = 10;
Все верно, 10 записей в таблице.
Покажем страны с населением больше 100 млн. Вводим 100 и жмем Население >
— Население больше 100 —
name = Китай; people = 1400; region = Азия;
name = США; people = 311; region = Америка;
name = Бразилия; people = 195; region = Америка;
name = Россия; people = 142; region = Европа;
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;
Осталась сортировка. Выберем, например, сортировку по населению и жмем кнопку Сортировка
— Сортировка по населению —
name = Канада; people = 35; region = Америка;
name = Италия; people = 60; region = Европа;
name = Франция; people = 66; region = Европа;
name = Египет; people = 80; region = Африка;
name = Германия; people = 82; region = Европа;
name = Япония; people = 128; region = Азия;
name = Россия; people = 142; region = Европа;
name = Бразилия; people = 195; region = Америка;
name = США; people = 311; region = Америка;
name = Китай; people = 1400; region = Азия;
По умолчанию сортировка идет по возрастанию.
Все работает так, как и должно. На этих примерах мы использовали все основные параметры метода query. Кроме описанных параметров, у метода query есть также реализации с использованием параметров limit и distinct. Я не стал их здесь отдельно показывать. Расскажу на словах:
limit – строковый параметр, указывается в формате [offset], rows. Т.е. если в query в качестве limit передать строку «5» — то запрос выдаст только пять первых записей. Если же передать «3,5«, то запрос выдаст пять записей, начиная с четвертой (НЕ с третьей).
distinct – это boolean-параметр, удаление дубликатов. Может быть true или false.
Надеюсь, что метод query, который сначала казался большим скоплением параметров, стал понятен и прост.
— читаем данные из связанных таблиц
— используем rawQuery
Присоединяйтесь к нам в Telegram:
— в канале StartAndroid публикуются ссылки на новые статьи с сайта startandroid.ru и интересные материалы с хабра, medium.com и т.п.
— в чатах решаем возникающие вопросы и проблемы по различным темам: Android, Compose, Kotlin, RxJava, Dagger, Тестирование, Performance
— ну и если просто хочется поговорить с коллегами по разработке, то есть чат Флудильня