Android Database Expert secret (8) -- use the aggregate function of LitePal, androidlitepal

Android Database Expert secret (8) -- use the aggregate function of LitePal, androidlitepal

Reprinted please indicate the 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. Obviously, LitePal provides us with a very powerful query API, this makes it extremely easy to complete various types of queries. However, in SQL statements, there is a special query, that is, aggregate function query, which does not query the data of certain columns in the table like traditional queries, instead, the query results are aggregated and statistical, and the statistical results are finally returned. Therefore, any relational database provides Aggregate functions such as count () and sum. As expected, LitePal encapsulates these Aggregate functions, making our operations easier. So today, let's take a look at the usage of Aggregate functions in LitePal. If you have not read any of the previous articles, we recommend that you refer to them first.Android Database Expert secret (7)-experience the query art of LitePal.

The Project address of LitePal is:Https://github.com/LitePalFramework/LitePal

Traditional aggregate function usage

Although it is an aggregate function, its usage is similar to that of 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 pass the column name to be counted into the aggregate function. Therefore, the rawQuery () method in SQLiteDatabase is used to execute the select statement. Next, let's try, for example, to count the total number of rows in the news table, you can write as follows:

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

We can see that in the rawQuery () method, we specify an aggregate query statement, where count (1) is used to calculate the total number of rows. Of course, you do not need to use count (1) Here. You can use count (*) or count (primary key. Then the rawQuery () method returns a Cursor object. we extract the data in the first column of the First row from this Cursor, which is the result of statistics.

How can we calculate the total number of comments in the news table? The Code is as follows:

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 is basically very similar, except that the count () function in the query statement is replaced with the sum () function. Of course, the sum () function requires the input of a specified column name, indicating that we want to summarize the total sum of this column, so here we pass in the commentcount column.

The usage of other Aggregate functions is similar. Therefore, we can conclude that aggregate functions all use the rawQuery () method for SQL queries, and then the results are encapsulated into the Cursor object. Then we can retrieve the results from the Cursor. Although you may think that the above usage is simple enough, because only six or seven lines of code are written in total, have you ever thought about simpler writing, for example, you can use only one line of code to complete the aggregation query operation. You are not mistaken. It is just a line of code. LitePal makes this possible. Let's take a look at the usage of Aggregate functions in LitePal.

Aggregate functions using LitePal

LitePal provides the count (), sum (), average (), max (), and min () Aggregate functions, basically, the most common Aggregate functions in SQL statements are covered. Next we will learn the usage of these five Aggregate functions one by one.

Count ()

The count () method is mainly used to count the number of rows. I just demonstrated how to use SQL statements to count the total number of rows in the news table, the following code shows how to implement the same function through LitePal:

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

You are not mistaken! That is, a line of code is enough. Call the count () method in the DataSupport Class. The count () method receives a Class parameter, which is used to specify the table for statistics and the return value is an integer data, that is, the statistical result.

In addition, all Aggregate functions in LitePal support concatenation, which means we can add conditional statements during statistics. For example, if you want to count the total number of news items that are not commented on, you can write them as follows:

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

This usage is similar to the concatenation query we learned in the previous article. In the DataSupport class, first specify a where statement for conditional constraints, and then concatenate a count () method, in this way, the results of the statements that meet the conditions are obtained. Concatenation not only applies to the count () method, but also applies to all the methods we will introduce below. However, since the usage is the same, we will not repeat it later.

Sum ()

After reading the count () method, it should be very simple. The remaining Aggregate functions are also simple. Let's continue to learn.

The sum () method is mainly used to sum the results. For example, if we want to count the total number of comments in the news table, we can write it like this:

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

The parameters of the sum () method are a little more. Let's take a look at them one by one. The first parameter is very simple. It is also the passed-in Class used to specify which table to calculate the data. The second parameter is the column name, indicating which column we want to merge the data. The third parameter is used to specify the result type. Here we specify it as int type, so the returned result is also int type.

Note that the sum () method can only combine columns with operational capabilities, such as integer or floating-point columns, no result is returned. At this time, only 0 is returned as the result.

Average ()

The average () method is mainly used to calculate the average. For example, if we want to calculate the average comment of each news record in the news table, we can write it like this:

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

The average () method receives two parameters. The first parameter, needless to say, is still a Class. The second parameter is used to specify the column name, indicating the average number of the column we want to calculate. It should be noted that the return value type here is double, because the average will basically carry decimal places, and the double type can be used to retain the precision of decimal places by the maximum program.

Similarly, the average () method can only calculate the average value of Columns with computing power. If you input a string column, you cannot get any results, at this time, only one 0 is returned as the result.

Max ()

The max () method is mainly used to obtain the maximum value in a column. For example, if you want to know the maximum number of comments in all news in the news table, you can write as follows:

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

We can see that the max () method receives three parameters. The first parameter is also a Class parameter, which is used to specify which table to calculate the data. The second parameter is the column name, indicating the maximum value in the column we want to calculate. The third parameter is used to specify the result type. You can select the type to be passed in based on the actual situation.

Needless to say, the max () method can only calculate the maximum value for columns with computing power. I hope you can keep this in mind when using it.

Min ()

The min () method is mainly used to obtain the smallest value in a column. For example, if we want to know the minimum comment value in all news in the news table, we can write it like this:

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

The usage of the min () method and the max () method are basically the same, and the parameters are the same, but the method name has changed. One is to find the maximum value in a column, and the other is to find the minimum value in a column.

Now we have learned all the usage of Aggregate functions in LitePal. How do you feel very simple? After learning this course, I believe that I was not doing anything at the beginning. Actually, I only need a line of code to complete various aggregate query operations, we did not write the second line of code for any of the above statistical operations.

Well, after eight articles, we have finished learning the most important functions of LitePal. I believe you have never understood LitePal from the beginning, currently, LitePal can be used skillfully. So our tutorial on the secret of the Android database experts will be suspended here, and this series will not be updated in the short term, but will wait until LitePal releases the new version, with the new function, we will continue to explain it. I will continue to share more articles related to Android technology later. Thank you for your continued attention to this column.

LitePal open source project: https://github.com/LitePalFramework/LitePal