tests for data access (sql queries) functionality

Question

hello guys, I want to know the best way of testing data access functionality.
I know that it is possible to create mocks to change data layer objects that is using to test business logic.
However is it possible to test if sql queries to database are correct.

Scenario: A method must return employees which were applied for work last month.
I can return list of object and check if each employee's startDate property is correct (last month).
So if it returns 3 employees and they have correct startDate value but there are more two employee in database which aren't returned. How to write test for that case? :)

Thanks in advance.

Answer 1

You set up the test DB so you shold know what data is in it. If you expect 5 employees to be returned from the query and you get only 3, you know there is an error.

You can test the query with different setups: empty table, only new employees, only old employees, a mix of the two (with special care to the borderline cases), etc.

Answer 2

I don't think you need to check the two other employees in the database which aren't returned.

The key is, when setting up your test data, you would want to ensure you have enough records that don't match the criteria (in addition to the records that do match), then you run the fetch and make sure you get back the correct number of records that do match the criteria.

Preparing the test data in this manner ensures that your method is returning the expected results.