/*
 * This file is part of Nuts Framework.
 * Copyright(C) 2009-2012 Nuts Develop Team.
 *
 * Nuts Framework is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License any later version.
 * 
 * Nuts Framework is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Nuts Framework. If not, see <http://www.gnu.org/licenses/>.
 */
package nuts.core.orm.sql.engine;

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

import nuts.core.beans.BeanHandler;
import nuts.core.beans.BeanHandlerFactory;
import nuts.core.orm.sql.AbstractSqlExecutor;
import nuts.core.orm.sql.SqlResultSet;
import nuts.core.sql.SqlLogger;
import nuts.core.sql.SqlUtils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * SimpleSqlExecutor.
 * 
 * <pre>
 * <b>SQL GRAMMER:</b>
 * 
 * 1. SELECT column name auto mapping
 *    1.1 column name --> JavaBean property name
 *      ex.) SELECT ID, NAME, ITEM_NAME FROM SAMPLE
 *        ID --> id
 *        NAME --> name
 *        ITEM_NAME --> itemName
 * 
 *    1.2 AS ALIAS --> JavaBean property name
 *      ex.) SELECT ID AS a.id, NAME AS a.name, ITEM_NAME AS a.item.name, B_ITEM_NAME AS a.b.itemName FROM SAMPLE
 *           -->
 *           SELECT ID AS A_0_ID, NAME AS A_0_NAME, ITEM_NAME AS A_0_ITEM_0_NAME, ITEM_NAME AS A_0_B_0_ITEM_NAME FROM SAMPLE
 *      
 *       mapping: 
 *         A_0_ID --> a.id
 *         A_0_NAME --> a.name
 *         A_0_ITEM_0_NAME --> a.item.name
 * 
 * 2. SQL Parameter
 *    :xxx --> PreparedStatement binding parameter (xxx: JavaBean property name)
 *    ::yyy --> replacement (yyy: JavaBean property name) [SQL injection!]
 * 
 *    ex.) SELECT * FROM SAMPLE
 *         WHERE
 *               ID=:id
 *           AND NAME=:name
 *           AND LIST IN (:list)
 *           AND KIND=:kind
 *         ORDER BY 
 *           ::orderCol ::orderDir
 * 
 *    ex.) UPDATE SAMPLE
 *         SET
 *           NAME=:name,
 *           KIND=:kind,
 *           STR='@kind[,KIND=:kind]'
 *         WHERE
 *           ID=:id
 * 
 * 3. SQL Amendment
 *    1) Delete 'AND/OR' after 'WHERE' or '('
 *     ex.) WHERE AND ID IS NULL --> WHERE ID IS NULL
 *          (AND NAME IS NULL) --> (NAME IS NULL)
 *  
 *    2) Delete ',' after 'SET'
 *     ex.) SET ,ID=1 ,NAME='a' --> SET ID=1 , NAME='a'
 *     
 * 4. Call Procedure/Function
 *    ex.) {:count:INTEGER:OUT = call SET_TEST_PRICE(:id,:price:DECIMAL.2:INOUT)}
 *   
 *    Grammer: 
 *      :price:DECIMAL.2:INOUT
 *     
 *      :price --> JavaBean property name
 *      :DECIMAL.2 --> JDBC type & scale
 *      :INOUT --> parameter type
 *         IN: input only (default)
 *         OUT: output only
 *         INOUT: input & output
 *         
 *    @see java.sql.CallableStatement 
 *
 *
 *
 * <b>EXAMPLE:</b>
 *   Class.forName("org.hsqldb.jdbcDriver" );
 *   Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:test", "sa", "");
 *   SqlExecutor executor = new SimpleSqlExecutor(connection);
 *   
 *   
 *   // queryForObject - Map
 *   String sql = "SELECT * FROM TEST WHERE ID=:id";
 *   Map<String, Object> param = new HashMap<String, Object>();
 *   param.put("id", 1001);
 *   
 *   Map result = executor.queryForObject(sql, param, HashMap.class);
 *   // result: { "id": 1001, "name": "test1001", ... }
 *   
 *   
 *   // queryForList - Map
 *   String sql = "SELECT * FROM TEST WHERE ID IN (:idArray)";
 *   Map<String, Object> param = new HashMap<String, Object>();
 *   param.put("idArray", new int[] { 1001, 1003 });
 *   
 *   List<Map> resultList = executor.queryForList(sql, param, HashMap.class);
 *   // resultList: [{ "id": 1001, "name": "test1001", ... }, { "id": 1003, "name": "test1003", ... }]
 *   
 *   
 *   // queryForObject - JavaBean
 *   String sql = "SELECT * FROM TEST WHERE ID=:id";
 *   TestA param = new TestA();
 *   param.id = 1001;
 *   
 *   TestB result = executor.queryForObject(sql, param, TestB.class);
 *   // result: { "id": 1001, "name": "test1001", ... }
 *
 *   
 *   // queryForList - JavaBean
 *   String sql = "SELECT * FROM TEST WHERE ID IN (:idArray)";
 *   TestA param = new TestA();
 *   param.idArray = new int[] { 1001, 1003 };
 *   
 *   List<TestB> resultList = executor.queryForList(sql, param, TestB.class);
 *   // resultList: [{ "id": 1001, "name": "test1001", ... }, { "id": 1003, "name": "test1003", ... }]
 *
 *
 *   // execute - Call Function
 *   String sql = "{:price:DECIMAL.2:OUT = call GET_TEST_PRICE(:id)}";
 *   Map<String, Object> param = new HashMap<String, Object>();
 *   param.put("id", 1001);
 *   
 *   executor.execute(sql, param);
 *   // param: { "id": 1001, "price": 1001.01 }
 *   
 *   
 *   // execute - Call Function
 *   String sql = "{:count:INTEGER:OUT = call SET_TEST_PRICE(:id, :price:DECIMAL.2:INOUT)}";
 *   Map<String, Object> param = new HashMap<String, Object>();
 *   param.put("id", 1001);
 *   param.put("price", new BigDecimal("9999"));
 *   
 *   executor.execute(sql, param);
 *   // param: { "count": 1, "price": 1001.01 }
 *   
 *   
 *   // queryForObject - Call Function
 *   String sql = "{call SELECT_TEST(:id)}";
 *   TestA param = new TestA();
 *   param.id = 1001;
 *   
 *   TestB result = executor.queryForObject(sql, param, TestB.class);
 *   // result: { "id": 1001, "name": "test1001", ... }
 * 
 * </pre>
 */
