How to test DAO methods using Mockito?

Question

I've started to discovered Mockito library and there is a question for which I didn't find the proper answer.

If I have for example such method in my UserDAO class that saves user in database:

public class UserDAO{
...
 public void create(User user) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet generatedKeys = null;
        try {

            connection = getConnection();
            pstmt = connection.prepareStatement(INSERT_USER,
                    PreparedStatement.RETURN_GENERATED_KEYS);
            int counter = 1;
            pstmt.setString(counter++, user.getFirstName());
            pstmt.setString(counter++, user.getLastName());
            pstmt.setString(counter++, user.getEmail());
            pstmt.setString(counter++, user.getPassword());
            pstmt.setString(counter++, user.getRole());
            pstmt.setString(counter, user.getLang());

            pstmt.execute();
            connection.commit();
            generatedKeys = pstmt.getGeneratedKeys();

            if (generatedKeys.next()) {
                user.setId(generatedKeys.getInt(Fields.GENERATED_KEY));
            }
        } catch (SQLException e) {
            rollback(connection);
            LOG.error("Can not create a user", e);
        } finally {
            close(connection);
            close(pstmt);
            close(generatedKeys);
        }
    }
  ....
}

How should I test it ?

If I want to test for example a DAO class then I need to create a DataSource mock, Connection mock, ResultSet mock etc ? And so not to test the database itself ?

But what if I want to also test the behavior of dao and database ?

Would you please produce some code samples, links that could be helpful and show best approaches of doing it ?

Answer 1

Here is a good start using Mockito to test your UserDAO. This code uses a good amount of the Mockito features, so you can see how to use them. Let me know if you have questions.

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; import org.junit.After; import org.junit.AfterClass; import org.junit.Before; import org.junit.BeforeClass; import org.junit.Test; import static org.junit.Assert.*; import org.junit.runner.RunWith; import static org.mockito.Matchers.anyInt; import static org.mockito.Matchers.anyString; import org.mockito.Mock; import static org.mockito.Mockito.doNothing; import static org.mockito.Mockito.times; import static org.mockito.Mockito.verify; import static org.mockito.Mockito.when; import org.mockito.runners.MockitoJUnitRunner;  @RunWith(MockitoJUnitRunner.class) public class TestUserDAO {      @Mock     DataSource mockDataSource;     @Mock     Connection mockConn;     @Mock     PreparedStatement mockPreparedStmnt;     @Mock     ResultSet mockResultSet;     int userId = 100;      public TestUserDAO() {     }      @BeforeClass     public static void setUpClass() throws Exception {     }      @AfterClass     public static void tearDownClass() {     }      @Before     public void setUp() throws SQLException {         when(mockDataSource.getConnection()).thenReturn(mockConn);         when(mockDataSource.getConnection(anyString(), anyString())).thenReturn(mockConn);         doNothing().when(mockConn).commit();         when(mockConn.prepareStatement(anyString(), anyInt())).thenReturn(mockPreparedStmnt);         doNothing().when(mockPreparedStmnt).setString(anyInt(), anyString());         when(mockPreparedStmnt.execute()).thenReturn(Boolean.TRUE);         when(mockPreparedStmnt.getGeneratedKeys()).thenReturn(mockResultSet);         when(mockResultSet.next()).thenReturn(Boolean.TRUE, Boolean.FALSE);         when(mockResultSet.getInt(Fields.GENERATED_KEYS)).thenReturn(userId);     }      @After     public void tearDown() {     }      @Test     public void testCreateWithNoExceptions() throws SQLException {          UserDAO instance = new UserDAO(mockDataSource);         instance.create(new User());          //verify and assert         verify(mockConn, times(1)).prepareStatement(anyString(), anyInt());         verify(mockPreparedStmnt, times(6)).setString(anyInt(), anyString());         verify(mockPreparedStmnt, times(1)).execute();         verify(mockConn, times(1)).commit();         verify(mockResultSet, times(2)).next();         verify(mockResultSet, times(1)).getInt(Fields.GENERATED_KEYS);     }      @Test(expected = SQLException.class)     public void testCreateWithPreparedStmntException() throws SQLException {           //mock          when(mockConn.prepareStatement(anyString(), anyInt())).thenThrow(new SQLException());           try {             UserDAO instance = new UserDAO(mockDataSource);             instance.create(new User());         } catch (SQLException se) {             //verify and assert             verify(mockConn, times(1)).prepareStatement(anyString(), anyInt());             verify(mockPreparedStmnt, times(0)).setString(anyInt(), anyString());             verify(mockPreparedStmnt, times(0)).execute();             verify(mockConn, times(0)).commit();             verify(mockResultSet, times(0)).next();             verify(mockResultSet, times(0)).getInt(Fields.GENERATED_KEYS);             throw se;         }      } } 

Answer 2

But what if I want to also test the behavior of dao and database ?

If you indeed want to test the database (as you should!), there's no way around it - you need an actual database. Mockito, albeit being a great library, is probably the wrong tool for this job.

Answer 3

Here is how you should test it:

public class UserDAOTest extends IntegrationTests
{
    // Or do it in a @Before method, if needed.
    UserDAO dao = new UserDAO();

    @Test
    public void createValidUser() {
        User validUser = new User(
            "John", "Smith", "[email protected]", "Abc123!@",
            "admin", "en"); // or use setters as needed

        dao.create(validUser);

        assertEntityCreatedInDB(validUser);
    }

    @Test
    public void attemptToCreateInvalidUser() {
        User invalidUser = new User("", null, null, "", null, "XY");

        dao.create(invalidUser);

        // This really shouldn't be done this way, as DAOs are not supposed
        // to manage transactions; instead, a suitable, descriptive
        // exception should be thrown by the DAO and checked in the test.
        assertTransactionWasRolledBack();
    }
}

A couple notes about the above:

1) The tests look short, simple, and easy to understand, as they should be; if they look big and ugly as those in another answer, you are doing something fundamentally wrong.

2) Test code can and should have its own infrastructure helpers, such as the IntegrationTests base class, which will hide any nasty JDBC/ORM access from the actual tests. I implemented such helpers in several projects, so I know this can be done, but that would be stuff for other questions.