The application of JDBC in Java Web--paging query

This article turns from http://www.cnblogs.com/gaopeng527/p/4448105.html thanks to the author 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:

Package Com.cn.gao;public class Product {public    static final int page_size=2;//per page record number    private int id;            Number    private String name;      Name    private double price;    Price    private int num;        Quantity    private String unit;   Unit public    int getId () {        return ID;    }    public void setId (int id) {        this.id = ID;    }    Public String GetName () {        return name;    }    public void SetName (String name) {        this.name = name;    }    Public double GetPrice () {        return price;    }    public void Setprice (double price) {        This.price = Price;    }    public int Getnum () {        return num;    }    public void setnum (int num) {        this.num = num;    }    Public String Getunit () {        return unit;    }    public void Setunit (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     * @return Connection Object     *    /Public Connection 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 block            e.printstacktrace ();        } catch ( SQLException e) {            //TODO auto-generated catch block            e.printstacktrace ();        }        return conn;    }

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 Query all product Information * @param page pages * @return list<product> */public list<product> find (int        Page) {list<product> List = new arraylist<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=new product ();                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 block       E.printstacktrace ();    } return list; }

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:

/**     * Total number of records     * @return Total Records * * * public    int Findcount () {        int count=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 value to total records            }            rs.close ();            Conn.close ();        } catch (SQLException e) {            //TODO auto-generated catch block            e.printstacktrace ();        }        return count;        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:

Package Com.cn.gao;import Java.awt.print.pageable;import Java.io.ioexception;import java.io.printwriter;import Java.util.list;import Javax.servlet.servletexception;import Javax.servlet.http.httpservlet;import Javax.servlet.http.httpservletrequest;import Javax.servlet.http.httpservletresponse;public class FindServlet1 Extends HttpServlet {public void doget (HttpServletRequest request, httpservletresponse response) throws Ser        Vletexception, IOException {int currpage=1;        if (Request.getparameter ("page")!=null) {Currpage=integer.parseint (Request.getparameter ("page"));        } Productdao DAO = new Productdao ();        list<product> list = Dao.find (currpage);        Request.setattribute ("list", list);  int pages; Total pages int count=dao.findcount ();        Query total number of records if (count%product.page_size==0) {pages=count/product.page_size;        }else{pages=count/product.page_size+1; } stringbuffer SB = new StringBuffer (); Constructs a paging bar for (int i=1;i<=pages;i++) {if (i==currpage) {//To determine whether the current page sb.append ("" "+i+") by looping  』");            Build the paging bar}else{sb.append ("<a href= ' findservlet1?page=" +i+ "' >" +i+ "</a>");//build Pagination 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 "%><%@ page import=" java.util.* "%><%@ page import=" com.cn.gao.* "% ><! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "Http://www.w3.org/TR/html4/loose.dtd" >
(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