JUnit: testing DAOs - rollback or delete

Question

I'm testing a very simple java application using JUnit 4. By "simple" I mean there is no spring and no hibernate. I need to test the data access layer (JDBC, MySQL) and my doubt is which approach is better for this kind of test? Insert data on @Before and delete on @After or create a transaction on @Before and rollback on @After?

Thanks!

@Reddy I think you should write that as a separate answer, as I see it as the most valid one. — bezmax, Jan 20 '12 at 12:31
as @Brian said in his answer i want to be sure that the sql querys are correct and they work at MySQL X platform. Also, in this case, acess to a MySQL server is not a problem. — Morvader, Jan 20 '12 at 12:38

score 4 · Answer 1 · answered Jan 20 '12 at 12:22

4

Transactions for two reasons:

writing/deleting might be more expensive then rolling back
margin of error is smaller (your code to delete data might have a bug)

answered Jan 20 '12 at 12:22

milan

11,872
3
42
49

score 4 · Accepted Answer · answered Jan 20 '12 at 12:34

I would disagree with using a DB other than MySQL, as you might be exposed to platform differences in your tests which mask problems your code has with MySQL. Some of your code/SQL might not even work on another platform without hefty refactoring.

But, agree with others about using transactions rather than deletes or updates to restore state.

One caveat: if you're using procs, functions, etc, those can do COMMITs internally which could muck up any attempts to rollback JUnit changes. Maybe not an issue for you, but an issue for maybe others to bear in mind, especially when dealing with legacy DB code for which unit testing was never considered.

score 2 · Answer 3 · answered Jan 20 '12 at 12:28

I'd also go for volatile in memory databases or for temporary tables in MySQL which are connection specific and automatically deleted when the connection is closed. I would not use transactions for this kind of test, because you might want to actually test transactions itself.

score 1 · Answer 4 · answered Jan 20 '12 at 15:42

Transaction rollback is more secure, because the test database is left unchanged, even if test is stopped before test method and @After.

However, commit and delete tests better, because some constraints are checked against new data during commit (deferred foreign keys etc.), so with rollback there are some things you won't test.

So it's up to you, but in most cases transaction rollback is preferrable choise (I prefer it too).

score 0 · Answer 5 · answered Jan 20 '12 at 13:08

This comes up time and time again wherever I work and different developers like different solutions.

Firstly I don't really like using an in-memory database for the following reasons

Code seem to outpace tests. We found a codebases where the tables being tested in the in-memory don't exist in the actual database.
The databases are not exactly the same, if you are using hsql in-memory but your main database is MySQL then there are differences in syntax, dates to name but a few. I know you can use ASCII Sql but you are testing something that is not what you are going to run on. There are going to be differences.

I prefer transaction rollbacks over deletes as they leave the database in the exact state prior to the beginning of the transaction but it can slow tests down significantly if you have thousands of them.

I do sometimes question the value of database tests and do favour a continuos integration where we run integration tests on a fresh database. That way we cover all the data access. In unit tests we then just mock the data access layer with Mockito or some similar mocking tool.

JUnit: testing DAOs - rollback or delete

5 Answers5

Linked