## ============================================================================
## Filename : FWJavaBeanSQLCommandTemplate.vm
## Note(s)  : This template is used to generate the SQLCommand class used by the
##            Java Bean DAO classes.
##
## Copyright (c) 2007-2008 ThinkUI Software Inc. All rights reserved.
## ============================================================================
##
## ****************************************************************************
## The following variables are available in this template.  For more
## information on any of the following variable, please refer to the user guide.
## ****************************************************************************
## $projectName
## $authorName
## $headerText
## $className
## $packageName
## $subPackageName
## $prjPkgName
## $prjClassPrefix
## $superClassName
## $date
## $codeGen
##
#if ($packageName)
package $packageName;
#end

#parse( "ClassHeaderInclude.vm" )

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

/**
 * Encapsulate access to a database by allowing SQL queries to be executed
 * on the configured database connection.  The close() method must be called
 * when the command is no longer required.
 */
public class SQLCommand {
    
    private Connection connection;
    
    private PreparedStatement stmt;
    
    private ResultSet rset;
    
    /**
     * SQLCommand constructor.
     */
    public SQLCommand(Connection connection) {
        this.connection = connection;
    }
    
    /**
     * Configure a prepared statement for the given SQL query and bind any parameters
     * to the statement before performing an executeQuery().  This method is intended
     * to be used for SQL SELECT statements.
     * @param sqlStatement the SQL query to execute.
     * @param stmtHelper the callback to bind any arguments to the prepared statement.
     * @return the ResultSet from the executeUpdate() or null.
     * @throws SQLException if a database error is encountered.
     */
    public final ResultSet executeQuery(String sqlStatement, SQLStatementHelper stmtHelper) throws SQLException {
        //System.out.println("SQLCommand.executeQuery() - "+ sqlStatement);
        
        // Ensure that any previous statement has been closed to prevent memory leak.
        close();
        
        try {
            stmt = connection.prepareStatement(sqlStatement);
            stmtHelper.bindArguments(stmt);
            rset = stmt.executeQuery();
        } catch (SQLException sqle) {
            close();
            System.out.println("SQLCommand.executeQuery() - sql="+ sqlStatement);
            System.out.println(sqle.toString());
            // Propagate the SQL exception up.
            throw sqle;
        }
        return rset;
    }
    
    /**
     * Configure a prepared statement for the given SQL query and bind any parameters
     * to the statement before performing an executeUpdate().  This method is intended
     * to be used for SQL INSERT and UPDATE statements.
     * @param sqlStatement the SQL query to execute.
     * @param stmtHelper the callback to bind any arguments to the prepared statement.
     * @param syncAttrNames the attribute names of columns to return from the insert or null.
     * @param rsetHelper the callback to retrieve any result from the generate key.
     * @return the count from the executeUpdate() or -1.
     * @throws SQLException if a database error is encountered.
     */
    public final int executeUpdate(String sqlStatement, SQLStatementHelper stmtHelper,
            String[] syncAttrNames, ResultSetHelper rsetHelper) throws SQLException {
        //System.out.println("SQLCommand.executeUpdate() - "+ sqlStatement);
        
        // Ensure that any previous statement has been closed to prevent memory leak.
        close();
        
        int count = -1;
        try {
            if (syncAttrNames != null) {
                stmt = connection.prepareStatement(sqlStatement, syncAttrNames);
            } else {
                stmt = connection.prepareStatement(sqlStatement);
            }
            stmtHelper.bindArguments(stmt);
            count = stmt.executeUpdate();
            
            if (rsetHelper != null) {
                ResultSet rset = stmt.getGeneratedKeys();
                if (rset.next()) {
                    rsetHelper.retrieveResults(rset);
                }
            }
        } catch (SQLException sqle) {
            close();
            sqle.printStackTrace();
            // Propagate the SQL exception up.
            throw sqle;
        }
        return count;
    }
    
    /**
     * Configure a prepared statement for the given SQL query and bind any parameters
     * to the statement before performing an executeUpdate().  This method is intended
     * to be used for SQL INSERT and UPDATE statements.
     * @param sqlStatement the SQL query to execute.
     * @param stmtHelper the callback to bind any arguments to the prepared statement.
     * @return the count from the executeUpdate() or -1.
     * @throws SQLException if a database error is encountered.
     */
    public final int executeUpdate(String sqlStatement, SQLStatementHelper stmtHelper) throws SQLException {
        return executeUpdate(sqlStatement, stmtHelper, null, null);
    }
    
    /**
     * Close any resources (statement and result set) currently opened
     * on the connection.  This method must be called when the command is
     * no longer needed.  The connection itself will not be closed.
     */
    public final void close() {
        try {
            if (rset != null) {
                rset.close();
                rset = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        } catch (SQLException sqle) {
            // Ignore.
            sqle.printStackTrace();
        }
    }
}