条件过滤(商品名称、价格以及商品类别的查询)

07.增加商品分类查询条件

操作步骤:

    1):提供ProductDir的DAO实现.

    2):在ProductServelt中,查询出所有的商品分类信息,并提供给list.jsp页面,目的:可以选择商品分类.

    3):修改list.jsp增加商品分类查询.

    4):修改ProductQueryObject类,增加查询条件:dir_id.

    5):修改ProductQueryObject中的getQuery方法.增加dir_id的查询代码   

    6):修改ProductServelt获取商品分类参数,并设置到ProductQueryObject类中.

    多一个查询条件,那么在Query对象中应该多封装一个查询信息,在getQuery方法中,多编写两行代码.    

dao

public interface IProductDAO {

    List<Product> list();

    List<Product> query(ProductQueryObject qo);
}

public interface IProductdirDAO {
    List<Productdir> list();
}

impl

public class ProductDAOImpl implements IProductDAO {

    public List<Product> list() {
        String sql = "SELECT * FROM product";
        return JdbcTemplate.query(sql, new ProductResultHandler());
    }

    public List<Product> query(ProductQueryObject qo) {
        String sqlQuery = qo.getQuery();
        List<Object> parameters = qo.getParameters();
        String sql = "SELECT * FROM product "+sqlQuery;
        System.out.println("SQL="+sql);
        System.out.println("数组:"+parameters);
        return JdbcTemplate.query(sql, new ProductResultHandler(),parameters.toArray());
    }


    //定义处理商品对象的结果集处理器
        class ProductResultHandler implements ResultSetHandler<List<Product>> {
            @Override
            public List<Product> handle(ResultSet rs) throws SQLException {
                List<Product> list = new ArrayList<>();
                while (rs.next()) {
                    Product pro = new Product();
                    list.add(pro);
                    pro.setId(rs.getLong("id"));
                    pro.setProductName(rs.getString("productName"));
                    pro.setBrand(rs.getString("brand"));
                    pro.setSupplier(rs.getString("supplier"));
                    pro.setSalePrice(rs.getBigDecimal("salePrice"));
                    pro.setCostPrice(rs.getBigDecimal("costPrice"));
                    pro.setCutoff(rs.getDouble("cutoff"));
                    pro.setDir_id(rs.getLong("dir_id"));
                }
                return list;
            }
        }
}

public class ProductdirDAOImpl implements IProductdirDAO {

    public List<Productdir> list() {
        String sql = "SELECT * FROM productdir";
        return JdbcTemplate.query(sql, new ProductResultHandler());
    }

    //定义处理商品对象的结果集处理器
    class ProductResultHandler implements ResultSetHandler<List<Productdir>> {
        @Override
        public List<Productdir> handle(ResultSet rs) throws SQLException {
            List<Productdir> list = new ArrayList<>();
            while (rs.next()) {
                Productdir pro = new Productdir();
                list.add(pro);
                pro.setId(rs.getLong("id"));
                pro.setName(rs.getString("name"));
                pro.setParent_id(rs.getLong("parent_id"));
            }
            return list;
        }
    }
}

domain

@Data
public class ProductQueryObject {
    private String name;
    private BigDecimal minSalePrice; 
    private BigDecimal maxSalePrice;
    private Long dir_id = -1L;

    private List<String> conditions = new ArrayList<>();
    //封装占位符参数
    private List<Object> parameters = new ArrayList<>();
    //返回查询条件,如:WHERE productName LIKE ? AND salePrice >= ?
    public String getQuery(){
        StringBuilder sql = new StringBuilder();
        //商品名称
        if(StringUtils.isNotBlank(name)){
            conditions.add("productName LIKE ?");
            parameters.add("%"+name+"%");
        }
        //最低价格
        if(minSalePrice != null){
            conditions.add("salePrice >= ?");
            parameters.add(minSalePrice);
        }
        //最高价格
        if(maxSalePrice != null){
            conditions.add("salePrice <= ?");
            parameters.add(maxSalePrice);
        }
        if(dir_id != -1){
            conditions.add(" dir_id = ?");
            parameters.add(dir_id);
        }
        if(conditions.size()==0){
            return "";
        }
        /*for (int i = 0; i < conditions.size(); i++) {
            if(i==0){
                sql.append(" WHERE ");
            }else{
                sql.append(" AND ");
            }
            sql.append(conditions.get(i));
        }*/

        String queryString = StringUtils.join(conditions," AND ");
        return sql.append(" Where ").append(queryString).toString();
    }
    public List<Object> getParameters() {
        return parameters;
    }
}

@Data
public class Productdir {
    private String name;
    private Long id;
    private Long parent_id;
}   

