Paged Query information (use JDBC to connect to MySQL database for paging query tasks)

Paging Query information use jdbc to connect mysql database for paging query tasks through The paging mechanism provided by MySQL database, the paging query function of commodity information is realized, and the information of the query is displayed on the JSP page.

This project is a simple application of the Eclipse+jdbc+mysql applet.

the database name for the connection is Db_database11, and the properties are as follows:

1. Create a package named Com.pmf.bean , which is a class named product that encapsulates the product information.

The full code is as follows:

Package Com.pmf.bean;

/**

* Commercial goods

*

*/

Public class Product {

Public Static Final int page_size = 2;

Number

Private int ID;

Name

Private String name;

Price

Private double Price;

Number

Private int num;

Unit

Private String 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;

}

}

2. Create A class named "Bookdao" that isprimarily used to encapsulate related operations on commodity databases. In the Bookdao class, first write the getconnection () method, which is used to create the Connection object. Then create a paged query method for the product information find (), where The page parameter is used to pass the page number to be queried. The total number of records that are required to obtain information during a paging query is used to calculate the total pages of the product information. This method is written in the findcount () method.

The code is as follows:

Package Com.pmf.bean;

import java.sql.Connection;

import Java.sql.DriverManager;

import java.sql.PreparedStatement;

import Java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

/**

* Commodity database operation

*

*/

Public class Bookdao {

/**

* Get database connection

* @return Connection object

*/

Public Connection getconnection () {

Database connection

Connection conn = null;

Try {

Load database driver, register to drive manager

Class. forname ("Com.mysql.jdbc.Driver");

Database connection string

String url = "Jdbc:mysql://localhost:3306/db_database11";

Database user Name

String username = "root";

Database Password

String password = "123123";

Create a Connection connection

conn = DriverManager. getconnection (Url,username,password);

} catch (ClassNotFoundException e) {

E.printstacktrace ();

} catch (SQLException e) {

E.printstacktrace ();

}

Back to database connection

return Conn;

}

/**

* Check all product information by paging

* @param page pages

* @return list<product>

*/

Public list<product> Find (int page) {

Create List

list<product> list = new arraylist<product> ();

Get database connection

Connection conn = getconnection ();

SQL statements for paged queries

String sql = "SELECT * from Tb_product the ORDER by id desc limit?,?";

Try {

Get PreparedStatement

PreparedStatement PS = conn.preparestatement (SQL);

Assigning a value to the 1th parameter in an SQL statement

Ps.setint (1, (page-1) * Product. Page_size);

Assigning a value to the 2nd parameter in an SQL statement

Ps.setint (2, Product. Page_size);

Perform a query operation

ResultSet rs = Ps.executequery ();

The cursor moves backwards and determines if it is valid

while (Rs.next ()) {

Instantiate product

Product P = New product ();

Assigning a value to an ID property

P.setid (Rs.getint ("id"));

Assigning a value to the Name property

P.setname (rs.getstring ("name"));

Assigning a value to the num Property

P.setnum (rs.getint ("num"));

Assigning a value to the price property

P.setprice (rs.getdouble ("price"));

Assigning a value to the Unit property

P.setunit (rs.getstring ("unit"));

Add product to the list collection

List.add (P);

}

Close ResultSet

Rs.close ();

Close PreparedStatement

Ps.close ();

Close connection

Conn.close ();

} catch (SQLException e) {

E.printstacktrace ();

}

return list;

}

/**

* Query Total Record count

* Total number of @return Records

*/

Public int Findcount () {

Total Record Count

int count = 0;

Get database connection

Connection conn = getconnection ();

Query total number of records SQL statement

String sql = "SELECT COUNT (*) from tb_product";

Try {

Create statement

Statement stmt = Conn.createstatement ();

Query and get resultset

ResultSet rs = stmt.executequery (SQL);

The cursor moves backwards and determines if it is valid

if (Rs.next ()) {

Assign a value to the total number of records

Count = Rs.getint (1);

}

Close ResultSet

Rs.close ();

Close connection

Conn.close ();

} catch (SQLException e) {

E.printstacktrace ();

}

Returns the total number of records

return count;

}

}

