Using SQL to Calculate the Popularity (on Stack Overflow) of Derby, H2, and HSQLDB

Few people know about this very very awesome feature of the Stack Exchange platform. The Stack Exchange Data Explorer

se-data-explorer

To be found here:
http://data.stackexchange.com

As you may know, much of the Stack Exchange platform runs on SQL Server (interesting architecture details here: http://stackexchange.com/performance), and the team has had the courtesy of making a lot of data publicly available through a SQL web API. Here’s the schmema that you can query:

se-data-explorer-schema

Using a running total to calculate cumulative daily questions per tag

The amount of analytics possibilities with such a public schema are infinite. Today, we’ll look into a question that has been interesting for a lot of users in the past: What is the most “popular” Java in-memory database among Derby (also known as Java DB, which ships with the JDK), the popular test database H2 (see also our interview with Thomas Müller, its creator), or HSQLDB.

What we’d like to do is sum up the number of questions per database, up to any given date. This should give us one of those nice exponential curves that managers like so much.

Here’s the SQL query that we’ll run:

SELECT 
  d,
  SUM(h2)     OVER (ORDER BY d) AS h2,
  SUM(hsqldb) OVER (ORDER BY d) AS hsqldb,
  SUM(derby)  OVER (ORDER BY d) AS derby
FROM (
  SELECT 
    CAST(CreationDate AS DATE) AS d, 
    COUNT(CASE WHEN Tags LIKE '%<h2>%'     THEN 1 END) AS h2,
    COUNT(CASE WHEN Tags LIKE '%<hsqldb>%' THEN 1 END) AS hsqldb,
    COUNT(CASE WHEN Tags LIKE '%<derby>%'  THEN 1 END) AS derby
  FROM Posts
  GROUP BY CAST(CreationDate AS DATE)
) AS DailyPosts
ORDER BY d ASC

A short explanation:

The nested select "DailyPosts" creates a PIVOT table with the aggregated number of questions per database and date. We could have used the SQL Server PIVOT clause, if the Stack Exchange platform had stored tagging information in a normalised form, but the equivalent COUNT(CASE) expressions work just as nicely (see also our article about PostgreSQL’s aggregation FILTER clause for more inspiration).

Now, that we have the number of posts per tag and day, all that’s left to do is sum up those numbers from the first day to any given day. That is often also called a “running total”, which can be calculated very easily using the SUM() OVER() window function.

Now we’re done. You can run and play around with this query here:
http://data.stackexchange.com/stackoverflow/query/469392/java-in-memory-database-popularity-by-time

The raw result is not very interesting. It’s a lot of numbers and dates. But if we plot that result in a graph / chart, we’re getting this nice-looking curve here:

se-data-explorer-derby-h2-hsqldb

As we can see, all three databases are roughly equivalent in terms of “popularity”, although H2 seems to be catching up momentum while HSQLDB is on a slight decline.

(Obviously, this “popularity” is not representative of true market share. More questions might just mean that people struggle more with the technology, or – less skilled people are using it).

Have fun further exploring the Stack Exchange Data Explorer:
http://data.stackexchange.com

(all Stack Exchange Subscriber Content is licensed under the terms of the CC BY-SA 3.0 license. For more details, see: http://stackexchange.com/legal)

Further articles

Further articles that are interesting in the context of the displayed query:

jOOQ Tuesdays: Thomas Müller Unveils How HSQLDB Evolved into the Popular H2 Database

Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics.

tom_grayscale

We have the pleasure of talking to Thomas Müller in this fifth edition who will be telling us about the exciting history of Java’s most popular embedded database H2

Hi Thomas – Your H2 database is virtually everywhere. It has become many Java developers’ favourite integration test database. How did it grow so popular?

I guess because it’s easy to use, relatively fast, and, up to some point, compatible with popular databases.

I understand that you have a common, personal history with HSQLDB, previously also known as Hypersonic SQL. How and why did H2 evolve from HSQLDB?

Back in 1998, I wanted to learn Java. For fun, I implemented the relatively new skip list algorithm, added a SQL interface, and published it as open source. I got feedback from people who thought it’s useful, so I continued and gave it the name Hypersonic SQL. In 2000 I got a job offer from a Silicon Valley startup, PointBase Inc. The plan was to continue with Hypersonic SQL, and keep it open source. But after I started, the company decided it’s better if I stop. This was a surprise to me. I told them I can’t prevent others from continuing. And so Fred Toussi took the code and started HSQLDB with it. At around 2005, PointBase ran out of money, I wanted to go back to HSQLDB. But I felt more radical changes were needed, and it would be better to start a new project instead, which was then H2.

Very interesting historic facts! … and when will you replace Derby / JavaDB in the JDK? :-)

Hopefully never! If H2 is integrated in the JDK, Oracle would put constraints to the future of H2. I don’t want that. I want to keep H2 independent.

You’ve been in the database industry for a while. What is your most interesting anecdote that you’d like to share?

When Oracle bought MySQL, I was very surprised I got mail from the European Union with a large questionary about the merger, how it will affect the industry and competition. I don’t know how they found my work address, it is not on the web site, and they never asked me by email. And Switzerland is not even part of the European Union. H2 is a very small fish in the “database pond”, but it seems H2 does matter.

It’s a small world or small pond, I guess!

You’re one of the few developers I know who is working both on a SQL database (H2) and on a NoSQL database (JackRabbit, Adobe CRX). Tell us a little bit about how those databases compare.

They are quite different. H2 is a relational database with the traditional SQL and JDBC API, and Jackrabbit is a mix between a file system and a database, with a very different API, and a hierarchical data model. The query language is different as well: for Jackrabbit, XPath is more commonly used, even thought SQL is available as well. Both the relational and the hierarchical models have advantages and disadvantages. The hierarchical model more easily supports semi-structured, JSON style data. The relational model, on the other hand, is more “mature”, and there is more competition.

At Adobe / JackRabbit, you’re heavily involved with implementing storage algorithms in Java. Is the JVM even good at implementing low-level storage stuff?

Yes! The real advantage of Java is that a programmer can concentrate at the algorithms, and doesn’t have to spend so much time with memory management and low level stuff. That way, there is more time to improve the algorithms. And in relational databases, the most important aspect is using the best possible algorithms, for example to reduce I/O. Even for very low level CPU intensive stuff like data compression and encryption, things like concurrency and cache efficiency nowadays are more important than whether to use Java or C.

That’s an interesting thought along the lines of avoiding premature optimisation!

One last question: What problems are you working on right now?

Optimizing the database for solid state disks (SSDs) and new file system. Almost all relational databases still use algorithms optimized for rotating disks, where overwriting small blocks (for example 4 KB) was the way to go. With SSDs and Btrfs this doesn’t work well. H2 version 1.4.x (beta) already uses a new storage subsystem (MVStore) that should work well, however there is still some work needed before it is ready for production.

Other than database stuff, I’m interested in various programming topics. If I implement something that might be useful, I publish it as open source within my H2 database project, in the “tools” directory, until it is used in the database or moved to another project. I wrote a archiving utility (like zip, gzip) called “ArchiveTool” that combines de-duplication with regular compression. It is fast but compresses large directories (source code, databases) very well. As part of my work on the new storage subsystem, I came across minimal perfect hash tables. I invented a new algorithm that needs less space than all known ones (“MinimalPerfectHash”). I would like to publish a paper about that. There are plenty of interesting problems to solve.

Java 8 Friday: No More Need for ORMs

At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem.

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub.

No More Need for ORMs

Debates about the usefulness of ORM (Object-Relational Mapping) have been going on for the last decade. While many people would agree that Hibernate and JPA solve a lot of problems very well (mostly the persistence of complex object graphs), others may claim that the mapping complexity is mostly overkill for data-centric applications.

JPA solves mapping problems by establishing standardised, declarative mapping rules through hard-wired annotations on the receiving target types. We claim that many data-centric problems should not be limited by the narrow scope of these annotations, but be solved in a much more functional way. Java 8, and the new Streams API finally allow us to do this in a very concise manner!

Let’s start with a simple example, where we’re using H2’s INFORMATION_SCHEMA to collect all tables and their columns. We’ll want to produce an ad-hoc data structure of the type Map<String, List<String>> to contain this information. For simplicity of SQL interaction, we’ll use jOOQ (as always, a shocker on this blog). Here’s how we prepare this:

public static void main(String[] args)
throws Exception {
    Class.forName("org.h2.Driver");
    try (Connection c = getConnection(
            "jdbc:h2:~/sql-goodies-with-mapping", 
            "sa", "")) {

        // This SQL statement produces all table
        // names and column names in the H2 schema
        String sql =
            "select table_name, column_name " +
            "from information_schema.columns " +
            "order by " +
                "table_catalog, " +
                "table_schema, " +
                "table_name, " +
                "ordinal_position";

        // This is jOOQ's way of executing the above
        // statement. Result implements List, which
        // makes subsequent steps much easier
        Result<Record> result =
        DSL.using(c)
           .fetch(sql)
    }
}

Now that we’ve set up this query, let’s see how we can produce the Map<String, List<String>> from the jOOQ Result:

DSL.using(c)
   .fetch(sql)
   .stream()
   .collect(groupingBy(
       r -> r.getValue("TABLE_NAME"),
       mapping(
           r -> r.getValue("COLUMN_NAME"),
           toList()
       )
   ))
   .forEach(
       (table, columns) -> 
           System.out.println(table + ": " + columns)
   );

The above example produces the following output:

FUNCTION_COLUMNS: [ALIAS_CATALOG, ALIAS_SCHEMA, ...]
CONSTANTS: [CONSTANT_CATALOG, CONSTANT_SCHEMA, ...]
SEQUENCES: [SEQUENCE_CATALOG, SEQUENCE_SCHEMA, ...]

How does it work? Let’s go through it step-by-step

DSL.using(c)
   .fetch(sql)

// Here, we transform a List into a Stream
   .stream()

// We're collecting Stream elements into a new
// collection type
   .collect(

// The Collector is a grouping operation, producing
// a Map
            groupingBy(

// The grouping operation's group key is defined by
// the jOOQ Record's TABLE_NAME value
       r -> r.getValue("TABLE_NAME"),

// The grouping operation's group value is generated
// by this mapping expression...
       mapping(

// ... which is essentially mapping each grouped
// jOOQ Record to the Record's COLUMN_NAME value
           r -> r.getValue("COLUMN_NAME"),

// ... and then collecting all those values into a
// java.util.List. Whew
           toList()
       )
   ))

// Once we have this Map<String, List<String>> we can
// simply consume its entries with the following Consumer
// lambda expression
   .forEach(
       (table, columns) -> 
           System.out.println(table + ": " + columns)
   );

Got it? These things are certainly a bit tricky when playing around with it for the first time. The combination of new types, extensive generics, lambda expressions can be a bit confusing at first. The best thing is to simply practice with these things until you get a hang of it. After all, the whole Streams API is really a revolution compared to previous Java Collections APIs.

The good news is: This API is final and here to stay. Every minute you spend practicing it is an investment into your own future.

Note that the above programme used the following static import:

import static java.util.stream.Collectors.*;

Note also, that the output was no longer ordered as in the database. This is because the groupingBy collector returns a java.util.HashMap. In our case, we might prefer collecting things into a java.util.LinkedHashMap, which preserves insertion / collection order:

DSL.using(c)
   .fetch(sql)
   .stream()
   .collect(groupingBy(
       r -> r.getValue("TABLE_NAME"),

       // Add this Supplier to the groupingBy
       // method call
       LinkedHashMap::new,
       mapping(
           r -> r.getValue("COLUMN_NAME"),
           toList()
       )
   ))
   .forEach(...);

We could go on with other means of transforming results. Let’s imagine, we would like to generate simplistic DDL from the above schema. It’s very simple. First, we’ll need to select column’s data type. We’ll simply add it to our SQL query:

String sql =
    "select " +
        "table_name, " +
        "column_name, " +
        "type_name " + // Add the column type
    "from information_schema.columns " +
    "order by " +
        "table_catalog, " +
        "table_schema, " +
        "table_name, " +
        "ordinal_position";

I have also introduced a new local class for the example, to wrap name and type attributes:

class Column {
    final String name;
    final String type;

    Column(String name, String type) {
        this.name = name;
        this.type = type;
    }
}

Now, let’s see how we’ll change our Streams API method calls:

result
    .stream()
    .collect(groupingBy(
        r -> r.getValue("TABLE_NAME"),
        LinkedHashMap::new,
        mapping(

            // We now collect this new wrapper type
            // instead of just the COLUMN_NAME
            r -> new Column(
                r.getValue("COLUMN_NAME", String.class),
                r.getValue("TYPE_NAME", String.class)
            ),
            toList()
        )
    ))
    .forEach(
        (table, columns) -> {

            // Just emit a CREATE TABLE statement
            System.out.println(
                "CREATE TABLE " + table + " (");

            // Map each "Column" type into a String
            // containing the column specification,
            // and join them using comma and
            // newline. Done!
            System.out.println(
                columns.stream()
                       .map(col -> "  " + col.name +
                                    " " + col.type)
                       .collect(Collectors.joining(",\n"))
            );

            System.out.println(");");
        }
    );

The output couldn’t be more awesome!

CREATE TABLE CATALOGS(
  CATALOG_NAME VARCHAR
);
CREATE TABLE COLLATIONS(
  NAME VARCHAR,
  KEY VARCHAR
);
CREATE TABLE COLUMNS(
  TABLE_CATALOG VARCHAR,
  TABLE_SCHEMA VARCHAR,
  TABLE_NAME VARCHAR,
  COLUMN_NAME VARCHAR,
  ORDINAL_POSITION INTEGER,
  COLUMN_DEFAULT VARCHAR,
  IS_NULLABLE VARCHAR,
  DATA_TYPE INTEGER,
  CHARACTER_MAXIMUM_LENGTH INTEGER,
  CHARACTER_OCTET_LENGTH INTEGER,
  NUMERIC_PRECISION INTEGER,
  NUMERIC_PRECISION_RADIX INTEGER,
  NUMERIC_SCALE INTEGER,
  CHARACTER_SET_NAME VARCHAR,
  COLLATION_NAME VARCHAR,
  TYPE_NAME VARCHAR,
  NULLABLE INTEGER,
  IS_COMPUTED BOOLEAN,
  SELECTIVITY INTEGER,
  CHECK_CONSTRAINT VARCHAR,
  SEQUENCE_NAME VARCHAR,
  REMARKS VARCHAR,
  SOURCE_DATA_TYPE SMALLINT
);

Excited? The ORM era may have ended just now

This is a strong statement. The ORM era may have ended. Why? Because using functional expressions to transform data sets is one of the most powerful concepts in software engineering. Functional programming is very expressive and very versatile. It is at the core of data and data streams processing. We Java developers already know existing functional languages. Everyone has used SQL before, for instance. Think about it. With SQL, you declare table sources, project / transform them onto new tuple streams, and feed them either as derived tables to other, higher-level SQL statements, or to your Java program.

If you’re using XML, you can declare XML transformation using XSLT and feed results to other XML processing entities, e.g. another XSL stylesheet, using XProc pipelining.

Java 8’s Streams are nothing else. Using SQL and the Streams API is one of the most powerful concepts for data processing. If you add jOOQ to the stack, you can profit from typesafe access to your database records and query APIs. Imagine writing the previous statement using jOOQ’s fluent API, instead of using SQL strings.

jooq-the-best-way-to-write-sql-in-java

The whole method chain could be one single fluent data transformation chain as such:

DSL.using(c)
   .select(
       COLUMNS.TABLE_NAME,
       COLUMNS.COLUMN_NAME,
       COLUMNS.TYPE_NAME
   )
   .from(COLUMNS)
   .orderBy(
       COLUMNS.TABLE_CATALOG,
       COLUMNS.TABLE_SCHEMA,
       COLUMNS.TABLE_NAME,
       COLUMNS.ORDINAL_POSITION
   )
   .fetch()  // jOOQ ends here
   .stream() // Streams start here
   .collect(groupingBy(
       r -> r.getValue(COLUMNS.TABLE_NAME),
       LinkedHashMap::new,
       mapping(
           r -> new Column(
               r.getValue(COLUMNS.COLUMN_NAME),
               r.getValue(COLUMNS.TYPE_NAME)
           ),
           toList()
       )
   ))
   .forEach(
       (table, columns) -> {
            // Just emit a CREATE TABLE statement
            System.out.println(
                "CREATE TABLE " + table + " (");

            // Map each "Column" type into a String
            // containing the column specification,
            // and join them using comma and
            // newline. Done!
            System.out.println(
                columns.stream()
                       .map(col -> "  " + col.name +
                                    " " + col.type)
                       .collect(Collectors.joining(",\n"))
            );

           System.out.println(");");
       }
   );

Java 8 is the future, and with jOOQ, Java 8, and the Streams API, you can write powerful data transformation APIs. I hope we got you as excited as we are! Stay tuned for more awesome Java 8 content on this blog.

Java 8 Friday Goodies: Lambdas and SQL

At Data Geekery, we love Java. And as we’re really into jOOQ’s fluent API and query DSL, we’re absolutely thrilled about what Java 8 will bring to our ecosystem. We have blogged a couple of times about some nice Java 8 goodies, and now we feel it’s time to start a new blog series, the…

Java 8 Friday

Every Friday, we’re showing you a couple of nice new tutorial-style Java 8 features, which take advantage of lambda expressions, extension methods, and other great stuff. You’ll find the source code on GitHub. tweet this

Java 8 Goodie: Lambdas and SQL

If you’re used to writing Groovy, this may appear “so 2003” to you. We know. Groovy has known a very useful way to write string-based SQL since its early days. Here’s an example written in Groovy (see the official docs here):

import groovy.sql.Sql
sql = Sql.newInstance( 
    'jdbc:h2:~/test', 'sa', '', 
    'org.h2.Driver' )
sql.eachRow( 
    'select * from information_schema.schemata' 
) { 
    println "$it.SCHEMA_NAME -- $it.IS_DEFAULT" 
}

Note also Groovy’s built-in String interpolation, where you can put expressions into strings. But we’re in Java land, and with Java 8, things get better in the Java / SQL integration as well, if we’re using third-party libraries, instead of JDBC directly.

In the following examples, we’re looking at how to fetch data from an H2 database and map records into custom POJOs / DTOs using these three popular libraries:

As always, the sources are also available from GitHub. For these tests, we’re creating a little POJO / DTO to wrap schema meta-information:

class Schema {
    final String schemaName;
    final boolean isDefault;

    Schema(String schemaName, boolean isDefault) {
        this.schemaName = schemaName;
        this.isDefault = isDefault;
    }

    @Override
    public String toString() {
        return "Schema{" +
               "schemaName='" + schemaName + '\'' +
               ", isDefault=" + isDefault +
               '}';
    }
}

Our main method will get an H2 connection through DriverManager:

Class.forName("org.h2.Driver");
try (Connection c = getConnection(
        "jdbc:h2:~/test", "sa", "")) {

  String sql = "select schema_name, is_default "+
               "from information_schema.schemata "+
               "order by schema_name";
  // Library code here...
}

Now, how does Java 8 improve upon the jOOQ API, when using String-based SQL? Greatly! Check out the following little query:

DSL.using(c)
   .fetch(sql)
   .map(r -> new Schema(
       r.getValue("SCHEMA_NAME", String.class),
       r.getValue("IS_DEFAULT", boolean.class)
   ))
   .forEach(System.out::println);

This is how things should be, right? Note that jOOQ’s native APIs are also capable of mapping the database Record onto your POJO directly, as such:

DSL.using(c)
   .fetch(sql)
   .into(Schema.class)
   .forEach(System.out::println);

Things look just as nice when doing the same with Spring JDBC and RowMapper (note, the following still throws checked SQLExceptions):

new JdbcTemplate(
        new SingleConnectionDataSource(c, true))
    .query(sql, (rs, rowNum) -> 
        new Schema(
            rs.getString("SCHEMA_NAME"),
            rs.getBoolean("IS_DEFAULT")
        ))
    .forEach(System.out::println);

… and if you’re using Apache DbUtils, you can do almost the same:

new QueryRunner()
    .query(c, sql, new ArrayListHandler())
    .stream()
    .map(array -> new Schema(
        (String) array[0],
        (Boolean) array[1]
    ))
    .forEach(System.out::println);

Conclusion

All three solutions are more or less equivalent and quite lean. The point here, again, is that Java 8 will improve all existing APIs. The more unambiguous (few overloads!) methods accepting SAM arguments (single abstract method types), the better for a Java 8 integration.

Java 8 and SQL look very lean tweet this

Next week, we’re going to see a couple of things that will greatly improve when using the java.util.Map API

More on Java 8

In the mean time, have a look at Eugen Paraschiv’s awesome Java 8 resources page

Use jOOQ inside your H2 database

I recently became aware of an interesting use-case for jOOQ when I was optimising my own H2 database integration tests:

H2 stored functions

H2 knows two operation modes for stored functions:

  1. “Inline mode” with source code provided
  2. “Reference mode” referencing a public static method of a Java class on the databases’ classpath

The above terms are not official H2-speak. For more information, consider the documentation:

http://www.h2database.com/html/grammar.html#create_alias
http://www.h2database.com/html/features.html#user_defined_functions

Now, regardless of the operation mode, your H2 stored functions are always written in Java. There is currently no PL/SQL-like procedural language. When your function needs to access the database again for further data processing, you’re back to JDBC, and that’s a pity. That makes writing stored functions quite verbose again.

Using jOOQ within H2 stored functions

… so why not just use jOOQ within the H2 database? Here’s a simple example about how you could do that:

Write your stored function

When writing your own stored function, you can use source code previously generated by jOOQ. This means that the SQL within your stored functions (triggers, etc) will actually compile!

package org.jooq.test.h2;

import static test.generated.Tables.*;

import java.sql.Connection;
import java.sql.SQLException;

import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.tables.TBook;

public class Functions {
  /**
   * This function returns the number of
   * books written by a given author.
   */
  public static int countBooks(
    Connection connection, 
    Integer authorId) 
  throws SQLException {
    return DSL.using(connection, SQLDialect.H2)
              .selectCount()
              .from(T_BOOK)
              .where(T_BOOK.AUTHOR_ID.eq(authorId))
              .fetchOne(0, int.class);
  }
}

Declare the above method as an ALIAS to H2

CREATE ALIAS countBooks 
   FOR "org.jooq.test.h2.Functions.countBooks";

Use the function in SQL

select t_author.last_name, countBooks(id) 
from t_author

… or with jOOQ’s generated classes

jOOQ generates a Routines class containing static access to all of your stored functions. This means that also your client code can make compile-safe use of your stored functions.

package org.jooq.test.h2;

import static test.generated.Tables.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.Routines;

public class Test {
  public static void main(String[] args) 
  throws SQLException {
    Connection connection = 
      DriverManager.getConnection(
        "jdbc:h2:~/test", "sa", "");

    System.out.println(
    DLS.using(connection, SQLDialect.H2)
       .select(
             T_AUTHOR.LAST_NAME,
             Routines.countbooks(T_AUTHOR.ID))
       .from(T_AUTHOR)
       .fetch());
  }
}

The logged output of the above will be

+---------+---------------------+
|LAST_NAME|"PUBLIC"."COUNTBOOKS"|
+---------+---------------------+
|Orwell   |                    2|
|Coelho   |                    2|
|Hesse    |                    0|
+---------+---------------------+

Conclusion

jOOQ can also be used within your database, wherever Java is supported. In such a case, jOOQ acts like a PL/SQL-like extension to your favourite database’s Java stored procedure / stored function support. Give it a try, yourself!

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:

https://lukaseder.wordpress.com/2011/08/31/rdbms-bind-variable-casting-madness

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:

http://www.h2database.com/html/tutorial.html#using_jooq

iciql, a fork of H2’s JaQu

I just came across a new project in the world of SQL-simulating fluent API’s. It’s called iciql and can be found here:

http://iciql.com/

iciql has been forked by James Moger who has recently contributed to H2’s JaQu library. JaQu is a side-product of the H2 database, which currently suffers from a low priority on the H2 roadmap (see the discussion about the status of JaQu here), so it’s a good move by James to keep his “contributing momentum” going and continue developing that library. Let’s wish him luck!