public class Product {
    private Long id;
    private String productName;
    private String brand;
    private String supplier;
    private BigDecimal salePrice;
    private BigDecimal costPrice;
    private Double cutoff;
    private Long dir_id;//分类编号

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getProductName() {
        return productName;
    }
    public void setProductName(String productName) {
        this.productName = productName;
    }
    public String getBrand() {
        return brand;
    }
    public void setBrand(String brand) {
        this.brand = brand;
    }
    public String getSupplier() {
        return supplier;
    }
    public void setSupplier(String supplier) {
        this.supplier = supplier;
    }
    public BigDecimal getSalePrice() {
        return salePrice;
    }
    public void setSalePrice(BigDecimal salePrice) {
        this.salePrice = salePrice;
    }
    public BigDecimal getCostPrice() {
        return costPrice;
    }
    public void setCostPrice(BigDecimal costPrice) {
        this.costPrice = costPrice;
    }
    public Double getCutoff() {
        return cutoff;
    }
    public void setCutoff(Double cutoff) {
        this.cutoff = cutoff;
    }
    public Long getDir_id() {
        return dir_id;
    }
    public void setDir_id(Long dir_id) {
        this.dir_id = dir_id;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", productName=" + productName + ", brand=" + brand + ", supplier=" + supplier
                + ", salePrice=" + salePrice + ", costPrice=" + costPrice + ", cutoff=" + cutoff + ", dir_id=" + dir_id
                + "]";
    }
}

Servlet

@WebServlet("/product")
public class ProductServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    private IProductDAO dao;
    private IProductdirDAO dao_dir;

    public void init() throws ServletException {
        dao = new ProductDAOImpl();
        dao_dir = new ProductdirDAOImpl();
    }

    //列表操作
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //1:接受请求参数,封装对象
        //2:调用业务方法处理请求
        ProductQueryObject qo = new ProductQueryObject();
        this.request2Object(req,qo);
        req.setAttribute("qo",qo);
        List<Product> list = dao.query(qo);
        req.setAttribute("p", list);

        req.setAttribute("dir", dao_dir.list());
        //3:控制界面跳转
        req.getRequestDispatcher("/WEB-INF/views/product/product.jsp").forward(req, resp);
    }

    private void request2Object(HttpServletRequest req, ProductQueryObject qo) {
        String name = req.getParameter("name");
        String minSalePrice = req.getParameter("minSalePrice");
        String maxSalePrice = req.getParameter("maxSalePrice");
        String dir_id = req.getParameter("dir_id");
        if(StringUtils.isNotBlank(name)){
            qo.setName(name);
        }
        if(StringUtils.isNotBlank(minSalePrice)){
            qo.setMinSalePrice(new BigDecimal(minSalePrice));
        }
        if(StringUtils.isNotBlank(maxSalePrice)){
            qo.setMaxSalePrice(new BigDecimal(maxSalePrice));
        }if(StringUtils.isNotBlank(dir_id)){
            qo.setDir_id(Long.valueOf(dir_id));
        }
    }
}

JDBC的操作模板（CRUD）

public class JdbcTemplate {

    private JdbcTemplate() {

    }

    /**
     * 
     *@param sql   DML各自的SQL,由调用者决定
     *@param params      DML操作需要的参数，由调用者决定
     *@return     受影响的行数
     */
    public static int update(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.INSTANCE.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.INSTANCE.close(conn, ps, null);
        }
        return 0;
    }

    public static <T>T query(String sql, ResultSetHandler<T> rsh ,Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.INSTANCE.getConn();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();
            return rsh.handle(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.INSTANCE.close(conn, ps, rs);
        }
        return null;
    }
}

jsp页面

<body>

    <form action="/product" method="post">
        商品名称:<input type="text" name="name" value="${qo.name}"/>
        商品价格:<input type="text" name="minSalePrice" value="${qo.minSalePrice}"/>到
        <input type="text" name="maxSalePrice" value="${qo.maxSalePrice}"/>
        商品种类:<select name="dir_id">
                    <option value="-1">全部商品</option>
                <c:forEach items="${dir}" var="d">

                    <option value="${d.id}" ${d.id == qo.dir_id? "selected":""} >${d.name}</option>
                </c:forEach>
                </select>

        <input type="submit" value=" 提交  " style="background-color: orange;"/>  
    </form>
    <table border="1" width="80%" cellpadding="0" cellspacing="0">
        <tr style="background-color: orange">
            <th>id</th>
            <th>productName</th>
            <th>brand</th>
            <th>supplier</th>
            <th>salePrice</th>
            <th>costPrice</th>
            <th>cutoff</th>
            <th>dir_id</th>
        </tr>
        <c:forEach items="${p}" var="p" varStatus="s">
            <tr style='background-color:${s.count % 2 == 0? "gray":""}'>
                <td>${p.id}</td>
                <td>${p.productName}</td>
                <td>${p.brand}</td>
                <td>${p.supplier}</td>
                <td>${p.salePrice}</td>
                <td>${p.costPrice}</td>
                <td>${p.cutoff}</td>
                <td>
                    <c:choose>
                        <c:when test="${p.dir_id == 1}">无线手机</c:when>
                        <c:when test="${p.dir_id == 3}">游戏手机</c:when>
                        <c:when test="${p.dir_id == 5}">有线手机</c:when>
                    </c:choose>
                </td>
            </tr>
        </c:forEach>
    </table>
</body>