Android database Master cheats (eight)--use Litepal's aggregation function

Reprint Please specify source: http://blog.csdn.net/guolin_blog/article/details/40614197

In the previous article, we have learned all the usage of the Litepal query operation, and it is clear that Litepal has provided us with a very powerful query API that makes it extremely easy to complete various types of queries. However, in the SQL statement, there is a kind of query is special, is the aggregate function query, it is not the same as the traditional query is the table of some columns of the data query, but the query results are aggregated and statistics, and finally the results of the statistics returned. As a result, aggregate functions such as count (), sum () are provided in any relational database. Well, as you can tell, these aggregate functions are encapsulated in Litepal, making our operations easier. Therefore, today we will learn about the use of aggregate functions in Litepal, have not read the previous article of the friend suggested first to refer to the Android database master cheats (vii)-Experience the litepal of the query art.

Litepal's project address is:https://github.com/LitePalFramework/LitePal

Traditional usage of aggregate functions

Although it is an aggregation function, its usage is similar to the traditional query, that is, the SELECT statement is still used. However, in the SELECT statement we usually do not specify the column name, but instead of the column names that need to be counted into the aggregate function, then the execution of the SELECT statement using the Rawquery () method in Sqlitedatabase. Let's try this, for example, to count the number of rows in the news table, so you can write:

Sqlitedatabase db = Dbhelper.getwritabledatabase (); Cursor C = Db.rawquery ("SELECT count (1) from news", null); if (c! = null && C.movetofirst ()) {int count = C.getint ( 0); LOG.D ("TAG", "result is" + count);} C.close ();

As you can see, in the Rawquery () method We specify an aggregate query statement, where COUNT (1) is used to count how many rows are in total. Of course, there is no need to use COUNT (1), either count (*) or count (primary key). Then the Rawquery () method returns a Cursor object, where we take the first column of data from the cursor, which is the result of the statistic.

So what if we want to count the total number of comments in the news list? The code looks like this:

Sqlitedatabase db = Dbhelper.getwritabledatabase (); Cursor C = db.rawquery ("Select sum (commentcount) from news", null); if (c! = null && C.movetofirst ()) {int count = C.getint (0); LOG.D ("TAG", "result is" + count);} C.close ();

We found that the code was basically very similar, except that the count () function in the query statement was replaced with the sum () function. Of course, the sum () function requires passing in a specified column name, indicating that we want to summarize the sum of this column, so here we pass the Commentcount column.

The use of other aggregate functions is similar, not listed. From this we can conclude that the aggregation function is to use the Rawquery () method for SQL queries, and then the results are encapsulated in the cursor object, and then we remove the result from the cursor. Although you may think that the above usage is simple enough, because there are only six or seven lines of code in total, but have you ever thought of a simpler writing, such as a single line of code to complete the aggregate query operation. You have not read wrong, is a line of code, Litepal make these all possible, then we will learn the use of aggregate functions in Litepal.

Using aggregate functions of Litepal

The five aggregate functions of count (), sum (), average (), Max (), and Min () are all provided in Litepal, which basically covers several of the most commonly used aggregate functions in SQL statements, so let's take a study of the usage of these five aggregate functions.

Count ()

The count () method is primarily used to count the number of rows, and just shows how to count the number of rows in the news table with an SQL statement, so let's look at how to do the same with Litepal, as shown in the code below:

int result = Datasupport.count (News.class);

You're not mistaken! This is just one line of code. Call the Count () method in the Datasupport class, and the count () method receives a class parameter that specifies the data in the table to be counted, and then the return value is an integer data, which is the result of the statistic.

In addition, all the aggregation functions in Litepal are supported by concatenating, which means we can add conditional statements at the time of statistics. For example, if you want to count how many news articles are 0 reviews, you can write:

int result = Datasupport.where ("Commentcount =?", "0"). Count (News.class);

This usage is more like the concatenating query we learned in the previous article, first specifying a where statement for the conditional constraint in the Datasupport class, and then concatenating a count () method, so that the results of the conditional statement are met. The concatenating applies not only to the count () method, but also to all of the methods we will cover below, but since the usage is the same, we will not repeat the introduction.

SUM ()

After reading the count () method It should be very simple, the rest of the aggregate functions are equally simple, we continue to learn.

The sum () method is mainly used to find the results, for example, if we want to count the total number of comments in the news table, we can write:

int result = Datasupport.sum (News.class, "Commentcount", Int.class);

The sum () method has a slightly more parameter, so let's take a look at it. The first parameter is simple, or the passed-in class, which specifies the data in which table to be counted. The second argument is the column name, which indicates which column of data we want to fit in. The third parameter specifies the type of the result, which we specify as int, so the returned result is also of type int.

It is important to note that the sum () method can only be used to compute columns, such as Integer columns or floating-point columns, if you pass a string-type column to fit, you will not get any results, then only return a 0 as the result.

Average ()

The average () method is mainly used for statistical averages, for example, if we want to count the average number of comments in the news list for each story, you can write:

Double result = Datasupport.average (News.class, "Commentcount");

where the average () method receives two parameters, the first argument, needless to say, is still class. The second parameter specifies the column name, indicating the average of which column we want to count. It is important to note that the type of return value here is double, because the average is basically a decimal, and the double type allows the maximum program to preserve the precision of the decimal place.

Similarly, the average () method can only be averaged over columns that have the ability to compute, and if you pass in a column of a string type, and you cannot get any results, you will return only one 0 as the result.

Max ()

The Max () method is primarily used to find the largest value in a column, such as when we want to know what the highest number of comments is in all the news reports, and you can write:

int result = Datasupport.max (News.class, "Commentcount", Int.class);

As you can see, the Max () method receives three parameters, and the first parameter is also class, which specifies the data in which table to be counted. The second parameter is the column name, which indicates the maximum value in which column we want to count. The third parameter specifies the type of the result and, depending on the actual situation, chooses which type to pass in.

So needless to say, the max () method can only be used to calculate the maximum value of the column, I hope you can remember this when using.

Min ()

The min () method is primarily used to find the smallest number of values in a column, such as when we want to know what the minimum number of comments is in all the news reports, and you can write:

int result = Datasupport.min (News.class, "Commentcount", Int.class);

The use of the Min () method and the Max () method are basically identical, the parameters are exactly the same, but the method name is changed a bit. One is to find the maximum value in a column, and the other is to find the smallest value in a column, that's all.

Now that we've learned all of the aggregation functions in Litepal, how is it that it feels very simple? After learning to believe that everyone also realized that I was not at the beginning of the story is not bragging, and really only need a line of code to complete a variety of aggregate query operations, any of the above statistical operations we have not written to the second line of code.

Well, after eight articles of study, we have litepal the most important function of the basic learning is finished, I believe you see here from the beginning, but also experienced a litepal 0 know, to the present can skillfully use the litepal process. Then our Android database Master cheats tutorial will be suspended here, this series will not be updated in the short term, but wait until Litepal released a new version, with the new features will not continue to explain it. I will still continue to share more of the other articles related to Android technology, and thank you for your continued attention to this column.

Litepal Open Source project address: Https://github.com/LitePalFramework/LitePal

Android database Master cheats (eight)--use Litepal's aggregation function