项目实战企业宣传网站

package s2jsp.bysj.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import s2jsp.bysj.entity.Product; public class ProductDao extends BaseDao { private Connection conn = null; // 保存数据库连接 private PreparedStatement pstmt = null; // 用于执行SQL语句 private ResultSet rs = null; // 用户保存查询结果集 public static final int PAGE_NUM = 5;// 定义产品列表页面显示产品个数 /** * 查询所有产品信息 * * @return 产品信息列表 */ 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 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 (Exception e) { e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return list; } /** * 根据产品ID查找相关产品信息 * * @return 产品对象 */ 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 (Exception e) { e.printStackTrace(); } finally { this.closeAll(conn, pstmt, rs); } return product; } /** * 实现产品分页显示 * * @param page * 当前页数 * @return 显示产品的列表 */ 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删除相关产品信息 * * @param productID * 产品ID * @return 执行SQL语句影响数据库的行数 */ public int deleteProductByID(String productID) { String sql = "delete from product where productID = ? "; String[] param = new String[] { productID }; int count = this.executeSQL(sql, param); return count; } /** * 添加产品信息 * * @param product * 封装了信息的产品对象 * @return 执行SQL语句所影响数据库的行数 */ 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和对象参数修改商品信息 * @param product封装了数据的商品对象 * @return 返回执行SQL语句，数据库影响行数 */ 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); } }