Javaweb page to display the content of the paging query three ways (database paging query) __c#

Reprinted from Http://www.cnblogs.com/ygj0930/p/6134851.html

In the development process, often do one thing, but also the most basic thing is to query data from the database, and then displayed on the client. When the data is young, it can be displayed within a page. However, if the query record is hundreds of, thousands. Direct a page to show exactly how long the form is. At this point, we can use paging technology.

What is paging. The effect chart is as follows:

Here is a total of 100 records, if a one-time display of the table will be many rows, the user experience is poor. And we use pagination display, a page shows 10 records, a total of 10 pages. Users can read their own, record less, clear display.

here to talk about the implementation of the paging effect, there are three kinds of ideas: one, pure JS implementation paging

A one-time query record and loaded into the HTML table. It then achieves the paging display by selectively displaying certain rows. This is a pseudo paging, a decoy. Can only be used in cases where there is little data. Once you have more data, it can be slow to load a hundred thousand of of data into HTML. And not real time, after loading the data will be written dead in the page, if the database has changed, browser-side display is still the last loaded data.

First : Use table to display the query out of the records, all displayed.

<table width= "id=" Iddata > <%, user_id, user_name, User_sex and User_phone
			;
			while (Sqlrst.next ()) {
				user_id = sqlrst.getstring (1);
				user_name = sqlrst.getstring (2);
				User_sex = sqlrst.getstring (3);
				User_phone = sqlrst.getstring (4);
				User_age = sqlrst.getstring (5);
	%>
	<tr>
		<td><%=user_id%></td>
		<td><%=user_name%></td >
		<td><%=user_sex%></td>
		<td><%=user_phone%></td>
		<td ><%=user_age%></td>
	</tr>
	<%
		}
	%>            
</table>        
< br/>
	
<table width= "60%" align= "right" > <tr><td><div id= "changepages"
	Changepages "></div></td></tr>
</table>

Then, in JS, some rows in the table are displayed and some rows are hidden.

