CODEWITHSUNDEEP
javamysqlspringjdbc
NoSuchUserException
NoSuchUserException

Reputation: 149

Test DAO class which is using JDBC with Mockito

I am trying to make unit test on a DAO class using Mockito. I have written some unit test before but not on a DAO class using some data base(in this case JDBC and MySQl).

I decided to start with this simple method but I do not now which are the good practices and I do not know how to start.

I do not know if this is important in this case but the project is using Spring Framework.

public class UserProfilesDao extends JdbcDaoSupport {

    @Autowired
    private MessageSourceAccessor msa;

    public long getUserId(long userId, int serviceId) {
        String sql = msa.getMessage("sql.select.service_user_id");
        Object[] params = new Object[] { userId, serviceId };
        int[] types = new int[] { Types.INTEGER, Types.INTEGER };
        return getJdbcTemplate().queryForLong(sql, params, types);
    }
}

Upvotes: 0

Views: 1665

Answers (1)

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26926

If you really like to test the DAO create an in memory database. Fill it with the expected values, execute the query within the DAO and check that the result is correct for the previous inserted values in the database.

Mocking the Connection, ResultSet, PreparedStatement is too heavy and the result are not as expected, because you are not accessing to a real db.

Note: to use this approach your in memory database should have the same dialect of your phisical database, so don't use specific functions or syntax of the final database, but try to follow the SQL standard.

If you use an in memory database you are "mocking" the whole database. So the result test is not a real Unit test, but is not also an integration test. Use a tool like DBUnit to easily configure and fill your database if you like this approach.

Consider that mocking the database classes (PreparedStatement, Statement, ResultSet, Connection) is a long process and you are not granted that it works as expected, because you are not testing the right format of your sql over an sql engine.

You can also take a look to an article of Lasse Koskela talking about unit testing daos.

To test the DAO you need to:

  • Empty the database (not necessary for in memory db)
  • Fill the database with data example (automatic with db unit, done in the @BeforeClass or @Before method)
  • Run the test (with JUnit)

If you like to formally separated real unit tests from integration tests you can move the DAO tests on a separate directory and test them when needed and in the integration tests.

A possible in memory database that has different compatibility modes is H2, with the following database compatibilities:

  • IBM DB2
  • Apache Derb
  • HSQLDB
  • MS SQL Server
  • MySQL
  • Oracle
  • PostgreSQL

Upvotes: 2

Related Questions