## ============================================================================
## Filename : FWJavaBeanAbstractDAOTemplate.vm
## Note(s)  : This template is used to generate the base class for all
##            Java Bean DAO classes.  It is intended to be used along with
##            the JavaBeanDAOTemplate.vm file.
##
## 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.math.BigDecimal;
import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

/**
 * Abstract class from which all DAO should inherit from.
 */
public class ${className} {
    private Connection conn;
    private Statement stmt;

    /**
     * ${className} constructor.
     */
    public ${className}(Connection conn) {
        this.conn = conn;
    }

    protected final Connection getConnection() {
        return conn;
    }

    /**
     * Create a database statement and cache it until closeStatement() is called.
     */
    private Statement getStatement() throws SQLException {
        if (stmt == null) {
            stmt = conn.createStatement();
        }
        return stmt;
    }

    /**
     * Helper method to close the cached database statement.  This method should be
     * invoked after statement resources (e.g. database cursors) are no longer needed.
     * Nothing is done if the statement has already been closed.
     */
    protected final void closeStatement() throws SQLException {
        if (stmt != null) {
            stmt.close();
            stmt = null;
        }
    }

    /**
     * Helper method to close the specified result set (if any) as well as the
     * cached database statement.  This method should be
     * invoked after statement resources (e.g. database cursors) are no longer needed.
     * Nothing is done if the statement has already been closed.
     */
    protected final void closeStatement(ResultSet rset) throws SQLException {
        if (rset != null) {
            rset.close();
        }
        if (stmt != null) {
            stmt.close();
            stmt = null;
        }
    }

    /**
     * Helper method to execute an SQL update query.
     */
    protected final int executeUpdate(String sql) throws SQLException {
        System.out.println("${className}.executeUpdate() - sql=\n" + sql);
        try {
            Statement stmt = getStatement();
            return stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // Display the SQL query that caused the SQLException.
            System.out.println(
                "${className}.executeUpdate() - Failed to execute update sql="
                    + sql);
            throw e;
        }
    }

    /**
     * Helper method to execute an SQL select query.
     */
    protected final ResultSet executeQuery(String sql) throws SQLException {
        System.out.println("${className}.executeQuery() - sql=\n" + sql);
        try {
            Statement stmt = getStatement();
            return stmt.executeQuery(sql);
        } catch (SQLException e) {
            // Display the SQL query that caused the SQLException.
            System.out.println(
                "${className}.executeQuery() - Failed to execute query sql="
                    + sql);
            throw e;
        }
    }

    protected final String getString(ResultSet rset, String columnName)
        throws SQLException {
        return rset.getString(columnName);
    }

    protected final Character getCharacter(ResultSet rset, String columnName)
        throws SQLException {
        String value = rset.getString(columnName);
        Character charValue = null;
        if (value != null) {
            charValue = new Character(value.charAt(0));
        }
        return charValue;
    }

    protected final Integer getInteger(ResultSet rset, String columnName)
        throws SQLException {
        int value = rset.getInt(columnName);
        Integer intValue = null;
        if (!rset.wasNull()) {
            intValue = new Integer(value);
        }
        return intValue;
    }

    protected final Long getLong(ResultSet rset, String columnName)
        throws SQLException {
        long value = rset.getLong(columnName);
        Long longValue = null;
        if (!rset.wasNull()) {
            longValue = new Long(value);
        }
        return longValue;
    }

    protected final Short getShort(ResultSet rset, String columnName) throws SQLException {
        short value = rset.getShort(columnName);
        Short shortValue = null;
        if (!rset.wasNull()) {
            shortValue = new Short(value);
        }
        return shortValue;
    }
    
    protected final Byte getByte(ResultSet rset, String columnName) throws SQLException {
        byte value = rset.getByte(columnName);
        Byte byteValue = null;
        if (!rset.wasNull()) {
            byteValue = new Byte(value);
        }
        return byteValue;
    }
    
    protected final Float getFloat(ResultSet rset, String columnName) throws SQLException {
        float value = rset.getFloat(columnName);
        Float floatValue = null;
        if (!rset.wasNull()) {
            floatValue = new Float(value);
        }
        return floatValue;
    }

    protected final Double getDouble(ResultSet rset, String columnName)
        throws SQLException {
        double value = rset.getDouble(columnName);
        Double doubleValue = null;
        if (!rset.wasNull()) {
            doubleValue = new Double(value);
        }
        return doubleValue;
    }

