Testing JDBC code

I'm using a SingleConnectionDataSource and JdbcTemplate to run some sql. This is not the main application, but it is still critical. Whats the best way to test my code? Ideally, I'd want the tests to run independant of a db. I guess this is not really specific to spring. But since the spring community promotes testable code, I thought its OK to ask here.

Maybe some kind of mock Connection object someone is using to test jdbc code?

Thanks
Srini

I would still test real db code against a real db of some sort. Testing DAOs against a fake db connection defeats the purpose.

You do have lightweight alternatives like HSQLDB, Cloudscape, or freely usable but more heavyweight db servers like PostgreSQL or MySQL...

Hi, Srini

I have been doing a little bit of testing using in-memory databases with Hibernate. The code creates a new database per test. It runs reasonably fast, as it uses Hypersonic SQL in-memory:

Code:

---

private SessionFactory sessionFactory;

public void setUp() throws Exception {
    Configuration config = new Configuration();
    config.setProperty("hibernate.connection.driver_class", jdbcDriver.class.getName());
    config.setProperty("hibernate.connection.url", "jdbc:hsqldb:mem:persistentTest");
    config.setProperty("hibernate.dialect", HSQLDialect.class.getName());
    config.addClass(TestClass.class);

    SchemaExport export = new SchemaExport(config);
    export.create(true, true);

    this.sessionFactory = config.buildSessionFactory();
}

---

If using Hibernate is not an option, you can try creating a seed database and loading it with HSQLDB's :res: URL. See my blog for a more thourough analysis.


~Johannes