com.github.datalking.jdbc.support.JdbcUtils Maven / Gradle / Ivy
Show all versions of play-mvc Show documentation
package com.github.datalking.jdbc.support;
import com.github.datalking.jdbc.datasource.DataSourceUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
/**
* Generic utility methods for working with JDBC.
* Mainly for internal use within the framework, but also useful for custom JDBC access code.
*/
public abstract class JdbcUtils {
private static final Logger logger = LoggerFactory.getLogger(JdbcUtils.class);
/**
* Constant that indicates an unknown (or unspecified) SQL type.
*/
public static final int TYPE_UNKNOWN = Integer.MIN_VALUE;
/**
* Close the given JDBC Connection and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
*
* @param con the JDBC Connection to close (may be {@code null})
*/
public static void closeConnection(Connection con) {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
logger.debug("Could not close JDBC Connection", ex);
} catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.debug("Unexpected exception on closing JDBC Connection", ex);
}
}
}
/**
* Close the given JDBC Statement and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
*
* @param stmt the JDBC Statement to close (may be {@code null})
*/
public static void closeStatement(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ex) {
logger.trace("Could not close JDBC Statement", ex);
} catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.trace("Unexpected exception on closing JDBC Statement", ex);
}
}
}
/**
* Close the given JDBC ResultSet and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
*
* @param rs the JDBC ResultSet to close (may be {@code null})
*/
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) {
logger.trace("Could not close JDBC ResultSet", ex);
} catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.trace("Unexpected exception on closing JDBC ResultSet", ex);
}
}
}
/**
* Retrieve a JDBC column value from a ResultSet, using the specified value type.
* Uses the specifically typed ResultSet accessor methods, falling back to
* {@link #getResultSetValue(ResultSet, int)} for unknown types.
*
Note that the returned value may not be assignable to the specified
* required type, in case of an unknown type. Calling code needs to deal
* with this case appropriately, e.g. throwing a corresponding exception.
*
* @param rs is the ResultSet holding the data
* @param index is the column index
* @param requiredType the required value type (may be {@code null})
* @return the value object
* @throws SQLException if thrown by the JDBC API
*/
public static Object getResultSetValue(ResultSet rs, int index, Class requiredType) throws SQLException {
if (requiredType == null) {
return getResultSetValue(rs, index);
}
Object value = null;
boolean wasNullCheck = false;
// Explicitly extract typed value, as far as possible.
if (String.class.equals(requiredType)) {
value = rs.getString(index);
} else if (boolean.class.equals(requiredType) || Boolean.class.equals(requiredType)) {
value = rs.getBoolean(index);
wasNullCheck = true;
} else if (byte.class.equals(requiredType) || Byte.class.equals(requiredType)) {
value = rs.getByte(index);
wasNullCheck = true;
} else if (short.class.equals(requiredType) || Short.class.equals(requiredType)) {
value = rs.getShort(index);
wasNullCheck = true;
} else if (int.class.equals(requiredType) || Integer.class.equals(requiredType)) {
value = rs.getInt(index);
wasNullCheck = true;
} else if (long.class.equals(requiredType) || Long.class.equals(requiredType)) {
value = rs.getLong(index);
wasNullCheck = true;
} else if (float.class.equals(requiredType) || Float.class.equals(requiredType)) {
value = rs.getFloat(index);
wasNullCheck = true;
} else if (double.class.equals(requiredType) || Double.class.equals(requiredType) ||
Number.class.equals(requiredType)) {
value = rs.getDouble(index);
wasNullCheck = true;
} else if (byte[].class.equals(requiredType)) {
value = rs.getBytes(index);
} else if (java.sql.Date.class.equals(requiredType)) {
value = rs.getDate(index);
} else if (java.sql.Time.class.equals(requiredType)) {
value = rs.getTime(index);
} else if (java.sql.Timestamp.class.equals(requiredType) || java.util.Date.class.equals(requiredType)) {
value = rs.getTimestamp(index);
} else if (BigDecimal.class.equals(requiredType)) {
value = rs.getBigDecimal(index);
} else if (Blob.class.equals(requiredType)) {
value = rs.getBlob(index);
} else if (Clob.class.equals(requiredType)) {
value = rs.getClob(index);
} else {
// Some unknown type desired -> rely on getObject.
value = getResultSetValue(rs, index);
}
// Perform was-null check if demanded (for results that the
// JDBC driver returns as primitives).
if (wasNullCheck && value != null && rs.wasNull()) {
value = null;
}
return value;
}
/**
* Retrieve a JDBC column value from a ResultSet, using the most appropriate
* value type. The returned value should be a detached value object, not having
* any ties to the active ResultSet: in particular, it should not be a Blob or
* Clob object but rather a byte array respectively String representation.
*
Uses the {@code getObject(index)} method, but includes additional "hacks"
* to get around Oracle 10g returning a non-standard object for its TIMESTAMP
* datatype and a {@code java.sql.Date} for DATE columns leaving out the
* time portion: These columns will explicitly be extracted as standard
* {@code java.sql.Timestamp} object.
*
* @param rs is the ResultSet holding the data
* @param index is the column index
* @return the value object
* @throws SQLException if thrown by the JDBC API
* @see Blob
* @see Clob
* @see java.sql.Timestamp
*/
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
Object obj = rs.getObject(index);
String className = null;
if (obj != null) {
className = obj.getClass().getName();
}
if (obj instanceof Blob) {
obj = rs.getBytes(index);
} else if (obj instanceof Clob) {
obj = rs.getString(index);
} else if (className != null &&
("oracle.sql.TIMESTAMP".equals(className) ||
"oracle.sql.TIMESTAMPTZ".equals(className))) {
obj = rs.getTimestamp(index);
} else if (className != null && className.startsWith("oracle.sql.DATE")) {
String metaDataClassName = rs.getMetaData().getColumnClassName(index);
if ("java.sql.Timestamp".equals(metaDataClassName) ||
"oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
obj = rs.getTimestamp(index);
} else {
obj = rs.getDate(index);
}
} else if (obj != null && obj instanceof java.sql.Date) {
if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
obj = rs.getTimestamp(index);
}
}
return obj;
}
/**
* Extract database meta data via the given DatabaseMetaDataCallback.
*
This method will open a connection to the database and retrieve the database metadata.
* Since this method is called before the exception translation feature is configured for
* a datasource, this method can not rely on the SQLException translation functionality.
*
Any exceptions will be wrapped in a Exception. This is a checked exception
* and any calling code should catch and handle this exception. You can just log the
* error and hope for the best, but there is probably a more serious error that will
* reappear when you try to access the database again.
*
* @param dataSource the DataSource to extract metadata for
* @param action callback that will do the actual work
* @return object containing the extracted information, as returned by
* the DatabaseMetaDataCallback's {@code processMetaData} method
*/
public static Object extractDatabaseMetaData(DataSource dataSource, DatabaseMetaDataCallback action) throws Exception {
Connection con = null;
try {
con = DataSourceUtils.getConnection(dataSource);
if (con == null) {
// should only happen in test environments
throw new Exception("Connection returned by DataSource [" + dataSource + "] was null");
}
DatabaseMetaData metaData = con.getMetaData();
if (metaData == null) {
// should only happen in test environments
throw new Exception("DatabaseMetaData returned by Connection [" + con + "] was null");
}
return action.processMetaData(metaData);
} catch (SQLException ex) {
throw new Exception("Error while extracting DatabaseMetaData", ex);
} catch (AbstractMethodError err) {
throw new Exception(
"JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver", err);
} catch (Exception ex) {
throw new Exception("Could not get Connection for extracting meta data", ex);
} finally {
DataSourceUtils.releaseConnection(con, dataSource);
}
}
/**
* Call the specified method on DatabaseMetaData for the given DataSource,
* and extract the invocation result.
*
* @param dataSource the DataSource to extract meta data for
* @param metaDataMethodName the name of the DatabaseMetaData method to call
* @return the object returned by the specified DatabaseMetaData method
* @throws Exception if we couldn't access the DatabaseMetaData
* or failed to invoke the specified method
* @see DatabaseMetaData
*/
public static Object extractDatabaseMetaData(DataSource dataSource, final String metaDataMethodName)
throws Exception {
return extractDatabaseMetaData(dataSource,
new DatabaseMetaDataCallback() {
public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, Exception {
try {
Method method = DatabaseMetaData.class.getMethod(metaDataMethodName, (Class[]) null);
return method.invoke(dbmd, (Object[]) null);
} catch (NoSuchMethodException ex) {
throw new Exception("No method named '" + metaDataMethodName +
"' found on DatabaseMetaData instance [" + dbmd + "]", ex);
} catch (IllegalAccessException ex) {
throw new Exception(
"Could not access DatabaseMetaData method '" + metaDataMethodName + "'", ex);
} catch (InvocationTargetException ex) {
if (ex.getTargetException() instanceof SQLException) {
throw (SQLException) ex.getTargetException();
}
throw new Exception(
"Invocation of DatabaseMetaData method '" + metaDataMethodName + "' failed", ex);
}
}
});
}
/**
* Return whether the given JDBC driver supports JDBC 2.0 batch updates.
*
Typically invoked right before execution of a given set of statements:
* to decide whether the set of SQL statements should be executed through
* the JDBC 2.0 batch mechanism or simply in a traditional one-by-one fashion.
*
Logs a warning if the "supportsBatchUpdates" methods throws an exception
* and simply returns {@code false} in that case.
*
* @param con the Connection to check
* @return whether JDBC 2.0 batch updates are supported
* @see DatabaseMetaData#supportsBatchUpdates()
*/
public static boolean supportsBatchUpdates(Connection con) {
try {
DatabaseMetaData dbmd = con.getMetaData();
if (dbmd != null) {
if (dbmd.supportsBatchUpdates()) {
logger.debug("JDBC driver supports batch updates");
return true;
} else {
logger.debug("JDBC driver does not support batch updates");
}
}
} catch (SQLException ex) {
logger.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
} catch (AbstractMethodError err) {
logger.debug("JDBC driver does not support JDBC 2.0 'supportsBatchUpdates' method", err);
}
return false;
}
/**
* Extract a common name for the database in use even if various drivers/platforms provide varying names.
*
* @param source the name as provided in database metedata
* @return the common name to be used
*/
public static String commonDatabaseName(String source) {
String name = source;
if (source != null && source.startsWith("DB2")) {
name = "DB2";
} else if ("Sybase SQL Server".equals(source) ||
"Adaptive Server Enterprise".equals(source) ||
"ASE".equals(source) ||
"sql server".equalsIgnoreCase(source)) {
name = "Sybase";
}
return name;
}
/**
* Check whether the given SQL type is numeric.
*
* @param sqlType the SQL type to be checked
* @return whether the type is numeric
*/
public static boolean isNumeric(int sqlType) {
return Types.BIT == sqlType || Types.BIGINT == sqlType || Types.DECIMAL == sqlType ||
Types.DOUBLE == sqlType || Types.FLOAT == sqlType || Types.INTEGER == sqlType ||
Types.NUMERIC == sqlType || Types.REAL == sqlType || Types.SMALLINT == sqlType ||
Types.TINYINT == sqlType;
}
/**
* Determine the column name to use. The column name is determined based on a
* lookup using ResultSetMetaData.
*
This method implementation takes into account recent clarifications
* expressed in the JDBC 4.0 specification:
*
columnLabel - the label for the column specified with the SQL AS clause.
* If the SQL AS clause was not specified, then the label is the name of the column.
*
* @param resultSetMetaData the current meta data to use
* @param columnIndex the index of the column for the look up
* @return the column name to use
* @throws SQLException in case of lookup failure
*/
public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {
String name = resultSetMetaData.getColumnLabel(columnIndex);
if (name == null || name.length() < 1) {
name = resultSetMetaData.getColumnName(columnIndex);
}
return name;
}
/**
* Convert a column name with underscores to the corresponding property name using "camel case". A name
* like "customer_number" would match a "customerNumber" property name.
*
* @param name the column name to be converted
* @return the name using "camel case"
*/
public static String convertUnderscoreNameToPropertyName(String name) {
StringBuilder result = new StringBuilder();
boolean nextIsUpper = false;
if (name != null && name.length() > 0) {
if (name.length() > 1 && name.substring(1, 2).equals("_")) {
result.append(name.substring(0, 1).toUpperCase());
} else {
result.append(name.substring(0, 1).toLowerCase());
}
for (int i = 1; i < name.length(); i++) {
String s = name.substring(i, i + 1);
if (s.equals("_")) {
nextIsUpper = true;
} else {
if (nextIsUpper) {
result.append(s.toUpperCase());
nextIsUpper = false;
} else {
result.append(s.toLowerCase());
}
}
}
}
return result.toString();
}
}