After several articles, we have mastered most of the content in LitePal. Now in retrospect, we have learned the first three of the four operations of add, delete, change and check. I do not know whether you feel particularly easy and simple to use the database now. However, as we all know, in all database operations, the query operation is certainly the most complex, and the use of the most, so LitePal in the query aspect of the API is relatively rich, and LitePal in the query aspect of the API design is quite artistic. So today we are going to use a blog post to explain the use of query operations and experience the art of LitePal query. Has not read the previous article friends suggested to refer to the Android database master secrets (six) – LitePal modify and delete operations.

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

Traditional way to query data

SQLiteDatabase rawQuery(); SQLiteDatabase rawQuery();

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

Copy the code

The rawQuery() method takes two arguments. The first argument takes an SQL string and the second argument replaces the placeholder (?) in the SQL statement. An array of strings. The rawQuery() method returns a Cursor object inside which all queried data is enclosed, and we simply retrieve it.

Of course, this usage is not very common, because most people still don’t like writing SQL statements. So Android specifically provides a packaged API that lets you query data without writing AN SQL statement: the Query () method in SQLiteDatabase. Query () provides three method overloads, the least of which has seven arguments. Let’s look at the method definition:

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

The first parameter is the table name, which indicates the table from which we want to query data. The second parameter specifies which columns to query, or by default, all columns. The third and fourth parameters are used to restrict the query for a row or rows. If this parameter is not specified, the query defaults to all rows. The fifth parameter is used to specify the columns that need to be group by. If this parameter is not specified, the query result will not be group by. The sixth parameter is used to further filter the data after group by. If this parameter is not specified, no filtering is performed. The seventh parameter specifies 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 of the query() methods, along with two other method overloads of eight and nine parameters. Although this method is very common in Android database table queries, the heavy parameters make it difficult to understand this method, and it can be quite unpleasant to use. For example, if we wanted to query all data in the news table, we would 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 parameter is specified as news, and the next six parameters are not needed, so we specify null.

If we want to query all news items in the news table, the number of comments is greater than zero. 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

Since the third and fourth arguments are used to specify constraints, we say commentcount>? In the third argument. In the fourth argument, replace the placeholders with a String array. The result is all news items in the news table whose comment count is greater than zero. What about the other parameters? It still doesn’t work, so it’s still null.

Query () returns a Cursor, encapsulating all query data within the Cursor, and then setting the data to the News entity class, as shown below:

List<News> newsList = new ArrayList<News>(); 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()); }Copy the code

This is probably how the traditional way of querying data is used, but in general it is pretty unfriendly, especially with the long argument list of the query() method, which requires many NULls to be passed in even if we don’t use those arguments. In addition, the query data is only encapsulated in a Cursor object, we also need to take out the data one by one and then set into the entity class object. Trouble? Maybe you don’t bother, because you’re used to it. But habits can change, and you’ll get a better idea of what LitePal’s query API can do for you. Let’s take a look at LitePal’s query art.

Query data using LitePal

LitePal provides a very rich API for querying, with a wide variety of functions, which can basically meet all our normal query needs. LitePal also takes care to design its query API, ditching the verbose argument lists of the native Query () method in favor of a more slick approach called concatenated queries. In addition, LitePal queries do not return Cursor objects that the developer can retrieve one by one. Instead, the results of LitePal queries return the wrapped objects. These changes make querying data easier and more reasonable, so let’s take a look at all the uses of querying data in LitePal.

A simple query

For example, if we want to implement the simplest function, we can use LitePal to query the record with id 1 in the news table:

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

My god! Do you feel too relaxed? It only takes one line of code to find the record with ID 1 in the NEWS table, and the result is automatically encapsulated in the news object, and we do not need to manually parse from the Cursor. If you were writing in a native SQL statement, or the query() method, it would take at least 20 lines of code to do the same thing!

Let’s take a moment to analyze the find() method. As you can see, the parameter list is relatively simple. It only takes two arguments. The first argument is a generic class, which means that the object returned is the same as the specified class. The second parameter is simpler, and is an ID value. If you want to query a record with ID 1, you pass 1, if you want to query a record with ID 2, pass 2, and so on.

With LitePal, what was once a fairly complex function has become so simple! You may already be itching to learn more about LitePal’s more query usage, but don’t worry, let’s take a look at each one.

You may have encountered situations where you need to fetch the first data in a table. What is the traditional way to do this? Specify a limit value in the SQL statement, and then get the first record of the returned result. In LitePal, however, we don’t need to do this. For example, if we want to fetch the first data in the news table, we just need to write:

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

Copy the code

If you call findFirst() and pass in the News class, you get the first item in the News table.

If you want to retrieve the last item in the News table, do you want to retrieve the last item in the News table? Also simple, as follows:

News lastNews = DataSupport.findLast(News.class);
Copy the code

Because it is common to get the first or last data in a table, LitePal provides these methods to facilitate this.

If you want to query more than one item of data, what should you do? Select * from news where id 1, id 3, id 5, id 7; Call the find() method four times and pass in id 1, id 3, id 5, id 7. Yes, this is entirely possible and not inefficient, but LitePal gives us an easier way to do it — findAll(). The usage of this method is very similar to the find() method, except that it can specify multiple ids and instead of returning a generic class object, it returns 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, the findAll() method is called, and the first argument to the method is still the specified generic class, but the following arguments are arbitrary. You can pass in any id, and the findAll() method will findAll the data that corresponds to the passed id. We then return together to the List generic collection.

