Geek Answers Handbook

Testing Java DAO Deployment

This is a very simple DAO attempt that I would like to share.

My question is, is this the right way to test DAO. I mean, I'm checking the SQL query and letting it return the layout. Then tell the layout to return these specific values ​​and approve them?

I updated the DAO to use a prepared statement instead of a 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 considering your feedback, I decided to use HSQLDB to actually test the database. If you encounter similar problems, find this as a resource.

 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 class Test:

 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(); } }
+4
source share
3 answers

I would use a in-memory database like H2 to check if your SQL is working.

  • When you test your save method, your test should call save , then select a row from the database and confirm that there is something there.
  • When you test your find method, your test should insert multiple rows into the database directly, and then call find and claim that the row or rows to find were found.
+2
source

First of all, you should not create SQL queries by concatenating, because they are vulnerable to SQL injection. Use PreparedStatement instead.

In fact, it does not make sense to test the DAO in this way. In your tests, just make sure your DAO passes the values ​​back and forth correctly, but does not cover the real complexity that lies in the correctness of the SQL queries issued by your DAO.

In other words, if you want to test your DAOs, you need to create an integration test that includes a real database. This way you can verify the correctness of SQL queries issued by your DAO.

+5
source

I really don't think the testing method really buys you anything, and the test code is extremely fragile. I would use something like DBUnit , which allows you to “mock” your database. This will allow you to verify the correctness of your requests.

+4
source

More articles:


All Articles