/*
    * 
    * 
    */
   package net.sf.diaz;
   
   import java.sql.*;
   import java.math.BigDecimal;
   import java.util.*;
  
  /**
   * 
   * @author Sean C. Sullivan
   * 
   */
  public abstract class AbstractJdbcDao
  {
  	private int fetchSize = 500;
  	private int maxRows = 2000;
  	private int queryTimeout = 180; /* seconds */
  	private boolean closeQuietly = false;
  	private int resultSetType = ResultSet.TYPE_FORWARD_ONLY;
  	private int resultSetConcurrency = ResultSet.CONCUR_READ_ONLY;
  	private int transactionIsolation = Connection.TRANSACTION_READ_COMMITTED;
  
  	protected int getTransactionIsolation()
  	{
  		return transactionIsolation;
  	}
  
  	protected void setTransactionIsolation(int transactionIsolation)
  	{
  		this.transactionIsolation = transactionIsolation;
  	}
  
  	/**
  	 * 
  	 */
  	protected GeneratedKeys executeInsert(CharSequence sql, Object... queryParameters)
  	{
  		GeneratedKeys keys = (GeneratedKeys) executeUpdate(sql, Statement.RETURN_GENERATED_KEYS, queryParameters);
  		return keys;
  	}
  
  
  	protected Object executeUpdate(CharSequence sql)
  	{
  		return executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
  	}
  	
  	/**
  	 * 
  	 * @param sql
  	 * @param queryParameters
  	 * 
  	 */
  	protected Object executeUpdate(CharSequence sql, int autoGeneratedKeys, Object... queryParameters)
  	{
  		Connection conn = null;
  		
  		Object result = null;
  		
  		try
  		{	
  			conn = getConnection();
  			result = executeUpdate(conn, sql, autoGeneratedKeys, queryParameters);
  			return result;
  		}
  		catch (SQLException ex)
  		{
  			throw new DataAccessException(
  							buildMessage(sql, queryParameters), 
  							ex);
  		}
  		finally
  		{
  			close(conn, null, null);
  		}
  	}
  
  	/**
  	 * 
  	 * @param conn
  	 * @param sql
  	 * @param queryParameters
  	 * 
  	 */
  	protected Object executeUpdate(Connection conn, CharSequence sql, int autoGeneratedKeys, Object... queryParameters)
  	{
  		PreparedStatement ps = null;
  		
  		Object result = null;
  		
  		String strSQL = sql.toString();
  		
  		try
  		{
  			conn = getConnection();
  			ps = conn.prepareStatement(strSQL, autoGeneratedKeys);
 			setPreparedStatementParameters(ps, queryParameters);
 			int rowCount = ps.executeUpdate();
 			if (autoGeneratedKeys == Statement.RETURN_GENERATED_KEYS)
 			{
 				result = new GeneratedKeys(ps.getGeneratedKeys());
 			}
 			else
 			{
 				result = new Integer(rowCount);
 			}
 			return result;
 		}
 		catch (SQLException ex)
 		{
 			throw new DataAccessException(
 							buildMessage(strSQL, queryParameters), 
 							ex);
 		}
 		finally
 		{
 			close(null, null, ps);
 		}
 	}
 
 	/**
 	 * 
 	 * @param sql
 	 * @param queryParameters
 	 * @return a List containing zero or more {@link Row} objects
 	 * 
 	 */
 	protected List<Row> executeQuery(CharSequence sql, Object... queryParameters)
 	{
 		ResultSet rs = null;
 		PreparedStatement ps = null;
 		Connection conn = null;
 		List<Row> rows = null;
 		
 		String strSQL = sql.toString();
 		
 		try
 		{
 			conn = getConnection();
 			conn.setTransactionIsolation(this.getTransactionIsolation());
 			ps = conn.prepareStatement(strSQL,
 							this.getResultSetType(),
 							this.getResultSetConcurrency());
 			setPreparedStatementParameters(ps, queryParameters);
 			ps.setFetchDirection(ResultSet.FETCH_FORWARD);
 			ps.setFetchSize(this.getFetchSize());
 			ps.setQueryTimeout(this.getQueryTimeout());
 			ps.setMaxRows(this.getMaxRows());
 			rs = ps.executeQuery();
 			rows = buildRows(rs);
 			return rows;
 		}
 		catch (SQLException ex)
 		{
 			throw new DataAccessException(
 							buildMessage(strSQL, queryParameters),
 							ex);
 		}
 		finally
 		{
 			close(conn, rs, ps);
 		}
 		
 	}
 	
 	protected int getFetchSize()
 	{
 		return fetchSize;
 	}
 	
 	protected void setFetchSize(int size)
 	{
 		this.fetchSize = size;
 	}
 	
 	protected List<Row> buildRows(ResultSet rs) throws SQLException
 	{
 		List<Row> rows = new LinkedList<Row>();
 
 		while (rs.next())
 		{
 			Row r = buildSingleRow(rs);
 			rows.add(r);
 		}
 		
 		return rows; 
 	}
 
 	protected Row buildSingleRow(ResultSet rs) throws SQLException
 	{
 		
 		// todo : add code to handle BLOB's?
 		
 		ResultSetMetaData metaData = rs.getMetaData();
 
 		int colCount = metaData.getColumnCount();
 		IndexedMap<String, Object> map = new IndexedMapImpl<String, Object>(colCount);
 		for (int colIndex = 1; colIndex < colCount; colIndex++)
 		{
 			String colName = metaData.getColumnName(colIndex).toUpperCase();
 			Object colValue = rs.getObject(colIndex);
 			map.put(colName, colValue);
 		}
 		return new Row(map);
 	}
 	
 	protected boolean getCloseQuietly()
 	{
 		return closeQuietly;
 	}
 	
 	protected void setCloseQuietly(boolean b)
 	{
 		closeQuietly = b;
 	}
 	
 	protected void close(Connection c, ResultSet rs, Statement st)
 	{
 		Exception caught = null;
 		
 		try
 		{
 			if (rs != null)
 			{
 				rs.close();
 			}
 		}
 		catch (Exception ex)
 		{
 			caught = ex;
 		}
 		try
 		{
 			if (st != null)
 			{
 				st.close();
 			}
 		}
 		catch (Exception ex)
 		{
 			caught = ex;
 		}
 		try
 		{
 			if (c != null)
 			{
 				c.close();
 			}
 		}
 		catch (Exception ex)
 		{
 			caught = ex;
 		}
 		
 		if ( ( ! getCloseQuietly()) && (caught != null) )
 		{
 			throw new DataAccessException(caught); 
 		}
 	}
 	
 
 	abstract protected Connection getConnection() throws SQLException;
 	
 	protected void setPreparedStatementParameters(PreparedStatement ps, Object[] parameters)
 				throws SQLException
 	{
 		for (int i = 0; i < parameters.length; i++)
 		{
 			int parameterIndex = i + 1;
 			Object param = parameters[i];
 			if (param == null)
 			{
 				ps.setObject(parameterIndex, param);
 			}
 			else if (param instanceof java.sql.Time)
 			{
 				ps.setTime(parameterIndex, (java.sql.Time) param);
 			}
 			else if (param instanceof java.sql.Timestamp)
 			{
 				ps.setTimestamp(parameterIndex, (java.sql.Timestamp) param);
 			}
 			else if (param instanceof java.sql.Date)
 			{
 				java.sql.Date value = (java.sql.Date) param;
 				ps.setDate(parameterIndex, value);
 			}
 			else if (param instanceof java.util.Date)
 			{
 				long time = ((java.util.Date) param).getTime();
 				java.sql.Date value = new java.sql.Date(time);
 				ps.setDate(parameterIndex, value);
 			}
 			else if (param instanceof java.util.Calendar)
 			{
 				Calendar c = (Calendar) param;
 				java.sql.Date d = new java.sql.Date(c.getTime().getTime());
 				ps.setDate(parameterIndex, d, c);
 			}
 			else if (param instanceof String)
 			{
 				ps.setString(parameterIndex, (String) param);
 			}
 			else if (param instanceof CharSequence)
 			{
 				String value = param.toString();
 				ps.setString(parameterIndex, value);
 			}
 			else if (param instanceof char[])
 			{
 				String value = new String( (char[]) param );
 				ps.setString(parameterIndex, value);
 			}
 			else if (param instanceof Character)
 			{
 				ps.setString(parameterIndex, String.valueOf(param));
 			}
 			else if (param instanceof Enum)
 			{
 				Enum e = (Enum) param;
 				int value = e.ordinal();
 				ps.setInt(parameterIndex, value);
 			}
 			else if (param instanceof Integer)
 			{
 				int value = ((Integer) param).intValue();
 				ps.setInt(parameterIndex, value);
 			}
 			else if (param instanceof Short)
 			{
 				short value = ((Short) param).shortValue();
 				ps.setShort(parameterIndex, value);
 			}
 			else if (param instanceof Long)
 			{
 				long value = ((Long) param).longValue();
 				ps.setLong(parameterIndex, value);
 			}
 			else if (param instanceof BigDecimal)
 			{
 				BigDecimal value = (BigDecimal) param;
 				ps.setBigDecimal(parameterIndex, value);
 			}
 			else if (param instanceof java.sql.Ref)
 			{
 				Ref value = (Ref) param;
 				ps.setRef(i, value);
 			}
 			else if (param instanceof Double)
 			{
 				double value = ((Double) param).doubleValue();
 				ps.setDouble(parameterIndex, value);
 			}
 			else if (param instanceof Float)
 			{
 				float value = ((Float) param).floatValue();
 				ps.setFloat(parameterIndex, value);
 			}
 			else if (param instanceof Boolean)
 			{
 				boolean value = ((Boolean) param).booleanValue();
 				ps.setBoolean(parameterIndex, value);
 			}
 			else if (param instanceof java.sql.Array)
 			{
 				ps.setArray(parameterIndex, (java.sql.Array) param);
 			}
 			else if (param instanceof java.net.URL)
 			{
 				ps.setURL(parameterIndex, (java.net.URL) param);
 			}
 			else if (param instanceof byte[])
 			{
 				ps.setBytes(parameterIndex, (byte[]) param);
 			}
 			else if (param instanceof Clob)
 			{
 				ps.setClob(parameterIndex, (Clob) param);
 			}
 			else if (param instanceof Blob)
 			{
 				ps.setBlob(parameterIndex, (Blob) param);
 			}
 			else if (param instanceof Byte)
 			{
 				byte value = ((Byte) param).byteValue();
 				ps.setByte(parameterIndex, value);
 			}
 			else
 			{
 				throw new IllegalArgumentException("unexpected parameter type: "
 							+ param.getClass());
 			}
 		}
 	}
 	
 	protected String buildMessage(CharSequence sql, Object[] queryParameters)
 	{
 		StringBuffer sb = new StringBuffer();
 		sb.append(sql);
 		if (queryParameters.length > 0)
 		{
 			sb.append(",");
 			for (Object o : queryParameters)
 			{
 				sb.append(" [");
 				sb.append(o);
 				sb.append("] ");
 			}
 		}
 		return sb.toString();
 	}
 	
 	protected int getQueryTimeout()
 	{
 		return queryTimeout;
 	}
 	
 	protected void setQueryTimeout(int value)
 	{
 		queryTimeout = value;
 	}
 	
 	protected int getMaxRows() 
 	{
 		return maxRows;
 	}
 	
 
 	protected void setMaxRows(int value) 
 	{
 		this.maxRows = value;
 	}
 	
 	public String toString()
 	{
 		return "QueryTimeout=" 
 			+ getQueryTimeout()
 			+ ",FetchSize="
 			+ getFetchSize()
 			+ ",MaxRows="
 			+ getMaxRows();
 	}
 
 	protected int getResultSetType()
 	{
 		return this.resultSetType;
 	}
 	
 	protected void setResultSetType(int value)
 	{
 		this.resultSetType = value;
 	}
 
 	protected int getResultSetConcurrency()
 	{
 		return resultSetConcurrency;
 	}
 
 	protected void setResultSetConcurrency(int resultSetConcurrency)
 	{
 		this.resultSetConcurrency = resultSetConcurrency;
 	}
 	
 }