## ============================================================================
## Filename : JavaBeanDAOTemplate.vm
## Note(s)  : This template is used to generate a Java Data Access Object (DAO)
##            class that uses JDBC to perform data persistence of Java Beans.
##
##            Note: The generated code requires that the corresponding Java Bean,
##                  CriteriaSearchData, and specified super class
##                  (i.e. AbstractDAO) must also generated.
##
## 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
## $tableName
## $fullTableName
## $className
## $objectName
## $objectVar
## $captionAttrName
## $packageName
## $prjPkgName
## $prjClassPrefix
## $dataClass
## $superClassName
## $date
## $targetDatabase
## $sequenceName
## $codeGen
##
#if ($packageName)
package $packageName;
#end

#parse( "ClassHeaderInclude.vm" )

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.CallableStatement;

import thinkui.${prjPkgName}.beans.${objectName};
import thinkui.${prjPkgName}.criteria.${objectName}SearchData;
import thinkui.${prjPkgName}.db.${superClassName};

/**
 * Implements the $objectName Data Access Object by using JDBC to execute
 * dynamically generated SQL queries.
#if ($targetDatabase)
 * <p/>
 * The generated code is specific for a ${targetDatabase} database
 * and may not work correctly for other databases.
#end
 */
public class $className extends $superClassName {
#if ($sequenceName)
    // ${objectName} related sequence name.
    private static final String ${tableName.toUpperCase()}_ID_SEQUENCE_NAME =
        "$sequenceName";

#end
    /**
     * ${className} constructor.
     */
    public ${className}(Connection conn) {
        super(conn);
    }

    /**
     * Count the number of records matching the specified criteria.
     *
     * @return the count of the number records matching the specified criteria.
     */
    public final int count${objectName}(${objectName}SearchData searchData)
    throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT COUNT(*) FROM ${fullTableName}");
        appendWhereClause(sql, searchData);
        ResultSet rset = null;
        int count = -1;
        try {
            rset = executeQuery(sql.toString());
            if (rset.next()) {
                count = rset.getInt(1);
            }
        } finally {
            closeStatement(rset);
        }
        //assert(count >= 0);
        return count;
    }

    /**
     * Find all records matching the specified criteria.
     *
     * @return a list of all records matching the specified criteria.
     */
    public final java.util.List find${objectName}(${objectName}SearchData searchData)
    throws Exception {
        java.util.List ${objectVar}List = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        appendSelectAttrNames(sql);
        sql.append(" FROM ${fullTableName}");
        appendWhereClause(sql, searchData);
        ResultSet rset = null;
        try {
            rset = executeQuery(sql.toString());
            while (rset.next()) {
                ${objectName} ${objectVar} = new ${objectName}();
                populateSelectValues(${objectVar}, rset);
                ${objectVar}List.add(${objectVar});
            }
        } finally {
            closeStatement(rset);
        }
        return ${objectVar}List;
    }

    /**
     * Get the ${objectName} object for the specified ${objectVar}Id.  If the object id is null,
     * simply create and return a new empty ${objectName} object.
     *
     * @return the ${objectName} object for the specified object id or a new empty ${objectName} object if the id is null.
     */
    public final ${objectName} get${objectName}(Object ${objectVar}Id) throws Exception {
        ${objectName} ${objectVar} = new ${objectName}();
        if (${objectVar}Id != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT ");
            appendSelectAttrNames(sql);
            sql.append(" FROM ${fullTableName} WHERE ");
            append(sql, "ID=", ${objectVar}Id);
            ResultSet rset = null;
            try {
                rset = executeQuery(sql.toString());
                if (rset.next()) {
                    populateSelectValues(${objectVar}, rset);
                }
            } finally {
                closeStatement(rset);
            }
        }
        return ${objectVar};
    }

    /**
     * Insert the specified ${objectName} object into the database.  Any defaulted
     * columns will be automatically populated and returned.
     */
    public final ${objectName} insert${objectName}(${objectName} ${objectVar}) throws Exception {
#if ($targetDatabase.equals("POSTGRESQL"))
        insert${objectName}NoSync(${objectVar});
#if ($sequenceName)
        if (getNumSyncAttrNames() > 0) {
            // POSTGRESQL specific syntax.
            StringBuffer syncSQL = new StringBuffer();
            syncSQL.append("SELECT ");
            appendSyncAttrNames(syncSQL);
            syncSQL.append(" FROM ${fullTableName} WHERE ID=currval('"+ ${tableName.toUpperCase()}_ID_SEQUENCE_NAME +"')");

            try {
                ResultSet rset = executeQuery(syncSQL.toString());
                if (rset.next()) {
                    populateSyncValues(${objectVar}, rset);
                }
            } finally {
                closeStatement();
            }
        }
#else
        // TODO - Use JDBC 3.0 feature to retrieve the generated primary key.
#end
        return ${objectVar};
#elseif ($targetDatabase.equals("MYSQL"))
        insert${objectName}NoSync(${objectVar});
        if (getNumSyncAttrNames() > 0) {
            // $targetDatabase specific syntax.
            StringBuffer syncSQL = new StringBuffer();
            syncSQL.append("SELECT ");
            appendSyncAttrNames(syncSQL);
            syncSQL.append(" FROM ${fullTableName} WHERE ID=LAST_INSERT_ID()");
            try {
                ResultSet rset = executeQuery(syncSQL.toString());
                if (rset.next()) {
                    populateSyncValues(${objectVar}, rset);
                }
            } finally {
                closeStatement();
            }
        }
        return ${objectVar};
#elseif ($targetDatabase.equals("CLOUDSCAPE") || $targetDatabase.equals("DB2"))
        insert${objectName}NoSync(${objectVar});
        if (getNumSyncAttrNames() > 0) {
            // $targetDatabase specific syntax.
            StringBuffer syncSQL = new StringBuffer();
            syncSQL.append("SELECT ");
            appendSyncAttrNames(syncSQL);
            syncSQL.append(" FROM ${fullTableName} WHERE ID=IDENTITY_VAL_LOCAL()");
            try {
                ResultSet rset = executeQuery(syncSQL.toString());
                if (rset.next()) {
                    populateSyncValues(${objectVar}, rset);
                }
            } finally {
                closeStatement();
            }
        }
        return ${objectVar};
#else
        if (getNumSyncAttrNames() > 0) {
            // ORACLE specific syntax.
            StringBuffer sql = new StringBuffer();
            sql.append("BEGIN INSERT INTO ${fullTableName} (");
            appendInsertAttrNames(sql);
            sql.append(") VALUES (");
            appendInsertValues(sql, ${objectVar});
            sql.append(") RETURNING ");
            appendSyncAttrNames(sql);
            sql.append(" INTO ");
            appendPlaceHolders(sql, getNumSyncAttrNames());
            sql.append("; END;");
            
            CallableStatement cstmt = null;
            try {
                cstmt = prepareCallable(sql.toString());
                registerSyncOutParameters(cstmt);
                cstmt.executeUpdate();
                populateSyncValues(${objectVar}, cstmt);
            } finally {
                if (cstmt != null) {
                    cstmt.close();
                    cstmt = null;
                }
            }
        } else {
            insert${objectName}NoSync(${objectVar});
        }
        return ${objectVar};
#end
    }

    /**
     * Insert the specified ${objectName} object into the database.  Any defaulted
     * values will not be synchronized.  This method in intended mainly for
     * bulk inserts where performance is important.
     */
    public final void insert${objectName}NoSync(${objectName} ${objectVar}) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO ${fullTableName} (");
        appendInsertAttrNames(sql);
        sql.append(") VALUES (");
        appendInsertValues(sql, ${objectVar});
        sql.append(")");
        try {
            executeUpdate(sql.toString());
        } finally {
            closeStatement();
        }
    }

    /**
     * Update the specified ${objectName} object in the database.  The number of records
     * that was actually updated is returned (normally this should be just one record).
     * @return the number of records that was updated.
     */
    public final int update${objectName}(${objectName} ${objectVar}) throws Exception {
        //assert(${objectVar}.getId() != null);
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE ${fullTableName} SET ");
        appendUpdateValues(sql, ${objectVar});
        appendPKWhereClause(sql, ${objectVar});
        try {
            int updateCount = executeUpdate(sql.toString());
            //assert(updateCount == 1);
            return updateCount;
        } finally {
            closeStatement();
        }
    }

    /**
     * Update all the ${objectName} objects matching the specified search criteria.
     * The number of records that was actually updated is returned.
     * @return the number of records that was updated.
     */ 
    public final int update${objectName}(${objectName} ${objectVar}, ${objectName}SearchData searchData) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE ${fullTableName} SET ");
        appendUpdateValues(sql, ${objectVar});
        appendWhereClause(sql, searchData);
        try {
            int updateCount = executeUpdate(sql.toString());
            return updateCount;
        } finally {
            closeStatement();
        }
    }

    /**
     * Delete the specified ${objectName} object.  The number of records
     * that was actually deleted is returned (normally this should be just one record).
     * @return the number of records that was deleted.
     */    
    public final int delete${objectName}(${objectName} ${objectVar}) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ${fullTableName}");
        appendPKWhereClause(sql, ${objectVar});
        try {
            int deleteCount = executeUpdate(sql.toString());
            //assert(deleteCount == 1);
            return deleteCount;
        } finally {
            closeStatement();
        }
    }

    /**
     * Delete all the ${objectName} objects matching the specified search criteria.
     * The number of records that was actually deleted is returned.
     * @return the number of records that was deleted.
     */ 
    public final int delete${objectName}(${objectName}SearchData searchData) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ${fullTableName}");
        appendWhereClause(sql, searchData);
        try {
            int deleteCount = executeUpdate(sql.toString());
            return deleteCount;
        } finally {
            closeStatement();
        }
    }

    /**
     * Helper method to append the primary key where clause for the given Java Bean.
     */
    private void appendPKWhereClause(StringBuffer sql, ${objectName} ${objectVar}) {
        sql.append(" WHERE ");
#foreach( $pkAttrName in $dataClass.getPrimaryKeyAttrNames() )
        append(sql, "${pkAttrName.toUpperCase()}=", ${objectVar}.get${codeGen.toTypeName($pkAttrName)}());
#end
    }

    /**
     * Helper method to append the select attr names for a ${objectName} object to the specified string buffer.
     * Note: the attribute names in this method should match those in the populateSelectValues() method.
     */
    private void appendSelectAttrNames(StringBuffer sql) {
        String attrNames =
#set( $firstItem = 1 )
#foreach( $attr in $dataClass.getAttributes() )
#if ($firstItem == 1)
#set( $firstItem = 0 )
            "$attr.getName()"
#else
            +",$attr.getName()"
#end
#end
            ;
        sql.append(attrNames);
    }

    /**
     * Helper method to populate the specified ${objectName} object with values from the ResultSet.
     * Note: the attribute names in this method should match those in the appendSelectAttrNames() method.
     */
    private void populateSelectValues(${objectName} ${objectVar}, ResultSet rset) throws SQLException {
#foreach( $attr in $dataClass.getAttributes() )
        ${objectVar}.set${codeGen.toTypeName($attr.getName())}(get${codeGen.getAttributeTypeValueClassName($attr.getType())}(rset, "$attr.getName()"));
#end    
    }

    /**
     * Helper method to append the insert attr names for a ${objectName} object to the specified string buffer.
     * Note: the attribute names in this method should match those in the appendInsertValues() method.
     *       The sequence of attribute names must correspond to the order the values are listed.
     */
    private void appendInsertAttrNames(StringBuffer sql) {
        String attrNames =
#set( $firstItem = 1 )
#foreach( $attr in $dataClass.getAttributes() )
#if (!$codeGen.isSyncAttrName($dataClass, $attr.getName()))
#if ($firstItem == 1)
#set( $firstItem = 0 )
            "$attr.getName()"
#else
            +",$attr.getName()"
#end
#end
#end
            ;
        sql.append(attrNames);
    }

    /**
     * Helper method to append the insert values from ${objectName} object to the specified string buffer.
     */
    private void appendInsertValues(StringBuffer sql, ${objectName} ${objectVar}) throws SQLException {
#set( $firstItem = 1 )
#foreach( $attr in $dataClass.getAttributes() )
#if ($attr.getSequenceName())
        ${objectVar}.set${codeGen.toTypeName($attr.getName())}(
            new Long(
                SQLUtils.selectSequenceNextVal(
                    getConnection(),
                    ${tableName.toUpperCase()}_ID_SEQUENCE_NAME)));
#end
#if (!$codeGen.isSyncAttrName($dataClass, $attr.getName()))
#if ($firstItem == 1)
#set( $firstItem = 0 )
        append(sql, null, ${objectVar}.get${codeGen.toTypeName($attr.getName())}());
#else
        append(sql, ",", ${objectVar}.get${codeGen.toTypeName($attr.getName())}());
#end
#end
#end
    }

    /**
     * Helper method to append the sync attr names for a ${objectName} object to the specified string buffer.
     * Note: the attribute names in this method should match those in the registerSyncOutParameters() and
     *       populateSyncValues() methods.  The sequence of attribute names must correspond accordingly.
     */
    private void appendSyncAttrNames(StringBuffer sql) {
        String attrNames =
#set( $numAttrNames = 0 )
#set( $firstItem = 1 )
#foreach( $attr in $dataClass.getAttributes() )
#if ($codeGen.isSyncAttrName($dataClass, $attr.getName()))
#set( $numAttrNames = $numAttrNames + 1 )
#if ($firstItem == 1)
#set( $firstItem = 0 )
            "$attr.getName()"
#else
            +",$attr.getName()"
#end
#end
#end
#if ($firstItem == 1)null#end
            ;
#if ($firstItem == 0)
        sql.append(attrNames);
#else
        // Empty.
#end
    }

    /**
     * @return the number of sync attribute names.
     */
    private int getNumSyncAttrNames() {
        return $numAttrNames;
    }

