You get a bonus - 1 coin for daily activity. Now you have 1 coin

36. SQLite. Learn more about the query method. Condition, Sort, Group

Lecture



In this lesson:

- parse the method of reading the data query
- we use sorting, grouping, conditions, having

  36. SQLite.  Learn more about the query method.  Condition, Sort, Group - video lesson version

In the previous lessons we used the query method to read all the data from the table. We used only the table name as an input parameter and received all the records. But query has other options:

columns - the list of fields we want to get
selection - WHERE condition string
selectionArgs is an array of arguments for selection. In the selection, you can use the signs ? , and which will be replaced by these values.
groupBy - grouping
having - using conditions for aggregate functions
orderBy - sorting

Let's try to use them as an example. Create an application - a directory of countries . Take ten countries and save in the database their name , number of population and region . We implement the following functions in the application:

- output all records
- output of aggregate function value (SUM, MIN, MAX, COUNT)
- withdrawal of countries with a population greater than specified
- grouping of countries by region
- the withdrawal of regions with a population of more than specified
- sorting countries by name, population or region

Display all data again will be in the log.

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 main.xml layout file and write:

  <? 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 = "Country Directory"
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 = "All posts"
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 = "Function">
</ 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 = "Population>">
</ 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 = "Population by Region"
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 = "Population by Region>">
</ 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 = "Sort">
</ 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 = "Name">
</ RadioButton>
<RadioButton
android: id = "@ + id / rPeople"
android: layout_width = "wrap_content"
android: layout_height = "wrap_content"
android: text = "Population">
</ RadioButton>
<RadioButton
android: id = "@ + id / rRegion"
android: layout_width = "wrap_content"
android: layout_height = "wrap_content"
android: text = "Region">
</ RadioButton>
</ RadioGroup>
</ LinearLayout>
</ LinearLayout>

6 buttons - 6 functions that we plan to implement. Fields to enter 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 do any checks on the values ​​entered from the screen, so as not to overload the code. It turned out to be rather big, but there is nothing complicated about it.

Three data arrays name , people , region . These are the names of countries, their population (in millions) and the regions to which countries belong. According to this data we will fill in the table.

In the onCreate method , we define and find screen elements, assign handlers, create a dbHelper object to manage the database, connect to the database and get a db object to work with the database, check for records in the table, if there is nothing, fill it with data, close the connection and emulate pressing the All records button - to immediately display the entire list.

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

btnAll - output all records. Call the query method with the table name and null for the remaining parameters. This is already familiar, done in the past lesson.

btnFunc - output the value of the aggregate function (or any field). I use the columns parameter to write the fields that I would like to get from the table, i.e. what is usually listed after the word SELECT in a SQL query. columns is of type String [] - an array of strings. Create an array of one value that is read from the etFunc field on the screen. Run the query.

btnPeople - output of countries with a population greater than the number entered on the screen. Use selection to form a condition. In this case we use one argument - ? . The argument value is set in selectionArgs - this is sPeople - the contents of the etPeople field. Run the query.

btnGroup - grouping countries by region and output the total population. Use columns to specify the columns you would like to receive - the region and the amount of the population. In groupBy we indicate that the grouping will be by region . Run the query.

btnHaving - output regions with a population greater than the specified number. It is completely similar to the case with grouping, but the condition is added in the having parameter — the sum of the region's population must be less than sRegionPeople (the etRegionPeople value from the screen).

btnSort - sorting countries. We determine which RadioButton is enabled and, accordingly, specify in the orderBy field to sort the data. Run the query.

In the cases described above, we ran a query and obtained an object c of the Cursor class. Next, we check that it exists and has entries ( moveToFirst ). If everything is ok, then we run a loop through the records in the do ... while loop (c.moveToNext ()) . For each record, we loop through the field names ( getColumnNames ), get its number for each field, and retrieve the data using the getString method. We form a list of fields and values ​​in the str variable, which we then output to the log. After all this, close the connection.

Well, at the end of the code is a description of the nested class DBHelper . Nothing has changed here from past lessons. Only when creating the table are other fields used .

Save everything and run the application.

At the start of the log, all the entries were displayed as if we had clicked the "All entries" button.

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

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

Let's try to use the aggregate function. For example - get the number of records . Enter the value:

  36. SQLite.  Learn more about the query method.  Condition, Sort, Group

Click the button Function . We look at the log:

--- Function count (*) as Count ---
Count = 10;

That's right, 10 entries in the table.

We show countries with a population of more than 100 million. Enter 100 and click Population>

  36. SQLite.  Learn more about the query method.  Condition, Sort, Group

Log:

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

Log:

--- Population by region ---
region = Asia; people = 1528;
region = America; people = 541;
region = Africa; people = 80;
region = Europe; people = 350;

Now we will display only those regions in which the population is above 500 million. Enter 500 and click on Population by Region>

  36. SQLite.  Learn more about the query method.  Condition, Sort, Group

Log:

--- Regions with a population of more than 500 ---
region = Asia; people = 1528;
region = America; people = 541;

Left sorting. For example, select sorting by population and click the Sort button

  36. SQLite.  Learn more about the query method.  Condition, Sort, Group

Log:

--- Sort 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 = United States; people = 311; region = America;
id = 1; name = China; people = 1400; region = Asia;

By default, sorting is in ascending order .

Everything works as it should. In these examples, we used all the basic parameters of the query method. In addition to the parameters described, the query method also has implementations using the limit and distinct parameters. I did not show them separately here. I'll tell you in words:

limit - string parameter, specified in the format [offset], rows . Those. if the query in the quality limit to transfer the string " 5 " - then the query will display only the first five entries. If, however, to transfer " 3.5 ", the request will issue five entries, starting with the fourth (NOT the third).

distinct is a boolean parameter, removing duplicates. May be true or false .

I hope that the query method, which at first seemed to be a large cluster of parameters, became clear and simple.

In the next lesson:

- read data from related tables
- use rawQuery


Comments


To leave a comment
If you have any suggestion, idea, thanks or comment, feel free to write. We really value feedback and are glad to hear your opinion.
To reply

Mobile Programming (Android IOs)

Terms: Mobile Programming (Android IOs)