Java DAO implementation testing

Question

This is a very simple DAO attempt I'd like to share.

My question is if this is a correct way to test a DAO. What I mean is that I'm verifying the SQL query and giving it return a mock. Then tell the mock to return these specific values and assert them?

I have updated the DAO to use prepared statement instead of simple Statement. Thanks.

public class PanelDao implements IO {

    private final static Logger LOGGER = Logger.getLogger(PanelDao.class);

    private Connection connection;

    public PanelDao() throws SQLException {
        this(MonetConnector.getConnection()); 
    }

    public PanelDao(Connection connection) throws SQLException {
        this.connection = connection;
    }

    @Override
    public void save(Panel panel) throws SQLException {
        final String query = "INSERT INTO panels VALUES ( ?, ?, ?, ?, ?, ?, ? )";
        final PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, panel.getId());
        statement.setString(2, panel.getColor());
        statement.setDate(3, (new Date(panel.getPurchased().getTime())) );
        statement.setDouble(4, panel.getCost());
        statement.setDouble(5, panel.getSellingPrice());
        statement.setBoolean(6, panel.isOnSale());
        statement.setInt(7, panel.getUserId());

        LOGGER.info("Executing: "+query);
        statement.executeUpdate();
    }

    @Override
    public void update(Panel object) {
        throw new UnsupportedOperationException();      
    }

    @Override
    public void delete(Panel object) {
        throw new UnsupportedOperationException();      
    }

    @Override
    public Panel find(String id) throws SQLException {
        final String query = "SELECT * FROM panels WHERE id = ? ";
        final PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, id);

        LOGGER.info("Executing: "+query);
        final ResultSet result = statement.executeQuery();

        final Panel panel = new Panel();
        if (result.next()) {
            panel.setId(result.getString("id"));
            panel.setColor(result.getString("color"));
        }
        return panel;       
    }
}

And the test class

public class PanelDaoTest {

    @InjectMocks
    private PanelDao panelDao;

    @Mock 
    private Connection connection;

    @Mock
    private Statement statement;

    @Mock
    private ResultSet result;

    private Panel panel;

    @BeforeClass
    public static void beforeClass() {
        BasicConfigurator.configure();
    }

    @Before
     public void init() throws SQLException {
        MockitoAnnotations.initMocks(this);
        Mockito.when(connection.createStatement()).thenReturn(statement);
        panel = new Panel("AZ489", "Yellow", new Date(), 10.00, 7.50, true, 1);
    }

   @Test
   public void testSave() throws SQLException {
        Mockito.when(connection.prepareStatement("INSERT INTO panels VALUES ( ?, ?, ?, ?, ?, ?, ? )")).thenReturn(statement);
        panelDao.save(panel);
        Mockito.verify(statement).executeUpdate();
    }

    @Test
    public void testFind() throws SQLException {
        Mockito.when(connection.prepareStatement("SELECT * FROM panels WHERE id = ? ")).thenReturn(statement);
        Mockito.when(statement.executeQuery()).thenReturn(result);
        Mockito.when(result.next()).thenReturn(true);
        Mockito.when(result.getString("id")).thenReturn("AZ489");
        Mockito.when(result.getString("color")).thenReturn("Yellow");
        Panel panel = panelDao.find("AZ489");
        assertEquals("AZ489",panel.getId());
        assertEquals("Yellow",panel.getColor());
        Mockito.verify(statement).executeQuery();
     }
}

---

2.0 Testing DAO with HSQLDB

After taking into account your feedback I decided to use HSQLDB for real database testing. Please find this as a resource if tackling similar problems.

public class PanelDao implements IO {

    private final static Logger LOGGER = Logger.getLogger(PanelDao.class);

    private Connection connection;

    /**
     * Default constructor is using Monet connector
     */
    public PanelDao() throws SQLException {
        this(MonetConnector.getConnection()); 
    }

    public PanelDao(Connection connection) throws SQLException {
        this.connection = connection;
    }

    @Override
    public void save(Panel panel) throws SQLException {
        final String query = "INSERT INTO panels VALUES ( ?, ?, ?, ?, ?, ?, ? )";
        final PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, panel.getId());
        statement.setString(2, panel.getColor());
        statement.setDate(3, (new Date(panel.getPurchased().getTime())) );
        statement.setDouble(4, panel.getCost());
        statement.setDouble(5, panel.getSellingPrice());
        statement.setBoolean(6, panel.isOnSale());
        statement.setInt(7, panel.getUserId());

