productdaoimpl.java

企业宣传网站后台

package com.westaccp.jsp.company.dao.impl;

import java.util.ArrayList;
import java.util.List;

import com.westaccp.jsp.company.dao.ProductDao;
import com.westaccp.jsp.company.model.Product;
import java.sql.*;

public class ProductDaoImpl extends BaseDao implements ProductDao {
	
	private Connection con = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;

	public int addProduct(Product product) {
		int rowCount = 0;
		String sql = "insert into tabProduct values(?, ?, ?, ?, ?, ?, ?)";
		
		try {
			con = this.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, product.getSeiralNumber());
			pstmt.setString(2, product.getName());
			pstmt.setString(3, product.getBrand());
			pstmt.setString(4, product.getModel());
			pstmt.setDouble(5, product.getPrice());
			pstmt.setString(6, product.getPicture());
			pstmt.setString(7, product.getDescription());
			rowCount = pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDB(null, pstmt, con);
		}
		
		return rowCount;
	}

	public int deleteProduct(int productId) {
		int rowCount = 0;
		String sql = "delete from tabProduct where productId = ?";
		
		try {
			con = this.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, productId);
			rowCount = pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDB(null, pstmt, con);
		}
		
		return rowCount;
	}

	public List findListProduct() {
		List productList = null;
		String sql = "select * from tabProduct";
		
		try {
			con = this.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			productList = new ArrayList();
			while(rs.next()) {
				Product product = new Product();
				product.setProductId(rs.getInt("productId"));
				product.setSeiralNumber(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"));
				productList.add(product);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDB(rs, pstmt, con);
		}
		
		return productList;
	}

	public Product findProduct(int productId) {
		Product product = null;
		String sql = "select * from tabProduct where productId = ?";
		
		try {
			con = this.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, productId);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				product = new Product();
				product.setProductId(rs.getInt("productId"));
				product.setSeiralNumber(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 (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDB(rs, pstmt, con);
		}
		
		return product;
	}

	public int updateProduct(Product product) {
		int rowCount = 0;
		String sql = "update tabProduct set serialNumber=?, name=?, brand=?, model=?, price=?, picture=?, description=? where productId=?";
		
		try {
			con = this.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, product.getSeiralNumber());
			pstmt.setString(2, product.getName());
			pstmt.setString(3, product.getBrand());
			pstmt.setString(4, product.getModel());
			pstmt.setDouble(5, product.getPrice());
			pstmt.setString(6, product.getPicture());
			pstmt.setString(7, product.getDescription());
			pstmt.setInt(8, product.getProductId());
			rowCount = pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDB(null, pstmt, con);
		}
		
		return rowCount;
	}
	
	public int findCountProduct() {
		int count = 0;
		String sql = "select count(*) as count from tabProduct";
		
		try {
			con = this.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDB(rs, pstmt, con);
		}
		
		return count;
	}
	
	public int calculatePages(int resPerPage) {
		int totalRes = this.findCountProduct();
		if(totalRes % resPerPage == 0) {
			return totalRes / resPerPage;
		} else {
			return totalRes / resPerPage + 1;
		}
	}
	
	public List findListProduct(int resPerPage, int pageNo) {
		List productList = null;
		String sql = "select top " + resPerPage + " * from tabProduct where productId not in (select top " + (pageNo - 1) * resPerPage + " productId from tabProduct)";
		
		try {
			con = this.getCon();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			productList = new ArrayList();
			while(rs.next()) {
				Product product = new Product();
				product.setProductId(rs.getInt("productId"));
				product.setSeiralNumber(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"));
				productList.add(product);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
 		} finally {
 			this.closeDB(rs, pstmt, con);
 		}
 		
 		return productList;
	}
	
	public static void main(String[] args) {
		ProductDaoImpl impl = new ProductDaoImpl();
		List list = impl.findListProduct(15, 1);
		for(int i=0; i<list.size(); i++) {
			Product pro = (Product) list.get(i);
			System.out.println(pro.getName());
		}
	}
}