"Cicada Hall Study Notes" JSP page data paging implementation (a)--paging concept and the main database paging query

I. Introduction to Pagination

1. Paging is a technique that presents a part of the data in a database to the user. Why did you do it? Because in general the user in the query when the data is very much, when a page can not display all the data, we need to page the query data, just like our books into a page by page. The simplest example is Baidu, when you Baidu, thousands of data, not presented in a page.

　　

2. How to implement Paging

1) False paging (not recommended): What is false paging? False paging is a one-time data in the database are all taken out, stored in the page, and then the data are displayed separately. This kind of false paging in the data can also play, when the data more up, this way exploded. Because the data is too large, it will cause your page to explode, click the next page to wait a long time, the user experience is very poor, so it is not recommended.

2) True paging: The real paging is how much data your page needs, and how much data it takes to get to the database. This kind of paging is very flexible and has a great advantage in dealing with large data queries. So this is the way we use this note.

Two. Database Paging query

Database paging query, is based on the needs of the page, only query part of the data at a time, when the user click on the next page to continue to query additional data. There are currently three main databases in the mainstream: Oracle, MySQL, SQL Server, and then we'll look at three types of databases that correspond to paging queries.

1.MySQL--Relatively simple paging query

MySQL paged query to use the limit keyword, the way is the limit m,n m indicates that the number of data n indicates how many data to start from the m+1 , such as

SELECT * FROM table limit m,n where m refers to the index at which the record starts, starting at 0, indicating the first record

Specific: SELECT * from tableName Where condition limit current page * page capacity-1, page capacity

2.Oracle Paging

The keyword used by Oracle paging is rownum (line number), and we look directly at its usage

1) The first n records in the query table: SELECT * FROM table where rownum <= n

2) query Nth to M Records: SELECT * FROM (select Table name. *, rownum rn from table name where RowNum <=m) where RN > N;
For this form of query, Oracle is not as convenient as MySQL, it must be implemented using subqueries or collection operations.

3. Paging forSQL Server

On a paged query, I feel SQL Server is laborious, there is no specific paging statement, and each version of the corresponding query method is different, such as:

1) SQL Server 2000 database paging
Select Top pagesize * FROM table name where column name not in (select top pagesize*page column name from table name order BY column name) Order BY column name

2) SQL Server 2005 database paging (the next page of data is a very similar point)
SELECT * FROM (select column name, Row_ search number () over (Order by column name 1) as alias from table name) as T where T. Column name 1>=startrow and T. Column name 1<=endr ow

Three. Writing the page

Paged query of data learned, we need to plan how the page is displayed on the page. In the General Web page, we all present the data in the form of the table, so we first do the JSP page, and then to write the background code to render the data on the page, here I give the JSP page we tested:

：

Attached code:

　　

<Divclass= "Search">            <P>Application Date:</P>            <SelectID= "Time">                <option>Select a Date</option>            </Select>            <inputclass= "button"type= "Submit"name=""value= "Query"></input>            <Divclass= "Tuxin"><imgsrc=".. /.. /images/graphical reports. png "> <ahref="#">Graphical reports</a></Div>            <Divclass= "Dayin"><imgsrc=".. /.. /images/printer. png "> <ahref="#">Export print</a></Div>            <form>            <P>Purchase number:</P>            <inputtype= "text"name="">            <P>Material Name:</P>            <inputtype= "text"name="">            <inputclass= "button"type= "Submit"name=""value= "Query"></input>            <inputtype= "Reset"class= "button"value= "Reset"style= "background: #e4e1e1; color: #000"></input>            </form>        </Div>        <Divclass= "Contents">            <Tableclass= "Table">                <TR>                    <TD>Application Date</TD>                    <TD>Applicant</TD>                    <TD>Buyer</TD>                    <TD>Material number</TD>                    <TD>Material name</TD>                    <TD>Specification model</TD>                    <TD>Unit of Measure</TD>                    <TD>Number of applications</TD>                    <TD>Note</TD>                    <TD>Operation</TD>                </TR>                <TR>                    <TD>2017/8/7</TD>                    <TD>Xiao Yin</TD>                    <TD>Tom</TD>                    <TD>1001</TD>                    <TD>Glass</TD>                    <TD>100x100x3</TD>                    <TD>CM (cm)</TD>                    <TD>1000</TD>                    <TD>No</TD>                    <TD><aclass= "Delete"href="#"><imgsrc=".. /.. /images/delete_8e.png "></a></TD>                </TR>            </Table>            <Divclass= "Paging">                <aclass= "button"href="">Previous page</a>                <ahref="#">1</a>                <ahref="#">2</a>                <ahref="#">3</a>                <ahref="#">4</a>                <ahref="#">5</a>                <aclass= "button"href="">Next page</a>                <P>Shared<span>5</span>Page&nbsp;&nbsp;&nbsp;to the first</P>                <inputtype= "text"name="">                <Pstyle= "margin:2px-1px 0 10px;">Page</P>                <inputclass= "button"type= "Submit"value= "Confirm">            </Div>        

In the next chapter we will implement the display of the page data:

"Cicada Hall Study Notes" JSP page data paging implementation (a)--paging concept and the main database paging query