Android Database Master cheats (vii)--experience Litepal's inquiry art

Reprint Please specify source: http://blog.csdn.net/guolin_blog/article/details/40153833

After a number of articles to learn, we have litepal in the majority of the content has been mastered. Now in retrospect, adding and removing changes to check four operations in the first three kinds of we have already learned, do not know the use of the database now, you have not felt particularly easy and simple. However, we all know that in all database operations, the query operation is certainly the most complex, usage is the most, so litepal in the query aspect of the API is also relatively rich, and Litepal in the query API design is quite artistic. So today we are dedicated to use a blog to explain the use of query operations, experience the art of Litepal query. Have not read the previous article of the friend suggested first to refer to the Android database Master cheats (vi)--litepal modification and deletion operations .

Litepal's project address is:https://github.com/LitePalFramework/LitePal

Traditional way of querying data

In fact, the most traditional way of querying data is the use of SQL statements, Android also provides a direct use of native SQL statements to query the database table method, that is, sqlitedatabase in the Rawquery () method, the method is defined as follows:

Public Cursor rawquery (String sql, string[] selectionargs)

where the Rawquery () method receives two parameters, the first parameter receives an SQL string, and the second parameter is a string array that replaces the placeholder (?) in the SQL statement. The Rawquery () method returns a Cursor object, and all data that is queried is enclosed in this object, and we only need one by one to remove it.

Of course, this usage is not very common, because I believe most people still do not like to write SQL statements. So, Android specifically provides a packaged API that allows us to query the data without writing an SQL statement-the query () method in Sqlitedatabase. Query () provides three method overloads, where the least of the parameters has seven parameters, and we look at the method definition:

Public Cursor query (string table, string[] columns, string selection,            string[] Selectionargs, String groupBy, String h Aving,            String by)

The first parameter is the table name, which indicates which table we want to query the data from. The second parameter is used to specify which columns to query, and if not specified, all columns are queried by default. The third and fourth parameters are used to constrain the query of data for a row or rows, and the default is to query the data for all rows without specifying it. The fifth parameter specifies the column that needs to go to group by, without specifying a group by operation for the query result. The sixth parameter is used to further filter the data after group by, without specifying that it is not filtered. The seventh parameter specifies how the query results are sorted, and does not specify that the default sort is used.

This method is one of the least method overloads for the query () method, and two additional method overloads are eight and nine parameters respectively. Although this method is very common in the Android database table query, but the heavy parameters let us understand this method can be very laborious, but also the use of the time will be quite uncomfortable. For example, if we want to query all the data in the news table, we should write this:

Sqlitedatabase db = Dbhelper.getwritabledatabase (); cursor cursor = db.query ("News", NULL, NULL, NULL, NULL, NULL, NULL);

As you can see, the first table name parameter is specified as news, and then the next six parameters are not used, and all are specified as null.

So what if we want to look at news reports that have more than 0 comments? The code looks like this:

Sqlitedatabase db = Dbhelper.getwritabledatabase (); cursor cursor = db.query ("News", NULL, "COMMENTCOUNT>?", New string[]{"0"}, NULL, NULL, NULL);

Since the third and fourth parameters are used to specify the constraint, we specify the Commentcount&gt in the third argument, and then replace the placeholder with a string array in the fourth argument, so the result is the news that all comments in the news table are greater than 0. So what about the other parameters? Still cannot be used, so it is only possible to 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 also need to remove the data from the cursor object one after another and set it to the news entity class as follows:

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"));D ate 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 use of traditional query data methods, in general, the usage is really very unfriendly, especially the query () method lengthy parameter list, even if we do not use those parameters, we must pass many null. In addition, the data that is queried is only encapsulated in a cursor object, and we need to remove the data one by one and set it into the entity class object. Are you in trouble? You may feel no trouble because you are accustomed to this usage. But habits can always be changed, perhaps when you experience the Litepal in the query API to bring us the convenience, there will be a new view, then we will be together to experience the litepal of the query art.

Querying data using Litepal

Litepal provides a very rich range of APIs in terms of query, features, and is basically able to meet all of our usual query needs. Not only that, Litepal in the query API design is also very hard, abandon the original query () method in the cumbersome parameter list, but instead of a more dexterous way-concatenating query. In addition, the result of the Litepal query no longer returns the cursor object, which is then taken out individually by the developer and returned directly to the encapsulated object. These changes make querying data easier and more reasonable, so let's take a complete look at all the usage of querying data in Litepal.

Simple query

For example, now we want to implement one of the simplest features, querying this record with the ID 1 in the news table, which can be written using Litepal:

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

Oh, my God! Did you find it too easy? Just one line of code, you can find the record ID 1 in the News table, and the result is automatically encapsulated in the news object, do not need us to manually and from the cursor to parse. If you write with a native SQL statement, or the query () method, you need at least 20 lines of code to complete the same function!

