Content Provider-based SQL

Keywords: content provider, SQL, SQLite
This is the second article in the content provider series. It is a sin for me to update my blog due to busy transactions (for some reason) recently. If you don't talk nonsense, go to the topic.
Because the implementation of content provider is mostly implemented through the database method, you must first understand the database language SQL before figuring out CP. The database management system used on the Android platform is SQLite, which is well-known and used by many embedded systems. Interested friends can go to their official website to see, there is a lot of useful information: http://www.sqlite.org/docs.html.
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 Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

 

The basic SQL format is as follows:

SQL code
Select column name from Table Name

The most basic SQL statement for Select column name from table name 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
Public cursor query (string table, string [] columns, string selection, string [] selectionargs,
String groupby, string having, string orderby, string limit)

Public cursor query (string table, string [] columns, string selection, string [] selectionargs,
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
Select firstname, lastname from employees

Select firstname, lastname from employees 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
Select * from employees where city = 'beijing'

Select * from employees where city = 'beijing' here the string followed by where is the string selection parameter corresponding to the 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
String selection = "city =? ";

String selection = "city =? "; In this case, we must assign a value in selectionargs.

Java code
String [] selectionargs = {"Beijing "};

String [] selectionargs = {"Beijing"}; that is, 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
Select customername, sum (orderprice) from orders where country =?
Group by customername
Having sum (orderprice)> 500
Order by customername

Select customername, sum (orderprice) from orders where country =?
Group by customername
Having sum (orderprice)> 500
Order by customername
The parameters of the query function corresponding to Android are as follows:

Java code
String table = "orders ";
String [] Columns = new string [] {"customername", "sum (orderprice )"};
String selection = "Country =? ";
String [] selectionargs = new string [] {"China "};
String groupby = "customername ";
String having = "sum (orderprices)> 500 ";
String orderby = "customername ";
Cursor c = dB. Query (table, columns, selection, selectionargs, groupby, having, orderby, null );

String table = "orders ";
String [] Columns = new string [] {"customername", "sum (orderprice )"};
String selection = "Country =? ";
String [] selectionargs = new string [] {"China "};
String groupby = "customername ";
String having = "sum (orderprices)> 500 ";
String orderby = "customername ";
Cursor c = dB. Query (table, columns, selection, selectionargs, groupby, having, orderby, null); the query result should be:

Customername sum (orderprice)
Arc2 700

 

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