preface

After studying several articles, we have mastered most of the content in LitePal. Now in retrospect, we have learned the first three of the four operations, do not know now to use the database, you have not felt particularly easy and simple.

However, we all know that queries are the most complex and most used of all database operations, so LitePal provides a rich API for queries, and LitePal’s API design for queries is quite artistic. So today we’re going to dedicate a blog post to the use of query operations and experience the art of LitePal query. For those who haven’t read the previous article, it is recommended to refer to the Android Database master’s secret book (six) — Modify and delete operations of LitePal.

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

The traditional way of querying data

The rawQuery() method of SQLiteDatabase can be used to query the database table. The rawQuery() method of SQLiteDatabase can be used to query the database table.

public Cursor rawQuery(String sql, String[] selectionArgs)

Copy the code

The rawQuery() method takes two arguments. The first argument receives an SQL string, and the second argument replaces the placeholder (?) in the SQL statement. Array of strings. The rawQuery() method returns a Cursor object in which all data is contained. All we need to do is fetch one Cursor at a time.

Of course, this usage is not very common, because most people don’t like writing SQL statements. So, Android specifically provides a wrapped API that allows you to query data without having to write an SQL statement: the query() method in SQLiteDatabase. Query () provides three method overloads, with the smallest number of arguments taking seven. Let’s look at the method definition:

