org.dbunit.database.IDatabaseConnection Java Examples

protected IDatabaseConnection getConnection() throws Exception {
    Properties cfg = getHibernateConfiguration();
    String username = cfg.getProperty("hibernate.connection.username");
    String password = cfg.getProperty("hibernate.connection.password");
    String driver = cfg.getProperty("hibernate.connection.driver_class");
    String url = cfg.getProperty("hibernate.connection.url");

    Class.forName(driver);

    Connection jdbcConnection = DriverManager.getConnection(url, username, password);

    IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
    DatabaseConfig config = connection.getConfig();
    // config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new
    // H2DataTypeFactory());
    config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory());

    return connection;
}
 

@Test
public void testVersionQuery() throws Exception {
    String expectVersion = KylinVersion.getCurrentVersion().toString();
    logger.info("---------- verify expect version: " + expectVersion);

    String queryName = "QueryKylinVersion";
    String sql = "SELECT VERSION() AS version";

    // execute Kylin
    logger.info("Query Result from Kylin - " + queryName);
    IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection);
    ITable kylinTable = executeQuery(kylinConn, queryName, sql, false);
    String queriedVersion = String.valueOf(kylinTable.getValue(0, "version"));

    // compare the result
    Assert.assertEquals(expectVersion, queriedVersion);
}
 

private Reader getDtdSchema(boolean resetDtdSchema) throws Exception {
        if (resetDtdSchema || dtdSchema == null) {
            Connection c = getEmireCfg().getConnection();
            try {

                IDatabaseConnection dc = createProgresConnection(c);
                StringWriter sw = new StringWriter();
                FlatDtdDataSet.write(dc.createDataSet(), sw);
                dtdSchema = sw.toString();
//                System.out.println(dtdSchema);
            } finally {
                c.close();
            }
        }
        return new StringReader(dtdSchema);
    }
 

@Override
public CleanupStrategyExecutor<IDatabaseConnection, IDataSet> usedTablesOnlyStrategy() {
    return (final IDatabaseConnection connection, final List<IDataSet> initialDataSets, final String... tablesToExclude) -> {
        if (initialDataSets.isEmpty()) {
            return;
        }

        try {
            IDataSet dataSet = excludeTables(mergeDataSets(initialDataSets), tablesToExclude);
            dataSet = new FilteredDataSet(new DatabaseSequenceFilter(connection), dataSet);
            DatabaseOperation.DELETE_ALL.execute(connection, dataSet);
        } catch (final SQLException | DatabaseUnitException e) {
            throw new DbFeatureException(UNABLE_TO_CLEAN_DATABASE, e);
        }
    };
}
 

protected void run(String queryFolder, String[] exclusiveQuerys, boolean needSort) throws Exception {
    logger.info("---------- test folder: " + queryFolder);
    Set<String> exclusiveSet = buildExclusiveSet(exclusiveQuerys);

    List<File> sqlFiles = getFilesFromFolder(new File(queryFolder), ".sql");
    for (File sqlFile : sqlFiles) {
        String queryName = StringUtils.split(sqlFile.getName(), '.')[0];
        if (exclusiveSet.contains(queryName)) {
            continue;
        }
        String sql = getTextFromFile(sqlFile);

        // execute Kylin
        logger.info("Query Result from Kylin - " + queryName + "  (" + queryFolder + ")");
        IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection);
        ITable kylinTable = executeQuery(kylinConn, queryName, sql, needSort);
        printResult(kylinTable);

    }
}
 

protected void verifyResultContent(String queryFolder) throws Exception {
    logger.info("---------- verify result content in folder: " + queryFolder);

    List<File> sqlFiles = getFilesFromFolder(new File(queryFolder), ".sql");
    for (File sqlFile : sqlFiles) {
        String queryName = StringUtils.split(sqlFile.getName(), '.')[0];
        String sql = getTextFromFile(sqlFile);

        File expectResultFile = new File(sqlFile.getParent(), sqlFile.getName() + ".expected.xml");
        IDataSet expect = new FlatXmlDataSetBuilder().build(expectResultFile);
        // Get expected table named "expect". FIXME Only support default table name
        ITable expectTable = expect.getTable("expect");

        // execute Kylin
        logger.info("Query Result from Kylin - " + queryName + "  (" + queryFolder + ")");
        IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection);
        ITable kylinTable = executeQuery(kylinConn, queryName, sql, false);

        // compare the result
        assertTableEquals(expectTable, kylinTable);
    }
}
 

