rponte · January 29, 2021 19:20 · rponte · Jun 2, 2018 · rponte · Jun 2, 2018
diff --git a/CustomOracleDbunitConnectionCreator.java b/CustomOracleDbunitConnectionCreator.java
 package base.dbunit;

 import javax.sql.DataSource;

 import org.dbunit.database.DatabaseConfig;
 import org.dbunit.database.IDatabaseConnection;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.stereotype.Component;

 import br.com.triadworks.dbunit.vendors.oracle.OracleDbUnitConnectionCreator;

 @Component
 public class CustomOracleDbunitConnectionCreator extends OracleDbUnitConnectionCreator {

 private DataSource dataSource;

 @Autowired
 public CustomOracleDbunitConnectionCreator(DataSource dataSource) {
 super(dataSource);
 this.dataSource = dataSource;
 }

 @Override
 public IDatabaseConnection create() {
 IDatabaseConnection dbunitConn = super.create();
 // it's necessary to configure PROPERTY_TABLE_TYPE and PROPERTY_METADATA_HANDLER to support Oracle synonyms
 dbunitConn.getConfig().setProperty(DatabaseConfig.PROPERTY_TABLE_TYPE, new String[] {"TABLE", "SYNONYM"});
 dbunitConn.getConfig().setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new OracleSynonymAwareMetaDataHandler(new JdbcTemplate(dataSource)));
 return dbunitConn;
 }
 }
diff --git a/Main.java b/Main.java
 import java.sql.*;

 /**
 * Just another alternative to solve this issue without having to change DbUnit internals
 */
 public class Main {

 public static void main(String[] args) {
 Connection con;
 DatabaseMetaData metaData;
 try {
 Class.forName("oracle.jdbc.driver.OracleDriver");
 con = DriverManager.
 getConnection("jdbc:oracle:thin:@localhost:1521:sid"
 , "USER", "PASSWORD");

 ( (oracle.jdbc.driver.OracleConnection)con ).setIncludeSynonyms(true);
 metaData = con.getMetaData();
 ResultSet columns = metaData.getColumns(null, "[SHEME NAME]", "[TABLE NAME]", "%");
 while (columns.next()) {
 String name = columns.getString("COLUMN_NAME");
 String type = columns.getString("TYPE_NAME");
 System.out.println(name + "\t" + type);
 }
 Statement stmt = con.createStatement();
 System.out.println("Created DB Connection....");
 } catch (ClassNotFoundException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 } catch (SQLException e) {
 // TODO Auto-generated catch block
 e.printStackTrace();
 }
 }
 }