public class SimpleSqlExecutor extends AbstractSqlExecutor {
	/**
	 * log
	 */
	private static Log log = LogFactory.getLog(SimpleSqlExecutor.class);

	/**
	 * sqlParserCache
	 */
	private static Map<String, SqlParser> sqlParserCache = new HashMap<String, SqlParser>();
	
	/**
	 * Constructor
	 * @param connection connection
	 */
	public SimpleSqlExecutor(Connection connection) {
		super(connection);
	}

	/**
	 * Constructor
	 * 
	 * @param connection connection
	 * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY,
	 *            ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE
	 */
	public SimpleSqlExecutor(Connection connection, int resultSetType) {
		super(connection, resultSetType);
	}

	/**
	 * Constructor
	 * 
	 * @param connection connection
	 * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY,
	 *            ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE
	 * @param resultSetConcurrency one of the following ResultSet constants:
	 *            ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
	 */
	public SimpleSqlExecutor(Connection connection, int resultSetType, int resultSetConcurrency) {
		super(connection, resultSetType, resultSetConcurrency);
	}

	/**
	 * Constructor
	 * 
	 * @param connection connection
	 * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY,
	 *            ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE
	 * @param resultSetConcurrency one of the following ResultSet constants:
	 *            ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
	 * @param resultSetHoldability one of the following ResultSet constants:
	 *            ResultSet.HOLD_CURSORS_OVER_COMMIT or ResultSet.CLOSE_CURSORS_AT_COMMIT
	 */
	public SimpleSqlExecutor(Connection connection, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) {
		super(connection, resultSetType, resultSetConcurrency, resultSetHoldability);
	}

