While unit/integration testing the repositories (dao’s in yesterdays lingo), try to use the same type of database, instead of some lightweight alternative like hsldb. Unless it also used in the production environment of course.
Using a different type of database could lead to not detecting problems like:
- differences in sql interpretation, types, precision and formatting.
- missing database constraints. Usually this is functionality I prefer to guarantee and like to have placed in handwritten ddl scripts, instead of it being out of control because it is generated by some or-mapping technology. This approach also invites for creating update scripts from day one, instead of it being an afterthought and causing pain when upgrading to the next release.
- differences in concurrency control mechanisms. What leads to a pessimistic lock in one database, could lead to an optimistic locking failure in another.
This means that there are differences in behavior between databases and these should not be ignored. Not using the real database could lead to problems that are only observed in the acceptation or production environment. And the longer it takes to encounter a bug, the more time it is going to cost to solve it.
I know that in continuous build environments using the same type of database can be difficult; not only the environment needs to support different databases, it also needs to support multiple versions. One of the ways to solve this problem is to use a virtualized operating system (using e.g. VMWare) per database version. This should prevent the build environment from changing into a maintenance nightmare.