条件查询
dao
//根据搜索条件筛选数据 public List<User> GetUserBySearch(String userName, String sex) throws SQLException { String sql = "select * from User where 1=1 "; List<String> params = new ArrayList<String>(); if (userName != null && userName != "") { sql += " and UserName like ?"; params.add("%"+userName+"%"); } if (sex != null && sex != "") { sql += " and sex = ?"; params.add(sex); } QueryRunner qr = new QueryRunner(DBUtil.GetDataSource()); List<User> users = qr.query(sql, new BeanListHandler<User>(User.class), params.toArray()); return users; }
service
public List<User> GetUserBySearch(String userName,String sex) throws SQLException { UserDao dao = new UserDao(); return dao.GetUserBySearch(userName,sex); }
userList servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String userName = request.getParameter("userName"); String sex = request.getParameter("sex"); UserService service = new UserService(); List<User> userList = null; try { //userList = service.GetAllService(); userList = service.GetUserBySearch(userName,sex); } catch (SQLException e) { e.printStackTrace(); } request.setAttribute("userName",userName); request.setAttribute("sex",sex); request.setAttribute("userList", userList); request.getRequestDispatcher("userList.jsp").forward(request,response); }
userList.jsp
<%@ page import="com.david.domain.User" %> <%@ page import="java.util.List" %> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>用户列表</title> </head> <body> <div> 按用户名称搜索：<input id="txtUserName" value="${userName}"> 按用户性别查找：<select id="selSex"> <option value="">全部</option> <option value="男">男</option> <option value="女">女</option> </select> <button type="button" onclick="search()">搜索</button> </div> <script> //设置默认值 window.onload = function (){ for(var i = 0;i<document.getElementById("selSex").options.length;i++){ var option = document.getElementById("selSex").options[i]; if(option.value == "${sex}"){ option.selected = true; } } } function search(){ var userName = document.getElementById("txtUserName").value; var sex = ""; for(var i = 0;i<document.getElementById("selSex").options.length;i++){ if(document.getElementById("selSex").options[i].selected){ sex = document.getElementById("selSex").options[i].value; } } location.href='userList?userName='+userName+"&sex="+sex; } </script> <table> <tr> <th>用户ID</th> <th>用户名</th> <th>用户密码</th> <th>用户年龄</th> <th>用户性别</th> <th>操作</th> </tr> <% if (request.getAttribute("userList") != null) { %> <%for (User u : (List<User>) request.getAttribute("userList")) {%> <tr> <td><%=u.getUserId()%> </td> <td><%=u.getUserName()%> </td> <td><%=u.getPassWord()%> </td> <td><%=u.getAge()%> </td> <td><%=u.getSex()%> </td> <td> <a href="GetUserById?userId=<%=u.getUserId()%>">修改</a> <a href="DeleteUser?userId=<%=u.getUserId()%>">删除</a> </td> </tr> <%}%> <%}%> </table> <a href="AddUser.jsp">添加用户</a> </body> </html>
列表分页
在domain中创建pageBean实体
package com.david.domain; import java.util.List; public class PageBean<T> { //当前页 private int curPage; //当前显示条数 private int pageSize; //总页数 private int totalPage; //总条数 private int totalCount; //展示的数据 private List<T> Data; public int getCurPage() { return curPage; } public int getPageSize() { return pageSize; } public int getTotalPage() { return totalPage; } public int getTotalCount() { return totalCount; } public List<T> getData() { return Data; } public void setCurPage(int curPage) { this.curPage = curPage; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public void setData(List<T> data) { Data = data; } }
在dao中 创建分页方法
//获取总条数 public int GetUserCount(String userName, String sex) throws SQLException { String sql = "select count(*) from User where 1=1 "; List<String> params = new ArrayList<String>(); if (userName != null && userName != "") { sql += " and UserName like ?"; params.add("%" + userName + "%"); } if (sex != null && sex != "") { sql += " and sex = ?"; params.add(sex); } QueryRunner qr = new QueryRunner(DBUtil.GetDataSource()); long count = (long)qr.query(sql,new ScalarHandler(),params.toArray()); return (int)count; } public List<User> GetUserListForPageBean(String userName, String sex, int page, int pageSize) throws SQLException { String sql = "select * from User where 1=1 "; List<Object> params = new ArrayList<Object>(); if (userName != null && userName != "") { sql += " and UserName like ?"; params.add("%" + userName + "%"); } if (sex != null && sex != "") { sql += " and sex = ?"; params.add(sex); } sql += " limit ?,?"; page = (page - 1) * pageSize; params.add(page); params.add(pageSize); QueryRunner qr = new QueryRunner(DBUtil.GetDataSource()); List<User> users = qr.query(sql, new BeanListHandler<User>(User.class), params.toArray()); return users; }
service
public PageBean<User> GetUserListForPageBean(String userName, String sex, int page, int pageSize) throws SQLException { UserDao dao = new UserDao(); PageBean pageBean = new PageBean(); pageBean.setCurPage(page); pageBean.setPageSize(pageSize); int totalCount = dao.GetUserCount(userName, sex); pageBean.setTotalCount(totalCount); int totalPage = (int) Math.ceil(1.0 * totalCount / pageSize); pageBean.setTotalPage(totalPage); List<User> data = dao.GetUserListForPageBean(userName,sex,page,pageSize); pageBean.setData(data); return pageBean; }
UserListForPage servlet
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String userName = request.getParameter("userName"); String sex = request.getParameter("sex"); String page = request.getParameter("page"); int curPage = 1; if(page != null && page != ""){ curPage = Integer.parseInt(page); } int pageSize = 5; UserService service = new UserService(); PageBean<User> pageBean = null; try { pageBean = service.GetUserListForPageBean(userName,sex,curPage,pageSize); } catch (SQLException e) { e.printStackTrace(); } request.setAttribute("userName",userName); request.setAttribute("sex",sex); request.setAttribute("pageBean", pageBean); request.getRequestDispatcher("userListForPage.jsp").forward(request,response); }
web.xml
<servlet> <servlet-name>UserListForPage</servlet-name> <servlet-class>com.david.web.UserListForPage</servlet-class> </servlet> <servlet-mapping> <servlet-name>UserListForPage</servlet-name> <url-pattern>/userListForPage</url-pattern> </servlet-mapping>
userListForPage.jsp
<%@ page import="com.david.domain.User" %> <%@ page import="java.util.List" %> <%@ page import="com.david.domain.PageBean" %> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>用户列表</title> </head> <body> <div> 按用户名称搜索：<input id="txtUserName" value="${userName}"> 按用户性别查找：<select id="selSex"> <option value="">全部</option> <option value="男">男</option> <option value="女">女</option> </select> <button type="button" onclick="search()">搜索</button> <a href="AddUser.jsp">添加用户</a> </div> <script> //设置默认值 window.onload = function (){ for(var i = 0;i<document.getElementById("selSex").options.length;i++){ var option = document.getElementById("selSex").options[i]; if(option.value == "${sex}"){ option.selected = true; } } } function search(){ var userName = document.getElementById("txtUserName").value; var sex = ""; for(var i = 0;i<document.getElementById("selSex").options.length;i++){ if(document.getElementById("selSex").options[i].selected){ sex = document.getElementById("selSex").options[i].value; } } location.href='userList?userName='+userName+"&sex="+sex; } </script> <table> <tr> <th>用户ID</th> <th>用户名</th> <th>用户密码</th> <th>用户年龄</th> <th>用户性别</th> <th>操作</th> </tr> <% PageBean pageBean = (PageBean)request.getAttribute("pageBean"); if (pageBean != null && pageBean.getData() != null) { %> <%for (User u : (List<User>)pageBean.getData()) {%> <tr> <td><%=u.getUserId()%> </td> <td><%=u.getUserName()%> </td> <td><%=u.getPassWord()%> </td> <td><%=u.getAge()%> </td> <td><%=u.getSex()%> </td> <td> <a href="GetUserById?userId=<%=u.getUserId()%>">修改</a> <a href="DeleteUser?userId=<%=u.getUserId()%>">删除</a> </td> </tr> <%}%> <%}%> </table> <div class="page"> <%if(pageBean.getCurPage() != 1){%> <a href="?page=1">首页</a> <a href="?page=<%=pageBean.getCurPage()-1%>">上一页</a> <%}%> <%for(int i = 1;i<=pageBean.getTotalPage();i++){ %> <%if(pageBean.getCurPage() == i){%> <a href="?page=<%=i%>" class="currentPage"><%=i%></a> <%}else{%> <a href="?page=<%=i%>"><%=i%></a> <%}%> <%}%> <%if(pageBean.getCurPage() != pageBean.getTotalPage()){%> <a href="?page=<%=pageBean.getCurPage()+1%>">下一页</a> <a href="?page=<%=pageBean.getTotalPage()%>">尾页</a> <%}%> 共<%=pageBean.getTotalCount()%>条数据，<%=pageBean.getTotalPage()%>页。 </div> <style> .page a{ font-size:12px; text-decoration: none; color:#ccc; } .page .currentPage{ color:#000; font-size:20px; } </style> </body> </html>