	/**
	 * retrive output parameters
	 * @param cs statement
	 * @param parameters parameters 
	 * @throws SQLException if a SQL exception occurs
	 */
	public static void logOutputParameters(CallableStatement cs, List<SqlParameter> parameters) throws SQLException {
		if (log.isDebugEnabled()) {
			StringBuilder sb = null;
			for (int i = 0; i < parameters.size(); i++) {
				SqlParameter parameter = parameters.get(i);
	
				if (parameter.isOutputAllowed()) {
					Object value = parameter.getTypeHandler().getResult(cs, i + 1);
					if (sb == null) { 
						sb = new StringBuilder();
						sb.append("Output Parameters: { ");
					}
					else {
						sb.append(", ");
					}
					sb.append(parameter.getName());
					sb.append(": ");
					sb.append(value);
					if (value != null) {
						sb.append('[');
						sb.append(value.getClass().getName());
						sb.append(']');
					}
				}
			}
			
			if (sb != null) {
				sb.append(" }");
				log.debug(sb.toString());
			}
		}
	}

	/**
	 * logParameters
	 * @param parameters parameters
	 */
	public static void logParameters(List<SqlParameter> parameters) {
		if (log.isDebugEnabled()) {
			for (int i = 0; i < parameters.size(); i++) {
				SqlParameter parameter = parameters.get(i);
				
				StringBuilder sb = new StringBuilder();
				sb.append("Parameters[" + (i + 1) + "]: { ");
				sb.append("name: ");
				sb.append(parameter.getName());
				sb.append(", jdbcType: ");
				sb.append(parameter.getJdbcType());
				sb.append(", mode: ");
				sb.append(parameter.getMode());
				sb.append(", value: ");
				sb.append(parameter.getValue());
				sb.append(", valueClass: ");
				sb.append(parameter.getValue() == null ? null : parameter.getValue().getClass().getName());
				sb.append(", typeHandler: ");
				sb.append(parameter.getTypeHandler().getClass().getName());
				sb.append(" }");

				log.debug(sb.toString());
			}
		}
	}

	/**
	 * getBeanHandler
	 * @param type class type
	 * @return bean handler
	 */
	protected BeanHandler getBeanHandler(Class type) {
		return BeanHandlerFactory.getInstance().getBeanHandler(type);
	}

	/**
	 * @param sql sql
	 * @return true if the sql is a callable sql statement
	 */
	protected boolean isCallableSql(String sql) {
		for (int i = 0; i < sql.length(); i++) {
			char c = sql.charAt(i);
			if (!Character.isWhitespace(c)) {
				if (c == '{') {
					return true;
				}
				break;
			}
		}
		return false;
	}

	/**
	 * parseSqlStatement
	 * @param sql sql
	 * @param parameterObject parameter object
	 * @param sqlParams sql parameter list
	 * @return translated sql
	 * @throws Exception if an error occurs
	 */
	protected String parseSqlStatement(String sql, Object parameterObject,
			List<SqlParameter> sqlParams) throws Exception {
		SqlParser parser;
		synchronized (sqlParserCache) {
			parser = sqlParserCache.get(sql);
			if (parser == null) {
				parser = createSqlParser(sql);
				sqlParserCache.put(sql, parser);
			}
		}
		return parser.parse(parameterObject, sqlParams);
	}

	/**
	 * createSqlParser
	 * @param sql sql
	 * @return SqlParser instance
	 * @throws Exception if an error occurs
	 */
	protected SqlParser createSqlParser(String sql) throws Exception {
		return new SimpleSqlParser(sql);
	}
	
