第三周 | |
所花时间 | 15左右 |
代码量 | 700行左右 |
博客量 | 2篇 |
学到的知识点 | 可视化图表的使用、条件查询,分页 |
摘要、本周主要通过视频资源学习了条件查询和分页显示的一些操作,条件查询不是很难,通过获得的查询条件拼接sql语句即可,实现了通过姓名模糊查询,通过性别精确查找。分页操作比较难,通过编写了一个PageBean,提供了相关的构造方法。在给出的代码中有具体的解释
一、代码部分
1、分页功能的相关代码
PageBean
package entity; import java.util.ArrayList; import java.util.List; public class PageBean<T> { //当前页 private int currentPage; //当前页显示的条数 private int currentCount; //总条数 private int totalCount; //总页数 private int totalPage; //每页显示的数据 private List<T> list = new ArrayList<T>(); public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getCurrentCount() { return currentCount; } public void setCurrentCount(int currentCount) { this.currentCount = currentCount; } public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public List<T> getList() { return list; } public void setList(List<T> list) { this.list = list; } }
Servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); PageService service = new PageService(); // 模拟当前是第一页 String currentPageStr = request.getParameter("currentPage"); if (currentPageStr == null) currentPageStr = "1"; int currentPage = Integer.parseInt(currentPageStr); // 认为每页显示2条 int currentCount = 2; PageBean<User> pageBean = null; pageBean = service.findPageBean(currentPage, currentCount); request.setAttribute("pageBean", pageBean); request.getRequestDispatcher("all.jsp").forward(request, response); }
service
package service; import java.util.ArrayList; import dao.PageDao; import entity.PageBean; import entity.User; public class PageService { PageDao dao=new PageDao(); public PageBean<User> findPageBean(int currentPage, int currentCount) { //目的:就是想办法封装一个PageBean 并返回 PageBean pageBean = new PageBean(); //1、当前页private int currentPage; pageBean.setCurrentPage(currentPage); //2、当前页显示的条数private int currentCount; pageBean.setCurrentCount(currentCount); //3、总条数private int totalCount; int totalCount = dao.getTotalCount(); System.out.println(totalCount); pageBean.setTotalCount(totalCount); //4、总页数private int totalPage; /* * 总条数 当前页显示的条数 总页数 * 10 4 3 * 11 4 3 * 12 4 3 * 13 4 4 * * 公式:总页数=Math.ceil(总条数/当前显示的条数) * */ int totalPage = (int) Math.ceil(1.0*totalCount/currentCount); pageBean.setTotalPage(totalPage); //5、每页显示的数据private List<T> productList = new ArrayList<T>(); /* * 页数与limit起始索引的关系 * 例如 每页显示4条 * 页数 其实索引 每页显示条数 * 1 0 4 * 2 4 4 * 3 8 4 * 4 12 4 * * 索引index = (当前页数-1)*每页显示的条数 * */ int index = (currentPage-1)*currentCount; ArrayList<User> findlist = dao.findlist(index,currentCount); pageBean.setList(findlist); System.out.println("66"); return pageBean; } }
dao
public int getTotalCount() { // TODO 自动生成的方法存根 int ans = 0; String sql = "select * from user"; Connection con = null; Statement state = null; ResultSet rs = null; con = DBUtil.getConn(); try { state = con.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { ans++; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBUtil.close(rs, state, con); return ans; } public ArrayList<User> findlist(int index, int currentCount) { // TODO 自动生成的方法存根 ArrayList<User> list = new ArrayList<>(); String sql = "select * from user limit ?,?"; Connection con = null; Statement state = null; PreparedStatement ps; con = DBUtil.getConn(); ResultSet rs1 = null; try { ps = con.prepareStatement(sql); ps.setInt(1, index); ps.setInt(2, currentCount); rs1 = ps.executeQuery(); } catch (SQLException e1) { // TODO 自动生成的 catch 块 e1.printStackTrace(); } User bean = null; try { while (rs1.next()) { String name = rs1.getString("name"); String sex = rs1.getString("sex"); String school = rs1.getString("school"); String adress = rs1.getString("address"); bean = new User(name, sex, school, adress); list.add(bean); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBUtil.close(rs1, state, con); return list; }
2、条件查询的相关代码
servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub request.setCharacterEncoding("utf-8"); String name = request.getParameter("name"); String sex = request.getParameter("sex"); String adress = request.getParameter("adress"); // System.out.println(name); // System.out.println(sex); User bean = new User(name, sex, adress); PageBean<User> pageBean = null; UserService service = new UserService(); pageBean = service.find(bean); request.setAttribute("pageBean", pageBean); request.getRequestDispatcher("all.jsp").forward(request, response); }
dao
public ArrayList<User> find(User bean) { ArrayList<User> list = new ArrayList<>(); String sql = "select * from user where "; if ((bean.getName() != null && !bean.getName().trim().equals("")) && (bean.getSex() != null && !bean.getSex().trim().equals(""))) { sql += "name like '%" + bean.getName() + "%' and sex like '%" + bean.getSex() + "%'"; } else if (bean.getName() != "") { sql += "name like '%" + bean.getName() + "%'"; } else if (bean.getSex() != "") { sql += "sex ='" + bean.getSex() + "'"; } if (bean.getName() == "" && bean.getSex() == "") { sql = "select * from user"; } Connection con = null; Statement state = null; ResultSet rs = null; con = DBUtil.getConn(); User beans = null; try { state = con.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String name = rs.getString("name"); String sex = rs.getString("sex"); String school = rs.getString("school"); String adress = rs.getString("address"); beans = new User(name, sex, school, adress); list.add(beans); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBUtil.close(rs, state, con); return list; }