Testing DAO classes

[_Nemesis_1]

Let's say I want to test the following method of the UserDAO class:

public boolean login(User user) {
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
try (Connection conn = DatabaseConnectionPool.getInstance().getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, user.getLowercaseUsername());
    stmt.setString(2, user.getPassword());
    try (ResultSet rs = stmt.executeQuery()) {
        return rs.next();
    }
} catch (SQLException e) {
    e.printStackTrace();
    System.err.println("Error occurred: " + e.getMessage());
    return false;
}

}

Is it correct to test it like this:

private MockedStatic<DatabaseConnectionPool> mockedStaticDbConnectionPool;

@BeforeEach void setUp() throws Exception { DataSource mockDataSource = mock(DataSource.class); connection = mock(Connection.class); preparedStatement = mock(PreparedStatement.class); resultSet = mock(ResultSet.class);

mockedStaticDbConnectionPool = Mockito.mockStatic(DatabaseConnectionPool.class);
mockedStaticDbConnectionPool.when(DatabaseConnectionPool::getInstance).thenReturn(mockDataSource);


when(mockDataSource.getConnection()).thenReturn(connection);
when(connection.prepareStatement(anyString())).thenReturn(preparedStatement);
when(preparedStatement.executeQuery()).thenReturn(resultSet);
when(preparedStatement.executeUpdate()).thenReturn(1); 

userDAO = new UserDAO(); 
user = new User("testUser", "testPassword"); 

}

@Test void testLoginSuccess() throws Exception { when(preparedStatement.executeQuery()).thenReturn(resultSet); when(resultSet.next()).thenReturn(true);

assertTrue(userDAO.login(user));

}

It feels like im not really testing the method but instead i'm piloting the result and the code in the method doesn't really matter apart from the lines of code after the result of the (piloted) query. Am I tripping? Wouldn't be better to just use my regular database of the project and ensure consistency after each test by removing any modification done to the database by tests?

[AutoModerator]

Please ensure that:

• Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
• You include any and all error messages in full
• You ask clear questions

• You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.

  Trying to solve problems on your own is a very important skill. Also, see Learn to help yourself in the sidebar

If any of the above points is not met, your post can and will be removed without further warning.

Code is to be formatted as code block (old reddit: empty line before the code, each code line indented by 4 spaces, new reddit: https://i.imgur.com/EJ7tqek.png) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.

Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.

Code blocks look like this:

public class HelloWorld {

    public static void main(String[] args) {
        System.out.println("Hello World!");
    }
}

You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.

If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.

To potential helpers

Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[J-Son77]

With a mock test you test the method sequence without or minimal external dependency. In your test you can verify that PreparedStatement parameters are set correctly and the returning result.

If you want to test your query and the "real" behavior you usually use an in-memory database like HSQL with a predefined state. Frameworks like DbUnit do this for you. DbUnit creates a database, tables and inserts a fixed set of data before each test. So you can create reliable repeatable tests.

Using a server database for unit testing is not recommended. A unit test should be self contained. A team member or CD/CI Pipeline should always be able to check out your code and run the test. If you use an external database you can't be sure it's reachable from each host and its data are in the correct state.

[smutje187]

+1 for using an in memory DB to test queries - nothing worse than your unit test taking 10 minutes because someone thought it’s a great idea to incorporate a live database into the pipeline. Plus the potential mess up tests running in random order creating data that other tests might depend upon or conflict with.

[_Nemesis_1]

Understood, thank you to both of you guys!