	/**
	 * prepareStatement
	 * @param sql sql
	 * @param parameterObject parameter object
	 * @param sqlParams sql parameter list
	 * @return JDBC PreparedStatement
	 * @throws SQLException if an sql error occurs
	 */
	protected PreparedStatement prepareStatement(String sql, Object parameterObject,
			List<SqlParameter> sqlParams) throws SQLException {
		String jdbcSql;
		try {
			jdbcSql = parseSqlStatement(sql, parameterObject, sqlParams);
		}
		catch (RuntimeException e) {
			throw e;
		}
		catch (Exception e) {
			throw new RuntimeException(e);
		}
		
		SqlLogger.logSatement(jdbcSql);

		if (isCallableSql(jdbcSql)) {
			CallableStatement cs;
			
			if (resultSetHoldability != 0) {
				cs = connection.prepareCall(jdbcSql, resultSetType, resultSetConcurrency, resultSetHoldability);
			}
			else {
				cs = connection.prepareCall(jdbcSql, resultSetType, resultSetConcurrency);
			}
	
			for (int i = 0; i < sqlParams.size(); i++) {
				SqlParameter parameter = sqlParams.get(i);
	
				if (parameter.isInputAllowed()) {
					parameter.getTypeHandler().setParameter(cs, i + 1, parameter.getValue(), parameter.getJdbcType());
				}
				if (parameter.isOutputAllowed()) {
					if (parameter.getScale() != null) {
						cs.registerOutParameter(i + 1, parameter.getSqlType(), parameter.getScale());
					}
					else {
						cs.registerOutParameter(i + 1, parameter.getSqlType());
					}
				}
			}
			
			SqlLogger.logParameters(cs);
			logParameters(sqlParams);

			return cs;
		}
		else {
			PreparedStatement ps;

			if (resultSetHoldability != 0) {
				ps = connection.prepareStatement(jdbcSql, resultSetType, resultSetConcurrency, resultSetHoldability);
			}
			else {
				ps = connection.prepareStatement(jdbcSql, resultSetType, resultSetConcurrency);
			}

			for (int i = 0; i < sqlParams.size(); i++) {
				SqlParameter parameter = sqlParams.get(i);
				parameter.getTypeHandler().setParameter(ps, i + 1, parameter.getValue(), parameter.getJdbcType());
			}
			
			SqlLogger.logParameters(ps);
			logParameters(sqlParams);

			return ps;
		}
	}

	/**
	 * retrieve output parameters
	 * @param ps statement
	 * @param parameterObject parameter
	 * @param sqlParams sql parameters
	 * @throws SQLException if a SQL error occurs
	 */
	@SuppressWarnings("unchecked")
	protected void retrieveOutputParameters(PreparedStatement ps, Object parameterObject, List<SqlParameter> sqlParams) throws SQLException {
		if (ps instanceof CallableStatement) {
			CallableStatement cs = (CallableStatement)ps;

			logOutputParameters(cs, sqlParams);

			if (parameterObject != null) {
				BeanHandler beanHandler = getBeanHandler(parameterObject.getClass());
				for (int i = 0; i < sqlParams.size(); i++) {
					SqlParameter parameter = sqlParams.get(i);
		
					if (parameter.isOutputAllowed()) {
						Object value = parameter.getTypeHandler().getResult(cs, i + 1);
						beanHandler.setBeanValue(parameterObject, parameter.getName(), value);
					}
				}
			}
		}
	}

