Java DAO 实现测试

Java DAO implementation testing

提问by Tian Na

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

这是我想分享的一个非常简单的 DAO 尝试。

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?

我的问题是这是否是测试 DAO 的正确方法。我的意思是我正在验证 SQL 查询并给它返回一个模拟。然后告诉模拟返回这些特定值并断言它们？

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

我已更新 DAO 以使用准备好的语句而不是简单的语句。谢谢。

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

2.0 使用 HSQLDB 测试 DAO

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

考虑到您的反馈后，我决定使用 HSQLDB 进行真正的数据库测试。如果解决类似问题，请将此作为资源。

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();
    }
}

采纳答案by Dawood ibn Kareem

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

我会使用诸如 H2 之类的内存数据库来测试您的 SQL 是否确实有效。

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

• 当您测试您的save方法时，您的测试应调用save，然后从数据库中选择该行并断言那里确实存在某些内容。
• 当您测试您的find方法时，您的测试应该直接在数据库中插入一些行，然后调用find并断言实际找到了所需的行。

回答by axtavt

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

首先，您不应该通过串联创建 SQL 查询，因为它容易受到 SQL 注入的影响。使用PreparedStatements 代替。

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.

实际上，这样测试DAO并没有多大意义。您的测试仅验证您的 DAO 是否正确地来回传递值，但它并未涵盖由您的 DAO 发出的 SQL 查询的正确性所带来的真正复杂性。

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.

换句话说，如果您想测试您的 DAO，您需要创建涉及真实数据库的集成测试。通过这种方式，您可以验证 DAO 发出的 SQL 查询是否正确。

回答by ach

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 DBUnitthat allows you to "mock" your database. This will actually allow you to test the correctness of your queries.

我真的不认为这种测试方法真的会给你买任何东西，而且测试代码非常脆弱。我会使用像DBUnit这样的东西，它允许你“模拟”你的数据库。这实际上将允许您测试查询的正确性。