3. create a class named "findservlet" in com.pmf.servlet . This class is a Servlet object that queries the commodity information by paging . In this class, write the doget () method to handle paging requests.

The code is as follows :

Package Com.pmf.servlet;

import java.io.IOException;

import java.util.List;

import javax.servlet.ServletException;

import Javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.pmf.bean.Product;

import Com.pmf.bean.BookDao;

/**

* Servlet Implementation class Findservlet

*/

Public class Findservlet extends httpservlet {

Private Static Final Long Serialversionuid = 1L;

protected void doget (httpservletrequest request, httpservletresponse response) throws Servletexception, IOException {

Current page number

int currpage = 1;

Determine if the pass page number is valid

if (Request.getparameter ("page")! = null) {

Assign a value to the current page number

Currpage = Integer. parseint (Request.getparameter ("page"));

}

Instantiate Productdao

Bookdao DAO = new Bookdao ();

Check all product information

list<product> list = Dao.find (currpage);

Place list in Request

Request.setattribute ("list", list);

Total pages

int pages;

Total number of records queried

int count = Dao.findcount ();

Calculate Total Pages

if (count% Product.) page_size = = 0) {

Assigning a total number of pages

Pages = Count/product. page_size;

}Else{

Assigning a total number of pages

Pages = Count/product. page_size + 1;

}

Instantiate StringBuffer

StringBuffer sb = new stringbuffer ();

Building a paging bar by looping

for (int i=1; i <= pages; i++) {

Determines whether the current page

if (i = = currpage) {

Building a paging bar

Sb.append ("" "+ i +" "");

}Else{

Building a paging bar

Sb.append ("<a href= ' findservlet?page=" + i + "' >" + i + "</a>");

}

Building a paging bar

Sb.append ("");

}

Place the string of the pagination bar into the request

Request.setattribute ("Bar", sb.tostring ());

Forward to product_list.jsp page

Request.getrequestdispatcher ("product_list.jsp"). Forward (request, response);

}

}

4. Create A product_list.jsp page that displays the data of the product by retrieving the query results list and pagination bar.

The code is as follows:

<%@ page language="java" contenttype="text/html; Charset=utf-8 "

pageencoding="UTF-8"%>

<! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "HTTP://WWW.W3.ORG/TR/HTML4/LOOSE.DTD" >

<% @page import="java.util.List"%>

<% @page import="com. PMF . Bean. Product "%>

<meta http-equiv="Content-type" content= "text/html; Charset=utf-8 ">

<title> All product information </title>

<style type="Text/css">

TD {font-size: 12px;}

H2 {margin: 0px}

</style>

<body>

<table align="center" width=" border=" 1 " height=" bordercolor= " White " bgcolor=" Black " cellpadding=" 1 " cellspacing=" 1 ">

<TR bgcolor="White">

<TD align="center" colspan="5">

All product information

</td>

</tr>

<TR align="center" bgcolor="#e1ffc1" >

<td><b>ID</b></td>

<td><b> Product Name </b></td>

<td><b> Price </b></td>

<td><b> Quantity </b></td>

<td><b> units </b></td>

</tr>

<%

list<product> list = (list<product>) request.getattribute ("list");

for (Product p:list) {

%>

<TR align="center" bgcolor="White">

<td><%=p.getid ()%></td>

<td><%=p.getname ()%></td>

<td><%=p.getprice ()%></td>

<td><%=p.getnum ()%></td>

<td><%=p.getunit ()%></td>

</tr>

<%

}

%>

<tr>

<TD align="center" colspan="5" bgcolor="White">

<%=request.getattribute ("Bar")%>

</td>

</tr>

</table>

</body>

5. write the main page of the program index.jsp, where you write a paginated query for product information to the hyperlink to Findservlet.

The code is as follows:

<%@ page language="java" contenttype="text/html; Charset=utf-8 "

pageencoding="UTF-8"%>

<! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" "HTTP://WWW.W3.ORG/TR/HTML4/LOOSE.DTD" >

<meta http-equiv="Content-type" content= "text/html; Charset=utf-8 ">

<title>insert title here</title>

<body>

<a href="Findservlet"> View all product information </a>

</body>

 

Paged Query information (use JDBC to connect to MySQL database for paging query tasks)