mysql - 如何在单个查询中使用 AND & LIKE OR 运算符进行搜索

Question

我正在设计一个图书馆管理系统，我想设计一个待处理请求页面，该页面选择并显示 issues_book 表中列 status=pending 的所有信息。我的观点是设计一个搜索字段，从待处理请求页面中的可用信息中搜索特定行，但它不会产生任何输出，请我的 friend ，我需要帮助

   public List<Issue_Use_Bean> search_pending_history(String search){
                    Connection connection=null;
                    DBConnection_LMS_Portal obj_DBConnection_SMS_Portal=new 
   DBConnection_LMS_Portal();
                    connection=obj_DBConnection_SMS_Portal.getConnection();
                    PreparedStatement ps=null;
                    ResultSet rs=null;
                    List<Issue_Use_Bean> list=new ArrayList<Issue_Use_Bean>();

                try { 


    String query="select * from issue_book where status='Pending' AND employee_id 
    like ? or book_title like ? or barcode like ? or issue_date like ? or return_date like ?  or 
    description like ? order by issue_date desc limit 500";
                    ps=connection.prepareStatement(query);
                    ps.setString(1, search);
                    ps.setString(2, "%"+search+"%");
                    ps.setString(3, "%"+search+"%");
                    ps.setString(4, "%"+search+"%");
                    ps.setString(5, "%"+search+"%");
                    ps.setString(6, "%"+search+"%");
                    ps.setString(7, "%"+search+"%");

                    System.out.println(ps);
                    rs=ps.executeQuery();

                    while(rs.next()){
                        Issue_Use_Bean obj_Issue_Use_Bean=new Issue_Use_Bean();

                      obj_Issue_Use_Bean.setStatus(rs.getString("status"));
                        obj_Issue_Use_Bean.setEmployee_id(rs.getString("employee_id"));
                        obj_Issue_Use_Bean.setBook_title(rs.getString("book_title"));

                        obj_Issue_Use_Bean.setBarcode(rs.getString("barcode"));
                        obj_Issue_Use_Bean.setIssue_date(rs.getString("issue_date"));
                        obj_Issue_Use_Bean.setReturn_date(rs.getString("return_date"));
                        obj_Issue_Use_Bean.setDescription(rs.getString("description"));
                        obj_Issue_Use_Bean.setIssue_sl_no(rs.getString("issue_sl_no"));

                        list.add(obj_Issue_Use_Bean);
                        }





                 }catch(Exception e){
                    e.printStackTrace();
                }finally{
                    if(connection!=null){
                        try {
                                connection.close();
                            }
                            catch (Exception e2) {
                            // TODO: handle exception
                            }
                    }
                    if(ps!=null){
                        try {

                            ps.close();
                            }
                         catch (Exception e2) {
                            // TODO: handle exception
                        }
                    }
                    if(rs!=null){
                        try {

                            rs.close();
                            }
                         catch (Exception e2) {
                            // TODO: handle exception
                        }
                    }

            }

                return list;

Answer 1

您需要括号来嵌套带有或运算符的部分:

select * 
  from issue_book 
 where status='Pending' 
   and (   employee_id like ? 
        or book_title like ? 
        or barcode like ? 
        or issue_date like ? 
        or return_date like ? 
        or description like ? ) 
 order by issue_date desc limit 500