花椒外教实验Productdao.java

package com.chenxinzi.dao;

import java.io.InputStream;
import java.sql.Blob;
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 org.apache.log4j.Logger;

import com.chenxinzi.model.Product;

/**
 * A data access object (DAO) providing persistence and search support for
 * Product entities. Transaction control of the save(), update() and
 * delete() operations can directly support Spring container-managed
 * transactions or they can be augmented to handle user-managed Spring
 * transactions. Each of these methods provides additional information for how
 * to configure it for the desired type of transaction control.
 * 
 * @see com.dabing.week12.product
 * @author MyEclipse Persistence Tools
 */
public class ProductDao implements IProductDao {
	private static final Logger log = Logger.getLogger(ProductDao.class);
	
		public static final String PRODUCT_ID = "productId";
		public static final String PRODUCT_NAME = "productName";
		public static final String PRODUCT_DESCRIPTION = "productDescription";
		public static final String PICTURE = "picture";
		public static final String PRICE = "price";
		public static final String CATEGORY_ID = "categoryId";
	@Override 
	public int save(Connection con,Product instance) {
		log.debug("saving product instance");
		int flag = 0;
		try {
			String sql = "insert into Product(productname,productdescription,picture,price,categoryid)values(?,?,?,?,?)";
			PreparedStatement statement = con.prepareStatement(sql);
			statement.setString(1, instance.getProductName());
			statement.setString(2, instance.getProductDescription());
			statement.setString(3, instance.getPicture());
			statement.setDouble(4, instance.getPrice());
			statement.setInt(5, instance.getCategoryId());
			flag = statement.executeUpdate();
			log.debug("save successful");
		} catch (Exception re) {
			log.error("save failed", re);
			try {
				throw re;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	@Override 
	public int delete(Connection con,Product instance) {
		log.debug("deleting product instance");
		int flag = 0;
		try {
			String sql = "delete from product where productid=?";
			PreparedStatement	statement = con.prepareStatement(sql);
			statement.setInt(1, instance.getProductId());
			flag = statement.executeUpdate();
			log.debug("delete successful");
		} catch (Exception re) {
			log.error("delete failed", re);
			try {
				throw re;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	@Override
	public int update(Connection con,Product instance) {
		log.debug("Updatig Product instance");
		int flag = 0;
		try {
			String sql = "update product set productname=?,productdescription=?,picture=?,price=?,categoryid=? where productid=?";
			PreparedStatement statement = con.prepareStatement(sql);
			statement.setString(1, instance.getProductName());
			statement.setString(2, instance.getProductDescription());
			statement.setString(3, instance.getPicture());
			statement.setDouble(4, instance.getPrice());
			statement.setInt(5, instance.getCategoryId());
			statement.setInt(6, instance.getProductId());
			flag = statement.executeUpdate();
			log.debug("Updatig successful");
		} catch (Exception re) {
			log.error("Updatig failed", re);
			try {
				throw re;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	@Override 
	public Product findById(Connection con,java.lang.Integer id) {
		log.debug("getting product instance with id: " + id);
		Product instance =new Product();
		try {
			String queryString = "select * from product as model where model.productId= ?";
			PreparedStatement statement = con.prepareStatement(queryString);
			statement.setInt(1, id);
		ResultSet	resultSet = statement.executeQuery();
			while(resultSet.next()){
				instance.setCategory(resultSet.getInt("categoryID"));
				instance.setPicture(resultSet.getString("picture"));
				instance.setPrice(resultSet.getDouble("price"));
				instance.setProductDescription(resultSet.getString("productdescription"));
			//	instance.setProductId(resultSet.getInt("productID"));
				instance.setProductName(resultSet.getString("productName"));
			}
//			
		} catch (Exception re) {
			log.error("get failed", re);
			try {
				throw re;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return instance ;
	}
	@Override 
	public List<Product> findByCategoryId(Connection con,int categoryId) {
		log.debug("finding product instance by categoryId");
		List<Product> productList=new ArrayList<Product>();
		try {
			String sql = "select * from product as model where model.categoryId= ?";
			//add code 
			PreparedStatement statement = con.prepareStatement(sql);
			statement.setInt(1, categoryId);
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next()){
				Product p = new Product();
				p.setCategory(categoryId);
				p.setPicture(resultSet.getString("picture"));
				p.setPrice(resultSet.getDouble("price"));
				p.setProductDescription(resultSet.getString("productdescription"));
				p.setProductId(resultSet.getInt("productid"));
				p.setProductName(resultSet.getString("productName"));
				productList.add(p);
			}
			log.debug("find by example successful, result size: " + productList.size());
		
		} catch (Exception re) {
			log.error("find by example failed", re);
			try {
				throw re;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return productList;
	}
	@Override 
	public List<Product> findByProperty(Connection con,String propertyName, Object value) {
		log.debug("finding product instance with property: " + propertyName + ", value: " + value);
		List<Product> list=new ArrayList<Product>();
		try {
			String queryString = "select * from product as model where model." + propertyName + "= ?";
			PreparedStatement statement = con.prepareStatement(queryString);
			statement.setObject(1, value);
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next()){
				Product p = new Product();
				p.setCategory(resultSet.getInt("categoryID"));
				p.setPicture(resultSet.getString("picture"));
				p.setPrice(resultSet.getDouble("price"));
				p.setProductDescription(resultSet.getString("productdescription"));
				p.setProductId(resultSet.getInt("productid"));
				p.setProductName(resultSet.getString("productName"));
				list.add(p);
			}
			
		} catch (Exception re) {
			log.error("find by property name failed", re);
			try {
				throw re;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	@Override 
	public List<Product> findByProductName(Connection con,Object name) {
		return findByProperty(con,PRODUCT_NAME, name);
	}
	@Override 
	public List<Product> findByPrice(Connection con,Object price) {
		return findByProperty(con,PRICE, price);
	}
	@Override 
	public List<Product> findByPicture(Connection con,Object photo) {
		return findByProperty(con, PICTURE, photo);
	}
	@Override 
	public List<Product> findAll(Connection con) {
		log.debug("finding all product instances");
		List<Product> list=new ArrayList<Product>();
		try {
			String queryString = "select * from  Product";
			PreparedStatement	statement = con.prepareStatement(queryString);
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next()){
				Product p = new Product();
				p.setCategory(resultSet.getInt("categoryid"));
				p.setPicture(resultSet.getString("picture"));
				p.setPrice(resultSet.getDouble("price"));
				p.setProductDescription(resultSet.getString("productdescription"));
				p.setProductId(resultSet.getInt("productId"));
				p.setProductName(resultSet.getString("productName"));
				list.add(p);
			}
		
		} catch (Exception re) {
			log.error("find all failed", re);
			try {
				throw re;
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	public int save(Connection con, Product product, InputStream inputStream) {
		log.debug("saving product instance");
		int flag = 0;
		try {
			String sql = "insert into Product(productname,productdescription,picture,price,"
					+ "categoryid)values(?,?,?,?,?)";
			PreparedStatement statement = con.prepareStatement(sql);
			statement.setString(1, product.getProductName());
			statement.setString(2, product.getProductDescription());
			if(inputStream!=null)
				statement.setBlob(3, inputStream);
			statement.setDouble(4, product.getPrice());
			statement.setInt(5, product.getCategoryId());
			flag = statement.executeUpdate();
			log.debug("save successful");
		} catch (Exception re) {
			log.error("save failed", re);
			try {
				throw re;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	/**
	 * Get picture of product by productId
	 * @param con
	 * @param id
	 * @return byte[]
	 */
	public byte[] getImgById(Connection con, int id) {
		log.debug("finding picture by productId");
		byte[] imgByte=null;
		try {
			String queryString = "select picture from  Product where productId=?";
			PreparedStatement	statement = con.prepareStatement(queryString);
			statement.setInt(1, id);
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next()){
				Blob blob =resultSet.getBlob("picture");
				imgByte=blob.getBytes(1, (int)blob.length());
			}
		
		} catch (Exception re) {
			log.error("find picture failed", re);
			try {
				throw re;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return imgByte;	}//end getImgById

	
}