#if ($targetDatabase.equals("POSTGRESQL") || $targetDatabase.equals("MYSQL") || $targetDatabase.equals("CLOUDSCAPE"))
    /**
     * Helper method to populate the specified ${objectName} object with values from the ResultSet.
     * Note: the attribute names in this method should match those in the appendSyncAttrNames() method.
     */
    private void populateSyncValues(${objectName} ${objectVar}, ResultSet rset) throws SQLException {
#foreach( $attr in $dataClass.getAttributes() )
#if ($codeGen.isSyncAttrName($dataClass, $attr.getName()))
        ${objectVar}.set${codeGen.toTypeName($attr.getName())}(get${codeGen.getAttributeTypeValueClassName($attr.getType())}(rset, "$attr.getName()"));
#end
#end
    }
#else
    /**
     * Helper method to register the sync out parameters for the CallableStatement.
     * Note: the attribute names in this method should match those in the appendSyncAttrNames() method.
     */
    private void registerSyncOutParameters(CallableStatement cstmt) throws SQLException {
#set( $paramIndex = 1 )
#foreach( $attr in $dataClass.getAttributes() )
#if ($codeGen.isSyncAttrName($dataClass, $attr.getName()))
        cstmt.registerOutParameter($paramIndex, java.sql.Types.${codeGen.getSQLTypeName($attr.getType())}); //$attr.getName()
#set( $paramIndex = $paramIndex + 1 )
#end
#end
    }

    /**
     * Helper method to populate the specified ${objectName} object with values from the CallableStatement.
     * Note: the attribute names in this method should match those in the appendSyncAttrNames() method.
     */
    private void populateSyncValues(${objectName} ${objectVar}, CallableStatement cstmt) throws SQLException {
#set( $paramIndex = 1 )
#foreach( $attr in $dataClass.getAttributes() )
#if ($codeGen.isSyncAttrName($dataClass, $attr.getName()))
        ${objectVar}.set${codeGen.toTypeName($attr.getName())}(get${codeGen.getAttributeTypeValueClassName($attr.getType())}(cstmt, $paramIndex)); //$attr.getName()
#set( $paramIndex = $paramIndex + 1 )
#end
#end
    }
#end

    /**
     * Helper method to append the update set values from ${objectName} object to the specified string buffer.
     */
    private void appendUpdateValues(StringBuffer sql, ${objectName} ${objectVar}) {
#set( $firstItem = 1 )
#foreach( $attr in $dataClass.getAttributes() )
#if ($codeGen.isUpdateAttrName($dataClass, $attr.getName()))
#if ($firstItem == 1)
#set( $firstItem = 0 )
        append(sql, "$attr.getName()=", ${objectVar}.get${codeGen.toTypeName($attr.getName())}());
#else
        append(sql, ",$attr.getName()=", ${objectVar}.get${codeGen.toTypeName($attr.getName())}());
#end
#end
#end
    }
}