Android Database Expert secret (7)-experience the query art of LitePal

Android Database Expert secret (7)-experience the query art of LitePal

 

After studying many articles, we have mastered most of the content in LitePal. In retrospect, we have all learned the first three operations in addition, deletion, modification, and query. I don't know if you are using the database now. Do you feel extremely easy and simple. However, we all know that, among all database operations, query operations must be the most complex and most frequently used. Therefore, LitePal provides a wide range of query APIs, in addition, LitePal's API design in query is also quite artistic. Today, we will use a special blog to explain how to use the query operation and try the art of LitePal query. If you have not read the previous article, we recommend that you refer to it first.Android Database Expert cheats (6)-LitePal modification and deletion operations.

 

Traditional data query methods

In fact, the most traditional way to query data is to use SQL statements. Android also provides a method to directly use native SQL statements to query database tables, that is, the rawQuery () method in SQLiteDatabase, the method is defined as follows:

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

The rawQuery () method receives two parameters. The first parameter receives an SQL string, and the second parameter replaces the placeholder (?) in the SQL statement (?) String Array. The rawQuery () method returns a Cursor object. All the queried data is enclosed in this object. We only need to retrieve them one by one.

 

Of course, this usage is not very common, because most people do not like to write SQL statements. Therefore, Android provides an encapsulated API that allows you to query data without writing SQL statements, that is, the query () method in SQLiteDatabase. Query () provides three methods for overloading. One of the minimum parameters also has seven parameters. Let's look at the method definition:

public Cursor query(String table, String[] columns, String selection,            String[] selectionArgs, String groupBy, String having,            String orderBy)

The first parameter is the table name, indicating the table from which we want to query data. The second parameter is used to specify which columns to query. If this parameter is not specified, all columns are queried by default. The third and fourth parameters are used to constrain the query of data of a certain row or several rows. If this parameter is not specified, data of all rows is queried by default. The fifth parameter is used to specify the columns to be removed from the group by statement. 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 data after group by. If this parameter is not specified, the data is not filtered. The seventh parameter is used to specify the sorting method of the query results. If this parameter is not specified, the default sorting method is used.

This method is the least overloaded method of the query () method. There are also two methods with eight and nine parameters. Although this method is very commonly used in table queries in the Android database, it may be very laborious to understand this method with a large number of parameters, in addition, it is quite uncomfortable to use it. For example, if we want to query all the data in the news table, we should write it like this:

SQLiteDatabase db = dbHelper.getWritableDatabase();Cursor cursor = db.query(news, null, null, null, null, null, null);

We can see that the first table name parameter is specified as news, and then all the six parameters that follow are not used are specified as null.

What should we do if we want to query news reports with more than zero comments in the news table? The Code is as follows:

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

Because the third and fourth parameters are used to specify constraints, we specify commentcount>? In the third parameter ?, Then replace the placeholder with a String array in the fourth parameter. The result is that all the news tables with comments greater than zero. What about other parameters? It is still unavailable, so null can only be passed.

 

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, therefore, we also need to extract data from the Cursor object one by one and set it to the News object class, as shown below:

List
 
   newsList = new ArrayList
  
   ();if (cursor != null && cursor.moveToFirst()) {do {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 news = new News();news.setId(id);news.setTitle(title);news.setContent(content);news.setPublishDate(publishDate);news.setCommentCount(commentCount);newsList.add(news);} while (cursor.moveToNext());}
  
 

This is probably the usage of the traditional data query method. In general, the usage is really unfriendly, especially the lengthy parameter list of the query () method, even if we do not use those parameters, you must also input multiple null values. In addition, the queried data is only encapsulated into a Cursor object. We also need to extract the data one by one and then set it to the object class object. Trouble? Maybe you don't have to worry about it because you are used to it. However, habits can always change. Maybe when you experience the convenience provided by querying APIs in LitePal, you will have a new view, now let's take a look at the query art of LitePal.

Use LitePal to query data

LitePal provides a wide range of APIS for query, with a variety of functions that can basically meet all our query needs at ordinary times. In addition, LitePal is very careful in the design of the query API. Instead of the tedious parameter list in the native query () method, LitePal uses a more clever method-concatenation query. In addition, the LitePal query result does not return the Cursor object, and then the developer will retrieve the result one by one, but directly return the encapsulated object. All these changes make Data Query easier and more reasonable. Next we will take a complete look at all the usage of data query in LitePal.

Simple Query

For example, we want to implement the simplest function. to query the record with id 1 in the news table, use LitePal to write it like this:

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

