Databases
StackOverflow2013
Postsdbo
POJava DAO implementation testing
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJava DAO implementation testing
    primarykey
    data
    text
    <p>This is a very simple DAO attempt I'd like to share.</p> <p>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? </p> <p>I have updated the DAO to use <strong>prepared statement</strong> instead of simple Statement. Thanks.</p> <pre><code>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; } } </code></pre> <p>And the test class</p> <pre><code>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(); } } </code></pre> <hr/> <h2>2.0 Testing DAO with HSQLDB</h2> <p><strong>After taking into account your feedback</strong> I decided to use HSQLDB for real database testing. Please find this as a resource if tackling similar problems.</p> <pre><code>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; } } </code></pre> <p>and the Test class:</p> <pre><code>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(); } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTian Na
    1. USTian Na
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJava DAO implementation testing
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POJava DAO implementation testing
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POJava DAO implementation testing
      1. This table or related slice is empty.
      1. VTUpMod
    1. COyes I have, I'm going to use prepared statements thanks
      singulars
      1. POJava DAO implementation testing
      1. USTian Na
    2. COThat would be so much easier to read if you just did string concat instead of the StringBuilder, with no change in performance. The comma and quote variables just make it extra hard as well. append(comma).append(quote) instead of append(",'"). Each line would be much more readable with [+ "'" + panel.getXXX() + "',"]
      singulars
      1. POJava DAO implementation testing
      1. USRobin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload