@Test
@XmlDataSet(location = {"/org/dbunit/ext/annotation/xmlDataSet.xml", "xmlDataSet2.xml"})
public void testWhenMultipleXmlDataSetsInserted(IDatabaseConnection connection) throws Exception {
ITable table = connection.createTable("fellowship");
Assert.assertEquals(5, table.getRowCount());
}
private void testExecute(IDataSet dataSet) throws Exception {
ITable[] tablesBefore = DataSetUtils.getTables(_connection.createDataSet());
// InsertIdentityOperation.CLEAN_INSERT.execute(_connection, dataSet);
InsertIdentityOperation.INSERT.execute(_connection, dataSet);
ITable[] tablesAfter = DataSetUtils.getTables(_connection.createDataSet());
assertEquals("table count", tablesBefore.length, tablesAfter.length);
// Verify tables after
for (int i = 0; i < tablesAfter.length; i++) {
ITable tableBefore = tablesBefore[i];
ITable tableAfter = tablesAfter[i];
String name = tableAfter.getTableMetaData().getTableName();
if (name.startsWith("IDENTITY")) {
assertEquals("row count before: " + name, 0, tableBefore.getRowCount());
if (dataSet instanceof ForwardOnlyDataSet) {
assertTrue(name, tableAfter.getRowCount() > 0);
} else {
Assertion.assertEquals(dataSet.getTable(name), tableAfter);
}
} else {
// Other tables should have not been affected
Assertion.assertEquals(tableBefore, tableAfter);
}
}
}
/**
* For the specified expected and actual tables (and excluding and including the specified
* columns), verify the actual data is as expected.
*
* @param expectedTable The expected table to compare the actual table to.
* @param actualTable The actual table to compare to the expected table.
* @param excludeColumns The column names to exclude from comparison. See {@link
* org.dbunit.dataset.filter.DefaultColumnFilter#excludeColumn(String)} .
* @param includeColumns The column names to only include in comparison. See {@link
* org.dbunit.dataset.filter.DefaultColumnFilter#includeColumn(String)} .
* @throws DatabaseUnitException
*/
public void verifyData(
ITable expectedTable, ITable actualTable, String[] excludeColumns, String[] includeColumns)
throws DatabaseUnitException {
final String method = "verifyData: ";
// Filter out the columns from the expected and actual results
LOG.debug(method + "Applying filters to expected table");
ITable expectedFilteredTable =
applyColumnFilters(expectedTable, excludeColumns, includeColumns);
LOG.debug(method + "Applying filters to actual table");
ITable actualFilteredTable = applyColumnFilters(actualTable, excludeColumns, includeColumns);
LOG.debug(method + "Sorting expected table");
SortedTable expectedSortedTable = new SortedTable(expectedFilteredTable);
LOG.debug(method + "Sorted expected table={}", expectedSortedTable);
LOG.debug(method + "Sorting actual table");
SortedTable actualSortedTable =
new SortedTable(actualFilteredTable, expectedFilteredTable.getTableMetaData());
LOG.debug(method + "Sorted actual table={}", actualSortedTable);
LOG.debug(method + "Comparing expected table to actual table");
Column[] additionalColumnInfo = expectedTable.getTableMetaData().getColumns();
Assertion.assertEquals(expectedSortedTable, actualSortedTable, additionalColumnInfo);
}
@Override
protected void verifyRows(AmbitRows<Object> rows) throws Exception {
IDatabaseConnection c = getConnection();
Assert.assertNotNull(rows);
Assert.assertEquals(1, rows.size());
while (rows.next()) {
ITable table =
c.createQueryTable(
"EXPECTED",
"select name,idreference,idproperty,idstructure,ifnull(text,value) as value_string,value_num,title,url,-1,id,units,comments from property_values \n"
+ "left join property_string using(idvalue_string) \n"
+ "join properties using(idproperty) join catalog_references using(idreference) \n"
+ "where idstructure=100215 and name='Property 1' "
/*
"select name,idreference,idproperty,idstructure,value_string,value_num,idtype from properties join\n"+
"(\n"+
"select idstructure,idproperty,null as value_string,value as value_num,1 as idtype from values_number where idstructure=100215\n"+
"union\n"+
"select idstructure,idproperty,value as value_string,null,0 as idtype from values_string where idstructure=100215\n"+
") as L using (idproperty)\nwhere name='Property 1'"
*/
);
Assert.assertEquals(1, table.getRowCount());
for (int i = 1; i <= rows.getMetaData().getColumnCount(); i++) {
Object expected = table.getValue(0, rows.getMetaData().getColumnName(i));
Object actual = rows.getObject(i);
if ((expected == null) && (actual == null)) continue;
else Assert.assertEquals(expected.toString(), actual.toString());
}
}
}
@Test
@XmlDataSet(location = "xmlDataSet.xml")
public void testWhenXmlDataSetInsertedWithRelativeClassPath(IDatabaseConnection connection)
throws Exception {
ITable table = connection.createTable("fellowship");
Assert.assertEquals(3, table.getRowCount());
}
@Test
@XmlDataSet(location = "/org/dbunit/ext/annotation/xmlDataSet.xml")
public void testWhenXmlDataSetInsertedWithAbsoluteClassPath(IDatabaseConnection connection)
throws Exception {
ITable table = connection.createTable("fellowship");
Assert.assertEquals(3, table.getRowCount());
}
@Test
public void testFindAll() throws Exception {
// get students table and counting rows
// must be 4
IDataSet databaseDataSet = getConnection().createDataSet();
ITable actualTable = databaseDataSet.getTable("STUDENT");
Assert.assertEquals(actualTable.getRowCount(), 4);
}
/** Creates a new XlsDataSet object that loads the specified Excel document. */
public XlsDataSet(InputStream in) throws IOException, DataSetException {
_tables = super.createTableNameMap();
HSSFWorkbook workbook = new HSSFWorkbook(in);
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
ITable table = new XlsTable(workbook.getSheetName(i), workbook.getSheetAt(i));
_tables.add(table.getTableMetaData().getTableName(), table);
}
}
@Test
@XmlDataSet(location = "xmlDataSet.xml")
public void testWhenXmlDataSetGivenAsParameter(
@XmlDataSet(location = "xmlDataSet.xml") IDataSet expectedDataSet,
IDatabaseConnection connection)
throws Exception {
ITable table = connection.createTable("fellowship");
Assert.assertEquals(3, table.getRowCount());
Assertion.assertEquals(expectedDataSet.getTable("fellowship"), table);
}
@Test
public void testRemove() throws Exception {
// looking for student and delete
Student student = studentDaoImpl.findById(1L);
studentDaoImpl.remove(student);
// get students table and counting rows
// must be 4-1=3
IDataSet databaseDataSet = getConnection().createDataSet();
ITable actualTable = databaseDataSet.getTable("STUDENT");
Assert.assertEquals(actualTable.getRowCount(), 3);
}
@Test
public void testByUserId() throws Exception {
// get students table
IDataSet databaseDataSet = getConnection().createDataSet();
ITable actualTable = databaseDataSet.getTable("STUDENT");
// tacking student id with user id = 3
long studentId = (Long) actualTable.getValue(2, "id");
// get student by tested method and check result
while (studentDaoImpl.findByUserId(3) == null) {}
Student student = studentDaoImpl.findByUserId(3);
Assert.assertEquals(studentId, student.getId());
}
@Test
public void testUpdate() throws Exception {
Student student = studentDaoImpl.findById(2L);
Boolean actual = !student.isActive();
student.setActive(!student.isActive());
// upload user to DB
studentDaoImpl.update(student);
// getting from DB
IDataSet databaseDataSet = getConnection().createDataSet();
ITable actualTable = databaseDataSet.getTable("STUDENT");
Boolean real = (Boolean) actualTable.getValue(1, "isActive");
Assert.assertEquals(real, actual);
}
/**
* Gets the primary key column names for the given DbUnit table.
*
* @param dbUnitTable The DbUnit table, not null
* @return The pk column names, empty if none found
*/
protected List<String> getPrimaryKeyColumnNames(ITable dbUnitTable) throws DataSetException {
List<String> result = new ArrayList<String>();
for (org.dbunit.dataset.Column column : dbUnitTable.getTableMetaData().getPrimaryKeys()) {
result.add(column.getColumnName());
}
return result;
}
/**
* Adds the tables of the DbUnit dataset to the given schema.
*
* @param dbUnitDataSet The DbUnit dataset containing the tables, not null
* @param schema The schema to add the tables to, not null
*/
protected void addTables(IDataSet dbUnitDataSet, Schema schema) throws DataSetException {
ITableIterator dbUnitTableIterator = dbUnitDataSet.iterator();
while (dbUnitTableIterator.next()) {
ITable dbUnitTable = dbUnitTableIterator.getTable();
String tableName = dbUnitTable.getTableMetaData().getTableName();
List<String> primaryKeyColumnNames = getPrimaryKeyColumnNames(dbUnitTable);
Table table = schema.getTable(tableName);
if (table == null) {
table = new Table(tableName);
schema.addTable(table);
}
addRows(dbUnitTable, table, primaryKeyColumnNames);
}
}
private static List<Map<String, Object>> createProps(
Class testClass, String file, String tableName) throws IOException, DataSetException {
List<Map<String, Object>> propsList = new ArrayList<Map<String, Object>>();
IDataSet expected = new XlsDataSet(testClass.getResourceAsStream(file));
ITable table = expected.getTable(tableName);
Column[] columns = table.getTableMetaData().getColumns();
for (int i = 0; i < table.getRowCount(); i++) {
Map<String, Object> props = new HashMap<String, Object>();
for (Column c : columns) {
Object value = table.getValue(i, c.getColumnName());
String propName = underlineToCamel(c.getColumnName());
props.put(propName, value);
}
propsList.add(props);
}
return propsList;
}
/**
* Extract column names from {@link #resultTable}.
*
* @throws DataSetException
* @see ITable#getTableMetaData()
* @see ITableMetaData#getColumns()
*/
private String[] extractColumnNames() throws DataSetException {
final Column[] columns = resultTable.getTableMetaData().getColumns();
final String[] columnNames = new String[columns.length]; // NOPMD
for (int i = 0; i < columns.length; i++) {
columnNames[i] = columns[i].getColumnName(); // NOPMD
}
return columnNames;
} /* extractColumnNames */
/** {@inheritDoc} */
public Object getValue(int row, String column) throws DataSetException {
Object theValue = wrapped.getValue(row, column);
// only strings can be processed
if (theValue instanceof String) {
String script = (String) theValue;
for (Entry<String, ScriptableDataSetConfig> oneEntry : handlers.entrySet()) {
String prefix = oneEntry.getKey();
// found engine for prefix
if (script.startsWith(prefix)) {
ScriptEngine engine = engines.get(oneEntry.getKey());
script = script.substring(prefix.length());
List<Class<? extends ScriptInvocationHandler>> handlerClasses =
getHandlerClasses(oneEntry.getValue());
List<ScriptInvocationHandler> handlers =
new ArrayList<ScriptInvocationHandler>(handlerClasses.size());
try {
// instantiate the handlers and call preInvoke
for (Class<? extends ScriptInvocationHandler> handlerClass : handlerClasses) {
ScriptInvocationHandler handler = handlerClass.newInstance();
handler.setScriptEngine(engine);
handlers.add(handler);
script = handler.preInvoke(script);
}
logger.debug("Executing script: {}", script);
// the actual script evaluation
theValue = engine.eval(script);
// call postInvoke in reversed order
Collections.reverse(handlers);
for (ScriptInvocationHandler handler : handlers) {
theValue = handler.postInvoke(theValue);
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
return theValue;
}
/**
* Check that the parameters for {@link #assertNext(String, Object[])} are legal and throw an
* exception if not
*
* @param updatedCurrentRow the number of the row that should have been chekced
* @param expectedValues
* @throws IllegalArgumentException
*/
private void checkAssertNextParams(final int updatedCurrentRow, final Object[] expectedValues)
throws IllegalArgumentException, AssertionFailedError {
Assert.assertTrue(
"There is no next row, the row count is " + resultTable.getRowCount(),
updatedCurrentRow < resultTable.getRowCount());
if (expectedValues == null) {
throw new IllegalArgumentException("Object[] expectedValues may not be null");
}
if (columnNames.length != expectedValues.length) {
throw new IllegalArgumentException(
"columnNames.length ("
+ columnNames.length
+ ") shall be same as expectedValues.length ("
+ expectedValues.length
+ ")");
}
}
/**
* Print the actual results of the query, for troubleshooting. (It might be little slow.)
*
* @param sqlChecker
* @throws DataSetException
*/
public void printSqlResults() throws DataSetException {
ITable resultTable = this.getResultTable();
List<String> columns = new ArrayList<String>();
for (Column column : resultTable.getTableMetaData().getColumns()) {
columns.add(column.getColumnName());
}
StringBuilder tableCsv = new StringBuilder();
tableCsv.append(columns).append('\n');
for (int rowIdx = 0; rowIdx < resultTable.getRowCount(); rowIdx++) {
List<Object> values = new ArrayList<Object>(columns.size());
for (String column : columns) {
values.add(resultTable.getValue(rowIdx, column));
}
tableCsv.append(values).append('\n');
}
System.out.format("Select results:\n%s", tableCsv);
}
/** Assert that the number of rows in this.{@link #resultTable} is as expected. */
public RowComparator assertRowCount(final int expected) throws AssertionFailedError {
Assert.assertEquals(
errorMessage.getTextForPrepend()
+ "There shall be "
+ expected
+ " rows in total. The SQL or test data is likely incorrect.",
expected,
resultTable.getRowCount());
errorMessage.assertDone();
return this;
} /* assertRowCount */
public void testSetCustomIdentityColumnFilter() throws Exception {
_connection
.getConfig()
.setProperty(
InsertIdentityOperation.PROPERTY_IDENTITY_COLUMN_FILTER,
InsertIdentityOperation.IDENTITY_FILTER_EXTENDED);
try {
IDataSet dataSet = _connection.createDataSet();
ITable table = dataSet.getTable("IDENTITY_TABLE");
InsertIdentityOperation op = new InsertIdentityOperation(DatabaseOperation.INSERT);
boolean hasIdentityColumn = op.hasIdentityColumn(table.getTableMetaData(), _connection);
assertTrue("Identity column not recognized", hasIdentityColumn);
} finally {
// Reset property
_connection
.getConfig()
.setProperty(InsertIdentityOperation.PROPERTY_IDENTITY_COLUMN_FILTER, null);
}
}
/**
* Adds the rows of the DbUnit table to the given table.
*
* @param dbUnitTable The DbUnit table containing the rows, not null
* @param table The table to add the rows to, not null
* @param primaryKeyColumnNames The names of the pk columns, empty if there are none
*/
protected void addRows(ITable dbUnitTable, Table table, List<String> primaryKeyColumnNames)
throws DataSetException {
org.dbunit.dataset.Column[] columns = dbUnitTable.getTableMetaData().getColumns();
int rowCount = dbUnitTable.getRowCount();
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
Row row = new Row();
table.addRow(row);
for (org.dbunit.dataset.Column dbUnitColumn : columns) {
String columnName = dbUnitColumn.getColumnName();
DataType columnType = dbUnitColumn.getDataType();
Object value = dbUnitTable.getValue(rowIndex, columnName);
Column column = new Column(columnName, columnType, value);
if (primaryKeyColumnNames.contains(columnName)) {
row.addPrimaryKeyColumn(column);
} else {
row.addColumn(column);
}
}
}
}
@Test
public void testGetObject() throws Exception {
setUpDatabase(getTestDatabase());
IDatabaseConnection c = getConnection();
ITable names = c.createQueryTable("EXPECTED_NAMES", "SELECT * FROM properties");
Assert.assertEquals(4, names.getRowCount());
QueryExecutor<RetrieveField> qe = new QueryExecutor<RetrieveField>();
qe.setConnection(c.getConnection());
ResultSet rs = qe.process((RetrieveField) query);
int count = 0;
while (rs.next()) {
count++;
}
Assert.assertTrue(count > 0);
rs.close();
qe.close();
c.close();
}
/* test case was added to validate the bug that tables with Identity columns that are not
one of the primary keys are able to figure out if an IDENTITY_INSERT is needed.
Thanks to Gaetano Di Gregorio for finding the bug.
*/
public void testIdentityInsertNoPK() throws Exception {
Reader in = TestUtils.getFileReader("xml/insertIdentityOperationTestNoPK.xml");
IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
ITable[] tablesBefore = DataSetUtils.getTables(_connection.createDataSet());
InsertIdentityOperation.CLEAN_INSERT.execute(_connection, xmlDataSet);
ITable[] tablesAfter = DataSetUtils.getTables(_connection.createDataSet());
// Verify tables after
for (int i = 0; i < tablesAfter.length; i++) {
ITable tableBefore = tablesBefore[i];
ITable tableAfter = tablesAfter[i];
String name = tableAfter.getTableMetaData().getTableName();
if (name.equals("TEST_IDENTITY_NOT_PK")) {
assertEquals("row count before: " + name, 0, tableBefore.getRowCount());
Assertion.assertEquals(xmlDataSet.getTable(name), tableAfter);
} else {
// Other tables should have not been affected
Assertion.assertEquals(tableBefore, tableAfter);
}
}
}
/**
* Compare the next row (or the 1st one if first use) in this.{@link #resultTable} with the
* provided expected values and throw an assertion failure if they differ.
*
* <p>If the row differs in a column then an AssertionFailedError is thrown including the row
* number, name of the column and the expected and actual values.
*
* <p>The type of the actual and expected values is taken into account when comparing them unless
* expectedValues is actually a String[]. See {@link #assertNext(String, Object[])} for
* type-insensitive comparison.
*
* <h4>Using a ValueChecker</h4>
*
* You have two options for comparing the actual and expected values: either providing the exact
* expected value, which will be tested via .equals() or providing an instance of a {@link
* ValueChecker} to which to delegate the check. See an example in its JavaDoc.
*
* @param msg (optional) A message to add to the AssertionFailedErrors produced
* @param expectedValues (required) the values to be expected in the columns; the values must be
* in the same order as columns in the internal {@link ITable}.
* @see ITable#getTableMetaData()
* @see ITableMetaData#getColumns()
* @see ValueChecker
*/
public RowComparator assertNext(final String msg, final Object[] expectedValues)
throws AssertionFailedError, DataSetException {
try {
final String errorMsg =
((msg == null) ? "" : msg)
+ // NOPMD
errorMessage.getTextDecorated(" [", "] ", "");
checkAssertNextParams(++currentRow, expectedValues);
final boolean stringComparison =
expectedValues
.getClass() // NOPMD
.getComponentType()
.equals(String.class);
// Do the test
for (int i = 0; i < columnNames.length; i++) {
Object actual = resultTable.getValue(currentRow, columnNames[i]);
if (stringComparison && actual != null) {
actual = actual.toString();
}
final Object expected = expectedValues[i];
final String columnUnequalMsg =
errorMsg
+ " (row (starting from 0) "
+ currentRow
+ ", column '"
+ columnNames[i]
+ "')"
+ createTypeDifferenceInfo(actual, expected);
if (expected instanceof ValueChecker) {
checkWithChecker(columnUnequalMsg, (ValueChecker) expected, actual);
} else {
Assert.assertEquals(columnUnequalMsg, expected, actual);
}
}
} finally {
errorMessage.assertDone();
}
return this;
}
/** {@inheritDoc} */
public ITableMetaData getTableMetaData() {
return wrapped.getTableMetaData();
}
/** {@inheritDoc} */
public int getRowCount() {
return wrapped.getRowCount();
}
@Test
public void testWhenEmptyTable(IDatabaseConnection connection) throws Exception {
ITable table = connection.createTable("fellowship");
Assert.assertEquals(0, table.getRowCount());
}
