How can I test database access methods in Java? [closed]

Question

I want to write a test for a method that accesses a database such as following.

public class MyClass{
    public String getAddress(Int id){
        String query = "Select * from Address where id="+id;
        //some additional statements           
        resultSet = statement.executeQuery();
        return result.getString(ADDRESS);        
    }
}

How can I test this method? I am using Java.

Answer 1

You would probably be better served to skip the integration testing for most of these methods. Instead, you would want to look into mocks and stubs (Mockito is a well-regarded mocking framework for Java) and verifying that the method behaves in the proper way. Namely, building the expected SQL for the input (on a sidenote, you should be using parameterized queries, not just concatenating strings), passing it to the correct object, and returning the correct value from the mocked up row.

Answer 2

Remember that you aren't testing whether your database works.

If you are using a database from one of the top vendors (or even the second and third tiers) you can guarantee that they have tested that a SQL statement will read or write.

You probably want to test a number of things:

• Did I get my connection properties right?
• Do the columns returned by my SQL statement match my data structure?

etc

Some of these can be addressed by mocking frameworks (as described by another poster). Some of these might be setup as integration tests that run independently of unit tests. Some of these can be tested using an in-memory database.

I've used HSQLDB in the past to create an inmemory database, pre-populate with data and then run my unit tests. In memory databases are orders of magnitude quicker than a traditional database, you don't have to worry about setting up transactions to clean them up etc.