增
public void addProduct(Product product) {
// TODO Auto-generated method stub
QueryRunner Runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "insert into product values(?,?,?,?,?,?,?,?,?,?)";
try {
Runner.update(sql, product.getPid(),product.getPname(),product.getMarket_price(),
product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),
product.getPdesc(),product.getPflag(),product.getCid());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
改
BeanUtils.populate(product,properties); 将数据封装到product 中
然后执行和添加一样的操作
public void updateProduct(Product product) throws SQLException {
// TODO Auto-generated method stub
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "update product set pname=?,market_price=?,shop_price=?,pimage=?,pdate=?,is_hot=?,pdesc=?,pflag=?,cid=? where pid=?";
runner.update(sql,product.getPname(),product.getMarket_price(),
product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),
product.getPdesc(),product.getPflag(),product.getCid(),product.getPid());
}
查询单条
public List<Product> searchProduct(Condition condition) throws SQLException {
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql ="select *from product where 1=1";
List<String> list = new ArrayList<String>();
if(condition.getPname()!=null&&!condition.getPname().trim().equals("")){
sql+=" and pname like ? ";
list.add("%"+condition.getPname().trim()+"%");
}
if(condition.getIs_hot()!=null&&!condition.getIs_hot().trim().equals("")){
sql+=" and is_hot=? ";
list.add(condition.getIs_hot().trim());
}
if(condition.getCid()!=null&&!condition.getCid().trim().equals("")){
sql+=" and cid=? ";
list.add(condition.getCid().trim());
}
List<Product> productList = runner.query(sql, new BeanListHandler<Product>(Product.class),list.toArray());
return productList;
}
查询所有
public static List<Category> findAllCategory() {
QueryRunner Runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "select *from category";
List<Category> categoryList=null;
try {
categoryList = Runner.query(sql, new BeanListHandler<Category>(Category.class)); //将数据封装到Bean 中 然后 封装到 list
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return categoryList;
}
删
public static void delete(String pid) {
try {
// TODO Auto-generated method stub
QueryRunner Runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "delete from product where pid=?";
Runner.update(sql,pid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}