javaWeb中分页和模糊查询

一、本文采用的是三层架构写的一个商品分页和模糊查询 工具是idea 适合新人参考学习。 需要准备好三个jar包： mysql驱动包，jstl.jar以及依赖的standard.jar。

项目主要结构：

具体代码如下：

实例类Product：

package com.pojo;

import java.io.Serializable;

public class Product implements Serializable {//本类可以序列化
    private int product_id;//产品id
    private String product_name;//产品信息
    private double price;//产品价格
    private String info;//产品信息

    public int getProduct_id() {
        return product_id;
    }

    public void setProduct_id(int product_id) {
        this.product_id = product_id;
    }

    public String getProduct_name() {
        return product_name;
    }

    public void setProduct_name(String product_name) {
        this.product_name = product_name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }
}

连接类：

package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

//数据库连接类封装
public class DBConnection {
    private static  final  String dbDriver="com.mysql.jdbc.Driver";//mysql驱动
    private static  final  String URL="jdbc:mysql://localhost:3306/product";//数据访问地址
    private static  final  String US="root";//数据库账号
    private static  final  String PW="123456";//数据库密码
    private Connection conn=null;//连接对象

    public DBConnection() throws Exception{//通过构造方法进行数据库连接
        try {
            Class.forName(dbDriver);//用反射机制加载驱动
            this.conn= DriverManager.getConnection(URL,US,PW);//获取数据库连接
        }catch (ClassNotFoundException e){
            System.out.println("加载数据库驱动时找不到类异常："+e.getMessage());
        }catch (SQLException e){
            System.out.println("获取数据库连接时异常："+e.getMessage());
        }catch (Exception e){
            throw e;
        }
    }

    public Connection getConn(){//返回数据连接对象
        return this.conn;
    }
    public  void close() throws Exception{//关闭连接对象
        try {
            if(conn!=null){conn.close();}
        }catch (Exception e){
            throw  e;
        }
    }
}

dao层：

package com.dao;

import com.pojo.Product;

import java.util.ArrayList;

public interface Userdao {
    //通过Product满足条件，获取总记录条数
    public  int getCout(String tj) throws Exception;
    //    通过每页显示计算出总页数
    public int getPoductCount(int pageNum, String tj) throws Exception;

    //    通过当前页码，每页显示记录条数，和用户查询条件显示对应的页码所有信息
    public ArrayList<Product> getProduct(int currPage, int pageNum, String tj, String product_name) throws Exception;
}

package com.dao;

import com.pojo.Product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
//数据访问层
public class UserdaoImpl implements Userdao {
    private Connection conn=null;//数据库连接对象
    private PreparedStatement pstmt=null;//sql执行对象
    private ResultSet rs=null;//结果集对象
//    通过构造函数获取数据库连接
    public  UserdaoImpl(Connection conn){
        this.conn=conn;
    }

    public int getCout(String tj) throws Exception {
        int count=0;//定义一个标识，表示总记录条数
        String sql="select * from product where ? ";
        this.pstmt=this.conn.prepareStatement(sql);//预加载sql
        this.pstmt.setString(1,tj);
        rs=this.pstmt.executeQuery();
        while (rs.next()){//如果还有数据，就迭代出每一行记录
            count++;
        }
        this.pstmt.close();
        return count;
    }


    public int getPoductCount(int pageNum, String tj) throws Exception {
//        通过每页显示的条数和总记录条数获取--总页数
        int allcout=this.getCout(tj);//总记录条数
        int coutPage=allcout/pageNum;//总页数
        if(allcout/pageNum !=0){//如果总页数趋于不等于0，则整页加1
            coutPage++;
        }
        return coutPage;
    }


    public ArrayList<Product> getProduct(int currPage, int pageNum, String tj, String product_name) throws Exception {
        ArrayList<Product> list=new ArrayList<>();
        String sql="select * from product where ? ";
        if(product_name!=null && product_name!="" && !"".equals(product_name)){
            sql+="and product_name like ? order by product_id  limit ?,?";
            this.pstmt=this.conn.prepareStatement(sql);
            this.pstmt.setString(1,tj);
            this.pstmt.setString(2,"%"+product_name+"%");
            this.pstmt.setInt(3,(currPage-1)*pageNum);
            this.pstmt.setInt(4,pageNum);

        }else {
            sql+="order by product_id limit ?,?";
            this.pstmt=this.conn.prepareStatement(sql);
            this.pstmt.setString(1,tj);
            this.pstmt.setInt(2,(currPage-1)*pageNum);
            this.pstmt.setInt(3,pageNum);
        }
        rs=this.pstmt.executeQuery();
        while (rs.next()){
            Product p=new Product();
            p.setProduct_id(rs.getInt(1));
            p.setProduct_name(rs.getString(2));
            p.setPrice(rs.getDouble(3));
            p.setInfo(rs.getString(4));
            list.add(p);
        }
        this.pstmt.close();
        return list;
    }
}

