复杂条件查询功能_分析与复杂条件查询功能_代码实现1与复杂条件查询功能_代码实现2_每页数据条件查询

复杂条件查询功能_分析

　　　　复杂查询，也就是模糊查询

　　　　

SELECT * FROM user WHERE gender LIKE '%女%' and address LIKE '%博城%' LIMIT 0,5;

 

 

 　　

　　　　　　　　　　　　PageBean
int totalCount;——— select count(*) from user where name like ? and adress like ?动态sql

 

List list ; ————select * from user where name like ? and address like ? limit ? , ?

1.定义初始化sql   select count(*) from user where 1 = 1

2.遍历map，判断其value是否有值

sb. append(" and key like?)

复杂条件查询功能_代码实现1

 

 

 

 

 

 

 

 

  //获取value
            String value = condition.get(key)[0];
            //判断value是否有值
            if (value !=null && !"".equals(value)){
                //有值
                    sb.append(" and "+key+" like? ");
                    params.add("%"+value+"%"); //?条件的值
            }
        }

 

@Override
    public PageBean<User> findUserByPage(String _currentPage, String _rows, Map<String, String[]> condition) {

        int currentPage = Integer.parseInt(_currentPage);
        int rows = Integer.parseInt(_rows);

        if (currentPage <=0){
            currentPage= 1;
        }

        //1.创建空的PageBean对象
        PageBean<User> pb = new PageBean<>();
        //2.设置参数
        pb.setCurrentPage(currentPage);
        pb.setRows(rows);


        //3.调用dao查询总记录数
        int totalCount = dao.findTotalCount(condition);
        pb.setTotalCount(totalCount);
        //4.调用dao查询list集合
        //计算开始的记录索引
        int start = (currentPage - 1) * rows;
        List<User> list =  dao.findByPage(start,rows,condition);
        pb.setList(list);
        //5.计算总页码
        int totalPage = (totalCount % rows)  == 0 ? totalCount/rows : (totalCount/rows) + 1;
        pb.setTotalPage(totalPage);
        return pb;
    }

 

   request.setCharacterEncoding("utf-8");
           //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";
        }

           //获取条件查询参数
        Map<String, String[]> stringMap = request.getParameterMap();

 <form class="form-inline" action="${pageContext.request.contextPath}/findUserByPageServlet" method="post">
            <div class="form-group">
                <label for="exampleInputName2">姓名</label>
                <input type="text" name="name" value="${condition.name[0]}" class="form-control" id="exampleInputName2" >
            </div>
            <div class="form-group">
                <label for="exampleInputName3">籍贯</label>
                <input type="text" name="address" value="${condition.address[0]}" class="form-control" id="exampleInputName3" >
            </div>

            <div class="form-group">
                <label for="exampleInputEmail2">邮箱</label>
                <input type="text" name="email" value="${condition.email[0]}" class="form-control" id="exampleInputEmail2"  >
            </div>
            <button type="submit" class="btn btn-default">查询</button>
        </form>

 

复杂条件查询功能_代码实现2_每页数据条件查询

  @Override
    public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
        String sql = "select * from user  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();
        System.out.println(sql);
        System.out.println(params);

        return template.query(sql,new BeanPropertyRowMapper<User>(User.class),params.toArray());
    }