Let's just calm down and analyze the Find () method. As can be seen, its argument list is also relatively simple, only receive two parameters, the first parameter is a generic class, that is, we specify what class here, the returned object is what class, so here to News.class, then the returned object is news. The second parameter is more simple, is an ID value, if you want to query the record ID 1 to pass 1, want to check the ID 2 of the record 2, and so on.

Originally a quite complex function, through the litepal after it became so simple! Then you may be eager to learn more litepal more query usage, don't worry, we look at one by one.

You may have encountered the following scenarios, and in some cases you need to take out the first piece of data in the table, so what is the traditional approach? Specify a limit value in the SQL statement, and then get the first record that returns the result. But it's not so much trouble in Litepal, for example, we want to get the first piece of data in the news table, just write this:

News firstnews = Datasupport.findfirst (News.class);

OK, semantic is very strong, let a person at a glance to understand what is meant, just call the FindFirst () method, and then passed to the news class, get the news table the first data.

So let's take three, if you want to get the last piece of data in the news table, how do you write it? Same as simple as follows:

News lastnews = Datasupport.findlast (News.class);

Because it is common to get the first or last data in a table, Litepal intentionally provides both methods to facilitate our operation.

So we see here, is only the function of querying a single piece of data, if you want to query multiple data what to do? For example, we would like to find out the information in the News table ID 1, 3, 5, 7, how to write it? Perhaps some friends will be more intelligent, immediately thought can go to check, call four times Find () method, and then put 1, 3, 5, 7 This four ID is passed in not to be able. Yes, this is entirely possible and not inefficient, but Litepal provides us with a much easier way to--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 no longer a generic class object, but rather a generic class collection, as follows:

list<news> newslist = Datasupport.findall (News.class, 1, 3, 5, 7);

As you can see, first we are calling the FindAll () method, and then the first parameter of this method is still the specified generic class, but the following parameters are very arbitrary, you can pass in any ID, and the FindAll () method will find out all the data corresponding to all incoming IDs. It then returns to the generic collection of list<news>.

Although this syntax design is quite human, but in some scenarios may not be very suitable, because you may want to query the number of IDs have been encapsulated in an array. So it's okay, the FindAll () method also receives array parameters, so you can write the same function:

long[] ids = new long[] {1, 3, 5, 7}; list<news> newslist = Datasupport.findall (News.class, IDS);

See here, that some friends may be strange, said FindAll () method should not be the meaning of querying all the data? How do you always query the data for 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:

list<news> allnews = Datasupport.findall (News.class);

See no, we just need to take back the parameters are removed, without specifying a specific ID, the FINDALL () method query is all the data in the news table, is not the semantics of a very strong?

And we do not think that these are just findall () a few methods of overloading, in fact, we have just a few of these usages are called the same findall () Method! A method can achieve a variety of different query results, and semantic is also very strong, let a person can understand, this is Litepal query art!

concatenating query

Of course, Litepal to provide us with the query function is far more than these, the show is still behind. I believe you have now found that our current query function is based on the ID to query, and can not arbitrarily specify the query criteria. So how do you specify the query criteria? Let's recall what the traditional situation should be, the query () method receives seven parameters, where the third and fourth parameters are used to specify the query criteria, and then a few other parameters are filled with null. However, before we have already had the pain of this writing, because the lengthy parameter list is too cumbersome, then litepal how to solve this problem? Let's study now.

To avoid lengthy parameter lists, Litepal uses a very ingenious solution called concatenating query, which is flexible enough to dynamically configure query parameters based on our actual query requirements. So here's a simple example: if we want to look at news reports that have more than 0 comments, you can write them like this.

list<news> newslist = Datasupport.where ("Commentcount >?", "0"). Find (News.class);

As you can see, the first is called the Where () method of Datasupport, where the query condition is specified. The Where () method receives any string parameter, where the first parameter is used for the condition constraint, starting with the second argument, which is used to replace the placeholder in the first argument. The Where () method corresponds to the where part of an SQL statement.

Then we directly concatenating a find () method after the Where () method, and then specify a generic class here that represents which table to query. So the above section of code, the query results and the following SQL statement is the same:

SELECT * from users where commentcount > 0;

But this will query all the columns in the news list, maybe you don't need that much data, but just the title and content columns. So it's simple, we just need to add another concatenating, as follows:

list<news> newslist = Datasupport.select ("title", "Content"). where ("Commentcount >?", "0"). Find (News.class) ;

As you can see, here we have added a select () method that takes any string parameter, each of which requires a column name, so that only the data of the corresponding column is queried, so the Select () method corresponds to the select part of an SQL statement.

So the above section of code, the query results and the following SQL statement is the same:

Select Title,content from users where Commentcount > 0;

It's fun, huh? But that's not the end of it, and we can continue to concatenating more things. For example, I would like to have the news published in reverse chronological order, that is, the latest news released in the first place, then you can write:

list<news> newslist = Datasupport.select ("title", "Content"). where ("Commentcount >?", "0"). Order (" Publishdate desc "). Find (News.class);