Oh, my God! Is it too easy? With only one line of code, you can check the records with id 1 in the news table, and the results are automatically encapsulated into the News object. You do not need to manually Parse them from the Cursor. If you use a native SQL statement or the query () method, at least 20 lines of code are required to complete the same function!

Let's calm down and analyze the find () method. As you can see, its parameter list is also relatively simple. It only receives two parameters. The first parameter is a generic class, that is, the class we specify here and the returned object is a class, so here we pass in News. class, then the returned object is News. The second parameter is simpler. It is an id value. If you want to query a record whose id is 1, 1 is passed. If you want to query a record whose id is 2, 2 is passed, and so on.

This is a complicated function. It becomes so simple after LitePal! So you may be eager to learn more query usage in LitePal. Don't worry. Let's look at it one by one.

You may have encountered the following scenarios. In some cases, you need to retrieve the first data in the table. What is the traditional approach? Specify a limit value in the SQL statement and obtain the first record of the returned result. However, LitePal does not need to be so troublesome. For example, if we want to obtain the first data in the news table, we only need to write it like this:

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

OK. The semantics is very strong. You just need to call the findFirst () method and input the News class to obtain the first data in the news table.

 

Let's take a look at three. If we want to get the last piece of data in the News table, how should we write it? It is also simple, as shown below:

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

Because it is common to obtain the first or last data in a table, LitePal provides these two methods to facilitate our operations.

 

So here we can see that all of them are currently only the function of querying a single piece of data. What should we do if we want to query multiple pieces of data? For example, we want to check all the data with IDs of 1, 3, 5, and 7 in the news table. How can we write this? Some friends may be smart. they immediately think that they can perform queries one by one and call the find () method four times, then, you can simply upload the IDs 1, 3, 5, and 7 to them. That's right. This is all done, and the efficiency is not low, but LitePal provides us with a simpler method-findAll (). The usage of this method is very similar to that of the find () method, except that it can specify multiple IDs and the return value is no longer a generic class object, but a generic class set, as follows:

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

We can see that the findAll () method is called first, and the first parameter of this method is still the specified generic class, but the following parameters are quite random, you can input any id. The findAll () method will find all the data corresponding to all the input IDs and return them to the List together. In this generic set.

 

Although the syntax design is quite user-friendly, it may not be applicable in some scenarios, because the multiple IDs you want to query may have been encapsulated into an array. The findAll () method also receives array parameters, so you can write the same function as follows:

long[] ids = new long[] { 1, 3, 5, 7 };List
  
    newsList = DataSupport.findAll(News.class, ids);
  

Some of my friends may be surprised to see that the findAll () method should not be used to query all the data? How can I always query the data corresponding to several IDs? Ha! This is a good question, because the findAll () method can also query all the data, and it is easier to query all the data, just write as follows:

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

No. We only need to remove all the following parameters. If no specific id is specified, all the data in the news table is queried by the findAll () method, is it very semantic?

 

In addition, you should not think that these are just several methods of findAll (). In fact, our usage just now is to call the same findAll () method! One method can achieve a variety of different query results, and the semantics is also very strong, so that people can understand at a glance, this is the art of LitePal query!

Concatenation Query

Of course, the query functions provided by LitePal are far more than that. I believe you have also discovered that our current query functions are based on IDs and cannot specify query conditions at will. How can we specify the query conditions? Let's look back at how to do it in the traditional situation. The query () method receives seven parameters. The third and fourth parameters are used to specify the query conditions, then you can set the other parameters to null. However, we have already approved this method. Because the lengthy parameter list is too cumbersome, how does LitePal solve this problem? Now let's take a look.

To avoid lengthy parameter lists, LitePal uses a clever solution called concatenation query, which is flexible and allows you to dynamically configure query parameters based on our actual query requirements. Here is a simple example. For example, if we want to query all news reports with comments greater than zero in the news table, we can write them as follows:

List
  
    newsList = DataSupport.where(commentcount > ?, 0).find(News.class);
  

We can see that the where () method of DataSupport is called first, and the query conditions are specified here. The where () method receives any string parameter. The first parameter is used for conditional constraints. Starting from the second parameter, it is used to replace the placeholder in the first parameter. The where () method corresponds to the where section of an SQL statement.

 

Then we directly concatenate a find () method after the where () method, and specify a generic class here to indicate which table to query. The query results of the preceding code are the same as those of the following SQL statement:

select * from users where commentcount > 0;

However, this will query all the columns in the news table. You may not need that much data, but only the data in the title and content columns. This is also very simple. We only need to add another suffix, as shown below:

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

We can see that here we add a select () method, which receives any string parameter, each parameter must correspond to a column name, in this way, only the data of the corresponding column is queried. Therefore, the select () method corresponds to the select part of an SQL statement.

