Android content provider-based SQL

The SQL language used by SQLite is basically the same as the standard SQL language, so I would like to review the usage of the SQL language first.

First, let's assume that we have the following table named "employees ":

ID	Lastname	Firstname	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

The basic SQL format is as follows:

SQL code

1. SelectColumn nameFromTable Name

The most basic SQL statement is to select the column data to be returned from the table without any filtering conditions. Of course, if our "column name" is "*", the whole table data will be returned. On Android, the SQL-related method usually has a string [] Columns parameter, which corresponds to the "column name" in the SQL statement ". We can see the method-query in Android:

Java code

1. PublicCursor query (string table, string [] columns, string selection, string [] selectionargs,
2. String groupby, string having, string orderby, string limit)

 

Suppose we want to get the full name of a person, then the SQL statement is as follows:

SQL code

1. SelectFirstname, lastnameFromEmployees

Of course, we usually have conditions to filter the results we want. For example, if I only want to return the information of people whose city is Beijing, then I need to use where to filter:

SQL code

1. Select*FromEmployeesWhereCity = 'beijing'

Here, the string followed by where is the string selection parameter corresponding to Android method. The android method usually has a parameter related to this: String [] selectionargs. I didn't understand what this parameter was for at first, but then I gradually realized that this parameter is actually inseparable from the selection parameter. When the selection parameter contains the question mark "? ", Then selectionargs will be used. For example, assume that the selection parameter is assigned as follows:

Java code

1. String selection = "city =? ";

In this case, we must assign a value in selectionargs.

Java code

1. String [] selectionargs = {"Beijing "};

That is to say, the string in selectionargs is the variable corresponding to the question mark in selection. In fact, the filtering condition city in selection can be dynamically assigned, rather than being written to the program.

Obviously, the string groupby parameter is the string corresponding to the group by parameter in the SQL statement. Group by is used together with Aggregate functions such as sum. You can check the Usage Details on the Internet.

The string having parameter corresponds to the character string after the SQL statement having and must be used together with the aggregate function.

The string orderby parameter corresponds to the string following the SQL statement order.

The limit parameter indicates the number of returned rows.

Let's take the following example. Suppose there is a data table named "orders ":

ID	Customername	Orderprice	Country	Orderdate
1	Arc	100	China	2010/1/2
2	Bor	200	USA	2010/3/20
3	Cut	500	Japan	2010/2/20
4	Bor	300	USA	2010/3/2
5	Arc	600	China	2010/3/25
6	Doom	200	China	2010/3/26

Suppose we want to query the total number of orders of a customer that is more than 500 yuan, and the name and total number of orders of county customers in China are sorted by customername. By default, ASC is sorted, the SQL statement should be:

SQL code

1. SelectCustomername, sum (orderprice)FromOrdersWhereCountry =?
2. Group ByCustomername
3. HavingSum (orderprice)> 500
4. Order ByCustomername

The parameters of the query function corresponding to Android are as follows:

Java code

1. String table = "orders ";
2. String [] Columns =NewString [] {"customername", "sum (orderprice )"};
3. String selection = "Country =? ";
4. String [] selectionargs =NewString [] {"China "};
5. String groupby = "customername ";
6. String having = "sum (orderprices)> 500 ";
7. String orderby = "customername ";
8. Cursor c = dB. Query (table, columns, selection, selectionargs, groupby, having, orderby,Null);

The query result should be:

Customername	Sum (orderprice)
Arc	700

OK. Through the above example, you should know how to use SQL in Android. Then I will introduce the related classes and methods in Android in more detail.