mysql 分页查询web_java web入门到精通java调用mysql分页查询

java web入门到精通java调用mysql分页查询

1、效果图

2、后台代码

2.1 Product.java

package com.lyq.bean;

/**

* 商品

* @author whh

*

*/

public class Product {

public static final int PAGE_SIZE = 2;

// 编号

private int id;

// 名称

private String name;

// 价格

private double price;

// 数量

private int num;

// 单位

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.2 ProductDao

package com.lyq.bean;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.mysql.jdbc.Statement;

public class ProductDao {

public static void main(String[] args) {

List listProducts=new ProductDao().find(1);

}

public Connection getConnection() {

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/hh";

String username = "root";

String password = "root";

conn = DriverManager.getConnection(url, username, password);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

public List find(int page){

List list=new ArrayList();

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);

}

rs.close();

ps.close();

conn.close();

}

catch(SQLException e){

e.printStackTrace();

}

return list;

}

public int findCount(){

int count=0;

Connection conn=getConnection();

String sql="select count(*) from tb_product";

try{

Statement stmt=(Statement) conn.createStatement();

ResultSet rs=stmt.executeQuery(sql);

if(rs.next()){

count=rs.getInt(1);

}

rs.close();

conn.close();

}catch(SQLException e){

e.printStackTrace();

}

return count;

}

}

2.3 FindServlet

package com.lyq.servlet;

import java.io.IOException;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.lyq.bean.Product;

import com.lyq.bean.ProductDao;

/**

* Servlet implementation class FindServlet

*/

@WebServlet("/FindServlet")

public class FindServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

/**

* @see HttpServlet#HttpServlet()

*/

public FindServlet() {

super();

// TODO Auto-generated constructor stub

}

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

// 当前页码

int currPage = 1;

// 判断传递页码是否有效

if(request.getParameter("page") != null){

// 对当前页码赋值

currPage = Integer.parseInt(request.getParameter("page"));

}

// 实例化ProductDao

ProductDao dao = new ProductDao();

// 查询所有商品信息

List list = dao.find(currPage);

// 将list放置到request之中

request.setAttribute("list", list);

// 总页数

int pages ;

// 查询总记录数

int count = dao.findCount();

// 计算总页数

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

// 对总页数赋值

pages = count / Product.PAGE_SIZE;

}else{

// 对总页数赋值

pages = count / Product.PAGE_SIZE + 1;

}

// 实例化StringBuffer

StringBuffer sb = new StringBuffer();

// 通过循环构建分页条

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

// 判断是否为当前页

if(i == currPage){

// 构建分页条

sb.append("『" + i + "』");

}else{

// 构建分页条

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

}

// 构建分页条

sb.append("　");

}

// 将分页条的字符串放置到request之中

request.setAttribute("bar", sb.toString());

// 转发到product_list.jsp页面

request.getRequestDispatcher("product_list.jsp").forward(request, response);}

}

3、前台代码

3.1 index.jsp

pageEncoding="UTF-8"%>

主页

查看所有商品信息

3.2 product_list.jsp

pageEncoding="UTF-8"%>

所有商品信息

td{font-size: 12px;}

h2{margin: 0px}

所有商品信息
ID	商品名称	价格	数量	单位

List list = (List)request.getAttribute("list");

for(Product p : list){

%>

}

%>

3.3 web.xml

11.8

index.html

index.htm

index.jsp

default.html

default.htm

default.jsp

FindServlet

FindServlet

com.lyq.servlet.FindServlet

FindServlet

/FindServlet

总结：在写这个例子中遇到了一些问题，例如在走getconnection()的时候会报错。后来把mysql引入到Lib下就好了。