The query results of the preceding code are the same as those of the following SQL statement:

select title,content from users where commentcount > 0;

Great, right? However, we can continue to attach more things. For example, if I want to sort the queried news in descending order of the release time, that is, to put the latest news at the beginning, I can write it like this:

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

The order () method receives a string parameter, which is used to specify the columns in which the query results are sorted. asc indicates positive sorting, and desc indicates reverse sorting. Therefore, order () method corresponds to the order by section in an SQL statement.

 

The query results of the preceding code are the same as those of the following SQL statement:

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

Then, maybe you don't want to query all the matching results at a time, because the data size may be a little too large, but you want to query only the first 10 pieces of data, the Code is as follows:

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

Here we have attached a limit () method, which receives an integer parameter to specify the first few pieces of data to be queried. Here we specify 10, query the first 10 data records in all matching results.

 

The query results of the preceding code are the same as those of the following SQL statement:

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

We just found the first 10 news that match all the conditions. Now I want to display the news by page. When I go to the second page, I will display 11th to 20th news, how can this be achieved? It doesn't matter. With the help of LitePal, these functions are very simple. You only need to concatenate an offset, as shown below:

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

As you can see, here we add another offset () method to specify the offset of the query result. If it is set to 10, it indicates the offset of 10 positions, it turns out to be the first 10 news queries. After the offset is 10, it turns into 11th to 20th news queries. If the offset is 20, that means querying 21st to 30th news records, and so on. Therefore, the limit () and order () Methods correspond to the limit part of an SQL statement.

 

The query results of the preceding code are the same as those of the following SQL statement:

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

This is probably all the usage of the concatenation query in LitePal. See the difference? The benefit of this query is that we can combine various query parameters at will and link them together when necessary. You don't need to specify them when you don't need them. Compare the lengthy parameter list in the query () method. Even if we do not use those parameters, null must be passed. Is it obvious that the query in LitePal is more user-friendly?

 

Aggressive Query

However, in all our usage above, only data in the specified table can be queried, and data in the associated table cannot be found, because the default LitePal mode is lazy query, of course, this is also the recommended query method. If you really want to query the data in the joined table at one time, it is also possible. LitePal also supports the aggressive query method. Let's take a look at it.

I wonder if you have found that each of the find () methods we just learned corresponds to a method overload with the isEager parameter, this parameter can be understood at a Glance. If it is set to true, it indicates a radical query. In this way, the data in the joined table will be queried together.

For example, if we want to query news with the id of 1 in the news table and query the comments corresponding to this news, we can write it like this:

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

It can be seen that there is no complicated usage here, that is, adding a true parameter at the end of the find () method indicates that radical query is used. This will also check the data in all the tables associated with the news table, so the comment table and the news table are multiple-to-one, so when you use the radical query for a news item, then the comments corresponding to the news will be queried together.

 

The usage of radical queries is very simple, so there are only so many other find () methods that are the same, so we will not repeat them. However, this query method is not recommended for LitePal, because the query speed may be very slow if there is a large amount of data in the associated table. In addition, radical queries can only query the associated table data of the specified table, but they cannot continue to iteratively query the associated table data of the associated table. Therefore, it is recommended that you use the default lazy loading method. As for how to query the data in the joined table, you only need to make a small modification in the model class. Modify the code in the News class as follows:

public class News extends DataSupport{...public List
  
    getComments() {return DataSupport.where(news_id = ?, String.valueOf(id)).find(Comment.class);}}
  

We can see that we have added a getComments () method to the News class, and this method uses a join query internally to find all the comments corresponding to the current News. After this method is changed, we can delay the query of associated table data. When we need to get the comments corresponding to the News, we can call the getComments () method of News, then the associated data is queried. This writing method is more efficient and reasonable than radical queries.

 

Native Query

I believe you have learned that LitePal provides a wide range of APIS for query. However, you may always encounter some strange demands. You may not be able to meet these requirements using the query API provided by LitePal. It doesn't matter, because even if LitePal is used, you can still use the native query method (SQL statement) to query data. The DataSuppport class also provides a findBySQL () method, which can be used to query data through Native SQL statements, as shown below:

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

The findBySQL () method receives any string parameter. The first parameter is an SQL statement. The subsequent parameters are used to replace the placeholder in the SQL statement, which is easy to use. In addition, the findBySQL () method returns a Cursor object, which is the same as the result returned by native SQL statements.

 

Now, we have learned all the data query methods provided in LitePal, so today's article is here, and the next article will begin to explain the usage of Aggregate functions.

 

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