package com.vblinov.tester;
import org.dbunit.Assertion;
import org.dbunit.IDatabaseTester;
import org.dbunit.PropertiesBasedJdbcDatabaseTester;
import org.dbunit.assertion.DiffCollectingFailureHandler;
import org.dbunit.assertion.Difference;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.SortedTable;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.ext.mysql.MySqlDataTypeFactory;
import org.junit.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
/**
* Test class for SQL queries
*/
public class QueriesTest {
private static final Logger log = LoggerFactory.getLogger(QueriesTest.class);
private static IDataSet expectedData;
private static IDatabaseConnection connection;
/**
* Load expected result data sets from dataSet.xml
*
* @throws Exception in case of i/o of file format errors
*/
@BeforeClass
public static void initDataSet() throws Exception {
if (null == expectedData) {
expectedData = new FlatXmlDataSet(QueriesTest.class.getResourceAsStream(
"/dataSet.xml"));
}
}
/**
* Init connection to MySQL server
*
* @throws Exception in case of SQL exceptions
*/
@BeforeClass
public static void initConnection() throws Exception {
if (null == connection) {
IDatabaseTester databaseTester = new PropertiesBasedJdbcDatabaseTester();
connection = databaseTester.getConnection();
DatabaseConfig dbConfig = connection.getConfig();
dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
}
}
/**
* Release connection
*
* @throws Exception in case of SQL Exception
*/
@AfterClass
public static void tearDown() throws Exception {
connection.close();
}
@Test
public void testQuery1() throws Exception {
processQuery(1, false);
}
@Test
public void testQuery2() throws Exception {
processQuery(2, false);
}
@Test
public void testQuery3() throws Exception {
processQuery(3, false);
}
@Test
public void testQuery4() throws Exception {
processQuery(4, true);
}
@Test
public void testQuery5() throws Exception {
processQuery(5, false);
}
@Test
public void testQuery6() throws Exception {
processQuery(6, false);
}
@Test
public void testQuery7() throws Exception {
processQuery(7, false);
}
@Test
public void testQuery8() throws Exception {
processQuery(8, false);
}
@Test
public void testQuery9() throws Exception {
processQuery(9, false);
}
@Test
public void testQuery10() throws Exception {
processQuery(10, false);
}
@Test
public void testQuery11() throws Exception {
processQuery(11, false);
}
@Test
public void testQuery12() throws Exception {
processQuery(12, true);
}
@Test
public void testQuery13() throws Exception {
processQuery(13, false);
}
@Test
public void testQuery14() throws Exception {
processQuery(14, false);
}
@Test
public void testQuery15() throws Exception {
processQuery(15, true);
}
@Test
public void testQuery16() throws Exception {
processQuery(16, false);
}
@Test
public void testQuery17() throws Exception {
processQuery(17, false);
}
@Test
public void testQuery18() throws Exception {
processQuery(18, false);
}
@Test
public void testQuery19() throws Exception {
processQuery(19, false);
}
@Test
public void testQuery20() throws Exception {
processQuery(20, false);
}
/**
* Processing query result check (with additional logic)
*
* @param queryNumber question number
* @param isSorted should be set to true if query performs sorting operations
* @throws Exception
*/
private void processQuery(int queryNumber, boolean isSorted) throws Exception {
String query = QueryTester.readQueryNio(queryNumber);
ITable actualTable = connection.createQueryTable("q" + queryNumber, query);
ITable expectedTable = expectedData.getTable("q" + queryNumber);
DiffCollectingFailureHandler failureHandler = new DiffCollectingFailureHandler();
if (!isSorted) {
actualTable = new SortedTable(actualTable);
expectedTable = new SortedTable(expectedTable);
}
Assertion.assertEquals(expectedTable, actualTable, failureHandler);
List differenceList = failureHandler.getDiffList();
if (!differenceList.isEmpty()) {
log.info("Query {} FAILED!", queryNumber);
for (Object obj : differenceList) {
Difference difference = (Difference) obj;
log.info("Column={}, expected={}, actual={},", difference.getColumnName(),
difference.getExpectedValue(), difference.getActualValue());
}
Assert.fail();
}
}
}