service层：

package com.service;

import com.dao.Userdao;
import com.dao.UserdaoImpl;
import com.db.DBConnection;
import com.pojo.Product;

import java.util.ArrayList;
//业务逻辑层
public class UserService implements Userdao {
    private DBConnection dbconn=null;//定义数据库连接类
    private Userdao dao=null;//定义DAO
//    在构造方法中实例化数据库连接，同时实例化Userdao的实现类
    public UserService() throws Exception{
        this.dbconn=new DBConnection();
        this.dao=new UserdaoImpl(this.dbconn.getConn());
    }
    //计算得到总记录条数
    public int getCout(String tj) throws Exception {
        int n=0;
        try {
            n=this.dao.getCout(tj);
        }catch (Exception e){
            throw e;
        }finally {
            this.dbconn.close();
        }
        return n;
    }

    //计算得到总页数
    public int getPoductCount(int pageNum, String tj) throws Exception {
        int allcout=0;
        try {
            allcout=this.dao.getPoductCount(pageNum,tj);
        }catch (Exception e){
            throw  e;
        }finally {
            this.dbconn.close();
        }
        return allcout;
    }

    //显示数据
    public ArrayList<Product> getProduct(int currPage, int pageNum, String tj, String product_name) throws Exception {
        ArrayList<Product> list=null;
        try {
            if(currPage>0){
                list=this.dao.getProduct(currPage,pageNum,tj,product_name);
            }
        }catch (Exception e){
            throw e;
        }finally {
            this.dbconn.close();
        }
        return list;
    }
}

Factory层：

package com.factory;

import com.dao.Userdao;
import com.service.UserService;
//工厂类
public class DAOFactory {
    public static Userdao getProuduntMap() throws  Exception{
        return  new UserService();
    }
}

Servlet类：

package com.servlet;

import java.io.IOException;
import java.util.ArrayList;

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.factory.DAOFactory;
import com.pojo.Product;


@WebServlet(name="GoodsList",urlPatterns="/GoodsList")
public class GoodsList extends HttpServlet{
	
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doPost(req, resp);
	}
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		req.setCharacterEncoding("utf-8");
		ArrayList<Product> list;
		try {
			String tj = "1=1";
	   		int pageNum = 3;
			String emPage = req.getParameter("currpage");
			String product_name=req.getParameter("product_name");
	   		int currpage = 1;
			if(emPage==null){
				currpage=1;
			}else{
				currpage=Integer.parseInt(emPage);
			}
			list = DAOFactory.getProuduntMap().getProduct(currpage,pageNum,tj,product_name);
			int n=DAOFactory.getProuduntMap().getPoductCount(pageNum, tj);
			req.setAttribute("list", list);
			req.setAttribute("n", n);
			req.setAttribute("currpage", currpage);
			req.setAttribute("totalCount",list.size());
			req.getRequestDispatcher("list.jsp").forward(req, resp);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
}

Jsp页面：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'list.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
   <body>
   <form action="GoodsList">
	   <input type="text" name="product_name"/>
	   <input type="submit" value="搜索"/>
   </form>
   <form action="GoodsList">
  	<table border="1">
		<tr>
   	 			<th>商品编码</th>
		   	 	<th>商品名称</th>
		   	 	<th>商品价格</th>
		   	 	<th>商品信息</th>
   	 	</tr> 	
   	 	<c:forEach items="${requestScope.list }" var="product">
   	 		<tr>
   	 			<td>${product.product_id}</td>
   	 			<td>${product.product_name }</td>
   	 			<td>${product.price}</td>
				<td>${product.info}</td>

			</tr>
   	 	</c:forEach>
   	 	<tr>
   	 		<td colspan="4">
   	 			当前${currpage }页/${n}页
   	 			<a href="GoodsList?currpage=1">首页</a>
   	 		
   	 			<a href="GoodsList?currpage=${currpage-1<=1?1:currpage-1}">上一页</a>
   	 		
   	 			<a href="GoodsList?currpage=${currpage+1>n?n:currpage+1}">下一页</a>
   	 		
   	 			<a href="GoodsList?currpage=${n}">尾页</a>
   	 			跳转到
   	 			<input type="number" min="1" max="${n}" name="currpage" style="width:50px" />
   	 			/${n }页
   	 			<input type="submit" value="GO">
   	 		</td>
   	 	</tr>
  	</table>
  </form>
  </body>
</html>

显示结果：