I am currently writing the Data Access Layer of a Java project using Hibernate and I want to apply TDD on my work. The part that is concerned with the database is the mapping of objects on the database schema, and the queries.
As a recap, there are the options:
use a memory-only database just for testing purpose, as described in Unit-Testing Hibernate with HSQL. That is my preferred option, but it requires either that I control the schema (I do not, though I’ve been on a previous project where the schema was generated from the Hibernate descriptors) or that the schema is vendor-independent enough to run it on HSQL (it is not; there might be good reasons for that, but I am not an expert)
use mock objects to drive the behavior of Hibernate. Though this is theoretically possible, it sounds hard to put in place (might need a mock db driver?). Also, I guess it would only test the generate SQL queries, not if the queries are appropriate for the schema. Too limited. This approach is suggested by Darrel Norton in TDD with a Database.
use the current database instance to populate it at the beginning of each tests, and cleaning the changes at the end (an improvement on this is to start a transaction at the beginning of the test, and rollback at the end, with no committing). This approach is limited and risky, as a simple query such as SELECT * FROM MY_DATA WHERE NAME = ‘test’ would be necessarily return the same data in the future, as we have no control on the current content on the db.
ask for a new database instance that would be used just for my tests. This means that each developer would need his own database instance, so that they would not step on each other’s toes. This is acceptable in my context where only 2 or 3 developers would be involved. The drawback is that it is much slower that having a memory-resident database, which can be a problem if many tests are written (as they should). This is discussed more in length by Richard Dallaway
install a local copy of a limited version of Oracle. This has been advised against by the db admin who pointed that even the Personal Edition would be taking all the resources of my computer. I have not checked this.
As a supplement to most of those points, using dbUnit can be a great help to populate the database.
Many details are discussed on the Unit Tests And Databases wiki page.
Update (15/03/2007): the point regarding roll-backing changes done in the db by tests is in fact supported by a number of tools. See the entry on Gienah for details.
About Eric Lefevre-Ardant
Independent technical consultant.
I know that there might be a good reason behind that, but why you want to use Oracle instance for testing? why not considering another for testing purposes?
You mean another database engine? The problem (well, *my* problem) is that the script that creates the schema is very specific to Oracle. The guy who would it is an Oracle admin, not just any db admin. He assures me that there are good reasons for using Oracle-specific things, as we are reaching some limits (we are storing weather data, very space consuming). I cannot argue with him, as I do not know databases enough (though David thought that there are ways to design a database with pure SQL commands, even with this amount of data).
that explains, now i get your problem better,i would be interested by the knowing results if you go for mocks :)