@Test
public void testCreateConnection() throws DatabaseUnitException {
    // GIVEN
    final String schema = "foo";

    // WHEN
    final IDatabaseConnection dbConnection = FACTORY.createConnection(connection, schema);

    // THEN
    assertThat(dbConnection, notNullValue());

    final Object typeFactory = dbConnection.getConfig().getProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY);
    assertThat(typeFactory, notNullValue());
    assertThat(typeFactory.getClass(), not(equalTo(DefaultDataTypeFactory.class)));

    assertThat(dbConnection.getSchema(), equalTo(schema));
}
 

protected void execAndCompColumnCount(String input, int expectedColumnCount) throws Exception {
    logger.info("---------- test column count: " + input);
    Set<String> sqlSet = ImmutableSet.of(input);

    for (String sql : sqlSet) {
        // execute Kylin
        logger.info("Query Result from Kylin - " + sql);
        IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection);
        ITable kylinTable = executeQuery(kylinConn, sql, sql, false);

        try {
            // compare the result
            Assert.assertEquals(expectedColumnCount, kylinTable.getTableMetaData().getColumns().length);
        } catch (Throwable t) {
            logger.info("execAndCompColumnCount failed on: " + sql);
            throw t;
        }
    }
}
 

@Test
public void testCreateConnection() throws DatabaseUnitException {
    // GIVEN
    final String schema = "foo";

    // WHEN
    final IDatabaseConnection dbConnection = FACTORY.createConnection(connection, schema);

    // THEN
    assertThat(dbConnection, notNullValue());

    final Object typeFactory = dbConnection.getConfig().getProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY);
    assertThat(typeFactory, notNullValue());
    assertThat(typeFactory.getClass(), equalTo(MsSqlDataTypeFactory.class));

    assertThat(dbConnection.getSchema(), equalTo(schema));
}
 

@Test
public void testCreateConnection() throws DatabaseUnitException {
    // GIVEN
    final String schema = "foo";

    // WHEN
    final IDatabaseConnection dbConnection = FACTORY.createConnection(connection, schema);

    // THEN
    assertThat(dbConnection, notNullValue());

    final Object typeFactory = dbConnection.getConfig().getProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY);
    assertThat(typeFactory, notNullValue());
    assertThat(typeFactory.getClass(), equalTo(MckoiDataTypeFactory.class));

    assertThat(dbConnection.getSchema(), equalTo(schema));
}
 

@Test
public void testCreateConnection() throws Exception {
    // GIVEN
    final String schema = "foo";

    // WHEN
    final IDatabaseConnection dbConnection = FACTORY.createConnection(connection, schema);

    // THEN
    assertThat(dbConnection, notNullValue());

    final Object typeFactory = dbConnection.getConfig().getProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY);
    assertThat(typeFactory, notNullValue());
    assertThat(typeFactory.getClass(), equalTo(Db2DataTypeFactory.class));

    final Object metadataHandler = dbConnection.getConfig().getProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER);
    assertThat(metadataHandler, notNullValue());
    assertThat(metadataHandler.getClass(), equalTo(Db2MetadataHandler.class));

    assertThat(dbConnection.getSchema(), equalTo(schema));
}
 

@Test
public void testCreateConnection() throws DatabaseUnitException {
    // GIVEN
    final String schema = "foo";

    // WHEN
    final IDatabaseConnection dbConnection = FACTORY.createConnection(connection, schema);

    // THEN
    assertThat(dbConnection, notNullValue());

    final Object typeFactory = dbConnection.getConfig().getProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY);
    assertThat(typeFactory, notNullValue());
    assertThat(typeFactory.getClass(), equalTo(H2DataTypeFactory.class));

    assertThat(dbConnection.getSchema(), equalTo(schema));
}
 

