Paging of query records using standard SQL statements

In most applications, it is common to return a large number of qualified records. The most typical method is to search. In the search application, the user gives the search criteria and the server finds the matching records. However, a search usually returns a large amount of data records. If you are on a webpage, the page is usually implemented by pagination. The page provides buttons such as the previous page and next page to implement paging.
The following methods are used to implement paging:
1. Find method of EJB
2. Put all records in the collection object before the first page is displayed, and all subsequent pages are retrieved in the memory.
3. Each page goes to the database for a duplicate query, and the matching records are displayed.
4. Save the search results of the previous pages in the memory (frequently used), and connect the subsequent records (these data are not patiently viewed by most users) to the database.
5. Other cache mechanisms.

We can select a method suitable for ourselves based on the actual situation. I will introduce you to a standard SQL statement to obtain qualified data. For example, from 10th to 20 records that meet the conditions (in the where Statement. In this way, there is a necessary condition for getting a record. There must be a field that can identify the record order, such as ID and time. The following is an example:
Query 10th to 20 records in the t_table table, sorted by ID.
The SQL statement is:
Select * From t_table T1 where (select count (*) from t_table T2 where t2.id <t1.id)> = 10 and (select count (*) from t_table T2 where t2.id <t1.id) <20

Another example is to query the records from key = '000000' 123 to 20 in the t_table table, sorted by ID.
Select * From t_table T1 where (select count (*) from t_table T2 where t2.id <t1.id and t2.key = '2013')> = 10 and (select count (*) from t_table T2 where t2.id <t1.id and t2.key = '000000') <20 and t1.key = '000000'

of course, the ID in the condition can be changed to any field that can identify the record sequence.