dbbuild

package ch.stefanrufer.libraries.junitextension.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
/**
 * Demo how to use the {@link AutoRollbackTestCase}.
 * 
 * It specifically shows how all of the insert/update/delete
 * operations in each test method work isolated because a 
 * rollback is executed during test tear down.
 */
public class ExampleRollbackTestCase extends AutoRollbackTestCase {
  /**
   * Set up test table in database.
   * 
   * @param ds the initialized data source
   * @throws SQLException if DB access fails
   */
  @Override
  public void setUp(DataSource ds) throws SQLException {
    Statement stmt = ds.getConnection().createStatement();
    stmt.execute("CREATE TABLE testtable (testcolumn VARCHAR(1))");
    stmt.close();
  }
  /**
   * Try to execute a DB insert and see if the table really contains
   * the row at the end.
   * 
   * @throws SQLException if DB access fails
   */
  public void testDbInsert() throws SQLException {
    Statement stmt = getDataSource().getConnection().createStatement();
    int rowCount = stmt.executeUpdate("DELETE FROM testtable");
    assertEquals("Excpect table to be empty initially", 0, rowCount);
    rowCount = stmt.executeUpdate("INSERT INTO testtable values ('X')");
    assertEquals("Expect insert to affect exactly 1 row", 1, rowCount);
    ResultSet rs = stmt.executeQuery("SELECT * FROM testtable");
    assertTrue("expect 1 row", rs.next());
    assertEquals("X", rs.getString("testcolumn"));
    assertFalse("expect only 1 row", rs.next());
  }
  /**
   * Try to execute a DB update and see if the table really contains
   * the updated values at the end.
   * 
   * @throws SQLException if DB access fails
   */
  public void testDbUpdate() throws SQLException {
    Statement stmt = getDataSource().getConnection().createStatement();
    stmt.executeUpdate("INSERT INTO testtable values ('X')");
    int rowCount = stmt.executeUpdate("UPDATE testtable SET testcolumn='Y' WHERE testcolumn='X'");
    assertEquals("expect exactly 1 update", 1, rowCount);
    ResultSet rs = stmt.executeQuery("SELECT * FROM testtable");
    // works because we are always on the same connection
    assertTrue("expect 1 row", rs.next());
    assertEquals("Y", rs.getString("testcolumn"));
    assertFalse("expect exactly 1 row", rs.next());
  }
  /**
   * Check if deleting everything works as expected.
   * 
   * @throws SQLException if DB access fails
   */
  public void testDelete() throws SQLException {
    Statement stmt = getDataSource().getConnection().createStatement();
    stmt.executeUpdate("INSERT INTO testtable values ('X')");
    stmt.executeUpdate("INSERT INTO testtable values ('Y')");
    int rowCount = stmt.executeUpdate("DELETE FROM testtable");
    assertEquals("Excpect 2 rows to be deleted", 2, rowCount);
    ResultSet rs = stmt.executeQuery("SELECT * FROM testtable");
    assertFalse("expect 0 rows after delete", rs.next());
  }
  /**
   * Check if the test table that we have is initially empty.
   * 
   * @throws SQLException if DB access fails
   */
  public void testInitiallyEmpty() throws SQLException {
    Statement stmt = getDataSource().getConnection().createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM testtable");
    // previous inserts in other unit tests must not affect this
    assertFalse("expect 0 rows", rs.next());
  }
  /**
   * Tear down test table in database.
   * 
   * @param ds the initialized data source
   * @throws SQLException if DB access fails
   */
  @Override
  public void tearDown(DataSource ds) throws SQLException {
    Statement stmt = ds.getConnection().createStatement();
    stmt.execute("DROP TABLE testtable");
    stmt.close();
  }
}