diff --git a/OracleSynonymAwareMetaDataHandler.java b/OracleSynonymAwareMetaDataHandler.java
 package base.dbunit;

 import java.sql.DatabaseMetaData;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;

 import org.apache.commons.lang3.builder.EqualsBuilder;
 import org.apache.commons.lang3.builder.HashCodeBuilder;
 import org.apache.commons.lang3.builder.ToStringBuilder;
 import org.apache.commons.lang3.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.JdbcTemplate;
 import org.springframework.jdbc.core.RowMapper;

 /**
 * Solves the problem where Dbunit throws a NoSuchColumnException because it
 * cannot resolve the column names through a chain of synonyms.
 *
 * http://code-ref.blogspot.com/2011/08/dbunit-nosuchcolumnexception-using.html
 * https://github.com/thirve-li/youthen-framework/blob/master/src/main/java/com/youthen/framework/test/OracleSynonymAwareMetaDataHandler.java
 * https://sourceforge.net/p/dbunit/bugs/368/
 * https://stackoverflow.com/questions/31274931/match-table-name-and-alias-at-runtime-from-databasemetadata
 */
 public class OracleSynonymAwareMetaDataHandler extends DefaultMetadataHandler {

 public static final Logger logger = LoggerFactory.getLogger(OracleSynonymAwareMetaDataHandler.class);

 private final Map<TableInfo, TableInfo> synonymMap;
 private final JdbcTemplate jdbcTemplate;

 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 = ? " +
 "\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(JdbcTemplate jdbcTemplate) {
 this.synonymMap = new HashMap<TableInfo, TableInfo>();
 this.jdbcTemplate = jdbcTemplate;
 }

 @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 = resolveSynonymFor(tableName);
 if (actualTableInfo == null) {
 actualTableInfo = requestTableInfo;
 }

 synonymMap.put(requestTableInfo, actualTableInfo);

 return actualTableInfo;
 }
 }

 private TableInfo resolveSynonymFor(final String synonymName) {
 List<TableInfo> metadata = jdbcTemplate.query(SQL_SELECT_USER_SYNONYM,
 new Object[] {synonymName}, new TableInfoMapper());
 TableInfo tableInfo = (TableInfo) DataAccessUtils.singleResult(metadata);
 return tableInfo;
 }

 private static class TableInfoMapper implements RowMapper<TableInfo> {
 @Override
 public TableInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
 String schemaName = rs.getString("table_owner");
 String tableName = rs.getString("table_name");
 return new TableInfo(schemaName, tableName);
 }
 }

 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);
 }
 }
 }
	package base.dbunit;

	import javax.sql.DataSource;

	import org.dbunit.database.DatabaseConfig;
	import org.dbunit.database.IDatabaseConnection;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.jdbc.core.JdbcTemplate;
	import org.springframework.stereotype.Component;

	import br.com.triadworks.dbunit.vendors.oracle.OracleDbUnitConnectionCreator;

	@Component
	public class CustomOracleDbunitConnectionCreator extends OracleDbUnitConnectionCreator {

	private DataSource dataSource;

	@Autowired
	public CustomOracleDbunitConnectionCreator(DataSource dataSource) {
	super(dataSource);
	this.dataSource = dataSource;
	}

	@Override
	public IDatabaseConnection create() {
	IDatabaseConnection dbunitConn = super.create();
	// it's necessary to configure PROPERTY_TABLE_TYPE and PROPERTY_METADATA_HANDLER to support Oracle synonyms
	dbunitConn.getConfig().setProperty(DatabaseConfig.PROPERTY_TABLE_TYPE, new String[] {"TABLE", "SYNONYM"});
	dbunitConn.getConfig().setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new OracleSynonymAwareMetaDataHandler(new JdbcTemplate(dataSource)));
	return dbunitConn;
	}
	}
	import java.sql.*;

	/**
	* Just another alternative to solve this issue without having to change DbUnit internals
	*/
	public class Main {

	public static void main(String[] args) {
	Connection con;
	DatabaseMetaData metaData;
	try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	con = DriverManager.
	getConnection("jdbc:oracle:thin:@localhost:1521:sid"
	, "USER", "PASSWORD");

	( (oracle.jdbc.driver.OracleConnection)con ).setIncludeSynonyms(true);
	metaData = con.getMetaData();
	ResultSet columns = metaData.getColumns(null, "[SHEME NAME]", "[TABLE NAME]", "%");
	while (columns.next()) {
	String name = columns.getString("COLUMN_NAME");
	String type = columns.getString("TYPE_NAME");
	System.out.println(name + "\t" + type);
	}
	Statement stmt = con.createStatement();
	System.out.println("Created DB Connection....");
	} catch (ClassNotFoundException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	}
	}
	package base.dbunit;

	import java.sql.DatabaseMetaData;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.util.HashMap;
	import java.util.List;
	import java.util.Map;

	import org.apache.commons.lang3.builder.EqualsBuilder;
	import org.apache.commons.lang3.builder.HashCodeBuilder;
	import org.apache.commons.lang3.builder.ToStringBuilder;
	import org.apache.commons.lang3.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.JdbcTemplate;
	import org.springframework.jdbc.core.RowMapper;

	/**
	* Solves the problem where Dbunit throws a NoSuchColumnException because it
	* cannot resolve the column names through a chain of synonyms.
	*
	* http://code-ref.blogspot.com/2011/08/dbunit-nosuchcolumnexception-using.html
	* https://github.com/thirve-li/youthen-framework/blob/master/src/main/java/com/youthen/framework/test/OracleSynonymAwareMetaDataHandler.java
	* https://sourceforge.net/p/dbunit/bugs/368/
	* https://stackoverflow.com/questions/31274931/match-table-name-and-alias-at-runtime-from-databasemetadata
	*/
	public class OracleSynonymAwareMetaDataHandler extends DefaultMetadataHandler {

	public static final Logger logger = LoggerFactory.getLogger(OracleSynonymAwareMetaDataHandler.class);

	private final Map<TableInfo, TableInfo> synonymMap;
	private final JdbcTemplate jdbcTemplate;

	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 = ? " +
	"\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(JdbcTemplate jdbcTemplate) {
	this.synonymMap = new HashMap<TableInfo, TableInfo>();
	this.jdbcTemplate = jdbcTemplate;
	}

	@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 = resolveSynonymFor(tableName);
	if (actualTableInfo == null) {
	actualTableInfo = requestTableInfo;
	}

	synonymMap.put(requestTableInfo, actualTableInfo);

	return actualTableInfo;
	}
	}

	private TableInfo resolveSynonymFor(final String synonymName) {
	List<TableInfo> metadata = jdbcTemplate.query(SQL_SELECT_USER_SYNONYM,
	new Object[] {synonymName}, new TableInfoMapper());
	TableInfo tableInfo = (TableInfo) DataAccessUtils.singleResult(metadata);
	return tableInfo;
	}

	private static class TableInfoMapper implements RowMapper<TableInfo> {
	@Override
	public TableInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
	String schemaName = rs.getString("table_owner");
	String tableName = rs.getString("table_name");
	return new TableInfo(schemaName, tableName);
	}
	}

	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);
	}
	}
	}