/*
 * 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;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;


/**
 * SqlExecutor
 */
public interface SqlExecutor {
	/**
	 * 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.
	 * @throws java.sql.SQLException If an error occurs.
	 */
	void execute(String sql) throws SQLException;

	/**
	 * 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.
	 */
	void execute(String sql, Object parameterObject) throws SQLException;

	/**
	 * 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/>
	 * This overload assumes no parameter is needed.
	 * 
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @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.
	 */
	<T> T insert(String sql, Class<T> resultClass) throws SQLException;

	/**
	 * 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.
	 */
	<T> T insert(String sql, Object parameterObject, Class<T> resultClass) throws SQLException;

	/**
	 * 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 resultObject    The result object instance that should be populated with result data.
	 * @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.
	 */
	<T> T insert(String sql, Object parameterObject, T resultObject) throws SQLException;

	/**
	 * 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.
	 * @return The number of rows effected.
	 * @throws java.sql.SQLException If an error occurs.
	 */
	int update(String sql) throws SQLException;

	/**
	 * 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.
	 */
	int update(String sql, Object parameterObject) throws SQLException;

    /**
     * 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
     * @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.
     */
    SqlResultSet queryForResultSet(String sql) throws SQLException;

    /**
     * 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.
     */
    SqlResultSet queryForResultSet(String sql, Object parameterObject) throws SQLException;

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a single object instance.
	 * <p/>
	 * This overload assumes no parameter is needed.
	 *
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @param resultClass The class of result object 
	 * @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.
	 */
	<T> T queryForObject(String sql, Class<T> resultClass) throws SQLException;

	/**
	 * 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 
	 * @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.
	 */
	<T> T queryForObject(String sql, Object parameterObject, Class<T> resultClass)
			throws SQLException;

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * the supplied result object.
	 * <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 resultObject    The result object instance that should be populated with result data.
	 * @return The single result object as supplied by the resultObject parameter, 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.
	 */
	<T> T queryForObject(String sql, Object parameterObject, T resultObject) throws SQLException;

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a number of result objects.
	 * <p/>
	 * This overload assumes no parameter is needed.
	 *
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @param resultClass The class of result object 
	 * @return A List of result objects.
	 * @throws java.sql.SQLException If an SQL error occurs.
	 */
	<T> List<T> queryForList(String sql, Class<T> resultClass) throws SQLException;

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a number of result objects.
	 * <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 
	 * @return A List of result objects.
	 * @throws java.sql.SQLException If an SQL error occurs.
	 */
	<T> List<T> queryForList(String sql, Object parameterObject, Class<T> resultClass)
			throws SQLException;

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a number of result objects within a certain range.
	 * <p/>
	 * This overload assumes no parameter is needed.
	 *
	 * @param <T> The type of result object 
	 * @param sql The SQL statement to execute.
	 * @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 SQL error occurs.
	 */
	<T> List<T> queryForList(String sql, Class<T> resultClass, int skip, int max)
			throws SQLException;

	/**
	 * 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 SQL error occurs.
	 */
	<T> List<T> queryForList(String sql, Object parameterObject, Class<T> resultClass, int skip,
			int max) throws SQLException;

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a number of result objects that 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 resultClass The class of result object 
	 * @param keyPropertyName The property to be used as the key in the Map.
	 * @return A Map keyed by keyProp with values being the result object instance.
	 * @throws java.sql.SQLException If an SQL error occurs.
	 */
	<T> Map<Object, T> queryForMap(String sql, Class<T> resultClass, String keyPropertyName)
			throws SQLException;

	/**
	 * Executes a mapped SQL SELECT statement that returns data to populate
	 * a number of result objects that 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 resultClass The class of result object 
	 * @param keyPropertyName The property to be used as the key in the Map.
	 * @return A Map keyed by keyProp with values being the result object instance.
	 * @throws java.sql.SQLException If an SQL error occurs.
	 */
	<T> Map<Object, T> queryForMap(String sql, Object parameterObject, Class<T> resultClass,
			String keyPropertyName) throws SQLException;

	/**
	 * 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 resultClass The class of result object 
	 * @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.
	 * @return A Map keyed by keyProp with values of valueProp.
	 * @throws java.sql.SQLException If an SQL error occurs.
	 */
	<T> Map queryForMap(String sql, Class<T> resultClass, String keyPropertyName,
			String valuePropertyName) throws SQLException;

	/**
	 * 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 resultClass The class of result object 
	 * @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.
	 * @return A Map keyed by keyProp with values of valueProp.
	 * @throws java.sql.SQLException If an SQL error occurs.
	 */
	<T> Map queryForMap(String sql, Object parameterObject, Class<T> resultClass,
			String keyPropertyName, String valuePropertyName) throws SQLException;

}
