I am coding crud APIs for my application built on Hibernate/SQL. I am using mysql for both testing and live environment.
For testing, I have a database, user and privileges. So every time to test, I have to follow these steps:
I prefer using H2, dbUnit, and spring-test. Hsqldb and Derby are other in-memory databases that are reasonable choices. Tests in spring use their own separate context with their own datasource, the test context configuration supersedes the normal application context values. That way there is no extra effort required to switch between databases, the tests can be setup to use H2 while the application uses mysql.
Be aware that mysql is permissive about the sql it accepts, other databases tend to be more strict.
Unlike Jim, I think it pays off to have tests exercising your data layer, especially when using Hibernate, which is complicated enough that I want to confirm it's really doing what I think it should be doing.