北大青鸟企业宣传网站

package s2jsp.bysj.dao.impl; import s2jsp.bysj.dao.BaseDao; import s2jsp.bysj.dao.ProductDao; import s2jsp.bysj.entity.Product; import java.sql.*; import java.util.ArrayList; import java.util.List; public class ProductDaoImpl extends BaseDao implements ProductDao{ private Connection conn; private PreparedStatement pstmt; private ResultSet rs; public static final int PAGE_NUM = 5;// 定义产品列表页面显示产品个数 /* * 查询所有产品信息 */ public List findAllProduct(int page){ List list=new ArrayList(); int rowBegin = 0; if(page>1){ rowBegin = PAGE_NUM * (page - 1); } String sql="select top 5 * 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()){ Product product=new Product(); product.setProductID(rs.getInt("productID")); product.setSerialNumber(rs.getString("serialNumber")); product.setName(rs.getString("name")); product.setPrice(rs.getDouble("price")); product.setBrand(rs.getString("brand")); product.setModel(rs.getString("model")); product.setPicture(rs.getString("picture")); product.setDescription(rs.getString("description")); list.add(product); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return list; } /* * 根据产品ID查找相关产品信息 */ public Product findProductByID(String productID){ Product product=null; String sql="select * from product where productID=?"; try { conn=this.getConn(); pstmt=conn.prepareStatement(sql); pstmt.setString(1, productID); rs=pstmt.executeQuery(); if(rs.next()){ product=new Product(); product.setProductID(rs.getInt("productID")); product.setSerialNumber(rs.getString("serialNumber")); product.setName(rs.getString("name")); product.setPrice(rs.getDouble("price")); product.setBrand(rs.getString("brand")); product.setModel(rs.getString("model")); product.setPicture(rs.getString("picture")); product.setDescription(rs.getString("description")); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ this.closeAll(conn, pstmt, rs); } return product; } /* * 实现产品分页显示 */ public List showProductForPage(int page){ List list = new ArrayList(); int number = 0; if (page > 1) { number = PAGE_NUM * (page - 1); } String sql = "select top " + PAGE_NUM + " * from product where productID not in ( select top " + number + " 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()) { Product product = new Product(); product.setProductID(rs.getInt("productID")); product.setSerialNumber(rs.getString("serialNumber")); product.setName(rs.getString("name")); product.setBrand(rs.getString("brand")); product.setModel(rs.getString("model")); product.setPrice(rs.getDouble("price")); product.setPicture(rs.getString("picture")); product.setDescription(rs.getString("description")); list.add(product); } } catch (Exception e) { e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return list; } /* * 根据产品ID删除相关产品信息 */ public int deleteProductByID(String productID){ String sql="delete from product where productID=?"; String[] param=new String[]{productID}; return this.executeSQL(sql, param); } /* * 添加产品信息 */ public int insertProduct(Product product){ String sql="insert into product values(?,?,?,?,"+product.getPrice()+",?,?)"; String[] param=new String[]{product.getSerialNumber(),product.getName(), product.getBrand(), product.getModel(),product.getPicture(), product.getDescription()}; return this.executeSQL(sql, param); } /* * 根据商品ID和对象参数修改商品信息 */ public int updateProduct(Product product){ String sql="update product set serialnumber=?,name=?,brand=?,model=?,price="+ product.getPrice()+ ",picture=?,description=? where productID="+ product.getProductID(); String[] param = new String[] { product.getSerialNumber(),product.getName(), product.getBrand(), product.getModel(),product.getPicture(), product.getDescription() }; return this.executeSQL(sql, param); } }