项目:条件查询 通过StringBulider和ArrayList(参数有序) 手动拼接sql

条件查询的sql拼接 参数拼接

 

public List<Product> findAll(Product product) throws SQLException {

//1.1 拼凑sql语句，格式：and 属性 符号 ?

StringBuilder builder = new StringBuilder();

//1.2 拼凑实际参数。实际参数可重复、参数需要顺序，所以选择List集合

List<Object> paramsList = new ArrayList<Object>();

 

//2.1 分类不为空 ,添加分类条件

if(product.getCategory_id() != null && !"".equals(product.getCategory_id())){

builder.append(" and category_id = ? ");

paramsList.add(product.getCategory_id());

}

 

//2.2 商品名不为空，添加商品名条件

if(product.getPname() != null && !"".equals(product.getPname())){

builder.append(" and pname like ? ");

//商品名需要模糊查询即可，需要手动的添加%

paramsList.add("%"+product.getPname()+"%");

}

 

//3 转换成需要的类型

String condition = builder.toString();

Object[] params = paramsList.toArray();

 

QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

String sql = "select * from product where 1=1 " + condition;

return queryRunner.query(sql, new BeanListHandler<Product>(Product.class), params);