What is the correct way of testing CRUD methods that are found in DAO classes using Java(Spring framework) and In-memory DB?

Question

I am trying to write good integration test case for practice. So far i have implemented test cases for three methods(save, findById and getAll method) but i can't help myself thinking i am doing this the wrong way.

Below is the code i have so far:

@ExtendWith(SpringExtension.class) 
@ContextConfiguration(classes = {DatabaseConfigs.class})
public class UserDaoImplTest {
    
    private DataSource dataSource;
    private Connection con;
    private UserDao userDao;
    
    @Autowired
    public UserDaoImplTest(@Qualifier("h2DataSource")DataSource dataSource) {
        this.dataSource = dataSource;
        userDao = new UserDaoImpl(dataSource);  
    }
    
    @BeforeEach
    public void setUp() throws SQLException {
            con = dataSource.getConnection();
            ScriptUtils.executeSqlScript(con, new ClassPathResource("config/schema.sql"));
            ScriptUtils.executeSqlScript(con, new ClassPathResource("config/data.sql"));  
    }
    @AfterEach
    public void tearDown() throws SQLException {
        con.close();
    }
             
    @Test
    @DisplayName("Test to check if the created user, using save method, is present in db.")
    public void testSave() throws SQLException {

        User user = new User("luka", "pass");
        userDao.save(user);
        
        String sql = "SELECT name, password from user order by id desc";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        boolean result = rs.next();
        
        assertTrue(result, "Cant retrieve inserted user");
        

        String name = rs.getString("name");
        String password = rs.getString("password");
        
        assertEquals("luka", name, "Retrieved username doesnt match.");
        assertEquals("pass", password, "Retrieved password doesnt match.");

    }
    @Test
    @DisplayName("Test to check if the getAll method is retrieving users from db.")
    public void testGetAll() {
        
        List<User> getAllUsers = userDao.getAll();
        
        assertTrue(getAllUsers.size() > 0,  "Size of retrieved users can't be 0");
    }
    @Test
    @DisplayName("Test to check if the id of a retrived user matches the id supplied.")
    public void testFindById() {
        long id = 2;
        Optional<User> userOpt = userDao.findById(id);
        assertTrue(userOpt.isPresent(), "No user retrieved");
        
        User user = userOpt.get();

        assertEquals(2, user.getId(), "Retrieved id of a user doesnt match the expected id.");
    }
    @Test
    public void testDelete() {      
        fail("Not yet implemented");
    }
    @Test
    public void testUpdate() {
        fail("Not yet implemented");
    }

}

For example, i want to know if i could write a test for save method like this:

private int getMaxId() throws SQLException  {
    String sql = "SELECT max(id) as id from user";
    PreparedStatement ps = con.prepareStatement(sql);
    ResultSet rs = ps.executeQuery();
    
    rs.next();
    int id = rs.getInt("id");
    
    ps.close();
    rs.close();
    
    return id;
    
}

@Test
@DisplayName("Test to check if the created user, using save method, is present in db.")
public void testSave() throws SQLException {

    User user = new User("luka", "pass");
    userDao.save(user);
    
    Optional<User> userOpt = userDao.findById(getMaxId());
    assertTrue(userOpt.isPresent,"Can't retrieve user.")
    
    assertTrue(result, "Cant retrieve inserted user");
    

    String name = rs.getString("name");
    String password = rs.getString("password");
    
    assertEquals("luka", name, "Retrieved username doesnt match.");
    assertEquals("pass", password, "Retrieved password doesnt match.");

}

In this case i am testing save method using findById method so i am basically testing two methods inside one test case. Is this an okay way to do tests, because i think i am breaking the rule of isolation of methods? I want to know what is the best approach for testing these methods. Any help is appreciated and if my code isnt good please tell me what i did wrong.

Answer 1

I'm sure you'll get a few different answers for this. For me though I'd want a test that ensures an integration with a database is good by:

1. spin up a db (hint: docker typically assists)
2. put db into a known state (load it with test data)
3. call your method that performs some CRUD operation
4. make some assertions

There's a term you may have heard used whereby if you can insert/update/delete some data then attempt to read it back using your Read method (from cRud) you'll have done a "round-trip" check.

Assuming all goes well, you get what you expect back (updated results (forinsert/update), or an empty set (for delete), or maybe an exception).

It also helps if you're using a build tool (preferred choice is gradle for me vs. maven but either should work) to manage the process of spinning up containers. Checkout https://www.testcontainers.org/ or https://github.com/avast/gradle-docker-compose-plugin (works with gradle build tool)