I don't know how to apply unit tests on data access layer. I always wonder if the data access layer should be tested. In my company, we have stable database to store unit test data and test data access layer by running data access objects and check the data they get from the stable database.
In order to pass the unit tests, data in the stable database can not be modified anymore. I think there is a better solution to this. If I am not mistaken, the the mock object cannot perform tests on SQL statements and
First, by most definitions, "unit" tests do not depend on external systems like a database. You want to create what are called "functional" or "integration" tests. In practice these types of tests will be implemented in the same way as unit tests, using something like Junit, but you should separate them from unit tests, which should run very fast and not break when your database is down or the data has changed.
Second, try to keep most of your business logic out of DAOs and instead put it into a service POJO layer so that you can test biz logic without involving the database.
Next, the ideal way to set up testing for DAOs is to start with an empty database, and load it with test data (often using the DAOs themselves), and then run your DAO tests against a known, and writable, test dataset. If you're fortunate enough to have a read-only database, the
stable database approach you outline will work, but most systems are read/write to the database.
Finally, it is valuable to test DAOs. Often the database queries are some of the most fragile parts of your system, and you don't want to wait until they are deployed to production to find out they are breaking.