@Test
public void testUsedRowsOnlyCleanupWithoutInitialDataSets() throws Exception {
    // GIVEN
    final CleanupStrategyProvider provider = new CleanupStrategyProvider();
    final CleanupStrategyExecutor<IDatabaseConnection, IDataSet> strategyExecutor = provider.usedRowsOnlyStrategy();
    assertThat(strategyExecutor, notNullValue());

    // WHEN
    strategyExecutor.execute(connection, Collections.emptyList());

    // THEN
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_1"), equalTo(4));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_2"), equalTo(1));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_3"), equalTo(1));
}
 

@Test
public void testStrictCleanupWithoutInitialDataSetsExcludingOneTable() throws Exception {
    // GIVEN
    final CleanupStrategyProvider provider = new CleanupStrategyProvider();
    final CleanupStrategyExecutor<IDatabaseConnection, IDataSet> strategyExecutor = provider.strictStrategy();
    assertThat(strategyExecutor, notNullValue());

    // WHEN
    strategyExecutor.execute(connection, Collections.emptyList(), "XML_TABLE_2");

    // THEN
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_1"), equalTo(0));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_2"), equalTo(1));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_3"), equalTo(0));
}
 

@Test
public void testUsedRowsOnlyCleanupWithoutInitialDataSetsExcludingOneTable() throws Exception {
    // GIVEN
    final CleanupStrategyProvider provider = new CleanupStrategyProvider();
    final CleanupStrategyExecutor<IDatabaseConnection, IDataSet> strategyExecutor = provider.usedRowsOnlyStrategy();
    assertThat(strategyExecutor, notNullValue());

    // WHEN
    strategyExecutor.execute(connection, Collections.emptyList(), "XML_TABLE_2");

    // THEN
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_1"), equalTo(4));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_2"), equalTo(1));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_3"), equalTo(1));
}
 

@Test
public void testUsedTablesOnlyCleanupWithInitialDataSets() throws Exception {
    // GIVEN
    final CleanupStrategyProvider provider = new CleanupStrategyProvider();
    final CleanupStrategyExecutor<IDatabaseConnection, IDataSet> strategyExecutor = provider.usedTablesOnlyStrategy();
    assertThat(strategyExecutor, notNullValue());

    // WHEN
    strategyExecutor.execute(connection, Arrays.asList(initialDataSet));

    // THEN
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_1"), equalTo(0));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_2"), equalTo(0));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_3"), equalTo(1));
}
 

protected DatabaseOperation getTearDownOperation() throws Exception {
	return new DatabaseOperation() {

		@Override
		public void execute(IDatabaseConnection connection, IDataSet dataSet)
				throws DatabaseUnitException, SQLException {

			DatabaseConfig databaseConfig = connection.getConfig();
			IStatementFactory statementFactory = (IStatementFactory) databaseConfig
					.getProperty(DatabaseConfig.PROPERTY_STATEMENT_FACTORY);
			IBatchStatement statement = statementFactory.createBatchStatement(connection);

			try {
				int count = 0;
				for (SingleCreateTableScriptEntry entry : createdTableList) {
					statement.addBatch("drop table " + entry.getTableName());
					count++;
				}

				if (count > 0) {
					statement.executeBatch();
					statement.clearBatch();
				}
			} finally {
				statement.close();
			}

		}
	};
}
 

/**
 * Deletes all rows of tables present in the specified dataset. If the
 * dataset does not contains a particular table, but that table exists in
 * the database, the database table is not affected. Table are truncated in
 * reverse sequence.
 *
 * @param dataSet
 * @throws Exception
 */
protected void deleteFromDatabase(IDataSet dataSet) throws Exception {
    IDatabaseConnection connection = null;
    try {
        connection = getConnection();
        DatabaseOperation.DELETE.execute(connection, dataSet);
    } finally {
        closeConnection(connection);
    }
}
 

