Simple JDBC console utility

JDBCUtility.java

/*
 * Copyright (c) 2009 Matthew Zipay
 * 
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 * 
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Properties;

/**
 * Use directly or extend to create simple JDBC console utilities.
 */
public class JDBCUtility {
    /** The fully-qualified class name of the JDBC driver. */
    private String driverClassName;

    /** The JDBC database URL (i.e. "jdbc:..."). */
    private String jdbcUrl;

    /** The database username. */
    private String dbUsername;

    /** The database password. */
    private String dbPassword;

    /** The database catalog. */
    private String catalog;

    /** The active database connection. */
    private Connection connection;

    /**
     * A map of cached prepared statements, managed internally since we don't
     * have container services to handle this from the command line.
     */
    private HashMap<String, PreparedStatement> preparedStatementCache;

    /**
     * Creates a new JDBCUtility.
     * 
     * <p>
     * When using this constructor, subclasses MUST call the
     * {@link #setDriverClassName(String)}, {@link #setURL(String)},
     * {@link #setUsername(String)}, and {@link #setPassword(String)} methods
     * explicitly.
     * </p>
     */
    public JDBCUtility() {
        /* subclass must call setter methods! */
    }

    /**
     * Creates a new JDBCUtility using the provided driver class name, URL,
     * username, and password.
     * 
     * @param driverClassName
     *          the fully-qualified JDBC driver class name
     * @param jdbcURL
     *          the JDBC database URL
     * @param dbUsername
     *          the database username
     * @param dbPassword
     *          the database password
     * @throws ClassNotFoundException if the driver cannot be loaded
     */
    public JDBCUtility(String driverClassName, String jdbcURL,
            String dbUsername, String dbPassword)
            throws ClassNotFoundException {
        if (driverClassName == null) {
            throw new IllegalArgumentException("driverClassName cannot be null");
        } else if (jdbcURL == null) {
            throw new IllegalArgumentException("jdbcURL cannot be null");
        } else if (dbUsername == null) {
            throw new IllegalArgumentException("dbUsername cannot be null");
        } else if (dbPassword == null) {
            throw new IllegalArgumentException("dbPassword cannot be null");
        }

        setDriverClassName(driverClassName);
        this.jdbcUrl = jdbcUrl;
        this.dbUsername = dbUsername;
        this.dbPassword = dbPassword;
    }

    /**
     * Creates a new JDBCUtility using the provided driver class name, URL,
     * username, password, and catalog.
     * 
     * @param driverClassName
     *          the fully-qualified JDBC driver class name
     * @param jdbcURL
     *          the JDBC database URL
     * @param dbUsername
     *          the database username
     * @param dbPassword
     *          the database password
     * @param catalog
     *          the database catalog (i.e. schema)
     * @throws ClassNotFoundException if the driver cannot be loaded
     */
    public JDBCUtility(String driverClassName, String jdbcURL,
            String dbUsername, String dbPassword, String catalog)
            throws ClassNotFoundException {
        this(driverClassName, jdbcURL, dbUsername, dbPassword);
        this.catalog = catalog;
    }

    /**
     * Creates a new JDBCUtility, retrieving the driver class name, URL,
     * username, and password from the specified properties file.
     * 
     * <p>
     * The file MUST define the following properties:
     * </p>
     * <ul>
     * <li><strong>jdbc.driver</strong></li>
     * <li><strong>jdbc.url</strong></li>
     * <li><strong>jdbc.username</strong></li>
     * <li><strong>jdbc.password</strong></li>
     * </ul>
     * 
     * <p>
     * The file MAY also define a <strong>jdbc.catalog</strong> property. If
     * this property is specified, the {@link Connection#setCatalog(String)}
     * method will be called when the connection is established.
     * </p>
     * 
     * @param propertiesFilename
     *          the location of the properties file
     * @throws IOException if the properties file cannot be read
     * @throws ClassNotFoundException if the driver cannot be loaded
     */
    public JDBCUtility(String propertiesFilename)
            throws IOException, ClassNotFoundException {
        Properties props = new Properties();

        ClassLoader classLoader = JDBCUtility.class.getClassLoader();
        if (classLoader == null) {
            classLoader = Thread.currentThread().getContextClassLoader();
        }

        InputStream inStream =
                classLoader.getResourceAsStream(propertiesFilename);
        if (inStream != null) {
            try {
                props.load(inStream);
            } finally {
                inStream.close();
            }
        } else {
            throw new FileNotFoundException(propertiesFilename);
        }

        String driverClassName = props.getProperty("jdbc.driver");
        if ((driverClassName == null) || driverClassName.isEmpty()) {
            throw new IllegalArgumentException(
                    "'jdbc.driver' property not defined");
        }
        setDriverClassName(driverClassName);

        jdbcUrl = props.getProperty("jdbc.url");
        dbUsername = props.getProperty("jdbc.username");
        dbPassword = props.getProperty("jdbc.password");

        if ((jdbcUrl == null) || jdbcUrl.isEmpty()) {
            throw new IllegalArgumentException(
                    "'jdbc.url' property not defined");
        } else if (dbUsername == null) {
            throw new IllegalArgumentException(
                    "'jdbc.username' property not defined");
        } else if (dbPassword == null) {
            throw new IllegalArgumentException(
                    "'jdbc.password' property not defined");
        }

        setCatalog(props.getProperty("jdbc.catalog"));
    }

    /**
     * Returns the JDBC driver class name.
     * 
     * @return a fully-qualified Java class name
     */
    public String getDriverClassName() {
        return driverClassName;
    }

