MyTetra Share
Делитесь знаниями!
Урок 36. SQLite. Подробнее про метод query. Условие, сортировка, группировка
29.04.2017
11:13
Текстовые метки: SQLite
Раздел: Android - Ресурсы - StartAndroid

Урок 36. SQLite. Подробнее про метод query. Условие, сортировка, группировка

24 ноября 2011

В этом уроке:

- подробно разбираем метод чтения данных 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 и пишем:


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

<?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:


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

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, "mytable", 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) {

 

    }

  }

 

}

 

Никаких проверок на вводимые с экрана значения я не делал, чтобы не перегружать код. Он и так немаленький получился, но ничего сложного в нем нет.

Три массива данных namepeopleregion. Это наименования стран, их население (в млн.) и регионы, к которым страны относятся. По этим данным мы будем заполнять таблицу.

В методе 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, который сначала казался большим скоплением параметров, стал понятен и прост.


Так же в этом разделе:
 
MyTetra Share v.0.52
Яндекс индекс цитирования