JavaWeb中实现带条件的分页查询步骤

前言：

因为条件是一个组合的条件，是动态的，可以有，也可以没有，可以是一个，也可以是多个。所以条件的添加是动态的过程。

第一步

对条件进行封装，例如下面的例子是为了实现对用户进行带年龄，姓名，地址的组合条件筛查

封装如下：

public class UserCondition {
    private String name;
    private String address;
    private Integer startAge;
    private Integer endAge;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Integer getStartAge() {
        return startAge;
    }

    public void setStartAge(Integer startAge) {
        this.startAge = startAge;
    }

    public Integer getEndAge() {
        return endAge;
    }

    public void setEndAge(Integer endAge) {
        this.endAge = endAge;
    }

    public UserCondition() {
    }

    public UserCondition(String name, String address, Integer startAge, Integer endAge) {
        this.name = name;
        this.address = address;
        this.startAge = startAge;
        this.endAge = endAge;
    }
}

第二步：前端页面中设置要传递的值

 <%
                UserCondition condition = (UserCondition)request.getAttribute("condition");
                String name ="";
                String address="";
                String startAge="";
                String endAge="";
                if(condition != null){
                    if(condition.getName() != null ){
                        name = condition.getName();
                    }
                    if(condition.getAddress() != null ){
                        address = condition.getAddress();
                    }
                    if(condition.getStartAge() != null ){
                        startAge = condition.getStartAge().toString();
                    }
                    if(condition.getEndAge() != null ){
                        endAge = condition.getEndAge().toString();
                    }
                }
            %>
            <!--隐藏域-->
            <input type="hidden" name="pageIndex" value="1" id="pI">
            <div class="form-group">
                <label for="exampleInputName2">姓名</label>
                <input value="<%=name%>" type="text" name="name" class="form-control" id="exampleInputName2" >
            </div>
            <div class="form-group">
                <label for="exampleInputName3">籍贯</label>
                <input type="text" value="<%=address%>" name="address" class="form-control" id="exampleInputName3" >
            </div>

            <div class="form-group">
                <label>年龄</label>
                <input type="number"  name="startAge" class="form-control" value="<%=startAge%>"> -
                <input type="number"  name="endAge" class="form-control" value="<%=endAge%>">
            </div>

第三步：后端代码对前端传过来的数据，进行读取和处理

        String name = request.getParameter("name").trim();
        String address = request.getParameter("address").trim();
        String startAgeStr = request.getParameter("startAge").trim();
        Integer startAge = null;
        if(!(startAgeStr == null ||startAgeStr.isEmpty()) ){
            startAge = Integer.parseInt(startAgeStr);
        }

        String endAgeStr = request.getParameter("endAge").trim();
        Integer endAge = null;
        if(!(endAgeStr == null ||endAgeStr.isEmpty()) ){
            endAge = Integer.parseInt(endAgeStr);
        }
        //把参数封装成条件对象
        UserCondition userCondition = new UserCondition(name,address,startAge,endAge);

        //================
        //3.调用业务层类的方法
        UserService userService = new UserServiceImpl();
        //Page<User> page =  userService.queryByPage(pageIndex,pageSize);

        Page<User> page =  userService.queryPageByCondition(pageIndex,pageSize,userCondition);
        //把数据存放在 request域
        request.setAttribute("page",page);
        //把条件对象保存到request域
        request.setAttribute("condition",userCondition);

        //4.根据3的结果 跳转页面  请求转发
        //list.html 变成list.jsp   html不是java技术,无法访问域, 变成jsp
        request.getRequestDispatcher("/list.jsp").forward(request,response);

第四步 Dao实现SQL语句实现对数据的查询

public List<User> selectPage(int pageIndex, int pageSize, UserCondition userCondition) {
       Connection conn=null;
        try {
            conn=JdbcUtil.getConnection();
            QueryRunner queryRunner = new QueryRunner();
//            sql拼接，拼接条件
           StringBuffer sql=new StringBuffer("select * from  tb_userinfo where i=1");
            ArrayList<Object> paramList = new ArrayList<>();
            joinSql(userCondition,sql,paramList);
//            动态排序，拓展
            sql.append("limit?,?");
            paramList.add((pageIndex-1)*pageSize);
            paramList.add(pageSize);

//            把List转换成数组
            Object[] params = paramList.toArray();
            return  queryRunner.query(conn,sql.toString(),new BeanListHandler<User>(User.class),params);


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JdbcUtil.close(conn);
        }
        return null;
    }

    private void joinSql(UserCondition userCondition, StringBuffer sql, ArrayList<Object> paramList) {
        if (userCondition !=null){
            if (userCondition.getName()!=null && ! userCondition.getName().isEmpty()){
                sql.append(" and name like ?");
                paramList.add("%"+userCondition.getName()+"%");
            }
            if (userCondition.getAddress()!=null && ! userCondition.getAddress().isEmpty()){
                sql.append(" and address like ?");
                paramList.add("%"+userCondition.getAddress()+"%");
            }
            if (userCondition.getStartAge()!=null){
                sql.append(" and age >= ? ");
                paramList.add(userCondition.getStartAge());
            }
            if (userCondition.getEndAge()!=null){
                sql.append(" and age <= ? ");
                paramList.add(userCondition.getEndAge());
            }

        }
    }