While this syntax design is fairly user-friendly, it may not be appropriate in some scenarios where multiple ids you want to query are already encapsulated in an array. The findAll() method also takes an array parameter, so you could write the same function as:

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

FindAll () = findAll(); findAll() = findAll(); How can I always query the data corresponding to several ids? Ha! Ha! This is a good question, because the findAll() method can also query all data, and it’s much easier to query all data by writing:

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

FindAll () finds all data in the news table without specifying an ID.

And don’t think that these are just a few method overloads of findAll(), because they’re all calls to the same findAll() method! A method is able to achieve a variety of different query effects, and semantic is also very strong, let a person can understand a look, this is LitePal query art!

Query:

Of course, LitePal’s search capabilities don’t stop there. I believe you have also found that our current query function is based on ID to query, and can not arbitrarily specify the query conditions. So how do you specify query criteria? The query() method takes seven arguments, of which the third and fourth are used to specify the query criteria, and then fills the others with null. But how does LitePal solve this problem, as we’ve already decried the verbose argument list? Let’s learn it now.

To avoid lengthy parameter lists, LitePal uses a clever solution called a concatenated query, which is flexible enough to dynamically configure query parameters based on our actual query needs. Select * from news where comment count is greater than 0; select * from news where comment count is greater than 0;

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

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

We then concatenate a find() method directly after the WHERE () method and specify a generic class here to indicate which table to query. SQL > query (); SQL > query ();

select * from users where commentcount > 0;
Copy the code

However, this will query all the columns in the news table. You may not need that much data, but only the title and content columns. It is easy to add one more concatenation, as follows:

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, and each argument requires a column name, so only the corresponding column is queried. Therefore, the select() method corresponds to the SELECT part of an SQL statement.

SQL > query (); SQL > query ();

select title,content from users where commentcount > 0;
Copy the code

Isn’t it fun? But that’s not the end of it, we can go on and connect more things. For example, if I want the news to be sorted in reverse order by the date of publication, with the most recent news being first, 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 which column the results of a query should be sorted by. The order() method corresponds to the ORDER by part of an SQL statement.

SQL > query (); SQL > query ();

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

However, if you don’t want to query all of the matches at once, because that might be too much data, and you want to query only the first 10 items, you can also easily solve this problem by using concatenation, as shown in the following code:

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

Here we attach a limit() method, which takes an integer parameter that specifies the first number of items to be queried, which is specified as 10, meaning the first 10 items of all matched results.

SQL > query (); SQL > query ();

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

Now I want to display the first 10 news items that match all the conditions. When I turn to the second page, I will display the 11th to 20th news items. How to do this? With The help of LitePal, these functions are very simple, just need to concatenate one more offset, as shown below:

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

The offset() method is used to specify the offset of the query result. The offset() method is used to specify the offset of the query result. The offset() method is used to specify the offset of the query result. That means look up items 21 to 30, and so on. Therefore, both the limit() and offset() methods correspond to the limit part of an SQL statement.

SQL > query (); SQL > query ();

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

That’s about all you can do with a LitePal concatenated query. See the difference? The advantage of this type of query is that you can combine various query parameters at will, concatenating them when needed, and not specifying them when not needed. Compared to the long list of arguments in the query() method, which must be passed null even if those arguments are not needed, does LitePal feel more human?

Radical query

However, in all of the above uses, we can only query the data in the specified table, the data in the associated table cannot be queried, because LitePal default mode is lazy query, of course, this is also recommended. If you really want to query an associated table at once, you can. LitePal also supports aggressive queries, so let’s take a look.

For each of the find() methods we have seen, an overload of the isEager parameter is used. If the value is set to true, the result will be an aggressive query, and the data in the associated table will be queried together.

For example, if we want to query the news with id 1 in the news table, and select the comment corresponding to this news, we can write:

News news = DataSupport.find(News.class, 1, true);
List<Comment> commentList = news.getCommentList();
Copy the code

As you can see, there is no complicated use here, except to add a true argument at the end of the find() method to indicate that aggressive queries are used. This will also check the data in all tables associated with the news table, so the comment table and the news table are many-to-one association, so when a news is queried using radical method, the corresponding comments of the news will also be queried together.

The use of aggressive queries is very simple, and there are only so many other find() methods that are used the same way. This is not recommended by LitePal, however, because queries can be very slow if there is a lot of data in the associated table. In addition, the radical query can only query the associated table data of the specified table, but cannot continue to iteratively query the associated table data of the associated table. Therefore, I recommend that you use the default lazy loading method, and make a small change in the model class to query the data in the associated table. Modify the code in the News class as follows:

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 have added a getComments() method to the News class, and inside this method is a single infix query that finds all the comments corresponding to the current News item. After changing this writing method, we can delay the query of the associated table data. When we need to get the corresponding comments of the News, we can call the getComments() method of the News, then we will query the associated data. This is much more efficient and reasonable than aggressive queries.

Native queries

As you can already see, LitePal has a pretty rich API for queries. However, you may encounter some strange requirements that you can’t handle using the query API provided by LitePal. It doesn’t matter, because even with LitePal, you can still query data using native queries (SQL statements). The DataSuppport class also provides a findBySQL() method that can be used to query data using native SQL statements, as shown below:

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 rest are used to replace placeholders in the SQL statement, which is very simple to use. In addition, the findBySQL() method returns a Cursor object, which is the same as the use of native SQL statements.

LitePal, LitePal, LitePal, LitePal, LitePal, LitePal, LitePal, LitePal, LitePal, LitePal, LitePal

Pay attention to my technical public account “Guo Lin”, there are high-quality technical articles pushed every day.