    protected final Timestamp getTimestamp(ResultSet rset, String columnName)
        throws SQLException {
        return rset.getTimestamp(columnName);
    }

    protected final java.sql.Date getDate(ResultSet rset, String columnName)
        throws SQLException {
        return rset.getDate(columnName);
    }

    protected final BigDecimal getBigDecimal(ResultSet rset, String columnName)
        throws SQLException {
        return rset.getBigDecimal(columnName);
    }

    protected final BigInteger getBigInteger(ResultSet rset, String columnName)
        throws SQLException {
        return toBigInteger(rset.getBigDecimal(columnName));
    }

    /**
     * Helper method to append the specified string value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        String value) {
        append(sql, prefix, value, null);
    }

    /**
     * Helper method to append the specified string value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        String value,
        String suffix) {
        append(sql, prefix, value, suffix, "null");
    }

    /**
     * Helper method to append the specified date value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     * If the value is null, then defaultExpr is used.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        String value,
        String suffix,
        String defaultExpr) {
        if (prefix != null) {
            sql.append(prefix);
        }
        if (value != null) {
            sql.append(SQLUtils.escapeSQL(value));
        } else {
            sql.append(defaultExpr);
        }
        if (suffix != null) {
            sql.append(suffix);
        }
    }

    /**
     * Helper method to append the specified character value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        Character value) {
        append(sql, prefix, value, null);
    }

    /**
     * Helper method to append the specified character value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        Character value,
        String suffix) {
        if (prefix != null) {
            sql.append(prefix);
        }
        if (value != null) {
            sql.append(SQLUtils.escapeSQL(value.toString()));
        } else {
            sql.append("null");
        }
        if (suffix != null) {
            sql.append(suffix);
        }
    }

    /**
     * Helper method to append the specified date value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        java.sql.Date value) {
        append(sql, prefix, value, null);
    }

    /**
     * Helper method to append the specified date value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        java.sql.Date value,
        String suffix) {
        append(sql, prefix, value, suffix, "null");
    }

    /**
     * Helper method to append the specified date value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     * If the value is null, then defaultExpr is used.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        java.sql.Date value,
        String suffix,
        String defaultExpr) {
        if (prefix != null) {
            sql.append(prefix);
        }
        if (value != null) {
            sql.append("{d '");
            sql.append(value);
            sql.append("'}");
        } else {
            sql.append(defaultExpr);
        }
        if (suffix != null) {
            sql.append(suffix);
        }
    }

    /**
     * Helper method to append the specified timestamp value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        java.sql.Timestamp value) {
        append(sql, prefix, value, null);
    }

    /**
     * Helper method to append the specified timestamp value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        java.sql.Timestamp value,
        String suffix) {
        append(sql, prefix, value, suffix, "null");
    }

    /**
     * Helper method to append the specified timestamp value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     * If the value is null, then defaultExpr is used. 
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        java.sql.Timestamp value,
        String suffix,
        String defaultExpr) {
        if (prefix != null) {
            sql.append(prefix);
        }
        if (value != null) {
            sql.append("{ts '");
            sql.append(value);
            sql.append("'}");
        } else {
            sql.append(defaultExpr);
        }
        if (suffix != null) {
            sql.append(suffix);
        }
    }

    /**
     * Helper method to append the specified value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        Object value) {
        append(sql, prefix, value, null);
    }

    /**
     * Helper method to append the specified value to the string buffer.
     * If a prefix is specified, prepend it before the value.
     * If a suffix is specified, append it after the value.
     */
    protected final void append(
        StringBuffer sql,
        String prefix,
        Object value,
        String suffix) {
        if (prefix != null) {
            sql.append(prefix);
        }
        sql.append(value);
        if (suffix != null) {
            sql.append(suffix);
        }
    }

    /**
     * Helper method to append a where clause (if any) based on the specified search criteria.
     */
    protected final void appendWhereClause(
        StringBuffer sql,
        SearchCriteriaData searchData) {
        if (searchData != null) {
            String expr = searchData.getSQLExpr();
            if (expr != null) {
                sql.append(" where ");
                sql.append(expr);
            }
        }
    }

    /**
     * Prepare and return a CallableStatement to execute the specified SQL query.
     */
    protected final CallableStatement prepareCallable(String sql)
        throws Exception {
        System.out.println("${className}.prepareCallable() - sql=" + sql);
        return conn.prepareCall(sql);
    }

    protected final String getString(
        CallableStatement cstmt,
        int parameterIndex)
        throws SQLException {
        return cstmt.getString(parameterIndex);
    }

