productdao.java

某电子商品销售公司为了提升企业形象和商品销售量

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);
	}

}