The application of JDBC in Java Web--paging query

Paging Query

There are many ways to implement paged queries through JDBC, and different database mechanisms provide different paging methods, and here are two very typical page-paging approaches.

1. Paging through resultset cursors

Through the ResultSet cursor implementation of paging, the advantage is common in various databases, the disadvantage is to occupy a large amount of resources, not suitable for large data situations.

2. Paging through the database mechanism

Many databases themselves provide paging mechanisms, such as the top keyword provided in SQL Server, the Limit keyword provided in the MySQL database, which can set the number of records returned by the data.

Paging query through various database paging mechanism, the advantage is to reduce the cost of database resources, improve the performance of the program; The disadvantage is that only one database is common.

Note: Because of the performance defects in data paging by resultset cursor, in the actual development, in many cases, the paging mechanism provided by the database is used to realize paging query function.

Example 1.1 through the MySQL database provides a paging mechanism, to achieve product information paging query function, the paging data display in the JSP page.

(1) Create a class named product that encapsulates the commodity information, which is the javabean of the commodity information. The key code is as follows:

 PackageCom.cn.gao; Public classProduct { Public Static Final intpage_size=2;//number of records per page    Private intId//numbering    PrivateString name;//name    Private DoublePrice//Price    Private intNum//Quantity    PrivateString Unit;//Unit     Public intgetId () {returnID; }     Public voidSetId (intID) { This. ID =ID; }     PublicString GetName () {returnname; }     Public voidsetName (String name) { This. Name =name; }     Public DoubleGetPrice () {returnPrice ; }     Public voidSetprice (DoublePrice ) {         This. Price =Price ; }     Public intGetnum () {returnnum; }     Public voidSetnum (intnum) {         This. num =num; }     PublicString Getunit () {returnUnit; }     Public voidsetunit (String unit) { This. Unit =Unit; }}

Tip: In the Java language, if a static final type variable is defined, this variable is typically capitalized. This method of writing is a specification that can easily be distinguished from other types of variables.

(2) Create a class named Productdao, which is used primarily for database-related operations that encapsulate commodity objects. In the Producedao class, The Getconnection () method is first written to create a database connection Connnection object with the following key code:

/*** Get database connection *@returnConnection Object*/     PublicConnection getconnection () {Connection conn=NULL; Try{class.forname ("Com.mysql.jdbc.Driver"); String URL= "Jdbc:mysql://localhost:3306/test"; String User= "Root"; String Password= "1234"; Conn=drivermanager.getconnection (URL, user, password); } Catch(ClassNotFoundException e) {//TODO auto-generated Catch blockE.printstacktrace (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }        returnConn; }

Then create a paged query method for the product information find (), which contains a page parameter that is used to pass the page number to be queried. The key code is as follows:

/*** Paging for all product information *@paramPage pages *@returnlist<product>*/     PublicList<product> Find (intpage) {List<Product> list =NewArraylist<product>(); Connection Conn=getconnection (); String SQL= "select* from tb_product ORDER by id desc limit?,?"; Try{PreparedStatement PS=conn.preparestatement (SQL); Ps.setint (1, (page-1) *product.page_size); Ps.setint (2, product.page_size); ResultSet RS=Ps.executequery ();  while(Rs.next ()) {Product P=NewProduct (); P.setid (Rs.getint ("id")); P.setname (Rs.getstring ("Name")); P.setnum (Rs.getint ("Num")); P.setprice (Rs.getdouble ("Price")); P.setunit (Rs.getstring ("Unit"));            List.add (P);            } ps.close ();        Conn.close (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }        returnlist; }

The Find () method is used to implement the paging query function, which queries the records in the specified page number based on the page number passed by the entry parameter page, and is implemented primarily through the Limit keyword.

Description: The Limit keyword provided by the MySQL database controls the starting position of the query data result set and the number of returned records, which are used in the following ways:

Limit arg1,arg2 parameter description: arg1: Used to specify the starting position of the query record. ARG2: Used to specify the number of records returned by the query data.

During the paging query process, you also need to obtain the total number of records for the product information, which is used to calculate the total pages of the commodity information, which is written in the Findcount () method. The key code is as follows:

/*** Query Total number of records *@returnTotal Record Count*/     Public intFindcount () {intCount=0; Connection Conn=getconnection (); String SQL= "SELECT count (*) from Tb_product"; Try{Statement sta=conn.createstatement (); ResultSet RS=sta.executequery (SQL); if(Rs.next ()) {count= Rs.getint (1);//assign a value to the total number of records} rs.close ();        Conn.close (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }        returnCount//returns the total number of records}

(3) Create a class named FindServlet1, which is a Servlet object that queries the commodity information by paging. Rewrite the Doget () method in the FindServlet1 class to process the paging request with the following key code:

 PackageCom.cn.gao;Importjava.awt.print.Pageable;Importjava.io.IOException;ImportJava.io.PrintWriter;Importjava.util.List;Importjavax.servlet.ServletException;ImportJavax.servlet.http.HttpServlet;Importjavax.servlet.http.HttpServletRequest;ImportJavax.servlet.http.HttpServletResponse; Public classFindServlet1extendsHttpServlet { Public voiddoget (httpservletrequest request, httpservletresponse response)throwsservletexception, IOException {intCurrpage=1; if(Request.getparameter ("page")! =NULL) {Currpage=integer.parseint (Request.getparameter ("page")); } Productdao DAO=NewProductdao (); List<Product> list =Dao.find (currpage); Request.setattribute ("List", list); intpages;//Total Pages        intCount=dao.findcount ();//total number of records queried        if(count%product.page_size==0) {pages=count/product.page_size; }Else{pages=count/product.page_size+1; } stringbuffer SB=NewStringBuffer (); //Building a paging bar by looping         for(inti=1;i<=pages;i++){            if(I==currpage) {//determines whether the current pageSb.append ("" "+i+" "");//Building a paging bar}Else{sb.append ("<a href= ' findservlet1?page=" +i+ ">" +i+ "</a>");//Building a paging bar} sb.append (" "); } request.setattribute ("Bar", sb.tostring ());; Request.getrequestdispatcher ("Product_list.jsp"). Forward (request, response); }}

Tip: The paging bar is dynamic in the JSP page and is reconstructed every time a new page is viewed, so the page's construction in the instance is placed in the servlet to simplify the code for the JSP page.

After getting the query result set list and the pagination bar, FindServlet1 the two objects into the request, and forwards the request to the Product_list.jsp page to make the display.

(4) Create the product_list.jsp page, which displays the product information data by retrieving the query result set list and the paging bar. The key code is as follows:

<%@ page language= "java" contenttype= "text/html; charset=gb18030 "pageencoding= "GB18030"%><%@ pageImport= "java.util.*"%><%@ pageImport= "Com.cn.gao.*"%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >List<Product> list= (list<product>) request.getattribute ("list");  for(Product p:list) {%> <tr align= "center" > <td><%=p.getid ()%></td> <td><% =p.getname ()%></td> <td><%=p.getprice ()%></td> &LT;TD&GT;&LT;%=P.GETN Um ()%></td> <td><%=p.getunit ()%></td> </tr> <%             }          %> <tr> <td align= "center" colspan= "5" > <%=request.getattribute (" Bar ")%> </td> </tr> </table></body>
(5) Write the main page of the program showproduct.jsp, in this page to write a page to query the product information hyperlink, point to FindServlet1. The key code is as follows:
<%@ page language= "java" contenttype= "text/html; charset=gb18030 "    pageencoding=" GB18030 "%><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >
After the page is written, the deployment runs the project, and the Showproduct.jsp page opens with the effect as shown:
The application of JDBC in Java Web--paging query