Use LitePal’s aggregation functions

preface

In the last article, we covered all the uses of LitePal query operations, and it’s clear that LitePal provides 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 special query, is the aggregation function query, it is not like the traditional query is to query the data of some columns in the table, but the query results are aggregated and counted, and finally the statistical results are returned. Therefore, aggregate functions such as count() and sum() are provided in any relational database.

As you might expect, these aggregate functions are encapsulated in LitePal, making our operations much easier. So, today we’re going to look at the use of aggregation functions in LitePal, and those of you who haven’t read the previous article suggest that you go to Android Database Master Tips (7) — experience the query art of LitePal.

LitePal’s project address is: github.com/LitePalFram…

Traditional aggregate function usage

Although it is an aggregate function, it is used in much the same way as a traditional query, using the select statement. Instead of specifying column names in a SELECT statement, we usually pass the names of the columns to be counted into the aggregate function, so the select statement is executed using the rawQuery() method of SQLiteDatabase. Let’s try it out. For example, if we want to find out how many rows there are in the news table, we can write:

SQLiteDatabase db = dbHelper.getWritableDatabase(); Cursor c = db.rawQuery("select count(1) from news", null); if (c ! = null && c.moveToFirst()) { Log.d("TAG", "result is " + count);Copy the code

As you can see, in the rawQuery() method we specify an aggregate query, where count(1) is used to count the total number of rows. You don’t have to use count(1), you can use count(*) or count(primary key). Then the rawQuery() method returns a Cursor object. We retrieve the first row and first column of the Cursor. This is the result of the statistic.

What if we wanted to count the total number of comments in the news table? The code looks like this:

SQLiteDatabase db = dbHelper.getWritableDatabase(); Cursor c = db.rawQuery("select sum(commentcount) from news", null); if (c ! = null && c.moveToFirst()) { Log.d("TAG", "result is " + count);Copy the code

We find that the code is basically similar, except that the count() function is replaced by the sum() function in the query statement. And of course, the sum() function requires that we pass in a column name, which means that we’re going to summarize the total of that column, so here we pass in commentcount.

The use of other aggregate functions is similar, so I won’t list them all. From this we can conclude that aggregate functions are used to perform SQL queries using the rawQuery() method, and the results are encapsulated into Cursor objects, which we then retrieve from Cursor. While you might think this is simple enough because you’ve only written six or seven lines of code, have you ever thought of something even simpler, such as a single line of code to perform an aggregate query? You read that right, one line of code, LitePal makes all of this possible, so let’s take a look at the aggregate functions in LitePal.

Use LitePal’s aggregate functions

LitePal provides five aggregation functions: count(), sum(), average(), Max (), and min(). It covers the most common aggregation functions in SQL statements. Let’s learn how to use these five aggregation functions one by one.

count()

The count() method is used to count the number of rows in the news table. We have shown how to count the number of rows in the news table using SQL statements. Let’s see how to do the same with LitePal.

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

Copy the code

You read that right! That’s one line of code. Call the count() method of the DataSupport Class. The count() method takes a Class parameter that specifies which table to count, and returns an integer, the result of the count.

In addition, all aggregation functions in LitePal are concatenated, which means we can add conditional statements to the statistics. For example, if you want to count the total number of news stories with zero comments, you could write:

int result = DataSupport.where("commentcount = ?" , "0").count(News.class);Copy the code

This usage is similar to the concatenation query we learned in the previous article, where a DataSupport class first specifies a WHERE statement for a condition, and then concatenates a count() method so that the results of the conditional statement are counted. Concatenation applies not only to the count() method, but also to all of the methods we’ll cover below, but because the usage is the same, I won’t repeat it.

sum()

The count() method is pretty simple, but so are the rest of the aggregate functions, so let’s continue.

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);

Copy the code

Sum () takes a few more arguments, so let’s look at them all. The first argument is simple, again the Class passed in, to specify which table to count. The second parameter is the column name, indicating which column we want to evaluate the data from. The third argument specifies the type of the result. In this case, we specify an int, so the return result is an int.

Note that the sum() method only evaluates columns that can be evaluated, such as integer columns or floating point columns. If you pass in a string column to evaluate the sum() method, it will return a 0.

average()

The average() method is mainly used to calculate the average. For example, if we want to calculate the average number of comments per news item in the news table, we can write:

double result = DataSupport.average(News.class, "commentcount");

Copy the code

The average() method takes two arguments, the first of which, needless to say, is Class. The second parameter, which specifies the column name, indicates which column we want to average. It is important to note that the return value is of type double, since averages are almost always decimal, and double allows the program to maximize the precision of the decimal place.

Similarly, the average() method averages only the columns that can be computed. If you pass in a string column, it won’t yield any results. It will return a 0 as the result.

max()

The Max () method is used to determine the maximum number of comments in a column. For example, if we want to know the highest number of comments in the news table, we can write:

int result = DataSupport.max(News.class, "commentcount", int.class);

Copy the code

As you can see, the Max () method takes three arguments, the first of which, again, is a Class that specifies which table to count. The second parameter is the column name, indicating which column we want to count the maximum value in. The third parameter is used to specify the type of the result, so you can choose which type to pass in depending on the situation.

Needless to say, the Max () method can only be used to maximize columns that are computable, so keep that in mind when using it.

min()

The min() method is used to find the minimum number of comments in a column. For example, if we want to know the minimum number of comments in the news table, we can write:

int result = DataSupport.min(News.class, "commentcount", int.class);

Copy the code

The min() and Max () methods are used essentially the same way, and the parameters are the same, but the method name has been changed. One is to maximize the value of a column, one is to minimize the value of a column, and that’s it.

Now that we’ve covered all of the aggregate functions in LitePal, how does that feel? After you’ve learned this, you’ll realize that I’m not bragging at the beginning, that there’s really only one line of code to do all the aggregate query operations, and we didn’t write a second line of any of the above statistical operations.

Well, after eight articles of study, we have been LitePal in the most important functions are basically finished learning, I believe you see here from the beginning, but also experienced a zero understanding of LitePal, so far can skillfully use LitePal a process. This is the end of our Android database master tutorial. We won’t be updating the series until LitePal releases a new version with new features. I will continue to share more articles on Android technology in the future, and thank you for staying with this column.