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