protected void execAndCompResultSize(String queryFolder, String[] exclusiveQuerys, boolean needSort)
        throws Exception {
    logger.info("---------- test folder: " + queryFolder);
    Set<String> exclusiveSet = buildExclusiveSet(exclusiveQuerys);

    List<File> sqlFiles = getFilesFromFolder(new File(queryFolder), ".sql");
    for (File sqlFile : sqlFiles) {
        String queryName = StringUtils.split(sqlFile.getName(), '.')[0];
        if (exclusiveSet.contains(queryName)) {
            continue;
        }
        String sql = getTextFromFile(sqlFile);

        // execute Kylin
        logger.info("Query Result from Kylin - " + queryName + "  (" + queryFolder + ")");
        IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection);
        ITable kylinTable = executeQuery(kylinConn, queryName, sql, needSort);

        // execute H2
        logger.info("Query Result from H2 - " + queryName);
        ITable h2Table = executeQuery(newH2Connection(), queryName, sql, needSort);

        try {
            // compare the result
            Assert.assertEquals(h2Table.getRowCount(), kylinTable.getRowCount());
        } catch (Throwable t) {
            logger.info("execAndCompResultSize failed on: " + sqlFile.getAbsolutePath());
            throw t;
        }

        compQueryCount++;
        if (kylinTable.getRowCount() == 0) {
            zeroResultQueries.add(sql);
        }
    }
}
 

protected DatabaseOperation getSetUpOperation() throws Exception {
	parseCreateTableScriptFile();

	initSpringContext();

	return new CompositeOperation(new DatabaseOperation[] { new DatabaseOperation() {

		@Override
		public void execute(IDatabaseConnection connection, IDataSet dataSet)
				throws DatabaseUnitException, SQLException {

			DatabaseConfig databaseConfig = connection.getConfig();
			IStatementFactory statementFactory = (IStatementFactory) databaseConfig
					.getProperty(DatabaseConfig.PROPERTY_STATEMENT_FACTORY);
			IBatchStatement statement = statementFactory.createBatchStatement(connection);
			try {
				int count = 0;
				for (SingleCreateTableScriptEntry entry : createdTableList) {
					statement.addBatch(entry.getCreateTableScript());
					count++;
				}

				if (count > 0) {
					statement.executeBatch();
					statement.clearBatch();
				}
			} finally {
				statement.close();
			}
		}
	}, DatabaseOperation.CLEAN_INSERT });
}
 

@Test
public void testAfterTest() throws Throwable {
    // GIVEN
    when(invocation.getException()).thenReturn(Optional.empty());

    // WHEN
    decorator.afterTest(invocation);

    // THEN
    verify(executor).executeAfterTest(eq(connection), eq(Boolean.FALSE));
    verify(ctx, times(0)).storeData(eq(Constants.KEY_CONNECTION), any(IDatabaseConnection.class));
    verifyZeroInteractions(connection);
}
 

protected void execAndCompDynamicQuery(String queryFolder, String[] exclusiveQuerys, boolean needSort) throws Exception {
    printInfo("---------- test folder: " + queryFolder);
    Set<String> exclusiveSet = buildExclusiveSet(exclusiveQuerys);

    List<File> sqlFiles = getFilesFromFolder(new File(queryFolder), ".sql");
    for (File sqlFile : sqlFiles) {
        String queryName = StringUtils.split(sqlFile.getName(), '.')[0];
        if (exclusiveSet.contains(queryName)) {
            continue;
        }
        String sql = getTextFromFile(sqlFile);
        List<String> parameters = getParameterFromFile(sqlFile);

        // execute Kylin
        printInfo("Query Result from Kylin - " + queryName + "  (" + queryFolder + ")");
        IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection);
        ITable kylinTable = executeDynamicQuery(kylinConn, queryName, sql, parameters, needSort);

        // execute H2
        printInfo("Query Result from H2 - " + queryName);
        IDatabaseConnection h2Conn = new DatabaseConnection(h2Connection);
        h2Conn.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new TestH2DataTypeFactory());
        ITable h2Table = executeDynamicQuery(h2Conn, queryName, sql, parameters, needSort);

        // compare the result
        Assertion.assertEquals(h2Table, kylinTable);
    }
}
 

@Test
public void testUsedRowsOnlyCleanupWithInitialDataSets() throws Exception {
    // GIVEN
    final CleanupStrategyProvider provider = new CleanupStrategyProvider();
    final CleanupStrategyExecutor<IDatabaseConnection, IDataSet> strategyExecutor = provider.usedRowsOnlyStrategy();
    assertThat(strategyExecutor, notNullValue());

    // WHEN
    strategyExecutor.execute(connection, Arrays.asList(initialDataSet));

    // THEN
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_1"), equalTo(1));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_2"), equalTo(0));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_3"), equalTo(1));
}
 

