public classBookDaoImpl {/*** 查找所有的图书
*@return*@throwsSQLException*/
public List findAllBooks() throwsSQLException{
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());return qr.query("select * from books", new BeanListHandler(Book.class));
}/*** 添加一本图书到数据库中
*@parambook 要添加的图书对象
*@throwsSQLException*/
public void addBook(Book book) throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());
qr.update("INSERT INTO books VALUES(?,?,?,?,?,?);", book.getId(),book.getName(),book.getPrice(),book.getPnum(),book.getCategory(),book.getDescription());
}/**根据图书的id,找到对应的对象
*@paramid 图书的id
*@return对应id的图书对象
*@throwsSQLException*/
public Book findBookById(String id) throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());return qr.query("select * from books where id=?", new BeanHandler(Book.class),id);
}/**根据图书对象更新数据库
*@parambook 图书对象
*@throwsSQLException*/
public void updateBook(Book book) throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());int i = qr.update("update books set name=?,price=?,pnum=?,category=?,description=? where id=?",
book.getName(),book.getPrice(),book.getPnum(),book.getCategory(),book.getDescription(),book.getId());
System.out.println(i>0?"更新成功":"更新失败");
}/**从数据库中删除指定id的图书
*@paramid 图书的id
*@throwsSQLException*/
public void deleteBook(String id) throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());
qr.update("delete from books where id=?", id);
}/**批量删除ids数组中的id
*@paramids
*@throwsSQLException*/
public void deleteAllBooks(String[] ids) throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());
Object[][] params= newObject[ids.length][];for (int i = 0; i < params.length; i++) {
params[i]= newObject[] {ids[i]};
}
qr.batch("delete from books where id=?", params);
}/**多条件查询图书
*@paramid
*@paramcategory
*@paramname
*@paramminprice
*@parammaxprice
*@return*@throwsSQLException*/
public List searchBooks(String id, String category, String name, String minprice, String maxprice) throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());
String sql= "select * from books where 1=1"; //因为下面用到的参数是Object类型,这里不必加泛型
List list = new ArrayList<>();if(!"".equals(id.trim())){ //==》空格也是字符
sql+=" and id like ?"; //不能在这写% %'1002'%
list.add("%"+id.trim()+"%");//'%1002%'
}if(!"".equals(name.trim())) {
sql+=" and name like ?";
list.add(name.trim());
}if(!"".equals(category.trim())) {
sql+=" and category=?";
list.add(category.trim());
}if(!"".equals(minprice.trim())) {
sql+=" and price>= ?";
list.add(minprice.trim());
}if(!"".equals(maxprice.trim())) {
sql+=" and price<= ?";
list.add(maxprice.trim());
}//System.out.println(list);//System.out.println(sql);//System.out.println(minprice);//System.out.println(maxprice);
return qr.query(sql, new BeanListHandler(Book.class),list.toArray());
}/***@return数据库中图书的总记录数
*@throwsSQLException*/
public int count() throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());long l = (Long) qr.query("select count(*) from books", new ScalarHandler(1));return (int)l;
}/**找到分页的数据
*@paramcurrentPagez
*@parampageSize
*@return*@throwsSQLException*/
public List findBooks(int currentPage, int pageSize) throwsSQLException {
QueryRunner qr= newQueryRunner(C3P0Util.getDataSource());return qr.query("select * from books limit ?,?", new BeanListHandler(Book.class),(currentPage-1)*pageSize,pageSize);
}
}