分页查询以及条件查询
技术栈
druid、mysql、JdbcTemplate、Servlet、Jsp、Filter等
效果展示
分页查询效果展示
条件查询效果展示
代码展示
部分展示列表的jsp代码(list.jsp)
<c:forEach items="${pb.list}" var="user" varStatus="s" >
<tr>
<td><input type="checkbox" name="uid" value="${user.id}"></td>
<td>${s.count}</td>
<td>${user.name}</td>
<td>${user.gender}</td>
<td>${user.age}</td>
<td>${user.address}</td>
<td>${user.qq}</td>
<td>${user.email}</td>
<td><a class="btn btn-default btn-sm" href="${pageContext.request.contextPath}/update.jsp?id=${user.id}">修改</a>
<a class="btn btn-default btn-sm" href="javascript:deleteUser(${user.id});">删除</a></td>
</tr>
</c:forEach>
findUserByPageServlet
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
HttpSession session =request.getSession();
if(session.getAttribute("login").equals("true")){
//1.获取参数
String currentPage = request.getParameter("currentPage");//当前页码
String rows = request.getParameter("rows");//每页显示的条数
if(currentPage == null || "".equals(currentPage)){
currentPage = "1";
}
if(rows == null || "".equals(rows)){
rows = "5";
}
if(Integer.parseInt(currentPage) <= 0){
currentPage = "1";
}
//获取条件查询参数
Map<String, String[]> condition = request.getParameterMap();
//2.调用service查询
UserService service = new UserServiceImpl();
PageBean<User> pb = service.findUserByPage(currentPage,rows,condition);
//3
request.setAttribute("pb",pb);
request.setAttribute("condition",condition);//将查询条件存入request
//4.转发到list.jsp
request.getRequestDispatcher("/list.jsp").forward(request,response);
}else {
request.getRequestDispatcher("/login.jsp").forward(request,response);
}
分页查询以及条件查询的动态sql语句的编写(UserDaoImpl)
public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
String sql = "select * from user2 where 1 = 1 ";
StringBuilder sb = new StringBuilder(sql);
//2.遍历map
Set<String> keySet = condition.keySet();
//定义参数的集合
List<Object> params = new ArrayList<Object>();
for(String key : keySet){
//排除分页条件参数
if("currentPage".equals(key) || "rows".equals(key)){
continue;
}
//获取value
String value = condition.get(key)[0];
//判断value是否有值
if(value != null && !"".equals(value)){
//有值
sb.append(" and "+ key +" like ? ");
params.add("%" + value + "%");
}
}
//添加分页查询
sb.append(" limit ?,? ");
params.add(start);
params.add(rows);
sql = sb.toString();
return template.query(sql,new BeanPropertyRowMapper<User>(User.class),params.toArray());
}