productdaojdbcimpl.java

shoppingCar 购物车

package com.tarena.shoppingcar.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tarena.shoppingcar.entity.Product;
import com.tarena.shoppingcar.factory.ProductException;
import com.tarena.shoppingcar.util.ConnectionFactory;
import com.tarena.shoppingcar.util.JdbcUtil;

public class ProductDaoJdbcImpl implements ProductDao {
	/**
	 * 查看所有商品
	 */
	public List<Product> findAll(Product product) throws ProductException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Product> products = null;
		try {
			con = ConnectionFactory.getConnection();
			StringBuffer sb = new StringBuffer("select id,pname,description,price from product");
			sb.append(" where 1=1");
			if(product.getId() != 0){
				sb.append(" and id=?");
			}
			pstmt = con.prepareStatement(sb.toString());
			if(product.getId() != 0){
				pstmt.setInt(1, product.getId());
			}
			rs = pstmt.executeQuery();
			products = getProducts(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(con, pstmt, rs);
		}
		return products;
	}

	public void modify(Product product) throws ProductException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "update product set pname=?,price=?,description=? where id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, product.getPname());
			pstmt.setDouble(2, product.getPrice());
			pstmt.setString(3, product.getDiscription());
			pstmt.setInt(4, product.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(con, pstmt);
		}
	}

	public void remove(int id) throws ProductException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "delete from product where id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(con, pstmt);
		}
	}

	public void save(Product product) throws ProductException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "insert into product(pname,price,description) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, product.getPname());
			pstmt.setDouble(2, product.getPrice());
			pstmt.setString(3, product.getDiscription());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(con, pstmt);
		}
	}
	private List<Product> getProducts(ResultSet rs) throws SQLException{
		List<Product> products = new ArrayList<Product>();
		while(rs.next()){
			Product product = new Product();
			product.setId(rs.getInt(1));
			product.setPname(rs.getString(2));
			product.setDiscription(rs.getString(3));
			product.setPrice(rs.getDouble(4));
			products.add(product);
		}
		return products;
	}
	public static void main(String[] args) {
		ProductDaoJdbcImpl dao = new ProductDaoJdbcImpl();
		List<Product> products = null;
		try {
			products = dao.findAll(new Product());
		} catch (ProductException e) {
			e.printStackTrace();
		}
		System.out.println(products.size());
	}

	public Product findById(int id){
		Product product = new Product();
		product.setId(id);
		List<Product> products = null;
		try {
			products = findAll(product);
		} catch (ProductException e) {
			e.printStackTrace();
		}
		if(products.size() == 0){
			return null;
		}
		return products.iterator().next();
	}

	public int findNum() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int rowCount = -1;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select count(id) from product";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			rowCount = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JdbcUtil.close(con, pstmt, rs);
		}
		return rowCount;
	}

	//按页查询
	public List<Product> findByPage(int StartRow, int rowPerPage) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Product> products = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select id,pname,description,price from product limit ?,?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, StartRow);
			pstmt.setInt(2, rowPerPage);
			rs = pstmt.executeQuery();
			products = getProducts(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(con, pstmt, rs);
		}
		return products;
	}

	public List<Product> findByOrderID(int id) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Product> products = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select id,pname,description,price from product " +
					"where id in(select product_id from item where order_id=?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			products = getProducts(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return products;
	}
}