        LOGGER.info("Executing: "+query);
        statement.executeUpdate();
    }

    @Override
    public void update(Panel object) {
        throw new UnsupportedOperationException();      
    }

    @Override
    public void delete(Panel object) {
        throw new UnsupportedOperationException();      
    }

    @Override
    public Panel find(String id) throws SQLException {
        final String query = "SELECT * FROM panels WHERE id = ? ";
        final PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, id);

        LOGGER.info("Executing: "+query);
        final ResultSet result = statement.executeQuery();

        if (result.next()) {
            final Panel panel = new Panel();
            panel.setId(result.getString("id"));
            panel.setColor(result.getString("color"));
            panel.setPurchased(new Date(result.getDate("purchased").getTime()));
            panel.setCost(result.getDouble("cost"));
            panel.setSellingPrice(result.getDouble("selling_price"));
            panel.setOnSale(result.getBoolean("on_sale"));
            panel.setUserId(result.getInt("user_id"));
            return panel;
        }
        return null;        
    }
}

and the Test class:

public class PanelDaoTest {

    private PanelDao panelDao;
    private Panel panel;

    /* HSQLDB */
    private static Server server;
    private static Statement statement;
    private static Connection connection;

    @BeforeClass
    public static void beforeClass() throws SQLException {
        BasicConfigurator.configure();
        server = new Server();
        server.setAddress("127.0.0.1");
        server.setDatabaseName(0, "bbtest");
        server.setDatabasePath(0, ".");
        server.setPort(9000);
        server.start();
        PanelDaoTest.connection = DriverManager.getConnection("jdbc:hsqldb:hsql://127.0.0.1:9000/bbtest", "SA", "");
        PanelDaoTest.statement = PanelDaoTest.connection.createStatement();
    }

    @Before
    public void createDatabase() throws SQLException {
        PanelDaoTest.statement.execute(SqlQueries.CREATE_PANEL_TABLE);
        panelDao = new PanelDao(PanelDaoTest.connection);
    }

    @Test
    public void testSave() throws SQLException {
        panel = new Panel();
        panel.setId("A1");
        panel.setPurchased(new Date());
        panelDao.save(panel);
        assertNotNull(panelDao.find("A1"));
    }

    @Test
    public void testFind() throws SQLException {
        final String id = "45ZZE6";
        panel = Panel.getPanel(id);

        Panel received = panelDao.find(id);
        assertNull(received);

        panelDao.save(panel);
        received = panelDao.find(id);
        assertNotNull(received);
        assertEquals(panel.getId(), received.getId());
        assertEquals(panel.getColor(), received.getColor());
        assertEquals(panel.getPurchased().getDate(), received.getPurchased().getDate());
        assertEquals(panel.getPurchased().getMonth(), received.getPurchased().getMonth());
        assertEquals(panel.getPurchased().getYear(), received.getPurchased().getYear());
        assertEquals(panel.getCost(), received.getCost(),0.001);
        assertEquals(panel.getSellingPrice(), received.getSellingPrice(),0.001);
        assertEquals(panel.getUserId(), received.getUserId());
    }

    @After
    public void tearDown() throws SQLException {
        statement.executeUpdate(SqlQueries.DROP_PANEL_TABLE);
    }

    @AfterClass
    public static void stopServer() {
        server.shutdown();
    }
}

Answer 1

First of all, you should not create SQL queries by concatenation, because it's vulnerable to SQL injection. Use PreparedStatements instead.

Actually, it doesn't make much sense to test DAO this way. Your tests only verify that your DAO passes values back and forth correctly, but it doesn't cover the real complexity that lies in correctness of SQL queries issued by your DAO.

In other words, if you want to test your DAOs you need to create integration test that involves real database. This way you can verify that SQL queries issued by your DAO are correct.

Answer 2

I don't really think that method of testing is really buying you anything, and the test code is extremely brittle. I would use something like DBUnit that allows you to "mock" your database. This will actually allow you to test the correctness of your queries.

Answer 3

I would use an in-memory database such as H2, to test that your SQL actually works.

• When you test your save method, your test should call save, then select the row from the database and assert that there is actually something there.
• When you test your find method, your test should insert some rows in the database directly, then call find and assert that the desired row or rows were actually found.