Implementing query record paging with standard SQL statements

Standard | pagination | In most applications, it is commonplace to return a large number of qualifying records, most typically a search. In the search application, the user gives the search condition, the server detects the record that meets the condition. But often search will return a large number of data records, if in the page, often through the way of pagination, the page provides such as the previous page, the next page, and so on buttons to achieve pagination.
Now there are several ways to implement pagination:
The Find method of 1.EJB
2. Place all records in the collection object before the first page is displayed, and subsequent pages are fetched in memory.
3. Each page makes a duplicate query to the database and then displays the qualifying records.
4. Save the previous pages of the search results in memory (often used), subsequent records (which most users are not patient to see) and then through the linked database.
5. Various other cache mechanisms.

We can choose our own method according to the actual situation, I will introduce a standard SQL statement to get the data that meet the criteria. such as a record from the qualifying condition (in the where statement) from article 10th to 20th. There is a need for this method to get records, and there must be a field that identifies the order of records, such as Id,time, and so on. Let me show you an example:
Query t_table all records in the table 10th to 20th, sorted by ID.
The SQL statement is:
SELECT * from t_table t1 where (select COUNT (*) from t_table T2 where t2.id < t1.id) >= and (select COUNT (*) FRO M t_table T2 WHERE t2.id < t1.id) < 20

Also such as Query t_table table key= ' 123 ' 10th to 20th record, sorted by ID.
SELECT * from t_table t1 where (select COUNT (*) from t_table T2 where T2.id < t1.id and T2.key = ' 123 ') >= and (S Elect Count (*) from t_table T2 WHERE t2.id < t1.id and T2.key = ' 123 ') <-T1.key = ' 123 '

Of course, the ID in the condition can be changed to any field that can form the record order.