WARN [2011-08-24 08:30:40] [user=] (DatabaseTableMetaData.java:getColumns:348) - No columns found for table 'XXX_TABLE' that are supported by dbunit. Will return an empty column list org.dbunit.dataset.NoSuchColumnException: XXX_TABLE.XXX_ID - (Non-uppercase input column: XXX_ID) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive. at org.dbunit.dataset.AbstractTableMetaData.getColumnIndex(AbstractTableMetaData.java:117) at org.dbunit.operation.AbstractOperation.getOperationMetaData(AbstractOperation.java:89) at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:140)
It looks like OracleDatabaseMetaData doesn't take into account chained synonyms, even with "SYNONYM" included in the dbUnit table types config property (DatabaseConfig.PROPERTY_TABLE_TYPE).
To work around this problem, I extended the DefaultMetadataHandler to resolve the actual schema_name/table_name when needed.
import org.apache.commons.lang.builder.EqualsBuilder;
import org.apache.commons.lang.builder.HashCodeBuilder;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import org.dbunit.database.DefaultMetadataHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcOperations;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* Solves the problem where Dbunit throws a NoSuchColumnException because it cannot resolve the column names through
* a chain of synonyms.
*/
public class OracleSynonymAwareMetaDataHandler extends DefaultMetadataHandler {
public static final Logger logger = LoggerFactory.getLogger(OracleSynonymAwareMetaDataHandler.class);
private final Map<TableInfo, TableInfo> synonymMap;
private final SimpleJdbcOperations simpleJdbcOperations;
private static final String SQL_SELECT_USER_SYNONYM =
"\n with filter1 as ( " +
"\n select connect_by_root synonym_name root_synonym_name, " +
"\n connect_by_root table_owner root_table_owner, " +
"\n level lvl, " +
"\n syn.* " +
"\n from user_synonyms syn " +
"\n connect by synonym_name = prior table_name and (table_owner != prior table_owner) " +
"\n start with synonym_name = :p_synonym_name and table_owner = :p_schema_name " +
"\n ), filter2 as ( " +
"\n select filter1.*, " +
"\n max(lvl) over (PARTITION BY root_synonym_name, root_table_owner) max_lvl " +
"\n from filter1 " +
"\n ) " +
"\n select table_owner, " +
"\n table_name " +
"\n from filter2 " +
"\n where lvl = max_lvl ";
public OracleSynonymAwareMetaDataHandler(SimpleJdbcOperations simpleJdbcOperations) {
synonymMap = new HashMap<TableInfo, TableInfo>();
this.simpleJdbcOperations = simpleJdbcOperations;
}
@Override
public ResultSet getColumns(final DatabaseMetaData databaseMetaData,
final String schemaName,
final String tableName)
throws SQLException {
final TableInfo tableInfo = getTableInfo(schemaName, tableName);
return super.getColumns(databaseMetaData, tableInfo.getSchemaName(),
tableInfo.getTableName());
}
@Override
public boolean matches(ResultSet columnsResultSet,
String catalog,
String schemaName,
String tableName,
String column,
boolean caseSensitive)
throws SQLException {
final TableInfo tableInfo = getTableInfo(schemaName, tableName);
return super.matches(columnsResultSet, catalog, tableInfo.getSchemaName(),
tableInfo.getTableName(), column, caseSensitive);
}
@Override
public ResultSet getPrimaryKeys(DatabaseMetaData metaData,
String schemaName,
String tableName)
throws SQLException {
final TableInfo tableInfo = getTableInfo(schemaName, tableName);
return super.getPrimaryKeys(metaData, tableInfo.getSchemaName(),
tableInfo.getTableName());
}
/**
* returns the ACTUAL schemaName and tableName for the provided values
*/
private TableInfo getTableInfo(final String schemaName, final String tableName) {
logger.debug("getTableInfo - schemaName: {}, tableName: {}", schemaName, tableName);
TableInfo requestTableInfo = new TableInfo(schemaName, tableName);
if (synonymMap.containsKey(requestTableInfo)) {
return synonymMap.get(requestTableInfo);
} else {
TableInfo actualTableInfo = (TableInfo) DataAccessUtils.singleResult(
simpleJdbcOperations.getNamedParameterJdbcOperations().query(
SQL_SELECT_USER_SYNONYM,
new HashMap<String, Object>() {{
put("p_synonym_name", tableName);
put("p_schema_name", schemaName);
}},
new ParameterizedRowMapper<tableinfo>() {
@Override
public TableInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
int ndx = 0;
return new TableInfo(
rs.getString(++ndx)
, rs.getString(++ndx)
);
}
}
));
if (actualTableInfo == null) {
actualTableInfo = requestTableInfo;
}
synonymMap.put(requestTableInfo, actualTableInfo);
return actualTableInfo;
}
}
private static class TableInfo {
private final String schemaName;
private final String tableName;
private TableInfo(String schemaName, String tableName) {
this.schemaName = schemaName;
this.tableName = tableName;
}
public String getSchemaName() {
return schemaName;
}
public String getTableName() {
return tableName;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof TableInfo)) return false;
TableInfo that = (TableInfo) o;
return EqualsBuilder.reflectionEquals(this, that);
}
@Override
public int hashCode() {
return HashCodeBuilder.reflectionHashCode(this);
}
@Override
public String toString() {
return ToStringBuilder.reflectionToString(this, ToStringStyle.SHORT_PREFIX_STYLE);
}
}
}
This OracleSynonymAwareMetaDataHandler class can be configured into dbUnit using the DatabaseConfig.PROPERTY_METADATA_HANDLER config property.
iDatabaseConnection.getConfig()
.setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER,
oracleSynonymAwareMetaDataHandlerInstance );
The hierarchical (oracle) query I wrote is a bit tricky, but it returns the 'deepest' synonym on the fly. The same result could be achieved by recursively retrieving a single synonym at a time until no result is found.
with filter1 as ( select connect_by_root synonym_name root_synonym_name, connect_by_root table_owner root_table_owner, level lvl, syn.* from user_synonyms syn connect by synonym_name = prior table_name and (table_owner != prior table_owner) start with synonym_name = :p_synonym_name and table_owner = :p_schema_name ), filter2 as ( select filter1.*, max(lvl) over (PARTITION BY root_synonym_name, root_table_owner) max_lvl from filter1 ) select table_owner, table_name from filter2 where lvl = max_lvl
Posts
