mysql条件查询

来源：互联网发布：java 获取classpath 编辑：程序博客网时间：2024/06/05 08:55

函数：

    public List<Book> findBookByManyContion(String id, String name, String category,            double minprice, double maxprice) throws SQLException

多条件查询其实很简单：

QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());String sql="select * from book where 1=1 ";if(!"".equals(id.trim()))sql+=" and id like '%"+id.trim()+"%'";if(!"".equals(name.trim()))sql+="and name like '%"+name.trim()+"%' ";if(!"".equals(category.trim())) sql+="and category='"+category.trim()+"' ";if(minprice!=0 && maxprice!=0)//if(!"".equals(minprice.trim()))sql+="and price between '"+minprice+"' and '"+maxprice+"' ";System.out.println(sql);return qr.query(sql, new BeanListHandler<Book>(Book.class));

此处给出一种更好的办法：防止sql注入。

QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());String sql = "select * from book where 1=1";List<Object> list =new ArrayList<Object>();if(!"".equals(id.trim())){sql+=" and id like ?";list.add("%"+id.trim()+"%");}if(!"".equals(name.trim())){sql+=" and name like ?";list.add("%"+name.trim()+"%");}if(!"".equals(category.trim())){sql+=" and category=?";list.add(category);}if(minprice!=0){sql+=" and price>?";list.add(minprice);}if(maxprice!=0){sql+=" and price<?";list.add(maxprice);}System.out.println(list);System.out.println(list.toArray());return qr.query(sql, new BeanListHandler<Book>(Book.class),list.toArray());//[]

附：表单传过来的是字符串，直接统一处理一下比较好

public List<Book> searchBooks(String id, String name, String category,String minprice, String maxprice)

QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());String sql = "select * from book where 1=1";List<Object> list =new ArrayList<Object>();if(!"".equals(id.trim())){sql+=" and id like ?";list.add("%"+id.trim()+"%");}if(!"".equals(name.trim())){sql+=" and name like ?";list.add("%"+name.trim()+"%");}if(!"".equals(category.trim())){sql+=" and category=?";list.add(category);}if(!"".equals(minprice.trim())){sql+=" and price>?";list.add(Double.parseDouble(minprice.trim()));}if(!"".equals(maxprice.trim())){sql+=" and price<?";list.add(Double.parseDouble(maxprice.trim()));}return qr.query(sql, new BeanListHandler<Book>(Book.class),list.toArray());//[]

0 0