Maintain DB state during integration tests, using Oracle

Integration testing is similar to Unit testing but it uses real components that interact with each other instead of mocks. This usually involves deploying whole solution and invoking remotely methods and comparing results with expected values.


Tests should be reliable, and should produce same results in subsequent runs if nothing changes in the outside enviroment. Because integration tests use real objects, they often instert/update/delete data from database. By doing so, they change the outside enviroment and can produce different results in next runs.
To avoid such errors, test should leave the database in the same state it was before the test.

Oracle has a nice feature called Flashback. This feature is not available under Oracle XE enviroments, so you need to use full oracle installation. To enable flashback on your oracle installation:

connect as sysdba and

 SQL> alter system set db_recovery_file_dest='<path to recovery folder that exists>' SCOPE=spfile; (normally this is already set when using oracle11g R2) 
 SQL> alter system set db_recovery_file_dest_size=10G SCOPE=spfile; (normally this is already set when using oracle11gr2, but you can adjust the recovery area size) 
 SQL> shutdown immediate; 
 SQL> startup mount; 
 SQL> ALTER DATABASE ARCHIVELOG; 
 SQL> alter database flashback on; 
 SQL> alter database open; 
 SQL> alter system set db_flashback_retention_target=2880; 
 SQL> grant execute on dbms_flashback to public; //or to 'username' if just for one user 
 SQL> grant flashback any table to public; //or to 'username' if just for one user 
 SQL> grant select on v_$database to '<username that will be used to run tests>'; 

After setting up the flashback on the oracle db sample test should look similar to this (java JUnit code):

 public class FlashbackTest {  
 protected static Connection connection = null;  
 protected static List<String> tableNames = null;  
 protected static PreparedStatement allUserTablesStatement = null;  
 protected static PreparedStatement scnStatement = null;  
 protected static String tableNamesStr = null;  
 protected String scn = null;  
 @BeforeClass  
 public static void beforeClass() throws SQLException, IOException {  
 registerDriver();  
 prepareConnection();  
 getAllUserTableNames(); //(1)  
 convertUserTableNamesToString();//(1)  
 scnStatement = connection.prepareStatement("SELECT current_scn FROM v$database"); //(2)  
 }  
 private static void convertUserTableNamesToString() {  
 StringBuilder tableNameBuilder = new StringBuilder();  
 for (String name : tableNames) {  
 tableNameBuilder.append(name).append(",");  
 }  
 tableNamesStr = tableNameBuilder.toString().substring(0, tableNameBuilder.toString().length() - 1);  
 }  
 private static void getAllUserTableNames() throws SQLException {  
 allUserTablesStatement = connection.prepareStatement("select table_name from user_tables where table_name like 'DX_%'");  
 ResultSet resultSet = allUserTablesStatement.executeQuery();  
 tableNames = new ArrayList<>();  
 while (resultSet.next()) {  
 String tableName = resultSet.getString(1);  
 tableNames.add(tableName);  
 System.out.println("Table name found: " + tableName);  
 }  
 resultSet.close();  
 }  
 private static void prepareConnection() throws IOException {  
 Properties dbProperties = new Properties();  
 dbProperties.load(FlashbackTest.class.getClassLoader().getResourceAsStream("db.properties"));  
 try {  
 connection = DriverManager.getConnection(dbProperties.getProperty("jdbc.connection.string"), dbProperties.getProperty("jdbc.username"),  
 dbProperties.getProperty("jdbc.password"));  
 } catch (SQLException e) {  
 e.printStackTrace();  
 Assert.fail(e.getMessage());  
 }  
 }  
 private static void registerDriver() {  
 try {  
 Class.forName("oracle.jdbc.OracleDriver");  
 } catch (ClassNotFoundException e) {  
 e.printStackTrace();  
 Assert.fail(e.getMessage());  
 }  
 }  
 @Before  
 public void setUp() throws SQLException { //(3)  
 ResultSet scnSet = scnStatement.executeQuery();   
 scnSet.next();  
 scn = scnSet.getString(1);  
 scnSet.close();  
 }  
 @Test  
 public void testSomething() { //(4)  
 //testing logic goes here, some in serts, updates, deletes etc  
 }  
 @After  
 public void tearDown() throws SQLException { //(5)  
 StringBuilder statement = new StringBuilder("flashback table ");  
 statement.append(tableNamesStr);  
 statement.append(" TO SCN ").append(scn);  
 PreparedStatement prepareStatement = connection.prepareStatement(statement.toString());  
 prepareStatement.execute();  
 prepareStatement.close();  
 }  
 @AfterClass  
 public static void afterClass() throws SQLException {  
 allUserTablesStatement.close();  
 scnStatement.close();  
 connection.close();  
 }  
 }  

Most important things in this example are:

1. get the table names that we will flashback after test - we need this only once per test class, so we get this data in the @BeforeClass method
2. we prepare sql statement that will be used to get the current SCN from the database. SCN is kind of a "checkpoint" in the database state. We will use the SCN number after the test to return to this checkpoint and by doing so, revert all changes done by the test.
3. Before each test we get the current SCN by executing scn statement.
4. we run the test - here we can safely do any inserts, updates etc
5. after each test we create a flashback statements with all table names and current scn, and we execute it. When this method finishes db is in the same state like it was when we executed (3)

Important: this feature was not tested with such things like creating/altering/dropping tables and most probably will not work well. If you wish to do complex schema changes in your test please take time and reat about flashbacking whole database instead of selected tables.