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

In this lesson:

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

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

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:

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>

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>

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

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