多条件查询

image.png

当完成的业务（例如搜索时）表单的数据跟我们封装的数据库的实体bean不完全对应，应该新建一个实体类，先在src目录下建包vo（value object）----专门传递值

查询业务难点：sql怎么写？----拼接
因为页面的条件不固定导致sql的where条件不固定

image.png
trim()的作用是去掉字符串两端的多余的空格，注意，是两端的空格，且无论两端的空格有多少个都会去掉

StringBuffer的常用方法
StringBuffer类中的方法主要偏重于对于字符串的变化，例如追加、插入和删除等，这个也是StringBuffer和String类的主要区别。
append方法：该方法的作用是追加内容到当前StringBuffer对象的末尾，类似于字符串的连接
StringBuffer sb = new StringBuffer(“abc”);
sb.append(true);
则对象sb的值将变成”abctrue”

deleteCharAt方法：该方法的作用是删除指定位置的字符，然后将剩余的内容形成新的字符串。
insert方法：该方法的作用是在StringBuffer对象中插入内容，然后形成新的字符串

数组与集合可当容器，集合不固定大小，数组固定大小。
Obiect[] obj = new Object[n];
List<String> list = new ArrayList<String>();

实体类Condition：

package com.zys.vo;

public class Condition {
    private String pname;
    private String is_hot;
    private String cid;
    
    public String getPname() {
        return pname;
    }
    public void setPname(String pname) {
        this.pname = pname;
    }
    public String getIs_hot() {
        return is_hot;
    }
    public void setIs_hot(String is_hot) {
        this.is_hot = is_hot;
    }
    public String getCid() {
        return cid;
    }
    public void setCid(String cid) {
        this.cid = cid;
    }
    
}

web层servlet代码：

package com.zys.web;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

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

import org.apache.commons.beanutils.BeanUtils;

import com.zys.domain.Category;
import com.zys.domain.Product;
import com.zys.service.AdminProductCategoryService;
import com.zys.service.AdminSearchProductService;
import com.zys.vo.Condition;

public class AdminSearchProductServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        
        //1.收集表单数据，
        Map<String, String[]> properties = request.getParameterMap();
        //2.将散装的数据封装到一个实体中
        Condition condition = new Condition();
        try {
            BeanUtils.populate(condition, properties);
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //3.将实体传递给service层
        AdminSearchProductService service = new AdminSearchProductService();
        List<Product> adminProductList = null;
        try {
            adminProductList = service.searchProductByCondition(condition);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        //4.准备商品类别
        //获得所有商品类别信息
        AdminProductCategoryService service1 = new AdminProductCategoryService();
        
        List<Category> categoryList = null;
        try {
            categoryList = service1.findAllCategory();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        request.setAttribute("categoryList", categoryList);
        
        request.setAttribute("condition", condition);
        
        //将返回的product数据保存到request域
        request.setAttribute("adminProductList", adminProductList);
        //转发数据到jsp页面
        request.getRequestDispatcher("/admin/product/list.jsp").forward(request, response);
        
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }
}

service层代码：

package com.zys.service;

import java.sql.SQLException;
import java.util.List;

import com.zys.dao.AdminSearchProductDao;
import com.zys.domain.Product;
import com.zys.vo.Condition;

public class AdminSearchProductService {

    public List<Product> searchProductByCondition(Condition condition) throws SQLException {
        // 无复杂业务，将数据传递到dao层
        AdminSearchProductDao dao = new AdminSearchProductDao();
        return dao.searchProductByCondition(condition);
        
    }

}

dao层代码：

package com.zys.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.zys.domain.Product;
import com.zys.utils.DataSourceUtils;
import com.zys.vo.Condition;

public class AdminSearchProductDao {

    public List<Product> searchProductByCondition(Condition condition) throws SQLException {
        // 操作数据库，根据参数Condition的条件完成搜索
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        //定义一个存储实际参数的容器
        List<String> list = new ArrayList<String>();
        //定义一个StringBuffer，方便对字符串修改
        StringBuffer sql = new StringBuffer("select * from product where 1=1");
        if(condition.getPname()!=null&&!condition.getPname().trim().equals("")){
            sql.append(" and pname like ?");
            list.add("%"+condition.getPname()+"%");
        }
        if(condition.getIs_hot()!=null&&!condition.getIs_hot().trim().equals("")){
            sql.append(" and is_hot=?");
            list.add(condition.getIs_hot());
        }
        if(condition.getCid()!=null&&!condition.getCid().trim().equals("")){
            sql.append(" and cid=?");
            list.add(condition.getCid());
        }
        List<Product> productList = runner.query(sql.toString(), new BeanListHandler<Product>(Product.class), list.toArray());
        return productList;
    }

}

多条件查询dao层实现：

image.png