高级查询

高级查询后台操作步骤：

1）在ProductDAO接口中编写高级查询的方法

public interface ProductDAO {
	/**
	 * 高级查询
	 * @param name              商品名称：productName LIKE '%name值%'
	 * @param minSalePrice      最低价格：salePrice>minPrice
	 * @param maxSalePrice      最高价格：salePrice<maxPrice
	 * @return
	 */
	List<Product> query(String name,BigDecimal minSalePrice,BigDecimal maxSalePrice);
	
}

2）

public class ProductDAOImpl implements ProductDAO{

	public List<Product> query(String name, BigDecimal minSalePrice, BigDecimal maxSalePrice) {
		StringBuilder sql=new StringBuilder(80);
		sql.append("SELECT * FROM product WHERE 1=1 ");
		//封装占位符参数
		List<Object> parameters=new ArrayList<>();
		//商品名称
		if(StringUtils.isNoneBlank(name)) {
			sql.append("AND productName LIKE ?");
			parameters.add("%"+name+"%");
		}
		//最低价格
		if(minSalePrice!=null) {
			sql.append("AND salePrice>=?");
			parameters.add(minSalePrice);
		}
		//最高价格
		if(maxSalePrice!=null) {
			sql.append("AND salePrice<=?");
			parameters.add(maxSalePrice);
		}
		System.out.println("SQL="+sql);
		System.out.println("参数="+parameters);
		return JdbcTemplate.query(sql.toString(), new BeanListHandler<>(Product.class), parameters.toArray());
	}

 

@Test
	public void testQuery() {
			List<Product> list=dao.query("鼠", new BigDecimal("50"),null);
			System.out.println(list.size());
			for(Product p:list) {
				System.out.println(p.getId()+p.getBrand());
			}
		
	}

缺陷：如果查询条件过多，此时参数就很多-->把多个查询条件封装到查询对象QueryObject中

 

缺陷：在上述查询过程中，为了避免判断哪一个条件是第一个，而使用了where 1=1

          但是使用where 1=1会影响性能，因为不能使用索引来查询了

          可以手动判断但是可以使用一种更方便的：使用Apache common-lang组件中的StringUtils.join方法，

把集合中每一个元素使用特定的字符串给连接起来。

public class ProductQueryObject {
	private String name;
	private BigDecimal minSalePrice;
	private BigDecimal maxSalePrice;
	public ProductQueryObject(String name, BigDecimal minSalePrice, BigDecimal maxSalePrice) {
		this.name = name;
		this.minSalePrice = minSalePrice;
		this.maxSalePrice = maxSalePrice;
	}
	public ProductQueryObject() {
	
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public BigDecimal getMinSalePrice() {
		return minSalePrice;
	}
	public void setMinSalePrice(BigDecimal minSalePrice) {
		this.minSalePrice = minSalePrice;
	}
	public BigDecimal getMaxSalePrice() {
		return maxSalePrice;
	}
	public void setMaxSalePrice(BigDecimal maxSalePrice) {
		this.maxSalePrice = maxSalePrice;
	}
	//参数集合
	private List<Object> parameters=new ArrayList<>();
	//查询条件集合
	private List<String> conditions=new ArrayList<>();
	//返回查询条件
	public String getQuery() {
		StringBuilder sql=new StringBuilder(80);
		//商品名称
		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(conditions.size()==0) {
			return "";
		}
		String queryString=StringUtils.join(conditions.toArray(), " AND ");
		return sql.append(" WHERE ").append(queryString).toString();
	}
	
	//返回查询条件中的占位符参数值
	public List<Object> getParameters(){
		return parameters;
	}

public List<Product> query(ProductQueryObject qo) {
		String sql="SELECT * FROM product"+qo.getQuery();  
		System.out.println(sql);
		return JdbcTemplate.query(sql, new BeanListHandler<>(Product.class), qo.getParameters().toArray());
	}

 

高级查询的前台设计

 

在list.jsp中增加表单

<form action="product" method="post">
		商品名称<input type="text" name="name" value="${qo.name }"><br>
		商品价格<input type="text" name="minSalePrice" value="${qo.minSalePrice }">到<input type="text" name="maxSalePrice" value="${qo.maxSalePrice }">
		<input type="submit" value="提交查询" style="background-color:blue">
</form>

public class ProductServlet2 extends HttpServlet{
	
	private static final long serialVersionUID = 1L;
	private ProductDAO2 dao;
	private ProductDirDAO2 dirDAO;
	public void init() {
		dao=new ProductDAOImpl2();
	}
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		req.setCharacterEncoding("UTF-8");
		ProductQueryObject qo=new ProductQueryObject(); 
		this.reqToObject(qo,req);
		req.setAttribute("qo", qo);
		List<Product> products=dao.query(qo);
		req.setAttribute("products", products);//
	    req.getRequestDispatcher("/WEB-INF/list.jsp").forward(req, resp);
	}
	
	
	private void reqToObject(ProductQueryObject qo, HttpServletRequest req) {
		String name=req.getParameter("name");
		String minSalePrice=req.getParameter("minSalePrice");
		String maxSalePrice=req.getParameter("maxSalePrice");
		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));
		}
	}
	
}