JavaWeb使用动态sql实现分页查询以及条件查询功能

分页查询以及条件查询

技术栈

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>&nbsp;
                        <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());
    }