I have a DAO infrastructure as follows:
StoreDao, CouponDao, PersonDao.
All these extend from a GenericDao which has the bulk of the functionality(using Java Generics). kind of explained here - [http://www.ibm.com/developerworks/java/library/j-genericdao/index.html][1]
When getAll() is called on StoreDao, what's actually called is GenericDao's getAll() which appends certain default where clause like active=true, expires>now() to the existing HQL query to be executed.
We have a bunch of Dao tests which create data in their setup(), have a bunch of tests that assert on the response. Not exactly unit tests since the database isn't mocked, but can be called integration tests i guess.
One of my team mates has created a testing infrastructure to test whether the sql queries generated are accurate. The way he does this is as follows:
Have a custom Hibernate interceptor that intercepts onPrepareStatement(), uses Hibernate's ASTParser to create an XML structure of the Query about to be executed and then use XPaths to validate the query, for instance, look if certain fields are present in the where clause, the joins are correct etc.
We are doing this to test the Generic Dao in isolation. For this, we're having to create GenericDomain, GenericChildDomain, GenericDomain.hbm.xml alongwith the tables backing these.
Questions:
Is this worth it? Unless we have completely mocked out the database for all our unit tests that we already have, I don't see a reason for us to create this infrastructure.
How can we not test the HQLs with the infrastructure we already have. If you want to make sure that active=true was appended to the query, just make sure that the DAO does not return data with active=false.
Lastly what we're doing here is heavily hibernate specific which will mostly be thrown away if we decide to replace our DAOs with say IBatis/JPA-EclipseLink/NoSQL.
Finally, why do we have to invent something like this. Isn't this a fairly common problem? Isn't there a solution already built by someone out there?
On the testing of generated SQL, my vote is, "Don't do it." Unless you have a highly specific need for a query to be generated in a particular way--in a highly performance-sensitive piece of an app, perhaps--there's no real need. Mostly what you're interested in testing at this level is that things save and load properly according to how they're mapped and that your finder methods find the things they should. You seem to be leaning in the direction of just starting up hibernate and exercising your DAOs against a real database, and I'd say that's the right approach.