    /**
     * Sets the JDBC driver class name.
     * 
     * <p>
     * The driver is immediately loaded when this method is called.
     * </p>
     * 
     * @param driverClassName
     *          the fully-qualified Java class name of a JDBC driver that is on
     *          the current class path
     * @throws ClassNotFoundException if the driver cannot be loaded
     */
    public void setDriverClassName(String driverClassName)
            throws ClassNotFoundException {
        this.driverClassName = driverClassName;
        Class.forName(driverClassName);
    }

    /**
     * Returns the JDBC database URL.
     * 
     * @return a URL using the <em>"jdbc:"</em> scheme
     */
    public String getUrl() {
        return jdbcUrl;
    }

    /**
     * Sets the JDBC database URL.
     * 
     * @param jdbcUrl
     *          the URL identifying the location of the database, using the
     *          <em>"jdbc:"</em> scheme
     */
    public void setUrl(String jdbcUrl) {
        this.jdbcUrl = jdbcUrl;
    }

    /**
     * Returns the database username.
     * 
     * @return the user/account name used to authenticate with the database
     */
    public String getUsername() {
        return dbUsername;
    }

    /**
     * Sets the database username.
     * 
     * @param dbUsername
     *          the user/account name used to authenticate with the database
     */
    public void setUsername(String dbUsername) {
        this.dbUsername = dbUsername;
    }

    /**
     * Sets the database password.
     * 
     * @param dbPassword
     *          the user/account password used to authenticate with the database
     */
    public void setPassword(String dbPassword) {
        this.dbPassword = dbPassword;
    }

    /**
     * Returns the database catalog.
     * 
     * @return the name identifying the catalog being accessed by
     */
    public String getCatalog() {
        return catalog;
    }

    /**
     * Sets the database catalog for the connection.
     * 
     * @param catalog the catalog name
     */
    public void setCatalog(String catalog) {
        this.catalog = catalog;
    }

    /**
     * Establishes the database connection.
     * 
     * @throws SQLException if the connection attempt fails
     */
    public void connect() throws SQLException {
        getConnection();
    }

    /**
     * Clears the prepared statement cache and closes the database connection.
     * 
     * @throws SQLException if an exception occurs while clearing the prepared
     *          statement cache or closing the connection
     */
    public void disconnect() throws SQLException {
        try {
            clearPreparedStatementCache();
            connection.close();
        } finally {
            preparedStatementCache = null;
            connection = null;
        }
    }

    /**
     * Returns the active database connection, creating it if necessary.
     * 
     * @return the active database connection
     * @throws SQLException if the connection cannot be established
     */
    protected Connection getConnection() throws SQLException {
        if (connection == null) {
            connection = DriverManager.getConnection(jdbcUrl,
                    dbUsername, dbPassword);
            if ((catalog != null) && !catalog.isEmpty()) {
                connection.setCatalog(catalog);
            }

            if (preparedStatementCache == null) {
                preparedStatementCache =
                        new HashMap<String, PreparedStatement>();
            } else {
                clearPreparedStatementCache();
            }
        }

        return connection;
    }

    /**
     * Closes and evicts all cached prepared statements.
     * 
     * <p>
     * Any {@link SQLException} thrown by {@link Statement#close()} is silently
     * ignored.
     * </p>
     */
    private void clearPreparedStatementCache() throws SQLException {
        Iterator<PreparedStatement> pstmtIter =
                preparedStatementCache.values().iterator();
        while (pstmtIter.hasNext()) {
            try {
                pstmtIter.next().close();
            } catch (SQLException ex) {
                /* ignored */
            } finally {
                pstmtIter.remove();
            }
        }
    }

    /**
     * Creates a new {@link Statement}.
     * 
     * <p>
     * Callers are responsible for closing the returned statement and any result
     * sets retrieved from it.
     * </p>
     * 
     * @return a statement object ready to send non-parameterized SQL statements
     *          to the database
     * @throws SQLException if the statement cannot be created
     * @see java.sql.Connection#createStatement()
     */
    public Statement getStatement() throws SQLException {
        return getConnection().createStatement();
    }

    /**
     * Creates a new {@link PreparedStatement} for the specified SQL statement.
     * 
     * <p>
     * <strong>Do not close the returned
     * <code>PreparedStatement</code>!</strong>. All
     * <code>PreparedStatement</code>s returned by this method are cached, and
     * will be closed when the {@link #disconnect()} method is called.
     * </p>
     * 
     * <p>
     * Callers are responsible for closing any result sets retrieved from the
     * returned prepared statement.
     * </p>
     * 
     * @param sql
     *          the parameterized SQL statement
     * @return a pre-compiled SQL statement
     * @throws SQLException if the statement cannot be compiled
     * @see java.sql.Connection#prepareStatement(String)
     */
    public PreparedStatement getPreparedStatement(String sql) throws SQLException {
        PreparedStatement pstmt = preparedStatementCache.get(sql);

        if (pstmt != null) {
            // clear the warnings and parameters before returning
            try {
                pstmt.clearWarnings();
                pstmt.clearParameters();
            } catch (SQLException ex) {
                // if an exception occurs, just close (will be recreated)
                pstmt.close();
                pstmt = null;
            }
        }

        if (pstmt == null) {
            pstmt = getConnection().prepareStatement(sql);
            preparedStatementCache.put(sql, pstmt);
        }

        return pstmt;
    }

    /**
     * Delegates to {@link Connection#getWarnings()} for the active database
     * connection.
     * 
     * @return the first warning reported by calls on the active database
     *          connection (subsequent warnings are chained to the returned
     *          warning)
     * @throws SQLException if the warnings cannot be retrieved
     * @see java.sql.Connection#getWarnings()
     */
    public SQLWarning getWarnings() throws SQLException {
        return getConnection().getWarnings();
    }
}