    protected final Character getCharacter(
        CallableStatement cstmt,
        int parameterIndex)
        throws SQLException {
        String value = cstmt.getString(parameterIndex);
        Character charValue = null;
        if (value != null) {
            charValue = new Character(value.charAt(0));
        }
        return charValue;
    }

    protected final Integer getInteger(
        CallableStatement cstmt,
        int parameterIndex)
        throws SQLException {
        int value = cstmt.getInt(parameterIndex);
        Integer intValue = null;
        if (!cstmt.wasNull()) {
            intValue = new Integer(value);
        }
        return intValue;
    }

    protected final Long getLong(CallableStatement cstmt, int parameterIndex)
        throws SQLException {
        long value = cstmt.getLong(parameterIndex);
        Long longValue = null;
        if (!cstmt.wasNull()) {
            longValue = new Long(value);
        }
        return longValue;
    }

    protected final Timestamp getTimestamp(
        CallableStatement cstmt,
        int parameterIndex)
        throws SQLException {
        return cstmt.getTimestamp(parameterIndex);
    }

    protected final java.sql.Date getDate(
        CallableStatement cstmt,
        int parameterIndex)
        throws SQLException {
        return cstmt.getDate(parameterIndex);
    }

    protected final BigDecimal getBigDecimal(
        CallableStatement cstmt,
        int parameterIndex)
        throws SQLException {
        return cstmt.getBigDecimal(parameterIndex);
    }

    protected final BigInteger getBigInteger(
        CallableStatement cstmt,
        int parameterIndex)
        throws SQLException {
        return toBigInteger(cstmt.getBigDecimal(parameterIndex));
    }

    protected final void setString(PreparedStatement pstmt, int parameterIndex, String value)
            throws SQLException {
        if (value != null) {
            pstmt.setString(parameterIndex, value);
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.VARCHAR);
        }
    }

    protected final void setCharacter(PreparedStatement pstmt, int parameterIndex, Character value)
            throws SQLException {
        if (value != null) {
            pstmt.setString(parameterIndex, String.valueOf(value.charValue()));
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.VARCHAR);
        }
    }

    protected final void setInteger(PreparedStatement pstmt, int parameterIndex, Integer value)
            throws SQLException {
        if (value != null) {
            pstmt.setInt(parameterIndex, value.intValue());
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.NUMERIC);
        }
    }

    protected final void setLong(PreparedStatement pstmt, int parameterIndex, Long value)
            throws SQLException {
        if (value != null) {
            pstmt.setLong(parameterIndex, value.longValue());
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.NUMERIC);
        }
    }

    protected final void setShort(PreparedStatement pstmt, int parameterIndex, Short value)
            throws SQLException {
        if (value != null) {
            pstmt.setShort(parameterIndex, value.shortValue());
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.NUMERIC);
        }
    }

    protected final void setByte(PreparedStatement pstmt, int parameterIndex, Byte value)
            throws SQLException {
        if (value != null) {
            pstmt.setByte(parameterIndex, value.byteValue());
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.NUMERIC);
        }
    }

    protected final void setDouble(PreparedStatement pstmt, int parameterIndex, Double value)
            throws SQLException {
        if (value != null) {
            pstmt.setDouble(parameterIndex, value.doubleValue());
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.DECIMAL);
        }
    }

    protected final void setTimestamp(PreparedStatement pstmt, int parameterIndex,
            java.sql.Timestamp value) throws SQLException {
        if (value != null) {
            pstmt.setTimestamp(parameterIndex, value);
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.TIMESTAMP);
        }
    }

    protected final void setDate(PreparedStatement pstmt, int parameterIndex, java.sql.Date value)
            throws SQLException {
        if (value != null) {
            pstmt.setDate(parameterIndex, value);
        } else {
            pstmt.setNull(parameterIndex, java.sql.Types.DATE);
        }
    }

    /**
     * Helper method to convert a big decimal to a big integer.
     * If the big decimal is null, null is returned.
     */
    protected static final BigInteger toBigInteger(BigDecimal bigDecimal) {
        if (bigDecimal != null) {
            return bigDecimal.toBigInteger();
        }
        return null;
    }

    /**
     * Construct a comma delimited place holder list (i.e. a list of '?') and
     * append it to the specified string buffer.
     */
    protected static final void appendPlaceHolders(
        StringBuffer sb,
        int count) {
        for (int i = 0; i < count; i++) {
            if (i != 0) {
                sb.append(",");
            }
            sb.append("?");
        }
    }
}