package com.hygj.service;

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

import com.hygj.bean.ProductsBean;

public class ProductsService {
	private Connection conn=null;
	private PreparedStatement pt=null;
	private ResultSet rs=null;
	private ArrayList products=null;
	private ProductsBean product=null;
	
	/**
	 * 返回全部商品
	 */
	public ArrayList getAll(){
		conn=com.hygj.util.ConnUtil.getConn();
		try {
			pt=conn.prepareStatement("select * from products");
			rs=pt.executeQuery();
			products=new ArrayList();
			while(rs.next()){
				product=new ProductsBean(rs.getInt(1),rs.getString(2),rs.getFloat(3),rs.getInt(4));
				products.add(product);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			close();			
		}
		
		return products;
	}
	
	
	/**
	 * 根據編號進行查詢	 *
	 */
	public ProductsBean getById(int productId){
		conn=com.hygj.util.ConnUtil.getConn();
		try {
			pt=conn.prepareStatement("select * from products where productId=?");
			pt.setInt(1,productId);
			rs=pt.executeQuery();
			if(rs.next()){
				product=new ProductsBean(rs.getInt(1),rs.getString(2),rs.getFloat(3),rs.getInt(4));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return product;
	}
	
	
	public void close(){
		try{
			if(rs!=null){
				rs.close();
			}
			if(pt!=null){
				pt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}catch(Exception e){
			
		}
		
	}
	
	
	
	
	
	
	
	
}