The contents of a database may be defined using setup methods in the JDBDT facade. The functionality at stake comprises:
the use of data sets to populate a table but also for table row insertions / updates / deletions;
These functionalities are described below, along with a discussion of a few database setup patterns that can be implemented using these operations.
The populate method may be used to populate a database table. Taking a data set for a table as argument, it first clears the table at stake, then inserts the data set into the table. The supplied data set also sets a snapshot for subsequent delta assertions.
Illustration
import static org.jdbdt.JDBDT.*; import org.jdbdt.DB; import org.jdbdt.Table; import org.jdbdt.DataSet; ... DB db = ... Table t = ... // Create a data set for t. DataSet initialData = data(t) ... // or ... builder(t) for instance populate(initialData);
The populateIfChanged method is a variant of populate that executes conditionally, i.e., if the table contents are seen as unchanged, no operation takes place. This only happens if an assertUnchanged assertion previously succeeded, and no intervening subsequent JDBDT setup or assertion methods were called for the table.
Illustration
static Table theTable ; static DataSet initialStata; @BeforeClass public static void globalSetup() { theTable = ... ; initialData = data(theTable) ... } @Before public void perTestSetup() { populateIfChanged(initialData); } @Test public void test1() { theSUT.methodThatShouldNotChangeAnythin(); assertUnchanged(theTable); // populateIfChanged will do nothing if the assertion succeeds } @Test public void test1() { theSUT.methodThatPerformsChanges(); assertXXX(...); // any other assertion method // populateIfChanged will repopulate the table again, // regardless of whether the assertion succeeds or not }
More generally, you may query the changed status of data sources using the changed facade method, and use it to guide database setup if convenient.
Illustration
@Before public void perTestSetup() { if (changed(theTable)) { populate(initialData); // re-populate ... // other necessary setup actions } }
Beyond populate, data sets may be used for table insertions, updates and deletes.
The insert method inserts a given data set onto a table, without deleting any previous contents (unlike populate that clears the table first).
Table t = ... DataSet additionalData = data(t) ... insert(additionalData);
The update and delete method respectively update and delete a data set in the database. They require that key columns are defined for the table at stake. The corresponding key values for each data set element will determine which rows are to be updated / deleted.
DB db = ...; Table t = table("MY_TABLE") .columns( ... ) .key( ... ) .build(db); DataSet ds = ... // Update update(ds); // Delete delete(ds);
Database data may be cleaned up using one of the following methods for a Table instance t:
Note: truncate may be faster than deleteAll, but the associated TRUNCATE TABLE statement may not respect integrity constraints and has variable semantics for different database engines (e.g., see here). Some engines do not support table truncation altogether (for instance SQLite).
Illustration
import static org.jdbdt.JDBDT.*; import org.jdbdt.DB; import org.jdbdt.Table; ... DB db = ...; Table t = table("USERS") .columns("ID", "LOGIN", "NAME", "PASSWORD", "CREATED") .build(db); ... // 1. Clear table using a DELETE statement. deleteAll(t); // 2. Delete all users whose login matches a certain filter String loginFilter = ...; deleteAll(t, "LOGIN LIKE ?", loginFilter); // 3. Clear table using TRUNCATE. truncate(t); // 4. Drop the table entirely. drop(t); // alternatively: drop(db, "USERS")
The execute method lets you execute plain SQL statements. This may useful for database setup, when the statement has an effect for which the JDBDT API provides no equivalent operations.
Illustration
import static org.jdbdt.JDBDT.*; import org.jdbdt.DB; ... DB db = ... ; arg1 = ... ; arg2 = ... ; execute(db, "UPDATE MY_TABLE SET X = ? WHERE Y = ?", arg1, arg2);
Database state may be saved and restored as follows per database handle db:
Note that that an unique one save-point is maintained per database handle, and that there should be exactly one restore call per each save call. These constraints try to ensure portable behavior across database engines.
In relation to save and restore, commit(db) is a shorthand for db.getConnection().commit(). Such a call commits all database changes and discards the JDBDT save-point (or any other save-point set for the database otherwise, e.g., by the SUT itself).
Illustration
import static org.jdbdt.JDBDT.*; import java.sql.Connection; import org.jdbdt.DB; ... // Database handle ... DB db = database(...); // Disable auto-commit db.getConnection().setAutoCommit(false); // Set save-point save(db); // Exercise the SUT, then execute some assertions letTheSUTWork(); assertXXX(); // Restore database state restore(db);
A number of database test patterns can be implemented using JDBDT, as exemplified in the JDBDT tutorial. The code skeleton below (assuming JUnit-based tests) illustrates the implementation of two patterns described in xunitpatterns.com:
Illustration
import java.sql.Connection; import org.junit.BeforeClass; import org.junit.AfterClass; import org.junit.Before; import org.junit.After; import org.junit.Test; import static org.jdbdt.JDBDT.*; import org.jdbdt.DB; import org.jdbdt.DataSet; import org.jdbdt.Table; public class MyTest { static DB myDB; static Table myTable1, myTable2, ... ; @BeforeClass public static void oneTimeSetup() { ... // Setup database handle myDB = database( ... ); // Define tables and corresponding initial data myTable1 = table(...) ... DataSet initialData1 = data(myTable1). ... ; populate(initialData1); // etc for myTable2 ... myTable2 = table(...) ...; ... // Ensure that auto-commit is off myDB.getConnection().setAutoCommit(false); } @AfterClass public void oneTimeTeardown() { // Alternatively use deleteAll ... truncate(myTable1); truncate(myTable2); ... teardown(myDB, true); // free resources and close DB connection } @Before public void setSavePoint() { save(myDB); } @After public void restoreSavePoint() { restore(myDB); } @Test public void test1() { // Specific setup for test ... // Exercise the SUT, perform assertions ... } @Test public void test2() { ... etc ... } ...
Operations using a data set data defined for a table t (t should correspond to data.getSource()):
Clean-up:
Arbitrary SQL code execution:
Save and restore: