JUnit test for a method that contains SQL queries

Question

I have an old Java project (no frameworks/build tools used) that has a class full of SQL methods and corresponding Bean-classes. The SQL methods mostly use SELECT, INSERT and UPDATE queries like this:

public static void sqlUpdateAge(Connection dbConnection, int age, int id) {

    PreparedStatement s = null;
    ResultSet r = null;

    String sql = "UPDATE person SET age = ? WHERE id = ?";

    try {
        s = dbConnection.prepareStatement(sql);
        s.setInt(1, age);
        s.setInt(2, id);
        s.addBatch();
        s.executeBatch();

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (r != null)
                r.close();
            if (s != null)
                s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

What is the best practice in unit testing when it comes to SQL queries?

The easiest way I can think of, would be to use my development database; just call the sqlUpdateAge() in the test class, query the database for a result set and assertTrue that the set age is in the result set. However, this would fill up the development database with unnecessary data, and I would like to avoid that.

Is the solution to create a so-called in-memory database or somehow rollback the changes I made?

If I need an in-memory database:

1. Where and how would I create it? Straight to the test class, or perhaps to a config file?
2. How do I pass it to the updateAge() method?

Answer 1

I would suggest to see if you can start with autmoating the build. That is either by introducing a build tool such as maven or gradle - or if not possible - scipting the build. In any case, your goal should be to get to a point where it's easy for you to trigger a buil together with tests whenever code changes.

If you are not able to produce a consistent build on every change with the guarantee that all unit tests have been run, then there's really no value in writing unit tests in the first place. That is because otherwise, your tests are going to fail eventually due to code modifications and you wouldn't notice unless all your tests are automatically run.

Once you have that, you might have some hints to how you would like to run unit or integration tests.

As you can't benefit from testing support that many application frameworks provide, you're basically left on your own for how to configure database testing setup. In that case, I don't think that an inmemory database is really the best opion, because:

• It's a different database technology than what you are normally using, and as the code indicates you are not using an ORM that will take care of different SQL dialects for you. As that's the case, you might find yourself in a position, where you are unable to accurately test your code because of SQL dialect differeces.
• You will need to do all the setup of the inmemory DB yourself - which is of course possible, but still it's a piece of code that you need to maintain and that can also fail.

The two alternatives I can think of are:

• Use Docker to start your actual database technology for every time you run the tests. (that's also something you have to script for yourself, but it will most likely be a very simple and short command you need to execute)
• have a test database running on your test environment that you use. Every time before you run the tests, esure the database is reset to the original state. (easiest way to do this is to drop the existing schema and restore to the original schema). In this case, you will need to ensure that you don't run multiple builds in parallell against the same test database.

These suggestions apply only if you have experience on the shell and/or have support from someone in ops. If not, setting up H2 might be easier and more straight forward.

Answer 2

Things would have been easy with a Spring Boot project. In your case, you have many strategies:

1. Configure a H2 database. You can initialize your database with the creation of a schema and insertion of data in a setUp method with the @BeforeEach annotation.
2. You can use a dedicated framework like DbUnit.
3. You will have to initialize your dbConnection also in a setUp method in your unit test.