高级查询

整理了高级查询的项目,放到这里方便查看。高级查询的实质就是拼接SQL语句,这个项目还增强了代码重构的能力。完成一个需求的功能一般都是从最直接也是最简单的方法入手,一步步迭代得到较理想的结果。这个项目解决了" where 1 = 1 "的问题,因为“ where 1 = 1 ”会降低数据库的效率。把查询条件封装成对象,并抽出了通用的代码作为查询对象的基类(父类),运行代码时会调用钩子方法,是模板方法设计模式的具体应用。

照例会贴出项目文件结构,和主要代码,并对代码作简要说明,文末有整个项目的源代码。

项目层次结构:

高级查询项目层次结构

源代码如下:

dao包:IProductDAO.java

package ee.coding.pss.dao;

import java.util.List;

import ee.coding.pss.domain.Product;
import ee.coding.pss.query.ProductQueryObject;

public interface IProductDAO {

    List<Product> query(ProductQueryObject qo);
}

dao\impl包:ProductDAOImpl.java

package ee.coding.pss.dao.impl;

import java.util.List;

import ee.coding.pss.dao.IProductDAO;
import ee.coding.pss.domain.Product;
import ee.coding.pss.handler.impl.BeanListHandler;
import ee.coding.pss.query.ProductQueryObject;
import ee.coding.pss.util.JdbcTemplate;

public class ProductDAOImpl implements IProductDAO {

    @Override
    public List<Product> query(ProductQueryObject qo) {
        String sql = "SELECT * FROM t_product " + qo.getConditions();
        return JdbcTemplate.query(sql, new BeanListHandler<>(Product.class), qo.getParams().toArray());
    }
}

domain包:Product.java

package ee.coding.pss.domain;

import java.math.BigDecimal;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Product {
    private Long id;
    private String productName;
    private Long dir_id;
    private BigDecimal salePrice;
    private String supplier;
    private String brand;
    private Double cutoff;
    private BigDecimal costPrice;
}

query包:查询对象的基类,QueryObject.java

package ee.coding.pss.query;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import lombok.Getter;

public class QueryObject {
    @Getter
    private List<Object> params = new ArrayList<>();
    // 定义一个List集合,用来存放sql中的条件

    private List<String> conditions = new ArrayList<>();

    public String getConditions() {
        //清空数据,不然每次都新增
        conditions.clear();
        params.clear();
        customizedQuery();
        // 定义一个StringBuilder类对象来拼接SQL语句
        StringBuilder sql = new StringBuilder();
        // 拼接SQL语句
        for (int i = 0; i < conditions.size(); i++) {
            if (i == 0) {
                sql.append(" WHERE ");
            } else {
                sql.append(" AND ");
            }
            sql.append(conditions.get(i));
        }
        return sql.toString();
    }

    // 供子类来覆写
    protected void customizedQuery() {

    }

    // 供子类调用,将对应的参数和条件传递进来
    protected void addQuery(String conditions, Object... params) {
        this.conditions.add(conditions);
        this.params.addAll(Arrays.asList(params));
    }
}

query包:查询对象的具体类,ProductQueryObject.java

package ee.coding.pss.query;

import java.math.BigDecimal;

import ee.coding.pss.util.StringUtils;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class ProductQueryObject extends QueryObject {
    private String productName;
    private BigDecimal minPrice;
    private BigDecimal maxPrice;
    private Long dir_id;
    private String keywords;
    // 定义一个List集合,用来保存需要拼接的参数

    public void customizedQuery() {
        if (StringUtils.hasLength(productName)) {
            addQuery(" productName LIKE ? ", "%" + productName + "%");
        }

        if (minPrice != null) {
            addQuery(" salePrice >= ? ", minPrice);
        }

        if (maxPrice != null) {
            addQuery(" salePrice <= ? ", maxPrice);
        }

        if (dir_id != null && dir_id != -1) {
            addQuery(" dir_id = ? ", dir_id);
        }

        if (StringUtils.hasLength(keywords)) {
            addQuery(" (productName LIKE ? OR supplier LIKE ? OR brand LIKE ?) ", "%" + keywords + "%",
                    "%" + keywords + "%", "%" + keywords + "%");
        }
    }
}

web\servlet包:ProductServlet.java

package ee.coding.pss.web.servlet;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import ee.coding.pss.dao.IProductDAO;
import ee.coding.pss.dao.impl.ProductDAOImpl;
import ee.coding.pss.domain.Product;
import ee.coding.pss.query.ProductQueryObject;
import ee.coding.pss.util.StringUtils;

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

    private static final long serialVersionUID = 1L;

    private IProductDAO dao;

    @Override
    public void init() throws ServletException {
        dao = new ProductDAOImpl();
    }

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 设置编码,防止中文乱码
        req.setCharacterEncoding("UTF-8");

        // 获取请求参数
        String productName = req.getParameter("productName");
        String minPrice = req.getParameter("minPrice");
        String maxPrice = req.getParameter("maxPrice");
        String dir_id = req.getParameter("dir_id");
        String keywords = req.getParameter("keywords");
        ProductQueryObject qo = new ProductQueryObject();
        // 如果请求参数不为空则设置到qo中去
        if (StringUtils.hasLength(productName)) {
            qo.setProductName(productName);
        }
        if (StringUtils.hasLength(minPrice)) {
            qo.setMinPrice(new BigDecimal(minPrice));
        }
        if (StringUtils.hasLength(maxPrice)) {
            qo.setMaxPrice(new BigDecimal(maxPrice));
        }
        if (StringUtils.hasLength(dir_id)) {
            qo.setDir_id(Long.valueOf(dir_id));
        }
        if (StringUtils.hasLength(keywords)) {
            qo.setKeywords(keywords);
        }

        // 把封装好的请求对象放到作用域中,方便JSP页面做回显
        req.setAttribute("qo", qo);

        List<Product> list = dao.query(qo);
        // 把商品分类信息存储到作用域中
        req.setAttribute("list", list);
        // 3.控制页面跳转
        req.getRequestDispatcher("/WEB-INF/views/product/list.jsp").forward(req, resp);
    }
}

JSP文件:list.jsp

<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>货品显示列表</title>
</head>
<body>
    <form action="/p">
        名称:<input type="text" name="productName" value="${qo.productName}">
        售价:<input type="text" name="minPrice" value="${qo.minPrice}">
        --- <input type="text" name="maxPrice" value="${qo.maxPrice}">
        分类: <select name="dir_id">
            <option value="-1">---请选择---</option>
            <option value="2" ${qo.dir_id == 2 ? "selected='selected'" : "" }>无线鼠标</option>
            <option value="3" ${qo.dir_id == 3 ? "selected='selected'" : "" }>有线鼠标</option>
            <option value="4" ${qo.dir_id == 4 ? "selected='selected'" : "" }>游戏鼠标</option>
        </select>
        关键词:<input type="text" name="keywords" value="${qo.keywords}" /> 
        <input type="submit" value="搜索">
    </form>
    <table align="center" border="1" cellpadding="0" cellspacing="0"
        width="80%">
        <tr style="background-color: gray;">
            <th>货品编号</th>
            <th>货品名称</th>
            <th>货品分类</th>
            <th>零&nbsp;售&nbsp;价</th>
            <th>供&nbsp;应&nbsp;商</th>
            <th>货品品牌</th>
            <th>折&emsp;&emsp;扣</th>
            <th>成&nbsp;本&nbsp;价</th>
        </tr>
        <c:forEach items="${list}" var="p" varStatus="vs">
            <tr style="background-color: ${vs.count%2==0?'gray':''};">
                <td>${p.id}</td>
                <td>${p.productName}</td>
                <td><c:choose>
                        <c:when test="${p.dir_id==1}">
                            鼠标
                        </c:when>
                        <c:when test="${p.dir_id==2}">
                            无线鼠标
                        </c:when>
                        <c:when test="${p.dir_id==3}">
                            有线鼠标
                        </c:when>
                        <c:when test="${p.dir_id==4}">
                            游戏鼠标
                        </c:when>
                    </c:choose></td>
                <td>${p.salePrice}</td>
                <td>${p.supplier}</td>
                <td>${p.brand}</td>
                <td>${p.cutoff}</td>
                <td>${p.costPrice}</td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

项目源码:AdvancedQuery.zip