It is important that we test our custom SQL against specific vendor DBs
So you should create a suite of test for each vendor DB
@RunWith(Suite.class)
@Suite.SuiteClasses({})
public class SQLServerTestSuite {
@BeforeClass
public static void setUpClass() throws Exception {
}
@AfterClass
public static void tearDownClass() throws Exception {
// clean database
}
}
Should a single database be dedicated to all running tests, but with no commits ?
It is recommended you perform SQL commands but with rollback command because if using commit you can change the state of the current test as well as change the state of others tests. Otherwise, you can deal with unexpected behavior.
For each developer, it is recommended a database sandbox. This allows each user to modify the database in any way they see fit and to exercise the application with tests without worrying about any interactions between their tests and the tests of other users
How do people deal with setup and teardown ?
public class PersonTest {
@Before
public void setUp() {
// set up state to run the test
}
@After
public void teardown() {
// Transaction rollback
}
}
The nice thing about Transaction rollback pattern is that it leaves the database in exactly the same state as when we started the test regardless of what changes we made to the database contents
Is DBUnit still in popular use ?
DBUnit is used to compare the database tables against an XML file that contains
the expected values. You should keep in mind DBUnit deal with hand-coded settings. However, although this approach is more thorough it is extremely tedious to develop and maintain these kinds of tests. In addition, the tests do not detect a missing mapping for a newly added field or property.
How could Spring help ?
Spring has built-in support to Transaction rollback pattern and supports named parameter (SqlParameterSource) which allows you to externalize plain jdbc queries in a multiline and readable xml file for each vendor database, for instance
<?xml version="1.0" encoding="UTF-8"?>
<queries>
<query name="PERSON_BY_ID">
<![CDATA[
select
*
from
PERSON
where
PERSON.ID = :integerId
]]>
</query>
</queries>
Notice the query parameter. It follows JavaScript code style where you declare the kind of parameter as prefix. Now you can create a class where you define the set of named queries
public class SQLServerQuery {
public static final String PERSON_BY_ID = "PERSON_BY_ID";
}
For dinamic queries, it is usually important to detect bugs in the logic of the query such as using < instead of <=. To catch these kinds of bugs, we need to write tests that populate the database with test data, execute the query, and verify that it returns the expected objects. Unfortunately, these kinds of tests are time consuming to both write and execute.