org.dbunit.util.SQLHelper Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of dbunit Show documentation
Show all versions of dbunit Show documentation
dbUnit is a JUnit extension (also usable from Ant and Maven) targeted for database-driven projects that, among other things, puts your database into a known state between test runs. This is an excellent way to avoid the myriad of problems that can occur when one test case corrupts the database and causes subsequent tests to fail or exacerbate the damage.
/*
*
* The DbUnit Database Testing Framework
* Copyright (C)2005, DbUnit.org
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*
*/
package org.dbunit.util;
import java.io.PrintStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Locale;
import org.dbunit.DatabaseUnitRuntimeException;
import org.dbunit.database.IMetadataHandler;
import org.dbunit.dataset.Column;
import org.dbunit.dataset.datatype.DataType;
import org.dbunit.dataset.datatype.DataTypeException;
import org.dbunit.dataset.datatype.IDataTypeFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Helper for SQL-related stuff.
*
* TODO: testcases, also think about refactoring so that methods are not static anymore (for better extensibility)
* @author Felipe Leme ([email protected])
* @version $Revision$
* @since Nov 5, 2005
*
*/
public class SQLHelper {
/**
* The database product name reported by Sybase JDBC drivers.
*/
public static final String DB_PRODUCT_SYBASE = "Sybase";
/**
* Logger for this class
*/
private static final Logger logger = LoggerFactory.getLogger(SQLHelper.class);
// class is "static"
private SQLHelper() {}
/**
* Gets the primary column for a table.
* @param conn connection with the database
* @param table table name
* @return name of primary column for a table (assuming it's just 1 column).
* @throws SQLException raised while getting the meta data
*/
public static String getPrimaryKeyColumn( Connection conn, String table ) throws SQLException {
logger.debug("getPrimaryKeyColumn(conn={}, table={}) - start", conn, table);
DatabaseMetaData metadata = conn.getMetaData();
ResultSet rs = metadata.getPrimaryKeys( null, null, table );
rs.next();
String pkColumn = rs.getString(4);
return pkColumn;
}
/**
* Close a result set and a prepared statement, checking for null references.
* @param rs result set to be closed
* @param stmt prepared statement to be closed
* @throws SQLException exception raised in either close() method
*/
public static void close(ResultSet rs, Statement stmt) throws SQLException {
logger.debug("close(rs={}, stmt={}) - start", rs, stmt);
try {
SQLHelper.close(rs);
} finally {
SQLHelper.close( stmt );
}
}
/**
* Close a SQL statement, checking for null references.
* @param stmt statement to be closed
* @throws SQLException exception raised while closing the statement
*/
public static void close(Statement stmt) throws SQLException {
logger.debug("close(stmt={}) - start", stmt);
if ( stmt != null ) {
stmt.close();
}
}
/**
* Closes the given result set in a null-safe way
* @param resultSet
* @throws SQLException
*/
public static void close(ResultSet resultSet) throws SQLException {
logger.debug("close(resultSet={}) - start", resultSet);
if(resultSet != null) {
resultSet.close();
}
}
/**
* Returns true if the given schema exists for the given connection.
* @param connection The connection to a database
* @param schema The schema to be searched
* @return Returns true if the given schema exists for the given connection.
* @throws SQLException
* @since 2.3.0
*/
public static boolean schemaExists(Connection connection, String schema)
throws SQLException
{
logger.trace("schemaExists(connection={}, schema={}) - start", connection, schema);
if(schema == null)
{
throw new NullPointerException("The parameter 'schema' must not be null");
}
DatabaseMetaData metaData = connection.getMetaData();
ResultSet rs = metaData.getSchemas(); //null, schemaPattern);
try
{
while(rs.next())
{
String foundSchema = rs.getString("TABLE_SCHEM");
if(foundSchema.equals(schema))
{
return true;
}
}
// Especially for MySQL check the catalog
if(catalogExists(connection, schema))
{
logger.debug("Found catalog with name {}. Returning true because DB is probably on MySQL", schema);
return true;
}
return false;
}
finally
{
rs.close();
}
}
/**
* Checks via {@link DatabaseMetaData#getCatalogs()} whether or not the given catalog exists.
* @param connection
* @param catalog
* @return
* @throws SQLException
* @since 2.4.4
*/
private static boolean catalogExists(Connection connection, String catalog) throws SQLException
{
logger.trace("catalogExists(connection={}, catalog={}) - start", connection, catalog);
if(catalog == null)
{
throw new NullPointerException("The parameter 'catalog' must not be null");
}
DatabaseMetaData metaData = connection.getMetaData();
ResultSet rs = metaData.getCatalogs();
try
{
while(rs.next())
{
String foundCatalog = rs.getString("TABLE_CAT");
if(foundCatalog.equals(catalog))
{
return true;
}
}
return false;
}
finally
{
rs.close();
}
}
/**
* Checks if the given table exists.
* @param metaData The database meta data
* @param schema The schema in which the table should be searched. If null
* the schema is not used to narrow the table name.
* @param tableName The table name to be searched
* @return Returns true if the given table exists in the given schema.
* Else returns false.
* @throws SQLException
* @since 2.3.0
* @deprecated since 2.4.5 - use {@link IMetadataHandler#tableExists(DatabaseMetaData, String, String)}
*/
public static boolean tableExists(DatabaseMetaData metaData, String schema,
String tableName)
throws SQLException
{
ResultSet tableRs = metaData.getTables(null, schema, tableName, null);
try
{
return tableRs.next();
}
finally
{
SQLHelper.close(tableRs);
}
}
/**
* Utility method for debugging to print all tables of the given metadata on the given stream
* @param metaData
* @param outputStream
* @throws SQLException
*/
public static void printAllTables(DatabaseMetaData metaData, PrintStream outputStream) throws SQLException
{
ResultSet rs = metaData.getTables(null, null, null, null);
try
{
while (rs.next())
{
String catalog = rs.getString("TABLE_CAT");
String schema = rs.getString("TABLE_SCHEM");
String table = rs.getString("TABLE_NAME");
StringBuffer tableInfo = new StringBuffer();
if(catalog!=null) tableInfo.append(catalog).append(".");
if(schema!=null) tableInfo.append(schema).append(".");
tableInfo.append(table);
// Print the info
outputStream.println(tableInfo);
}
outputStream.flush();
}
finally
{
SQLHelper.close(rs);
}
}
/**
* Returns the database and JDBC driver information as pretty formatted string
* @param metaData The JDBC database metadata needed to retrieve database information
* @return The database information as formatted string
*/
public static String getDatabaseInfo(DatabaseMetaData metaData)
{
StringBuffer sb = new StringBuffer();
sb.append("\n");
String dbInfo = null;
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return metaData.getDatabaseProductName();
}
}.executeWrappedCall(metaData);
sb.append("\tdatabase product name=").append(dbInfo).append("\n");
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return metaData.getDatabaseProductVersion();
}
}.executeWrappedCall(metaData);
sb.append("\tdatabase version=").append(dbInfo).append("\n");
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return String.valueOf(metaData.getDatabaseMajorVersion());
}
}.executeWrappedCall(metaData);
sb.append("\tdatabase major version=").append(dbInfo).append("\n");
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return String.valueOf(metaData.getDatabaseMinorVersion());
}
}.executeWrappedCall(metaData);
sb.append("\tdatabase minor version=").append(dbInfo).append("\n");
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return metaData.getDriverName();
}
}.executeWrappedCall(metaData);
sb.append("\tjdbc driver name=").append(dbInfo).append("\n");
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return metaData.getDriverVersion();
}
}.executeWrappedCall(metaData);
sb.append("\tjdbc driver version=").append(dbInfo).append("\n");
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return String.valueOf(metaData.getDriverMajorVersion());
}
}.executeWrappedCall(metaData);
sb.append("\tjdbc driver major version=").append(dbInfo).append("\n");
dbInfo = new ExceptionWrapper(){
public String wrappedCall(DatabaseMetaData metaData) throws Exception {
return String.valueOf(metaData.getDriverMinorVersion());
}
}.executeWrappedCall(metaData);
sb.append("\tjdbc driver minor version=").append(dbInfo).append("\n");
return sb.toString();
}
/**
* Prints the database and JDBC driver information to the given output stream
* @param metaData The JDBC database metadata needed to retrieve database information
* @param outputStream The stream to which the information is printed
* @throws SQLException
*/
public static void printDatabaseInfo(DatabaseMetaData metaData, PrintStream outputStream) throws SQLException
{
String dbInfo = getDatabaseInfo(metaData);
try {
outputStream.println(dbInfo);
}
finally {
outputStream.flush();
}
}
/**
* Detects whether or not the given metadata describes the connection to a Sybase database
* or not.
* @param metaData The metadata to be checked whether it is a Sybase connection
* @return true if and only if the given metadata belongs to a Sybase database.
* @throws SQLException
*/
public static boolean isSybaseDb(DatabaseMetaData metaData) throws SQLException
{
String dbProductName = metaData.getDatabaseProductName();
boolean isSybase = (dbProductName != null && dbProductName.equals(DB_PRODUCT_SYBASE));
return isSybase;
}
/**
* Utility method to create a {@link Column} object from a SQL {@link ResultSet} object.
*
* @param resultSet A result set produced via {@link DatabaseMetaData#getColumns(String, String, String, String)}
* @param dataTypeFactory The factory used to lookup the {@link DataType} for this column
* @param datatypeWarning Whether or not a warning should be printed if the column could not
* be created because of an unknown datatype.
* @return The {@link Column} or null if the column could not be initialized because of an
* unknown datatype.
* @throws SQLException
* @throws DataTypeException
* @since 2.4.0
*/
public static final Column createColumn(ResultSet resultSet,
IDataTypeFactory dataTypeFactory, boolean datatypeWarning)
throws SQLException, DataTypeException
{
String tableName = resultSet.getString(3);
String columnName = resultSet.getString(4);
int sqlType = resultSet.getInt(5);
//If Types.DISTINCT like SQL DOMAIN, then get Source Date Type of SQL-DOMAIN
if(sqlType == java.sql.Types.DISTINCT)
{
sqlType = resultSet.getInt("SOURCE_DATA_TYPE");
}
String sqlTypeName = resultSet.getString(6);
// int columnSize = resultSet.getInt(7);
int nullable = resultSet.getInt(11);
String remarks = resultSet.getString(12);
String columnDefaultValue = resultSet.getString(13);
// This is only available since Java 5 - so we can try it and if it does not work default it
String isAutoIncrement = Column.AutoIncrement.NO.getKey();
try {
isAutoIncrement = resultSet.getString(23);
}
catch (Exception e)
{
// Ignore this one here
final String msg =
"Could not retrieve the 'isAutoIncrement' property"
+ " because not yet running on Java 1.5 -"
+ " defaulting to NO. Table={}, Column={}";
logger.debug(msg, tableName, columnName, e);
}
// Convert SQL type to DataType
DataType dataType =
dataTypeFactory.createDataType(sqlType, sqlTypeName, tableName, columnName);
if (dataType != DataType.UNKNOWN)
{
Column column = new Column(columnName, dataType,
sqlTypeName, Column.nullableValue(nullable), columnDefaultValue, remarks,
Column.AutoIncrement.autoIncrementValue(isAutoIncrement));
return column;
}
else
{
if (datatypeWarning)
logger.warn(
tableName + "." + columnName +
" data type (" + sqlType + ", '" + sqlTypeName +
"') not recognized and will be ignored. See FAQ for more information.");
// datatype unknown - column not created
return null;
}
}
/**
* Checks if the given resultSet matches the given schema and table name.
* The comparison is case sensitive.
* @param resultSet A result set produced via {@link DatabaseMetaData#getColumns(String, String, String, String)}
* @param schema The name of the schema to check. If null it is ignored in the comparison
* @param table The name of the table to check. If null it is ignored in the comparison
* @param caseSensitive Whether or not the comparison should be case sensitive or not
* @return true if the column metadata of the given resultSet matches
* the given schema and table parameters.
* @throws SQLException
* @since 2.4.0
* @deprecated since 2.4.4 - use {@link IMetadataHandler#matches(ResultSet, String, String, String, String, boolean)}
*/
public static boolean matches(ResultSet resultSet,
String schema, String table, boolean caseSensitive)
throws SQLException
{
return matches(resultSet, null, schema, table, null, caseSensitive);
}
/**
* Checks if the given resultSet matches the given schema and table name.
* The comparison is case sensitive.
* @param resultSet A result set produced via {@link DatabaseMetaData#getColumns(String, String, String, String)}
* @param catalog The name of the catalog to check. If null it is ignored in the comparison
* @param schema The name of the schema to check. If null it is ignored in the comparison
* @param table The name of the table to check. If null it is ignored in the comparison
* @param column The name of the column to check. If null it is ignored in the comparison
* @param caseSensitive Whether or not the comparison should be case sensitive or not
* @return true if the column metadata of the given resultSet matches
* the given schema and table parameters.
* @throws SQLException
* @since 2.4.0
* @deprecated since 2.4.4 - use {@link IMetadataHandler#matches(ResultSet, String, String, String, String, boolean)}
*/
public static boolean matches(ResultSet resultSet,
String catalog, String schema,
String table, String column, boolean caseSensitive)
throws SQLException
{
String catalogName = resultSet.getString(1);
String schemaName = resultSet.getString(2);
String tableName = resultSet.getString(3);
String columnName = resultSet.getString(4);
// MYSQL provides only a catalog but no schema
if(schema != null && schemaName == null && catalog==null && catalogName != null){
logger.debug("Switching catalog/schema because the are mutually null");
schemaName = catalogName;
catalogName = null;
}
boolean areEqual =
areEqualIgnoreNull(catalog, catalogName, caseSensitive) &&
areEqualIgnoreNull(schema, schemaName, caseSensitive) &&
areEqualIgnoreNull(table, tableName, caseSensitive) &&
areEqualIgnoreNull(column, columnName, caseSensitive);
return areEqual;
}
/**
* Compares the given values and returns true if they are equal.
* If the first value is null or empty String it always
* returns true which is the way of ignoring nulls
* for this specific case.
* @param value1 The first value to compare. Is ignored if null or empty String
* @param value2 The second value to be compared
* @return true if both values are equal or if the first value
* is null or empty string.
* @since 2.4.4
*/
public static final boolean areEqualIgnoreNull(String value1, String value2, boolean caseSensitive)
{
if(value1==null || value1.equals(""))
{
return true;
}
else
{
if(caseSensitive && value1.equals(value2))
{
return true;
}
else if(!caseSensitive && value1.equalsIgnoreCase(value2))
{
return true;
}
else
{
return false;
}
}
}
/**
* Corrects the case of the given String according to the way in which the database stores metadata.
* @param databaseIdentifier A database identifier such as a table name or a schema name for
* which the case should be corrected.
* @param connection The connection used to lookup the database metadata. This is needed to determine
* the way in which the database stores its metadata.
* @return The database identifier in the correct case for the RDBMS
* @since 2.4.4
*/
public static final String correctCase(final String databaseIdentifier, Connection connection)
{
logger.trace("correctCase(tableName={}, connection={}) - start", databaseIdentifier, connection);
try
{
return correctCase(databaseIdentifier, connection.getMetaData());
}
catch (SQLException e)
{
throw new DatabaseUnitRuntimeException("Exception while trying to access database metadata", e);
}
}
/**
* Corrects the case of the given String according to the way in which the database stores metadata.
* @param databaseIdentifier A database identifier such as a table name or a schema name for
* which the case should be corrected.
* @param databaseMetaData The database metadata needed to determine the way in which the database stores
* its metadata.
* @return The database identifier in the correct case for the RDBMS
* @since 2.4.4
*/
public static final String correctCase(final String databaseIdentifier, DatabaseMetaData databaseMetaData)
{
logger.trace("correctCase(tableName={}, databaseMetaData={}) - start", databaseIdentifier, databaseMetaData);
if (databaseIdentifier == null) {
throw new NullPointerException(
"The parameter 'databaseIdentifier' must not be null");
}
if (databaseMetaData == null) {
throw new NullPointerException(
"The parameter 'databaseMetaData' must not be null");
}
try {
String resultTableName = databaseIdentifier;
String dbIdentifierQuoteString = databaseMetaData.getIdentifierQuoteString();
if(!isEscaped(databaseIdentifier, dbIdentifierQuoteString)){
if(databaseMetaData.storesLowerCaseIdentifiers())
{
resultTableName = databaseIdentifier.toLowerCase(Locale.ENGLISH);
}
else if(databaseMetaData.storesUpperCaseIdentifiers())
{
resultTableName = databaseIdentifier.toUpperCase(Locale.ENGLISH);
}
else
{
logger.debug("Database does not store upperCase or lowerCase identifiers. " +
"Will not correct case of the table names.");
}
}
else
{
if(logger.isDebugEnabled())
logger.debug("The tableName '{}' is escaped. Will not correct case.", databaseIdentifier);
}
return resultTableName;
}
catch (SQLException e)
{
throw new DatabaseUnitRuntimeException("Exception while trying to access database metadata", e);
}
}
/**
* Checks whether two given values are unequal and if so print a log message (level DEBUG)
* @param oldValue The old value of a property
* @param newValue The new value of a property
* @param message The message to be logged
* @param source The class which invokes this method - used for enriching the log message
* @since 2.4.4
*/
public static final void logInfoIfValueChanged(String oldValue, String newValue, String message, Class source)
{
if(logger.isInfoEnabled())
{
if(oldValue != null && !oldValue.equals(newValue))
logger.debug("{}. {} oldValue={} newValue={}", new Object[] {source, message, oldValue, newValue});
}
}
/**
* Checks whether two given values are unequal and if so print a log message (level DEBUG)
* @param oldValue The old value of a property
* @param newValue The new value of a property
* @param message The message to be logged
* @param source The class which invokes this method - used for enriching the log message
* @since 2.4.8
*/
public static final void logDebugIfValueChanged(String oldValue, String newValue, String message, Class source)
{
if (logger.isDebugEnabled())
{
if (oldValue != null && !oldValue.equals(newValue))
logger.debug("{}. {} oldValue={} newValue={}", new Object[] {source, message, oldValue, newValue});
}
}
/**
* @param tableName
* @param dbIdentifierQuoteString
* @return
* @since 2.4.4
*/
private static final boolean isEscaped(String tableName, String dbIdentifierQuoteString)
{
logger.trace("isEscaped(tableName={}, dbIdentifierQuoteString={}) - start", tableName, dbIdentifierQuoteString);
if (dbIdentifierQuoteString == null) {
throw new NullPointerException(
"The parameter 'dbIdentifierQuoteString' must not be null");
}
boolean isEscaped = tableName!=null && (tableName.startsWith(dbIdentifierQuoteString));
if(logger.isDebugEnabled())
logger.debug("isEscaped returns '{}' for tableName={} (dbIdentifierQuoteString={})",
new Object[]{Boolean.valueOf(isEscaped), tableName, dbIdentifierQuoteString} );
return isEscaped;
}
/**
* Performs a method invocation and catches all exceptions that occur during the invocation.
* Utility which works similar to a closure, just a bit less elegant.
* @author gommma (gommma AT users.sourceforge.net)
* @author Last changed by: $Author$
* @version $Revision$ $Date$
* @since 2.4.6
*/
static abstract class ExceptionWrapper{
public static final String NOT_AVAILABLE_TEXT = "";
/**
* Default constructor
*/
public ExceptionWrapper()
{
}
/**
* Executes the call and catches all exception that might occur.
* @param metaData
* @return The result of the call
*/
public final String executeWrappedCall(DatabaseMetaData metaData) {
try{
String result = wrappedCall(metaData);
return result;
}
catch(Exception e){
logger.trace("Problem retrieving DB information via DatabaseMetaData", e);
return NOT_AVAILABLE_TEXT;
}
}
/**
* Calls the method that might throw an exception to be handled
* @param metaData
* @return The result of the call as human readable string
* @throws Exception Any exception that might occur during the method invocation
*/
public abstract String wrappedCall(DatabaseMetaData metaData) throws Exception;
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy