## ============================================================================
## Filename : FWJavaBeanSQLUtilsTemplate.vm
## Note(s)  : This template is used to generate the SQLUtils class used by the
##            Java Bean DAO classes.
##
## Copyright (c) 2007 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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Helper class to implement various SQL related utility methods.
 */
public final class SQLUtils {

    /**
     * Hide constructor.
     */
    private SQLUtils() {
        // Empty.
    }

    /**
     * Select the next value of the given sequence name for the given connection.
     */
    public static final long selectSequenceNextVal(
        Connection conn,
        String sequenceName)
        throws SQLException {
        Statement stmt = null;
        ResultSet rset = null;

        try {
            stmt = conn.createStatement();
            // ORACLE specific syntax.
            rset =
                stmt.executeQuery(
                    "SELECT " + sequenceName + ".NEXTVAL FROM DUAL");
            if (rset.next()) {
                return rset.getLong(1);
            } else {
                throw new SQLException(
                    "Unable to select from sequence " + sequenceName);
            }
        } finally {
            close(rset);
            close(stmt);
        }
    }

    /**
     * @return the system time for the given connection database.
     */
    public static final java.sql.Timestamp getSystemTime(Connection conn)
        throws Exception {
        Statement stmt = null;
        ResultSet rset = null;

        try {
            stmt = conn.createStatement();
            // ORACLE specific syntax.
            rset = stmt.executeQuery("SELECT SYSDATE FROM DUAL");
            if (rset.next()) {
                return rset.getTimestamp(1);
            } else {
                throw new SQLException("Unable to select database system time");
            }
        } finally {
            close(rset);
            close(stmt);
        }
    }

    /**
     * Helper method to close the given result set (if it is not null).
     */
    public static final void close(ResultSet rset) {
        if (rset != null) {
            try {
                rset.close();
            } catch (SQLException sqle) {
                // Ignore exception.
                sqle.printStackTrace();
            }
        }
    }

    /**
     * Helper method to close the given statement (if it is not null).
     */
    public static final void close(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqle) {
                // Ignore exception.
                sqle.printStackTrace();
            }
        }
    }

    /**
     * Surround the specified string with single quotes and duplicate
     * any single quotes inside.  If the value is null, null is returned.
     */
    public static final String escapeSQL(String value) {
        if (value != null) {
            int length = value.length();
            StringBuffer sb = new StringBuffer(length * 2);
            sb.append('\'');
            char ch;
            for (int i = 0; i < length; i++) {
                ch = value.charAt(i);
                if (ch == '\'') {
                    sb.append("''");
                } else {
                    sb.append(ch);
                }
            }
            sb.append('\'');
            return sb.toString();
        }
        return null;
    }    
}