Database Summary of pagination in Oracle and MySQL

Physical paging • In SQL queries, retrieve only the data that is needed for paging from the database • Typically different databases have different physical paging statements MySQL physical paging, using the Limit keyword • For example: Retrieving 11-20 select * from user limit 10,10;

* Only 10 records are queried at a time. When you click on the next page, query the database, query the following 10.

* Advantage: If the amount of data is very large, it will not cause memory overflow.

* Cons: Interact with the database every time.

* Paging generally uses the database SQL statement to complete the paging query.

* MySQL Paging: Use the Limit keyword.

* Oracle Paging: Using ROWNUM SQL Server paging: using the top keyword.

* SELECT * FROM customer limit A, B;

* Parameter A: Represents the beginning of the record, the initial value is 0.

* parameter B: query length.

* Paging Relationship:

Page (current page) limit (number of bars per page) start (from where)

1 10 0

2 10 10

3 10 20

Start = (page-1) * limit;

* to pass a value to the background: The current number of pages.

* background Display data to page: Currpage ( current page) , list<customer> , Totalpage ( total pages. It needs to be calculated by the total number of records.) , TotalCount ( total record Count) , limit ( the number of records displayed per page).

* these parameters are encapsulated. encapsulated into a javabean access JavaBean with the request domain.

Paging in Oracle:

RowNum for paging in Oracle;

RowNum is characterized by:

1. RowNum is always generated in the default order

2. RowNum can only use < <=; Cannot use > >=

(On the characteristics of RowNum I explained the first question in the last blog post that I have said something: Forget please click)

Sql> --page OutSql> SelectRownum,empno,ename,sal2   fromEMP3  whereRowNum>=5  andRowNum<=8; row SQL not selected> SelectRownum,empno,ename,sal2   fromEMP3  whereRowNum>=5; row not selected

The workaround is to:

-- Oracle Paging

Sql>  SELECT *  2   from      (select RowNum r,e1.*  3 from      (SELECT * from emp ORDER by Sal) E1  4      where rownum <=8  5     )  6   where R >=5;         R      EMPNO ename      JOB              MGR hiredate              SAL       COMM     DEPTNO------------------------------------ ---------------------------------------------------------         5       7654 MARTIN     salesman        7698 2 August-September- Bayi           1250       1400         6       7934 MILLER     clerk           7782 2 March-January -82           1300         7       7844 TURNER     Salesman        7698 August-September -81          0         8       7499 ALLEN      salesman        7698 20月-February-81            from

Detailed analysis process because the blog Park editor is not strong enough, I explained with Word, here echo some SQL text:

This is the SQL statement starting with the second select:
Sql> SelectRowNum r,e1.*  2       from(Select *  fromEmpOrder  bySal) E13      whereRowNum<=8  4  ; R EMPNO ename JOB MGR hiredate SAL COMM DEPTNO---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------         1       7369SMITH Clerk7902  --December- the             -                     -         2       7900JAMES Clerk7698 Geneva-December-Bayi            950                     -         3       7876ADAMS Clerk7788  at-May- the           1100                     -         4       7521WARD salesman7698  A-February-Bayi           1250         -          -         5       7654MARTIN salesman7698  --September-Bayi           1250       1400          -         6       7934MILLER Clerk7782  at-January- the           1300                    Ten         7       7844TURNER salesman7698  ,-September-Bayi            the          0          -         8       7499ALLEN salesman7698  --February-Bayi            the         -          -has been selected8Yes.

Logical Paging

• When SQL queries, retrieve the result set of all data from the database • Within the program, the data needed for paging is obtained through logical statements • For example: Search for 11-20 userlist.sublist (10,20);

* Once all the records in the database are queried, stored in the list collection, each time the query, the list collection sublist. The length of the list collection is truncated to complete the paging.

* Advantage: Access database only once.

* Cons: If the amount of data is very large, it is easy to cause memory overflow.

Design of JavaBean When passing information about encapsulating paging in Java

/*** Paging Query data class store paging related all data*/ Public classPagebean {Private intPagenum;//Current page number    Private intNumperpage;//number of record bars per page    Private intTotalCount;//total number of records    Private intTotalpagenum;//Total Pages    PrivateList<customer> customers;//The current page requires data}

Database Summary of pagination in Oracle and MySQL