Java DAO 实现测试

Question

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

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

我已经更新了 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;       
    }
}

和测试类

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 使用 HSQLDB 测试 DAO

在考虑了您的反馈后，我决定使用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;        
    }
}

和测试类：

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

首先，您不应该通过串联创建 SQL 查询，因为它容易受到 SQL 注入的影响。请改用 PreparedStatement s。

实际上，以这种方式测试 DAO 没有多大意义。您的测试仅验证您的 DAO 是否正确来回传递值，但它没有涵盖您的 DAO 发出的 SQL 查询的正确性的真正复杂性。

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

Answer 2

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

Answer 3

我会使用内存数据库（如 H2）来测试您的 SQL 是否实际工作。

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