package Dao;

import java.util.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;


public class SQLCommandBean {

	private Connection conn;
	private String sqlValue;
	private List values;
	
	//設定連接類
	public void setConn(Connection conn) {
		this.conn = conn;
	}
	//設定SQL語句
	public void setSqlValue(String sqlValue) {
		this.sqlValue = sqlValue;
	}
	//設定SQL語句的參數
	public void setValues(List values) {
		this.values = values;
	}
	//執行語句
	public Result executeQuery()throws Exception
	{
		
		Result result = null;
		ResultSet rs = null;
		PreparedStatement pst = null;
		Statement st = null;
		try
		{
			
			if(values != null && values.size() > 0)
			{
				pst = conn.prepareStatement(this.sqlValue);
				setValues(pst,values);
				rs = pst.executeQuery();
			}
			else
			{
				st = conn.createStatement();
				rs = st.executeQuery(this.sqlValue);
				
			}
			result = ResultSupport.toResult(rs);

			
		}
		finally
		{
			try
			{
				
				if(rs != null)
				{
					rs.close();
				}
				if(st != null)
				{
					st.close();
				}
				if(pst != null)
				{
					pst.close();
				}
			}catch(Exception ex)
			{
				System.out.println("查詢語句出錯");
				ex.printStackTrace();
			}
			
		}
		return result;
	}
	
	//執行Update語句
	public int executeUpdate() throws Exception
	{
		int noOfRows = 0;
		ResultSet rs = null;
		PreparedStatement pst = null;
		Statement st = null;
		try
		{
			if(values != null && values.size() > 0)
			{
				pst = conn.prepareStatement(sqlValue);
				setValues(pst,values);
				noOfRows = pst.executeUpdate();
			}
			else
			{
				st = conn.createStatement();
				noOfRows = st.executeUpdate(this.sqlValue);
		
			}
		}finally
		{
			try
			{
				
				if(rs != null)
				{
					rs.close();
				}
				if(st != null)
				{
					st.close();
				}
				if(pst != null)
				{
					pst.close();
				}
			}catch(Exception ex)
			{
				ex.printStackTrace();
			}
			
		}
		return noOfRows;
	}
	//設定語句參數的方法
	private void setValues(PreparedStatement pst,List values)throws Exception
	{
		for(int i = 0; i< values.size(); i ++)
		{
			Object v = values.get(i);
			pst.setObject(i+1,v);
		}
	}
	
	
}