AO数据访问

package dao;
​
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
​
import utils.JdbcUtils;
import utils.PageBean;
import entity.Muli;
​
public class MuliDao {
 // 创建QueryRunner对象
 private QueryRunner qr = JdbcUtils.getQueryRuner();
​
 
 // 查找全部信息列表
 public List<Muli> listMuliAll() {
 String sql = "SELECT * FROM mulihuji ";
 try {
 // 多行数据，封装成对象的集合
 List<Muli> listmuli = qr.query(sql, new BeanListHandler<Muli>(Muli.class));
 return listmuli;
 } catch (Exception e) {
 throw new RuntimeException(e);
 }
 }
 
 // 分页显示全部数据
 public void listMuliAll(PageBean<Muli> pb,Muli muli) throws SQLException {
​
 String countsql = "select count(*) from mulihuji where 1=1";
 String sql = "select * from mulihuji where 1=1";
 
 List<Object> params = new ArrayList<Object>();
 // 添加查询条件
 String xm = muli.getXm();
 if (xm != null && xm != "") {
 countsql += " and xm like ?";
 sql += " and xm like ?";
 params.add("%" + xm + "%");
 }
 String sfzh = muli.getSfzh();
 if (sfzh != null && sfzh != "") {
 countsql += " and sfzh like ?";
 sql += " and sfzh like ?";
 params.add("%" + sfzh + "%");
 } 
 
 //得到总记录数 
 Long count = qr.query(countsql, new ScalarHandler<Long>(),params.toArray());
 pb.setTotalCount(count.intValue());
 /*
 * 问题： jsp页面，如果当前页为首页，再点击上一页报错！ 如果当前页为末页，再点下一页显示有问题！ 解决： 1. 如果当前页 <= 0;
 * 当前页设置当前页为1; 2. 如果当前页 > 最大页数； 当前页设置为最大页数
 */
 // 判断
 if (pb.getCurrentPage() <= 0) {
 pb.setCurrentPage(1); // 把当前页设置为1
 } else if (pb.getCurrentPage() > pb.getTotalPage()) {
 pb.setCurrentPage(pb.getTotalPage()); // 把当前页设置为最大页数
 }
​
 // 1. 获取当前页： 计算查询的起始行、返回的行数
 int currentPage = pb.getCurrentPage();
 int index = (currentPage - 1) * pb.getPageCount(); // 查询的起始行
 int pagecount = pb.getPageCount(); // 每页的行数
 
 
 sql += " limit ?,?";
 params.add(index);
 params.add(pagecount);
 
 List<Muli> pageData = qr.query(sql, new BeanListHandler<Muli>(Muli.class),params.toArray() ); //获取一页的数据
 // 设置到pb对象中
 pb.setPageData(pageData);
 
​
 }
​
 
}
​

Servlet设计

package servlet;
​
import java.io.IOException;
import java.sql.SQLException;
​
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 dao.MuliDao;
import entity.Muli;
import utils.PageBean;
​
/**
 * Servlet implementation class QueryListMuliServlet
 */
@WebServlet("/querymuli")
public class QueryListMuliServlet extends HttpServlet {
 private static final long serialVersionUID = 1L;
​
 /**
 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
 */
 
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 
 request.setCharacterEncoding("utf-8");
 response.setCharacterEncoding("utf-8");
 String xm = request.getParameter("xm");
 String sfzh = request.getParameter("sfzh");
 //实例化实体类并赋值 
 Muli muli=new Muli();
 muli.setXm(xm);
 muli.setSfzh(sfzh);
 
 //1. 获取“当前页”参数； (第一次访问当前页为null) 
 String currPage = request.getParameter("currentPage");
 // 判断
 if (currPage == null || "".equals(currPage.trim())){
 currPage = "1"; // 若第一次访问，设置当前页为1;
 }
 // 转换为整数型
 int currentPage = Integer.parseInt(currPage);
 //实例化pageben类
 PageBean<Muli> pb = new PageBean<Muli>();
 pb.setCurrentPage(currentPage);
 
 try {
 //调用数据访问的方法，传递两个参数
 MuliDao mulidao = new MuliDao();
 mulidao.listMuliAll(pb,muli);
 
 request.setAttribute("pageBean", pb);
 request.getRequestDispatcher("/querylist.jsp").forward(request, response);
 return;
 
 } catch (SQLException e) {
 e.printStackTrace();
 request.setAttribute("errorMsg", e.getMessage());
 }
 request.getRequestDispatcher("/error.jsp").forward(request, response);
 
 }
​
 /**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 */
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 // TODO Auto-generated method stub
 doGet(request, response);
 }
​
}

JSP页面：分为两个页面，1.提交查询条件，2.返回查询结果