	//------------------------------------------------------------------------------
	// Override & Implements
	//------------------------------------------------------------------------------
	/**
	 * Executes a mapped SQL INSERT statement. Insert is a bit different from
	 * other update methods, as it provides facilities for returning the primary
	 * key of the newly inserted row (rather than the effected rows). This
	 * functionality is of course optional.
	 * <p/>
	 * The parameter object is generally used to supply the input data for the
	 * INSERT values.
	 * 
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @param parameterObject The parameter object (e.g. JavaBean, Map, etc.).
	 * @param resultClass The class of result object 
	 * @return The primary key of the newly inserted row. This might be
	 *         automatically generated by the RDBMS, or selected from a sequence
	 *         table or other source.
	 * @throws java.sql.SQLException If an error occurs.
	 */
	@Override
	public <T> T insert(String sql, Object parameterObject,
			Class<T> resultClass, T resultObject) throws SQLException {
		if (log.isDebugEnabled()) {
			log.debug("insert: " + sql);
		}

		List<SqlParameter> sqlParams = new ArrayList<SqlParameter>();
		PreparedStatement ps = prepareStatement(sql, parameterObject, sqlParams);
		try {
			ps.execute();
			
			retrieveOutputParameters(ps, parameterObject, sqlParams);

			ResultSet rs = ps.getGeneratedKeys();
			if (rs == null || !rs.next()) {
				return null;
			}
			
			SqlLogger.logResultHeader(rs);
			SqlLogger.logResultValues(rs);
			
			SimpleSqlResultSet rse = new SimpleSqlResultSet(rs); 
			BeanHandler beanHandler = getBeanHandler(resultClass);
			resultObject = rse.getResult(beanHandler, resultObject);

			return resultObject;
		}
		finally {
			SqlUtils.closeQuietly(ps);
		}
	}

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a single object instance.
	 * <p/>
	 * The parameter object is generally used to supply the input
	 * data for the WHERE clause parameter(s) of the SELECT statement.
	 *
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @param parameterObject The parameter object (e.g. JavaBean, Map, XML etc.).
	 * @param resultClass The class of result object 
	 * @param resultObject    The result object instance that should be populated with result data.
	 * @return The single result object populated with the result set data,
	 *         or null if no result was found
	 * @throws java.sql.SQLException If more than one result was found, or if any other error occurs.
	 */
	@Override
	protected <T> T queryForObject(String sql, Object parameterObject, Class<T> resultClass, T resultObject) throws SQLException {
		if (log.isDebugEnabled()) {
			log.debug("queryForObject: " + sql);
		}

		List<SqlParameter> sqlParams = new ArrayList<SqlParameter>();
		PreparedStatement ps = prepareStatement(sql, parameterObject, sqlParams);
		ResultSet rs = null;
		try {
			ps.execute();

			retrieveOutputParameters(ps, parameterObject, sqlParams);

			rs = ps.getResultSet();
			if (rs == null || !rs.next()) {
				return null;
			}
			
			SqlLogger.logResultHeader(rs);
			SqlLogger.logResultValues(rs);
			
			SimpleSqlResultSet rse = new SimpleSqlResultSet(rs); 
			BeanHandler beanHandler = getBeanHandler(resultClass);
			resultObject = rse.getResult(beanHandler, resultObject);

			if (rs.next()) {
		        throw new SQLException("Error: queryForObject returned too many results.");
			}
			return resultObject;
		}
		finally {
			SqlUtils.closeQuietly(rs);
			SqlUtils.closeQuietly(ps);
		}
	}

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a number of result objects within a certain range.
	 * <p/>
	 * The parameter object is generally used to supply the input
	 * data for the WHERE clause parameter(s) of the SELECT statement.
	 *
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @param parameterObject The parameter object (e.g. JavaBean, Map, XML etc.).
	 * @param resultClass The class of result object 
	 * @param skip            The number of results to ignore.
	 * @param max             The maximum number of results to return.
	 * @return A List of result objects.
	 * @throws java.sql.SQLException If an error occurs.
	 */
	@Override
	public <T> List<T> queryForList(String sql, Object parameterObject, Class<T> resultClass, int skip, int max) throws SQLException {
		if (log.isDebugEnabled()) {
			log.debug("queryForList: " + sql);
		}

		List<SqlParameter> sqlParams = new ArrayList<SqlParameter>();
		PreparedStatement ps = prepareStatement(sql, parameterObject, sqlParams);
		ResultSet rs = null;

		try {
			List<T> resultList = new ArrayList<T>();

			ps.execute();

			retrieveOutputParameters(ps, parameterObject, sqlParams);

			rs = ps.getResultSet();
			if (rs != null) {
				SqlLogger.logResultHeader(rs);
	
				SqlUtils.skipResultSet(rs, skip);
	
				SimpleSqlResultSet rse = new SimpleSqlResultSet(rs);
				BeanHandler beanHandler = getBeanHandler(resultClass);
				
				int cnt = 0;
				while (rs.next()) {
					SqlLogger.logResultValues(rs);
	
					T resultObject = rse.getResult(beanHandler, (T)null);
					resultList.add(resultObject);
					
					if (max > 0) {
						cnt++;
						if (cnt >= max) {
							break;
						}
					}
				}
			}

			return resultList;
		}
		finally {
			SqlUtils.closeQuietly(rs);
			SqlUtils.closeQuietly(ps);
		}
	}

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a number of result objects from which one property will be keyed into a Map.
	 * <p/>
	 * The parameter object is generally used to supply the input
	 * data for the WHERE clause parameter(s) of the SELECT statement.
	 *
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @param parameterObject The parameter object (e.g. JavaBean, Map, XML etc.).
	 * @param keyPropertyName The property to be used as the key in the Map.
	 * @param valuePropertyName The property to be used as the value in the Map.
	 * @param resultClass The class of result object 
	 * @return A Map keyed by keyProp with values of valueProp.
	 * @throws java.sql.SQLException If an error occurs.
	 */
	@Override
	@SuppressWarnings("unchecked")
	public <T> Map queryForMap(String sql, Object parameterObject, Class<T> resultClass, String keyPropertyName, String valuePropertyName) throws SQLException {
		List<T> list = queryForList(sql, parameterObject, resultClass);

		Map map = new HashMap();
		BeanHandler beanHandler = getBeanHandler(resultClass);

		for (int i = 0, n = list.size(); i < n; i++) {
			T bean = list.get(i);
			Object key = beanHandler.getBeanValue(bean, keyPropertyName);
			Object value = null;
			if (valuePropertyName == null) {
				value = bean;
			} else {
				value = beanHandler.getBeanValue(bean, valuePropertyName);
			}
			map.put(key, value);
		}

		return map;
	}

