JUnit testing SQL queries

Question

I'm working on a project and we are required to create some test cases. I have a SQL database and parsing data there with queries like:

public Contractor create(String name, String address, String email, String phone, String city, int cvr) throws SQLException{
    Contractor contractor = new Contractor(name, address, email, phone, city, cvr);
    String sql = String.format("INSERT INTO person (name, address, email, phone, city, category) VALUES ('%s', '%s', '%s', '%s', '%s', 2)", name, address, email, phone, city);
    try{
        Connection conn = DBConnection.getInstance().getDBcon();
        conn.createStatement().executeUpdate(sql);

        String sql2 = "SELECT TOP 1 id FROM Person ORDER BY id DESC";
        ResultSet rs = conn.createStatement().executeQuery(sql2);

        if(rs.next()) {
            String sql3 = "INSERT INTO contractor (cvr, person_id) VALUES (2666,"+rs.getInt("id")+")";
            conn.createStatement().executeUpdate(sql3);
        }else{
            throw new SQLException();
        }

    } catch (SQLException e){
        e.printStackTrace();
    } finally {
        DBConnection.closeConnection();
    }
    return contractor;
}    

How would the test with JUnits look like?

Answer 1

Well, the way Junit must be used is by testing each public method of your class, with as many different parametrizations as the number of experiments can success or fail.

Each testing method, then, must:

• Chose a proper set of parameter values.
• (Optionally) Initialize the tested component in a required initial state.
• Invoke the tested method.
• Check that the returned result is equal to the expected result.
• Clean up the tested component not to let any "dirt" from the executed test.

In your case, there is a difficult checking the results, because the connection is managed locally by your method. Even worse: The data is committed automatically into the connection, leaving dirty records in the database on each test.

To avoid this difficult, it would be enough with a little previous refactoring to ease the test development:

• Overload in your class the method create, with a package-access version which recieves also a Connection, and put there all the business logic.
• The public overload should only manage the connection and call the newly created overload.

Then, you can make your test safely:

• If your class is named MyClass, create a MyClassTest in the same package (typically in a different source path, like src\test\java in a Maven project).
• Create a method createSinglePerson() which invokes create with an arbitrary set of parameters and one connection (not auto-commit). After that, you must check that there is one record more than initially in the Person table, with a certain set of values, and one more record in the Contractor table with a certain set of values. To compare each value you must use Asserts.assertEquals(expected, real). At the end (in a finally clause), do a rollback to the connection and close it.

You can run your test as many times as needed, knowing that it won't alter the database state.

(Note: Parameter cvr is never used. Maybe you did it on purpose, I don't know).

Example

public class MyClassTest
{
    @Test
    public void createSinglePerson()
    {
        MyClass myClass=new MyClass();
        try(Connection connection=...)
        {
            try(Statement stCheck=connection.createStatement())
            {
                connection.setAutoCommit(false);

                // Initial cleanup:
                stCheck.executeUpdate("DELETE FROM person");
                stCheck.executeUpdate("DELETE FROM contractor");

                // Setting input parameters:
                String name="a";
                String address="b";
                String email="[email protected]";
                String phone="001";
                String city="f";
                int cvr=11;

                // Do the call:
                Contractor contractor=myClass.create(name, address, email, phone, city, cvr);

                // Javabean Checks: Check the javabean contains the expected values:
                assertEquals(name, contractor.getName());
                assertEquals(address, contractor.getAddress());
                ...

                // Database Checks:
                int personId;
                // Check the Person table contains one row with the expected values:
                try(ResultSet rs=stCheck.executeQuery("SELECT * FROM person"))
                {
                    assertTrue(rs.next());
                    personId=rs.getInt("id");
                    asssertEquals(name, rs.getString("name"));
                    asssertEquals(address, rs.getString("address"));
                    ...
                    assertFalse(rs.next());
                }

                // Check the Contractor table contains one row with the expected values:
                try(ResultSet rs=stCheck.executeQuery("SELECT * FROM contractor WHERE person_id="+personId))
                {
                    assertTrue(rs.next());
                    asssertEquals(2666, rs.getInt("cvr"));
                    ...
                    assertFalse(rs.next());
                }
            }
            finally
            {
                 // Undo the testing operations:
                 connection.rollback();
            }
        }
        catch (SQLException e)
        {
            fail(e.toString());
        }
    }
}