public Cursor query(String table, String[] columns, String selection,

            String[] selectionArgs, String groupBy, String having,
Copy the code

The first parameter is the table name, which indicates the table from which we want to query the data. The second parameter specifies which columns to query. If you do not specify, all columns are queried by default. The third and fourth parameters are used to query the data of a row or several rows. If not specified, the data of all rows is queried by default. The fifth parameter specifies the column to be deleted from the group by column. If this parameter is not specified, the group by operation is not performed on the query results. The sixth parameter is used to further filter the data after group by. If it is not specified, no filtering is performed. The seventh parameter specifies the sorting mode of the query results. If it is not specified, the default sorting mode is used.

This method is the least overloaded method of the Query () method. There are two other overloaded methods with eight and nine parameters, respectively. Although this method is commonly used for querying tables in An Android database, it can be a bit of a struggle to understand and a bit of a pain to use because of the number of parameters. For example, if we want to query all data in the news table, we should write:

SQLiteDatabase db = dbHelper.getWritableDatabase();

Cursor cursor = db.query("news", null, null, null, null, null, null);
Copy the code

As you can see, the first table name argument is specified as news, and then the next six arguments, which we don’t use, are all specified as NULL.

What if we want to query all the stories in the news table with more than zero comments? The code looks like this:

SQLiteDatabase db = dbHelper.getWritableDatabase(); Cursor cursor = db.query("news", null, "commentcount>?" , new String[]{"0"}, null, null, null);Copy the code

And since the third and fourth arguments are used to specify the constraint, in the third argument we say commentCount >? , and then replace the placeholders with an array of strings in the fourth argument. The result is all the stories in the news table that have more than zero comments. What about the other parameters? I still don’t need it, so I can only pass null.

Then we can see that the return value of the query() method is a Cursor object, and all the queried data is encapsulated in this object, so we need to take the data out of the Cursor object one by one and set it to the News entity class, as shown below:

List<News> newsList = new ArrayList<News>(); if (cursor ! = null && cursor.moveToFirst()) { int id = cursor.getInt(cursor.getColumnIndex("id")); String title = cursor.getString(cursor.getColumnIndex("title")); String content = cursor.getString(cursor.getColumnIndex("content")); Date publishDate = new Date(cursor.getLong(cursor.getColumnIndex("publishdate"))); int commentCount = cursor.getInt(cursor.getColumnIndex("commentcount")); news.setContent(content); news.setPublishDate(publishDate); news.setCommentCount(commentCount); } while (cursor.moveToNext());Copy the code

This is probably the traditional way of querying data, but in general, the usage is really unfriendly, especially with the long argument list of the query() method, which we have to pass many null’s even if we don’t use those arguments. In addition, the query data is encapsulated in a Cursor object, and we need to retrieve the data one by one and then set it to the entity class object. Trouble? Maybe you don’t bother because you’re used to it. But habits can always be changed. Maybe you will have a new perspective after you experience the convenience of LitePal’s query API, so let’s take a look at LitePal’s query art.

Use LitePal to query data

LitePal provides a very rich API for queries, with a variety of functions, which can basically meet all of our usual query needs. Not only that, but LitePal has taken great care in designing the query API, getting rid of the tedious parameter lists of the native Query () method in favor of a more clever approach of concatenating queries. In addition, the result of a LitePal query does not return Cursor objects, which the developer can retrieve individually. Instead, it returns the wrapped objects. All of these changes make it easier and more reasonable to query data, so let’s take a complete look at all the uses of querying data in LitePal.

A simple query

For example, if we want to implement a simple function, we can query the entry (id: 1) in the news table by using LitePal:

News news = DataSupport.find(News.class, 1);

Copy the code

My god! Does it feel too easy? In a single line of code, the result is automatically encapsulated in the news object and does not need to be resolved by manual parsing of the Cursor. If written in native SQL, or in the query() method, it would take at least 20 lines of code to do the same thing!

Let’s calm down and analyze the find() method. As you can see, its argument list is relatively simple. It only takes two arguments, and the first argument is a generic class, which means that if we specify a class, the object we return is the class, so we pass news.class, and the object we return is News. The second parameter is even simpler. It is an ID value. If you want to query a record with ID 1, pass 1, if you want to query a record with ID 2, pass 2, and so on.

What was a fairly complex feature has become so simple with LitePal! So you can’t wait to learn more about LitePal’s query usage, so let’s take a look one by one.

You may have encountered the following scenarios. In some cases, you need to pull out the first data in the table. What is the traditional approach? Specify a limit value in the SQL statement and get the first record returned as a result. But in LitePal, we don’t have to do that. For example, if we want to retrieve the first data in the news table, we just need to write:

News firstNews = DataSupport.findFirst(News.class);

Copy the code

Call the findFirst() method, pass in the News class, and you’ll get the first data in the News table.

If you want to get the last piece of data in the News table, what should you write? Again, it’s as simple as this:

News lastNews = DataSupport.findLast(News.class);

Copy the code

Because it is common to get the first or last piece of data in a table, LitePal has purposely provided these two methods to facilitate this.

So we see here, currently only query a single data function, if you want to query multiple data how to do? Select * from ‘news’ where id =’ 1 ‘, ‘3’, ‘5’ and ‘7’; You can call find() four times, and then pass in id 1, id 3, ID 5, id 7. Yes, this is perfectly possible and not inefficient, but LitePal gives us an easier way to do it — findAll(). The use of this method is very similar to the find() method, except that it can specify multiple ids, and the return value is not a generic class object, but a collection of generic classes, as shown below:

List<News> newsList = DataSupport.findAll(News.class, 1, 3, 5, 7);

Copy the code

As you can see, first we call the findAll() method, and then the first argument to the method is still the generic class, but the arguments are arbitrary. You can pass in any id, and findAll() will look up all the data for all the ids passed in. And then return to the List of generics.

While this syntax design is very user-friendly, it may not be applicable in some scenarios where you may need to query for multiple ids that are already encapsulated in an array. The findAll() method also takes an array argument, so you can do the same thing:

long[] ids = new long[] { 1, 3, 5, 7 };

List<News> newsList = DataSupport.findAll(News.class, ids);
Copy the code

The findAll() method is used to query all data. Why do you always query the data corresponding to several ids? Ha! Ha! That’s a good question, because the findAll() method can also query all the data, and it’s easier to write all the data as follows:

List<News> allNews = DataSupport.findAll(News.class);

Copy the code

FindAll () ¶ findAll() ¶ findAll() ¶ findAll(); findAll(); findAll();

And don’t think that these are just overloads of the findAll() method. In fact, these are all calls to the same findAll() method! One method can achieve a variety of different query effects, and the semantics are strong, let people understand at a glance, this is LitePal query art!

Query:

Of course, LitePal provides us with more query capabilities than that. I believe you have now found that our current query function is based on the ID to query, and can not be arbitrarily specified query conditions. So how do you specify query criteria? To recall the traditional case, the query() method takes seven arguments, the third and fourth of which are used to specify the query conditions, and nulls the remaining arguments. However, we’ve already criticized this approach for being too cumbersome with long parameter lists, so how does LitePal solve this problem? We’re going to learn that now.

To avoid lengthy parameter lists, LitePal uses a clever solution called concatenated queries, which are flexible enough to dynamically configure query parameters based on our actual query needs. For example, if we want to query all news items in the news table where the number of comments is greater than zero, we can write:

List<News> newsList = DataSupport.where("commentcount > ?" , "0").find(News.class);Copy the code

As you can see, the DataSupport where() method is called first, where the query criteria are specified. The where() method takes any string argument, where the first argument is used for the constraint, and from the second argument onwards, is used to replace the placeholder in the first argument. The WHERE () method corresponds to the WHERE part of an SQL statement.

We then attach a find() method directly after the WHERE () method, and specify a generic class here to indicate which table to query for. The result of the query is the same as the following SQL statement:

select * from users where commentcount > 0;

Copy the code

But this will query all the columns in the news table, so maybe you don’t need that much data, but just the title and content columns. So it’s easy, we just need to add one more connection, like this:

List<News> newsList = DataSupport.select("title", "content") .where("commentcount > ?" , "0").find(News.class);Copy the code

As you can see, we have added a select() method. This method takes any string argument, each of which requires a column name. This will only query the data for that column, so select() corresponds to the select part of an SQL statement.

The result of the query is the same as the following SQL statement:

select title,content from users where commentcount > 0;

Copy the code

Isn’t that fun? But that’s not the end of it. There’s a lot more we can add. For example, if I want to put the news in reverse chronological order, with the latest news at the top, I could write:

List<News> newsList = DataSupport.select("title", "content") .where("commentcount > ?" , "0") .order("publishdate desc").find(News.class);Copy the code

The order() method takes a string argument that specifies the column by which to sort the results of the query. Asc means to sort the results in positive order, and desc means to sort the results in reverse order. Therefore, the order() method corresponds to the order by part of an SQL statement.

The result of the query is the same as the following SQL statement:

select title,content from users where commentcount > 0 order by publishdate desc;

Copy the code

Then, maybe you don’t want to query all the results of the condition match at once, because that might be a bit too large, but you want to query only the first 10 data, then you can easily solve this problem using concatenation, as follows:

List<News> newsList = DataSupport.select("title", "content") .where("commentcount > ?" , "0") .order("publishdate desc").limit(10).find(News.class);Copy the code

Here we add a limit() method, which takes an integer parameter that specifies the first few items of data to be queried. In this case, the value is 10, meaning that the first 10 items of all matches are queried.

The result of the query is the same as the following SQL statement:

select title,content from users where commentcount > 0 order by publishdate desc limit 10;

Copy the code

So what we’ve just found is the top 10 stories that match all the criteria, so now I want to page out the stories, and when I turn to the second page, I want to show stories 11 through 20. How do I do that? It doesn’t matter, with The help of LitePal, these functions are quite simple, and all you need to do is concatenate an offset, like this:

List<News> newsList = DataSupport.select("title", "content") .where("commentcount > ?" , "0") .order("publishdate desc").limit(10).offset(10)Copy the code

As you can see, we have added an offset() method to the query result, which specifies the offset() of the query result. This method specifies the offset() of the query result. This method specifies the offset() of the query result, which specifies the offset() of the query result. That means looking for articles 21 through 30, and so on. Thus, the limit() and offset() methods together correspond to the LIMIT section of an SQL statement.

The result of the query is the same as the following SQL statement:

Select title,content from users where commentcount > 0 order by publishdate desc limit 10,10;Copy the code

That’s about all the use of concatenation queries in LitePal. See the difference? The advantage of this type of query is that you can arbitrarily combine various query parameters, concisely concaming them when you need them and not specifying them when you don’t need them. Compare that to the long list of arguments in the query() method, where we have to pass null even if we don’t need those arguments.

Radical query

However, in all of our usage, we can only query the data in the specified table. The data in the associated table is not available, because LitePal’s default mode is lazy query, and this is the recommended method. So, if you really want to query all the associated tables at once, you can, of course, LitePal also supports aggressive queries, so let’s take a look at that.

For each type of find(), there is a method overload that takes the isEager parameter, which is set to true. This parameter is used to query the data in the associated table together.

Select * from ‘news’ where id =’ 1 ‘; select * from ‘news’ where id =’ 1 ‘;

News news = DataSupport.find(News.class, 1, true);

List<Comment> commentList = news.getCommentList();
Copy the code

As you can see, there’s no tricky use here, except to add a true argument at the end of the find() method to indicate aggressive queries. In this case, the data in all the tables associated with the news table will be searched out together. In this case, the comment table and the news table are many-to-one associated. Therefore, when a radical query is used for a news, the corresponding comments of the news will be queried together.

The use of radical queries is very simple, and that’s it. The other find() methods are the same, so I won’t repeat them. However, LitePal does not recommend this type of query because it can be very slow if there is a lot of data in the associated table. In addition, radical query can only query the associated table data of the specified table, but it cannot continue to iteratively query the associated table data of the associated table. For this reason, I recommend that you use the default lazy load and make a small change in the model class to query the data in the associated tables. Modify the code in the News class to look like this:

public class News extends DataSupport{ public List<Comment> getComments() { return DataSupport.where("news_id = ?" , String.valueOf(id)).find(Comment.class);Copy the code

As you can see, we added a getComments() method to the News class, and the inner part of the method is to use a concatenated query to find all the comments for the current News item. In this way, we can defer the query of the associated table data until we call the getComments() method of the News to query the associated data when we need to retrieve the comments corresponding to the News. This is a much more efficient and reasonable way to write than aggressive queries.

Native queries

As you can see, LitePal has a rich API for queries. However, there may always be some bizarre requirements that you may not be able to fulfill using the query API provided by LitePal. That’s ok, because even with LitePal, you can still query the data using a native query (SQL statement). The DataSuppport class also provides a findBySQL() method that allows you to query data using native SQL statements like this:

Cursor cursor = DataSupport.findBySQL("select * from news where commentcount>?" , "0");Copy the code

The findBySQL() method takes any string argument, the first of which is the SQL statement, and the subsequent arguments are used to replace placeholders in the SQL statement. The usage is very simple. In addition, the findBySQL() method returns a Cursor object, which is the same as native SQL usage.

Ok, so we have learned all the methods provided in LitePal to query data, so today’s article is finished, the next article will begin to explain the use of aggregate functions, interested friends please read on.