productdao.java

简单的企业宣传的网站

package dao;
import entity.*;
import java.sql.*;
import java.util.*;
public class ProductDao extends BaseDao{
	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 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 (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally
		{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	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();
			while(rs.next())
			{
				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"));
			}
		} catch (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			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 (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally
		{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public int deleteProductByID(String productID)
	{
		String sql="delete from product where productID = ?";
		String[] param = new String[] { productID };
		int count = this.excuteSQL(sql, param);
		return count;
	}
	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()};
		int count=this.excuteSQL(sql, param);
		return count;
	}
	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.excuteSQL(sql, param);
	}
}