@Test(expected = DbFeatureException.class)
public void testStrictCleanupOnClosedConnection() throws Exception {
    // GIVEN
    final CleanupStrategyProvider provider = new CleanupStrategyProvider();
    final CleanupStrategyExecutor<IDatabaseConnection, IDataSet> strategyExecutor = provider.strictStrategy();
    assertThat(strategyExecutor, notNullValue());
    connection.close();

    // WHEN
    strategyExecutor.execute(connection, Arrays.asList());
}
 

public static void main(String[] args) throws Exception {
    DataSource dataSource = new DataSourceFactoryBean().getDataSource();
    File file = new File("src/main/resources/mycollab.dtd");
    IDatabaseConnection connection = new DatabaseDataSourceConnection(dataSource);
    connection.getConfig().setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
    // write DTD file
    FlatDtdDataSet.write(connection.createDataSet(), new FileOutputStream(file));
}
 

@Test
public void testStrictCleanupWithoutInitialDataSets() throws Exception {
    // GIVEN
    final CleanupStrategyProvider provider = new CleanupStrategyProvider();
    final CleanupStrategyExecutor<IDatabaseConnection, IDataSet> strategyExecutor = provider.strictStrategy();
    assertThat(strategyExecutor, notNullValue());

    // WHEN
    strategyExecutor.execute(connection, Collections.emptyList());

    // THEN
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_1"), equalTo(0));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_2"), equalTo(0));
    assertThat(getRecordCountFromTable(connection, "XML_TABLE_3"), equalTo(0));
}
 

/**
 * 从数据库中读取数据写入到文件中，支持excel和xml格式，以文件后缀名区分
 * @param databaseConnection  使用的IDatabaseConnection数据连接
 * @param destFilePath 写入文件的路径
 */
public static void writeToFileFromDataBase(IDatabaseConnection databaseConnection,String destFilePath){
	IDataSet dataSet=null;
	
	try {
		dataSet=databaseConnection.createDataSet();
	} catch (Exception e) {
		log.error("未获取到测试集");
		log.error(e.getMessage());
		return;
	}
	writeToFileFromDataSet(dataSet,destFilePath);
}
 

protected void compare(String queryFolder, String[] exclusiveQuerys, boolean needSort) throws Exception {
    logger.info("---------- test folder: " + queryFolder);
    Set<String> exclusiveSet = buildExclusiveSet(exclusiveQuerys);

    List<File> sqlFiles = getFilesFromFolder(new File(queryFolder), ".sql");
    for (File sqlFile : sqlFiles) {
        String queryName = StringUtils.split(sqlFile.getName(), '.')[0];
        if (exclusiveSet.contains(queryName)) {
            continue;
        }
        String sql = getTextFromFile(sqlFile);

        // execute Kylin
        logger.info("Query Result from Kylin - " + queryName + "  (" + queryFolder + ")");
        IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection);
        ITable kylinTable = executeQuery(kylinConn, queryName, sql, needSort);

        // execute H2
        sql = sql.replace("massin(test_kylin_fact.SELLER_ID,'vip_customers')", "test_kylin_fact.SELLER_ID in ( " + org.apache.commons.lang.StringUtils.join(vipSellers, ",") + ")");
        logger.info("Query Result from H2 - " + queryName);
        logger.info("Query for H2 - " + sql);
        ITable h2Table = executeQuery(newH2Connection(), queryName, sql, needSort);

        try {
            // compare the result
            assertTableEquals(h2Table, kylinTable);
        } catch (Throwable t) {
            logger.info("execAndCompQuery failed on: " + sqlFile.getAbsolutePath());
            throw t;
        }
    }
}
 

@Override
public IDatabaseConnection createConnection(final Connection connection, final String schema) throws DatabaseUnitException {
    final IDatabaseConnection dbUnitConnection = new DatabaseConnection(connection, schema);

    dbUnitConnection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());

    return dbUnitConnection;
}
 

private IDatabaseConnection createProgresConnection(Connection c) throws DatabaseUnitException {
    DatabaseConnection dbc = new DatabaseConnection(c);
    DatabaseConfig config = dbc.getConfig();
    config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory());
    // Progress cannot handle columns names like XML thus we have to escape them.
    config.setProperty(DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "\"?\"");
    config.setProperty(DatabaseConfig.FEATURE_CASE_SENSITIVE_TABLE_NAMES, false);
    return dbc;
}