How to apply unit test on DAOs in java

Question

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 ResultSet mappings.

What is the best way to unit test the DAO? Is there a better way to do this with TDD?

Answer 1

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.

Answer 2

Strictly speaking, you're writing a functional test. To do this you're going to need a test database of one sort or another. Let's talk about your options.

1. HSQL/in-memory DB. Small and fast. Trivial to setup and get rolling, and great performance on unit test sized data. The downside is, unless you are deploying using these environments then you risk having your unit test work but actual code fail. It also means you can't use any SQL constructs which are not support in both HSQL and your production DB. This can be mitigated to an extent by using Hibernate or similar. A good way to go if you only have very simple queries.

2. Mock out the DB calls entirely. Pointless unless you are doing too much heavy lifting in your DAO.

3. Use a test instance of your production DB. This will give you the best results in terms of accuracy or results. It will let you test to make sure that all your calls work as expected and lets you use non-portable SQL. You can use something like DBUnit to load database data or just use the DAO under test to do it. I would recommend this if you large and nasty queries. Ones with a lot of edge cases, roll-up views and subtle behavior. The downside is that real DBs will incur performance penalties since they'll be doing real work (transactions, index updates, rollback support).