	/**
	 * Executes the given SQL statement, which returns a single <code>SqlResultSet</code> object.
	 * 
	 * @param sql an SQL statement to be sent to the database, typically a static SQL
	 *            <code>SELECT</code> statement
	 * @param parameterObject The parameter object (e.g. JavaBean, Map, XML etc.).
	 * @return a <code>ResultSet</code> object that contains the data produced by the given query;
	 *         never <code>null</code>
	 * @throws java.sql.SQLException If an SQL error occurs.
	 */
	@Override
	public SqlResultSet queryForResultSet(String sql, Object parameterObject) throws SQLException {
		if (log.isDebugEnabled()) {
			log.debug("queryForResultSet: " + sql);
		}

		List<SqlParameter> sqlParams = new ArrayList<SqlParameter>();
		PreparedStatement ps = prepareStatement(sql, parameterObject, sqlParams);

		ResultSet rs = ps.executeQuery();
		
		retrieveOutputParameters(ps, parameterObject, sqlParams);

		SqlResultSet rse = new SimpleSqlResultSet(rs);
		return rse;
	}

	/**
	 * Executes the given SQL statement, which may be an INSERT, UPDATE, 
	 * or DELETE statement or an SQL statement that returns nothing, 
	 * such as an SQL DDL statement. 
	 *
	 * @param sql The SQL statement to execute.
	 * @param parameterObject The parameter object (e.g. JavaBean, Map, XML etc.).
	 * @return The number of rows effected.
	 * @throws java.sql.SQLException If an error occurs.
	 */
	@Override
	public int update(String sql, Object parameterObject) throws SQLException {
		if (log.isDebugEnabled()) {
			log.debug("update: " + sql);
		}

		List<SqlParameter> sqlParams = new ArrayList<SqlParameter>();
		PreparedStatement ps = prepareStatement(sql, parameterObject, sqlParams);
		try {
			ps.execute();
			
			retrieveOutputParameters(ps, parameterObject, sqlParams);

			int updateCount = ps.getUpdateCount();
			return updateCount;
		}
		finally {
			SqlUtils.closeQuietly(ps);
		}
	}

	/**
	 * Executes the given SQL statement, which may be an INSERT, UPDATE, 
	 * or DELETE statement or an SQL statement that returns nothing, 
	 * such as an SQL DDL statement. 
	 *
	 * @param sql The SQL statement to execute.
	 * @param parameterObject The parameter object (e.g. JavaBean, Map, XML etc.).
	 * @throws java.sql.SQLException If an error occurs.
	 */
	@Override
	public void execute(String sql, Object parameterObject) throws SQLException {
		if (log.isDebugEnabled()) {
			log.debug("execute: " + sql);
		}

		List<SqlParameter> sqlParams = new ArrayList<SqlParameter>();
		PreparedStatement cs = prepareStatement(sql, parameterObject, sqlParams);
		try {
			cs.execute();
			retrieveOutputParameters(cs, parameterObject, sqlParams);
		}
		finally {
			SqlUtils.closeQuietly(cs);
		}
	}
}
