北大青鸟s2品红项目

package s2jsp.bysj.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import s2jsp.bysj.dao.BaseDao; import s2jsp.bysj.dao.productDao; import s2jsp.bysj.entity.product; public class productDaoImpl extends BaseDao implements productDao { private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; public static final int PAGE_NUM = 5;// 定义产品列表页面显示产品个数 // 查找所有商品 public List findAllProduct() { List list = new ArrayList(); String sql = "select * from product"; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { product pro = new product(); pro.setProductId(rs.getInt("productId")); pro.setSerialNumber(rs.getString("serialNumber")); pro.setName(rs.getString("name")); pro.setPrice(rs.getDouble("price")); pro.setBrand(rs.getString("brand")); pro.setModel(rs.getString("model")); pro.setPicture(rs.getString("picture")); pro.setDescription(rs.getString("description")); list.add(pro); } } catch (Exception e) { e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return list; } // 根据Id查找商品 public product findPorductById(int productId) { product pro = null; String sql = "select * from product where productID='"+productId+"'"; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { pro = new product(); pro.setProductId(rs.getInt("productId")); pro.setSerialNumber(rs.getString("serialNumber")); pro.setName(rs.getString("name")); pro.setPrice(rs.getDouble("price")); pro.setBrand(rs.getString("brand")); pro.setModel(rs.getString("model")); pro.setPicture(rs.getString("picture")); pro.setDescription(rs.getString("description")); } } catch (Exception e) { e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return pro; } // 分页查找商品 public List showProductForPage(int pageNum) { List list = new ArrayList(); product pro = null; int rowBegin = 0; if (pageNum > 1) { rowBegin = 6 * (pageNum - 1); } String sql = "select top 6 * from PRODUCT where productID not in ( select top " + rowBegin + "productID from PRODUCT order by productID desc)order by productID desc"; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { pro = new product(); pro.setProductId(rs.getInt("productId")); pro.setSerialNumber(rs.getString("serialNumber")); pro.setName(rs.getString("name")); pro.setPrice(rs.getDouble("price")); pro.setBrand(rs.getString("brand")); pro.setModel(rs.getString("model")); pro.setPicture(rs.getString("picture")); pro.setDescription(rs.getString("description")); list.add(pro); } } catch (Exception e) { e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return list; } //添加 public int add(product pro) { String sql = "insert into product values(?,?,?,?,?,?,?)"; String[] a = new String[7]; a[0] = pro.getSerialNumber(); a[1] = pro.getName(); a[2] = pro.getBrand(); a[3] = pro.getModel(); a[4] = String.valueOf(pro.getPrice()); a[5] = pro.getPicture(); a[6] = pro.getDescription(); return this.executeSQL(sql, a); } // 根据id删除 public int delete(int productId) { String sql = "delete from product where productId=?"; String[] p = { "" + productId }; return this.executeSQL(sql, p); } // 根据品牌查找 public List findListProductByBrand(String brand) { List list = new ArrayList(); product pro = null; String sql = "select * from PRODUCT where name like'%"+brand+"%'"; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { pro = new product(); pro.setProductId(rs.getInt("productId")); pro.setSerialNumber(rs.getString("serialNumber")); pro.setName(rs.getString("name")); pro.setPrice(rs.getDouble("price")); pro.setBrand(rs.getString("brand")); pro.setModel(rs.getString("model")); pro.setPicture(rs.getString("picture")); pro.setDescription(rs.getString("description")); list.add(pro); } } catch (Exception e) { e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return list; } // 根据商品名称查询 public List findListProductByName(String name) { List list = new ArrayList(); product pro = null; String sql = "select * from PRODUCT where name like'%"+name+"%'"; try { conn = this.getConn(); pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { pro = new product(); pro.setProductId(rs.getInt("productId")); pro.setSerialNumber(rs.getString("serialNumber")); pro.setName(rs.getString("name")); pro.setPrice(rs.getDouble("price")); pro.setBrand(rs.getString("brand")); pro.setModel(rs.getString("model")); pro.setPicture(rs.getString("picture")); pro.setDescription(rs.getString("description")); list.add(pro); } } catch (Exception e) { e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return list; } // 修改 public int update(product pro) { String sql = "serialNumber=?,name=?,brand=?," + " model=?,description=?,price=?,picture=? where productID=?"; String[] p = new String[8]; p[0] = pro.getSerialNumber(); p[1] = pro.getName(); p[2] = pro.getBrand(); p[3] = pro.getModel(); p[4] = String.valueOf(pro.getPrice()); p[5] = pro.getPicture(); p[6] = pro.getDescription(); p[7] = "" + pro.getProductId(); return this.executeSQL(sql, p); } }