How to use H2 with jOOQ

Living through the latest developments of the H2 and HSQLDB databases has been very exciting for me as jOOQ developer. Those two databases have a common heritage and share a lot of functionality. Both are progressing at a high pace with a very active community around their respective lead developers. I have recently posted an article about how those databases treat variable binding in a similar manner, one with respect to the other. Both infer a lot of types at compile time, but only few at bind / execution time:

While these details may seem like a minor lack of functionality compared to the big ones (mainly DB2, Oracle, Postgres, SQL Server, Sybase) the two databases really make it up in terms of speed and flexibility. Both H2 and HSQLDB mimick a big set of functions, syntax clauses and other specialties from the “big” databases, which means they can easily be used as test database on integration tests systems or in development environments. This is mainly true for mimicking

  • MySQL
  • Ingres
  • Oracle
  • Postgres

A bit less though, for

  • DB2 (Type system is probably too strong to mimick)
  • SQL Server (T-SQL is a bit different from SQL-92)
  • Sybase SQL Anywhere (T-SQL again…)

When running integration tests for jOOQ, I really like the fact that H2 and HSQLDB are embeddable and high-performing Java databases as well. In the near future, I want to roll out a fully-fledged ready-to-run integration combining any of these tools:

  • Play ! Framework, Wicket, Vaadin as the GUI layer
  • jOOQ as the intermediary
  • H2 / HSQLDB as the data layer

Until then, I’m proud to see the H2 tutorial section about how to use jOOQ with H2:

Leave a Reply