Skip to content

Instantly share code, notes, and snippets.

@mzipay
Created June 16, 2015 21:13
Show Gist options
  • Save mzipay/04b4fe85fb59b35128e3 to your computer and use it in GitHub Desktop.
Save mzipay/04b4fe85fb59b35128e3 to your computer and use it in GitHub Desktop.
Simple JDBC console utility
/*
* 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();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment