How to make unit tests for DAO classes less brittle in the absence of a static test database?

Here's the scanario:

I am working on a DAO object which uses hibernate criteria API to form a number of complicated queries to perform certain tasks on the database (keyword search across multiple fields for example).

We need to unit test this to ensure that the generated query is correct for various scenarios. One way of testing it -which could be preferable- would be to test the hibernate criteria is created correctly by checking it at the end and mocking the database interaction. However this is not desirable as firstly it's kinda cheating (it's merely duplicating what the code would be doing) and also it doesn't check if the criteria itself causes hibernate to barf or when it goes to database it causes issues.

The option to use is then run the query against a test database. However, for historical reasons there is no static test database (one that code be checked in as part of the code for example) and the remit of my project does not allow me to embark on creating one, we have to content with testing against a shared development database that's periodically refreshed with production data.

When theses refreshes happen, the data behind the tests could change too, and this would make our unit tests brittle. We can get over it by not using exact numbers in tests but it's not really adequate testing that way.

The question is then: what do people do in cases like this to make tests less brittle? One option that I have in mind is to run a native SQL that does the same query (behaviourally - it doesn't have to be exact same as the query generated by hibernate) to get the expected number and then run the DAO version to see if it matches. This way, the behaviour of the query can be always implemented in the initial native SQL and you will always have the correct numbers.

Any feedback on this or other ideas on how to manage this situation would be greatly appreciated.

A.

UPDATE:

With regards to hsqldb/h2/derby suggestions, I am familiar with them but the company is not ready to go down t开发者_如何学运维hat route just yet and doing it piecemeal on just one test case won't be suitable.

With regards to my earlier suggestion I would like to elaborate a bit more - consider this scenario:

I want to ensure that my relatively complicated keyword search returns 2100 matches for "John Smith".

In order to find the expected number, I would have analyzed my database and found out the number using a SQL Query. What is the downside of having that query as part of the test, so that you will always know the you are testing the behaviour of the criteria?

So basically the question is: if for some reason you could not have a static data set for testing, how would you perform you integration tests in a non-brittle way?

One approach could be to use in-memory database like Apache Derby or HSQLDB, and prepopulate it with data before test start using DBUnit.

UPDATE: Here is a nice article about the aproach.

I agree with Andrey and Bedwyr that the best approach in the long term is to create an hsqldb database specifically for testing. If you don't have the option of doing that, then your solution seems like an appropriate one. You can't test everything, but you don't want to test nothing either. I've used this approach a few times for testing web services against integration databases etc. But remember that this database has to be maintained as well, if you add new columns etc.

You have to decide what you're trying to test. You don't want to test hibernate, you don't want to test that the database is giving what you've asked for (in terms of SQL). In your tests, you can assume that hibernate works, as does the database.

You say:

We need to unit test this to ensure that the generated query is correct for various scenarios. One way of testing it -which could be preferable- would be to test the hibernate criteria is created correctly by checking it at the end and mocking the database interaction. However this is not desirable as firstly it's kinda cheating (it's merely duplicating what the code would be doing) and also it doesn't check if the criteria itself causes hibernate to barf or when it goes to database it causes issues.

Why should hibernate barf on the criteria you give it? Because you're giving it the wrong criteria. This is not a problem with hibernate, but with the code that is creating the criteria. You can test that without a database.

It has problems when it gets to the database? Hibernate, in general, creates the sql that is appropriate to the criteria and database dialect you give it, so again, any problem is with the criteria.

The database does not match what hibernate is expecting? Now you are testing that the criteria and the database are aligned. For this you need a database. But you're not testing the criteria any more, you're testing that everything is aligned, a different sort of test.

So actually, it seems to me you're doing an integration test, that the whole chain from the criteria to the structure of the database works. This is a perfectly valid test.

So, what I do is in my tests to create another connection to the database (jdbc) to get information. I execute SQL to get number of rows etc, or check that an insert has happened.

I think your approach is a perfectly valid one.

However, for historical reasons there is no static test database (one that code be checked in as part of the code for example) and the remit of my project does not allow me to embark on creating on

All you need to do is fire up H2 or similar - put some entities in it and execute your integration tests. Once you've done this for a few tests you should be able to extract a data setup utility that creates a schema with some test data that you can use for all the integration tests if you feel the need.