How do I unit test jdbc code in java?

You have several options:

• Mock the database with a Mock library, e.g. JMock. The huge drawback of this that your queries and the data will most likely be not tested at all.
• Use a light weight database for the tests, such as HSQLDB. If your queries are simple, this is probably the easiest way to go.
• Dedicate a database for the tests. DBUnit is a good option, or if you are using Maven, you can also use the sql-maven-plugin to set up and tear down the database properly (be careful of dependencies between tests). I recommend this option as it will give you the biggest confidence that the queries work properly with your db vendor.

Sometimes it is necessary and useful to make these tests configurable so that these tests are only executed if the database is available. This can be done with e.g. build properties.

That's why you have derby (now called JavaDB) or sqlite -- they are small, simple databases that you can create, load, test against and destroy relatively quickly and simply.

You could use DBUnit together with a HSQLDB which can read its initial data from CSV files for example.

I like to use a combination of:

• DBUnit
• HSQLDB
• Unitils (specifically, the database testing and maintenance modules)

You can get pretty far with just DBUnit and HSQLDB. Unitils provides the last mile of code to manage and reset database state. It also provides a nice way of managing database schema changes and makes it easy to use specific RBDMS (Oracle, DB2, SQL Server, etc). Finally, Unitils provides some nice wrappers around DBUnit which modernizes the API and makes DBUnit much more pleasant to work with.

If you haven't checked out Unitils yet, you definitely should. Unitils is often overlooked and under-appreciated.