基于java web晚上书城系统的设计与实现

package com.gw.bookstroe.dao.impl; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import com.gw.bookstroe.dao.ProductDao; import com.gw.bookstroe.domain.Order; import com.gw.bookstroe.domain.OrderItem; import com.gw.bookstroe.domain.Product; import com.gw.bookstroe.utils.DataSourceUtils; public class ProductDaoimpl implements ProductDao { //0.创建QueryRunner对象 QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource()); //1. 添加商品 @Override public void addProduct(Product p) throws SQLException{ String sql = "insert into products values(?,?,?,?,?,?,?)"; runner.update(sql, p.getId(), p.getName(), p.getPrice(), p.getCategory(), p.getPnum(), p.getImgurl(), p.getDescription()); } // 2.查找所有商品 @Override public List<Product> listAll() throws SQLException{ String sql = "select * from products"; return runner.query(sql, new BeanListHandler<Product>(Product.class)); } // 3.根据id查找商品 @Override public Product findProductById(String id) throws SQLException { String sql = "select * from products where id=?"; return runner.query(sql, new BeanHandler<Product>(Product.class), id); } // 4.多条件查询 @Override public List<Product> findProductByManyCondition(String id, String name, String category, String minprice, String maxprice) throws SQLException{ //List<Object>是Object的参数化类型，其引用变量可以接受List，可以添加元素 List<Object> list = new ArrayList<Object>(); //1.构造存在where,但是条件又根据不同情况而不同的 String sql = "select * from products where 1=1 "; //2.创建sql语句，并拼接sql if (id != null && id.trim().length() > 0) { sql += " and id=?"; list.add(id); } //2.名字进行模糊查询 if (name != null && name.trim().length() > 0) { sql += " and name like ?"; list.add("%"+name+"%"); } if (category != null && category.trim().length() > 0) { sql += " and category=?"; list.add(category); } if (minprice != null && maxprice != null && minprice.trim().length() > 0 && maxprice.trim().length() > 0) { sql += " and price between ? and ?"; list.add(minprice); list.add(maxprice); } //3.把参数放入Object类（集合类） Object[] params = list.toArray(); return runner.query(sql, new BeanListHandler<Product>(Product.class), params); } // 5.修改商品信息 @Override public void editProduct(Product p) throws SQLException { // TODO Auto-generated method stub //1.创建集合并将商品信息添加到集合中 List<Object> obj = new ArrayList<Object>(); obj.add(p.getName()); obj.add(p.getPrice()); obj.add(p.getCategory()); obj.add(p.getPnum()); obj.add(p.getDescription()); //2.创建sql语句，并拼接sql String sql = "update products " + "set name=?,price=?,category=?,pnum=?,description=? "; //3判断是否有图片 if (p.getImgurl() != null && p.getImgurl().trim().length() > 0) { sql += " ,imgurl=?"; obj.add(p.getImgurl()); } //4.注意： where id=?要拼接在最后面 sql += " where id=?"; obj.add(p.getId()); //5.使用QueryRunner对象的update()方法更新数据 runner.update(sql, obj.toArray()); } //6.后台系统，根据id删除商品信息 @Override public void deleteProduct(String id) throws SQLException{ String sql = "DELETE FROM products WHERE id = ?"; runner.update(sql, id); } // 7.销售榜单 @Override public List<Object[]> salesList(String year, String month) throws SQLException { String sql = "SELECT products.name,SUM(orderitem.buynum) AS totalsalnum FROM orders,products,orderItem WHERE orders.id=orderItem.order_id AND products.id=orderItem.product_id AND orders.paystate=1 and year(ordertime)=? and month(ordertime)=? GROUP BY products.name ORDER BY totalsalnum DESC"; return runner.query(sql, new ArrayListHandler(), year, month); } //8删除订单时，修改商品数量 @Override public void updateProductNum(List<OrderItem> items) throws SQLException { String sql = "update products set pnum=pnum+? where id=?"; Object[][] params = new Object[items.size()][2]; for (int i = 0; i < params.length; i++) { params[i][0] = items.get(i).getBuynum(); params[i][1] = items.get(i).getP().getId(); } runner.batch( sql, params); } // 9.获取数据总条数 @Override public int findAllCount(String category) throws SQLException { String sql = "select count(*) from products"; if (!"全部商品".equals(category)) { sql += " where category=?"; Long count = (Long) runner.query(sql, new ScalarHandler(), category); return count.intValue(); } else { Long count = (Long) runner.query(sql, new ScalarHandler()); return count.intValue(); } } //10.获取当前页数据 @Override public List<Product> findByPage(int currentPage, int currentCount, String category) throws SQLException { // TODO Auto-generated method stub // 要执行的sql语句 String sql = null; // 参数 Object[] obj = null; // 如果category不为null,代表是按分类查找 if (!"全部商品".equals(category)) { sql = "select * from products where category=? limit ?,?"; obj = new Object[] { category, (currentPage - 1) * currentCount, currentCount, }; } else { sql = "select * from products limit ?,?"; obj = new Object[] { (currentPage - 1) * currentCount, currentCount, }; } return runner.query(sql, new BeanListHandler<Product>(Product.class), obj); } // 11.生成订单时，将商品数量减少 @Override public void changeProductNum(Order order) throws SQLException { String sql = "update products set pnum=pnum-? where id=?"; List<OrderItem> items = order.getOrderItems(); Object[][] params = new Object[items.size()][2]; for (int i = 0; i < params.length; i++) { params[i][0] = items.get(i).getBuynum(); params[i][1] = items.get(i).getP().getId(); } runner.batch(DataSourceUtils.getConnection(), sql, params); } //12.前台，用于搜索框根据书名来模糊查询相应的图书 @Override public List<Product> findBookByName(int currentPage, int currentCount, String searchfield) throws SQLException { //根据名字模糊查询图书 String sql = "SELECT * FROM products WHERE name LIKE '%"+searchfield+"%' LIMIT ?,?"; return runner.query(sql,new BeanListHandler<Product>(Product.class),currentPage-1,currentCount); } //13.前台搜索框，根据书名模糊查询出的图书总数量 @Override public int findBookByNameAllCount(String searchfield) throws SQLException { String sql = "SELECT COUNT(*) FROM products WHERE name LIKE '%"+searchfield+"%'"; //查询出满足条件的总数量，为long类型 Long count = (Long)runner.query(sql, new ScalarHandler()); return count.intValue(); } }