Java多条件查询的方法

首先在dao层写一个普通的查询语句：

private String findPage = "SELECT * from bt_assayitemstable where 1=1 ";

在语句后面加上where1=1，是为了后面更好的拼接条件进行查询，避免在查询的时候直接拼接成

where and … 而报语法错误，下面是实现的方法：

public List<BtAssayItems> findPage(String assayItemsNumber,
            String assayItemsName, String chineseName, int start, int PageSize) {
 
    List<BtAssayItems> list = null; 
    con = DbUtil.getConnection(); 
    try { 
        StringBuffer sql = new  StringBuffer(); 
        if(Tools.isNotNull(assayItemsNumber)){ 
                sql.append("and AssayItemsNumber like '%"+ assayItemsNumber + "%'"); 
        } 
        if(Tools.isNotNull(assayItemsName)){ 
                sql.append("and AssayItemsName like '%"+ assayItemsName + "%'"); 
        } 
        if(Tools.isNotNull(chineseName)){ 
                sql.append("and ChineseName like '%"+ chineseName + "%'"); 
        } 
        sql.append(" LIMIT ?,?"); 
        findPage+=sql; 
        ps = con.prepareStatement(findPage); 
        ps.setInt(1, start); 
        ps.setInt(2, PageSize); 
        rs = ps.executeQuery(); 
        list = DbUtil.getResult(rs, BtAssayItems.class);
         } catch (SQLException e) { 
        e.printStackTrace(); 
        }finally{ 
            DbUtil.close(con, ps, rs); 
        } 
        return list; 
    } 

然后再去service包内写它接口与实现类，这里就不多叙述，servlet那边通过

request.getParameter接收jsp页面传过来的值，如果是int要通过Integer转换为int型。

我这里使用了JQ.bsgrid进行分页，

下面是对应servlet层的代码：

private void getlist(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
        String strpageSize = request.getParameter("pageSize"); 
        String strcurPage = request.getParameter("curPage"); 
        IAssayItemsService assayItemsService = new AssayItemsServiceImpl(); 
        String assayItemsNumber = request.getParameter("assayItemsNumber"); 
        String assayItemsName = request.getParameter("assayItemsName"); 
        String chineseName = request.getParameter("chineseName");   
        Bsgrid<BtAssayItems> bsgrid = new Bsgrid<>(); 
        if (Tools.isNum(strcurPage) && Tools.isNum(strpageSize)) { 
            int pageSize = Integer.parseInt(strpageSize); 
            int curPage = Integer.parseInt(strcurPage); 
            int start = (curPage - 1) * pageSize;             
List<BtAssayItems> list = assayItemsService.findPage(assayItemsNumber, assayItemsName, chineseName, start, pageSize); 
int totalRows = assayItemsService.getTotalRow(assayItemsNumber, assayItemsName, chineseName);
            bsgrid.setCurPage(curPage);
            bsgrid.setTotalRows(totalRows); 
            bsgrid.setSuccess(true); 
            bsgrid.setData(list); 
        } else { 
            bsgrid.setSuccess(false); 
        } 
        JSONObject jsonObject = JSONObject.fromObject(bsgrid);         
        PrintWriter out = response.getWriter(); 
        out.write(jsonObject.toString()); 
        out.close(); 
    }

下面是jsp页面对应的js代码

function querys(){ 
       var assayItemsNumber = $("#qassayItemsNumber").val(); 
       if (assayItemsNumber == undefined || assayItemsNumber=="") { 
                assayItemsNumber = ""; 
                }  
        var assayItemsName = $("#qassayItemsName").val(); 
        if (assayItemsName == undefined || assayItemsName=="") { 
                assayItemsNames = ""; 
                }  
        var chineseName = $("#qchineseName").val(); 
        if (chineseName == undefined || chineseName=="") { 
                chineseName = ""; 
                }   tbAssayItem.search({ assayItemsNumber:assayItemsNumber,assayItemsName: 
assayItemsName,chineseName:chineseName});         
    }

然后再bsgrid初始化表格时调用这个方法，和在点击查询按钮的时候调用该方法即可实现多条件查询。