<script type= "Text/javascript" > Function gopage (pno,psize) {var itable = document.getElementById ("Iddata");
    Table var num = itable.rows.length;//Gets the total number of records var totalpage = 0; var pageSize = psize;//page display PageSize record//Total pages if (Num/pagesize > parseint (num/pagesize)) {Totalp   
       Age=parseint (num/pagesize) +1;   
       }else{Totalpage=parseint (num/pagesize);
    }//Current page var currentpage = pno;
       Gets the line number of the first and last record of the current page var startrow = (currentPage-1) * PAGESIZE+1;
       var endrow = currentpage * pageSize; Endrow = (Endrow > num)?
    Num:endrow;
        Modify the properties of the row corresponding to the current page in table to display, not the record for this page is hidden for (Var i=1;i< (num+1); i++) {var irow = itable.rows[i-1];    
        if (I>=startrow && i<=endrow) {irow.style.display = "block";
        }else{Irow.style.display = "None"; Page page Number list var tempstr = "+num+" section "+totalpage+" page current section +currentpage+ "Page";
        if (currentpage>1) {tempstr + = "<a href=\" #\ "onclick=\" Gopage ("+ (1) +", "+psize+") \ "> Home </a>";
        TempStr + = "<a href=\" #\ "onclick=\" Gopage ("+ (currentPage-1) +", "+psize+") \ ">< prev </a>"}else{
        TempStr + + "Home";    
    TempStr = "< prev"; } if (currentpage<totalpage) {tempstr = "<a href=\" #\ "onclick=\" Gopage ("+ (currentpage+1) +", "+psize+") \
        "> Next page ></a>";
    TempStr + + "<a href=\" #\ "onclick=\" Gopage ("+ (Totalpage) +", "+psize+") \ "> Last </a>";
        }else{TempStr + = "Next page >";    
    TempStr = "Last";   
} document.getElementById ("Changepages"). InnerHTML = TempStr; } </script>

二、一次 Query, partial display

That is, we can perform a database query operation to get the result set Rs. The current page's record is then displayed through the movement of the pointer. This allows you to navigate to the first record of the current page with Rs.absolute (current page number * per page record number), and then through the while loop to display N Records (n shows the number of records per page). When you jump the page, simply modify the currentpage to change the current page number when you reposition to the next page, reposition the record pointer, and display n records through a while traversal. Unlike JS selective display, this is the selective traversal. And JS pagination is different, here paging every page to modify the pointer to traverse, each page should be a comprehensive query. Similarly, it is not suitable for large data volume queries. Here is more than JS page optimization is the place-real-time. Every jump page will query the database, to ensure real-time data.

Reference code:

<% int intpagesize = 10;                 The number of records displayed on one page int introwcount;                 Total records int intpagecount;                    Total number of pages String strpage;                     The displayed page number int intpage that is sent from the form or URL; Number to be displayed, Integer//---converted by strpage the first way to calculate the total number of records: Query all records, move the result set pointer to the last one, get the line number of the last record//query all data ResultSet Sqlrst = s
　　Qlstmt.executequery ("SELECT * from user");	Get the total number of records sqlrst.last (); 	The cursor is Introwcount = Sqlrst.getrow () on the last line;
 	Gets the current line number, that is, the total number of records//Count total pages Intpagecount = (int) Math.ceil (introwcount/(intpagesize*1.0));
	Position the record pointer over the first record of the page to be displayed Sqlrst.absolute ((intPage-1) * intpagesize + 1);
	display data int i=0;
	String user_id, user_name, User_sex, User_phone, user_age;
		while (I < intpagesize &&!sqlrst.isafterlast ()) {user_id = sqlrst.getstring (1);
		user_name = sqlrst.getstring (2);
		User_sex = sqlrst.getstring (3);
		User_phone = sqlrst.getstring (4);
User_age = sqlrst.getstring (5); %> <tr> &LT;TD&GT;&LT;%=USER_ID%&GT;&Lt;/td> <td><%=user_name%></td> <td><%=user_sex%></td> <td><%=user
		_phone%></td> <td><%=user_age%></td> </tr> <% sqlrst.next ()//move record pointer to next record i++;//statistics How many records are displayed on the current page}%>

third, in the service end paging

Jump to nth page to query, show N page content. The point is to calculate the position of the first record of the current page to query based on the page of the client table.

Advantages: Real-time: Jump page before querying. Small amount of data: Only the records of the current page are loaded for display.

The focus is on two statements:

Select COUNT (*) from ...//query to record the total number of articles

SELECT * From ... limit pageno,rowscount//query Rowscount bar data starting with article PageNo.

int pages=0;            To display the page int count=0;        Total bar number int totalpages=0;            Total pages int limit=10;
Display the number of record bars per page//The second way to calculate the total number of records: Use the MySQL aggregate function count (*) ResultSet Sqlrst = Sqlstmt.executequery ("SELECT count (*) from user"); if (Sqlrst.next ()) {count = Sqlrst.getint (1);//The result is a count (*) table with only one column.
Here the value is obtained by the subscript index (1) of the column and the total number of pages totalpages = (int) Math.ceil (count/(limit*1.0)) by dividing the total records by the number of records per page;
Gets the current page argument in which the page jumps newsletters the String strpage = Request.getparameter ("pages"); Determine the legality of the current page parameter and process the illegal page number (blank to display the first page, less than 0 to display the first page, greater than the total number of pages to display the last page) if (strpage = null) {pages = 1;} else {try{pages = Java.l Ang.
	Integer.parseint (strpage);
	}catch (Exception e) {pages = 1;
	if (pages < 1) {pages = 1;
	} if (pages > TotalPages) {pages = TotalPages; (pages-1) *limit work out the first record of the current page, limit query limit Records. The record of the current page is Sqlrst = Sqlstmt.executequery ("SELECT * from User order by user_id limit" + (pages-1) * limit + "," + limit)
; while (Sqlrst.next ()) {//Traverse display}

Jump page Implementation: Jump page is through redirection to achieve, by the current page into the pages to be displayed, after the jump according to pages to recalculate page display of the first, check limit bar display.

<form name= "F1" method= "POST" action= "index.jsp" onsubmit= "return Checknum ()" >
	<table border= "0" align= " Center ">
		<tr>
			<td> Chapter <%=pages%> page Total <%=totalpages%> page <a href=" Index.jsp?pages=1 "> Home </a></td>
			<td><a href=" index.jsp?pages=<%= (pages<1)? Pages: (pages-1)%> " > prev </a></td>
			<td><a href= "index.jsp?pages=<%= (pages>=totalpages)? totalpages :(pages+1)%> "> Next </a></td>
			<td><a href=" index.jsp?pages=<%=totalpages%> " > Last page </a></td>
			<td> goto: <input type= "text" name= "page" size= "8 > Page <input type=" Submit "value=" Go "name=" Cndok "></td>
		</tr>
	</table> 
 </form>

attached: Common database Paging query statement

1.oracle Database Paging
    SELECT * FROM (select A.*,rownum rc from table name where Rownum<=endrow) a where A.rc>=startrow

2.db2 Database Paging
    SELECT * FROM (select RowNumber () as Rc,a.* from (SELECT * from table name order by column name) where RC between STA Rtrow and Endrow

3.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 an order by column name

4.SQL Server 2005 Database Paging
    select * FROM (Select column name, Row_number () over (the Order by column name 1) as Alias from table name) as T where T. Column name 1>=startrow and T. Column name 1<=endrow

5.MySQL Database Paging
    Select * FROM table name limit Startrow,pagesiz E
    (pagesize the number of records per page)

6.PostgreSQL database Paging
    Select * FROM table name limit Pagesize,offset StartRow
    ( PageSize the number of record bars displayed per page)

The github:https://github.com/ygj0930 of the original author