多條件查詢

image.png

當(dāng)完成的業(yè)務(wù)（例如搜索時）表單的數(shù)據(jù)跟我們封裝的數(shù)據(jù)庫的實體bean不完全對應(yīng)石蔗，應(yīng)該新建一個實體類娃属，先在src目錄下建包vo（value object）----專門傳遞值

查詢業(yè)務(wù)難點：sql怎么寫？----拼接
因為頁面的條件不固定導(dǎo)致sql的where條件不固定

image.png

trim()的作用是去掉字符串兩端的多余的空格蛉顽，注意蝗砾，是兩端的空格，且無論兩端的空格有多少個都會去掉

StringBuffer的常用方法
StringBuffer類中的方法主要偏重于對于字符串的變化蜂林，例如追加遥诉、插入和刪除等，這個也是StringBuffer和String類的主要區(qū)別噪叙。
append方法：該方法的作用是追加內(nèi)容到當(dāng)前StringBuffer對象的末尾矮锈，類似于字符串的連接
StringBuffer sb = new StringBuffer(“abc”);
sb.append(true);
則對象sb的值將變成”abctrue”

deleteCharAt方法：該方法的作用是刪除指定位置的字符，然后將剩余的內(nèi)容形成新的字符串睁蕾。
insert方法：該方法的作用是在StringBuffer對象中插入內(nèi)容苞笨，然后形成新的字符串

數(shù)組與集合可當(dāng)容器债朵，集合不固定大小，數(shù)組固定大小瀑凝。
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.收集表單數(shù)據(jù)序芦，
        Map<String, String[]> properties = request.getParameterMap();
        //2.將散裝的數(shù)據(jù)封裝到一個實體中
        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.準(zhǔn)備商品類別
        //獲得所有商品類別信息
        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數(shù)據(jù)保存到request域
        request.setAttribute("adminProductList", adminProductList);
        //轉(zhuǎn)發(fā)數(shù)據(jù)到j(luò)sp頁面
        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 {
        // 無復(fù)雜業(yè)務(wù)，將數(shù)據(jù)傳遞到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 {
        // 操作數(shù)據(jù)庫粤咪，根據(jù)參數(shù)Condition的條件完成搜索
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        //定義一個存儲實際參數(shù)的容器
        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;
    }

}