The order () method receives a string parameter that specifies which column the result of the query is sorted by, ASC indicates a positive order, and DESC is a reverse order, so the order () method corresponds to the order by part of an SQL statement.

So the above section of code, the query results and the following SQL statement is the same:

Select Title,content from the users where Commentcount > 0 order by publishdate Desc;

And then, maybe you don't want all of the results of the match to be queried all at once, because the amount of data may be a bit too large, but rather to query only the first 10 data, then using concatenating can also easily solve the problem, the code is as follows:

list<news> newslist = Datasupport.select ("title", "Content"). where ("Commentcount >?", "0"). Order (" Publishdate desc "). Limit (). find (News.class);

Here we concatenating a limit () method, which takes an integer parameter that specifies the first few data in the query, which is specified as 10, meaning that the first 10 data in all matching results is queried.

So the above section of code, the query results and the following SQL statement is the same:

Select Title,content from the users where Commentcount > 0 ORDER BY publishdate DESC limit 10;

Just now we are looking at the first 10 news of all matching criteria, then I would like to display the news page, to the second page to show the 11th to 20th news, how can this be achieved? It doesn't matter, with the help of Litepal, these functions are very simple, only need to concatenating an offset, as follows:

list<news> newslist = Datasupport.select ("title", "Content"). where ("Commentcount >?", "0"). Order (" Publishdate desc "). Limit (Ten). Offset (news.class);

As you can see, here we have added an offset () method, which specifies the offsets of the query result, which is specified here as 10, which means that the offset is 10 positions, then the original query 10 news, offset 10 position, it becomes the query 11th to 20th news, if the offset is 20, That means querying the 21st to 30th news, and so on. Therefore, the limit () method and the order () method collectively correspond to the limit portion of an SQL statement.

So the above section of code, the query results and the following SQL statement is the same:

Select Title,content from the users where Commentcount > 0 ORDER BY publishdate DESC limit 10, 10;

This is probably all the usage of concatenating query in Litepal. You see the difference? The advantage of this kind of query is that we can arbitrarily combine various query parameters, need to use the time to concatenating them together, do not need to use the time without specifying it. Compare the verbose parameter list in the query () method, even if we don't use those parameters, we have to pass NULL, is it obvious that the query in Litepal is more humane?

Aggressive Query

However, all of our usages above can only be queried to the data in the specified table, the data in the associated table is not found, because litepal default mode is lazy query, of course, this is the recommended query method. Well, if you really want to one-time the data in the association table also query out, of course, it is possible, Litepal also support the way of radical query, let's take a look.

I do not know if you have found that we have just learned each type of find () method, which corresponds to a method overload with the Iseager parameter, this parameter is believed to see what the meaning of, set to TRUE indicates radical query, so that the data in the association table together query out.

For example, we would like to query the news table ID 1, and the corresponding comments on the news to find out together, you can write:

News news = Datasupport.find (News.class, 1, true); list<comment> commentlist = News.getcommentlist ();

As you can see, there is no complicated usage here, that is, when you add a true parameter at the end of the Find () method, you are using aggressive queries. This will also identify the data in all the tables associated with the news table, so the comment and news tables are many-to-one, so when you use a radical query for a piece of news, the corresponding comment is queried.

The use of radical queries is very simple, so much so that the other find () methods are the same usage, and they are not repeated. However, this query is not recommended Litepal, because if the data in the association table is large, the query speed may be very slow. And the radical query can only query the associated table data of the specified table, but it is not possible to continue iterating through the associated table data of the associated table. So, here I suggest that you still use the default lazy loading more appropriate, as to how to query out the data in the associated table, in fact, only need to make a small change in the model class can be. 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);}}

As you can see, we've added a getcomments () method to the news class, and the inside of this method is using a concatenating query to find out all the comments that are currently in the news. After changing to this, we can delay the query of the associated table data, and when we need to get a comment on the news, we call the Getcomments () method, and then we query the associated data. This is more efficient and more reasonable than aggressive queries.

Native query

I believe you have realized that the API that Litepal provides in terms of query is already quite rich. However, you may encounter some strange needs, and you may not be able to complete these requirements using the query API provided by Litepal. No, because even with litepal, you can still query data using native query methods (SQL statements). The Datasuppport class also provides a Findbysql () method that can be used to query data using native SQL statements, as follows:

cursor cursor = DATASUPPORT.FINDBYSQL ("SELECT * from news where commentcount>?", "0");

The Findbysql () method receives any string argument, where the first argument is the SQL statement, and the subsequent arguments are used to replace the 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 result of the native SQL statement's usage.

Well, so that we have all the methods of querying data provided in Litepal have been learned, then today's article will be here, the next article will begin to explain the use of aggregate functions.

Litepal Open Source project address: Https://github.com/LitePalFramework/LitePal

Android Database Master cheats (vii)--experience Litepal's inquiry art