BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News H2 1.0 Database by Hypersonic Creator is Out

H2 1.0 Database by Hypersonic Creator is Out

Bookmarks
HSQLDB creator Thomas Mueller has released 1.0 final of H2, his pure Java database successor to HSQLDB. H2's focus is to be best database for the lower end (low number of concurrent connections, embedded usage).    H2's features are comparable to MySQL and PostgreSQL; it has views, subqueries, triggers, clustering, role based security, encryption, user defined functions, disk and in-memory usage, embedded and client-server usage, referential integrity, scrollable result sets, schema support, transaction isolation. There are a few tools like the browser based Console application (now with auto-complete).  A few things are still missing: H2 currently only offers table-level locking, full outer joins are not supported yet, the ODBC driver is only 'experimental' so far, and the standard API for distributed transactions (two-phase commit) is incomplete, however for most use cases these may not be critical.

"It makes sense to use H2 whenever you need an embedded database in your Java application. Or if you need a database for regression testing, or to learn SQL or the JDBC API. Or if you need a high-performance database," H2 creator Thomas Mueller told InfoQ., who also made a comparison to HSQLDB and Derby:
There are some architecural problems in HSQLDB that are hard to fix or work around: The opening / closing a database in HSQLDB can be very slow if the database is big, because the whole database is always backed up. HSQLDB doesn't offer any transaction isolation. Some operations on HSQLDB are limited by the memory (result set size, transaction size, BLOB / CLOB size). The current query optimizer of HSQLDB is really bad in my opinion. A lot of the higher level features are not supported (updatable result sets, encrypted database, data compression, computed columns, linear index, hash index, multi-dimensional index, linked table). In my view the source code of H2 is cleaner than that of HSQLDB. But the jar file of H2 is a bit bigger than the one of HSQLDB (1 MB jar file size instead of 600 KB). And for some very simple operations HSQLDB is currently a little bit faster.

The comparison between H2 and Derby / Java DB is also quite interesting. The biggest difference is, in my view, speed. Derby is just really slow (like, ten times slower) for simple operations. This didn't change since it was a standalone company (Cloudscape Inc.). In my view this performance problem of Derby is an architectural problem, and I don't think it can be changed without re-writing a large part of the code. But that's just my opinion. I know, many people will say that the speed depends on the the application and that it's easy to write a benchmark that favors one product. But my performance tests are open source, fair (in my view), and based on known algorithms like TPC-A and TPC-C (modified for the single-user case). There are a few other problems with Derby, like each table and index is stored in it's own file in Derby. Feature-wise, H2 and Derby are probably at about the same level.
There aren't plans to merge H2 with HSQLDB due to the licensing. HSQLDB is available on a BSD license whereas H2 is has a copy of the Mozilla Public License. Thomas clarified:
The main difference to the BSD license is: Under the BSD license, anybody can take the source code, change it and sell it without providing the changes back to the community. This is not possible under the MPL. It is still commercial-friendly (more friendly than GPL): You can use H2 in a commercial application without having to provide the source code of your application. You only have to provide changes in H2 code (for example, bugfixes). If a company could also add a new indexing mechanism (I don't know, for example an R-Tree index) to H2 and sell the resulting product without having to provide the source code for this added feature. The MPL is similar to the LGPL, but in my view clearer in what you can do and what not, at least for Java applications. There is still the option to dual- or tripple-license the code, for example if somebody needs the LGPL license.
Going forward, Thomas will be focused on testing, bugfixing, but also "performance optimizations, improving concurrency and compatibility with other databases."   Thomas doesn't have any plans to commercialize his work but may consider offering support should sufficient interest arise.

Rate this Article

Adoption
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

  • Hibernate support

    by Alex Popescu,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Is there H2 support in Hibernate or is it planned to be added?

    ./alex
    --
    .w( the_mindstorm )p.
    TestNG co-founder

  • Re: Hibernate support

    by Thomas Mueller,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Hi,

    Hibernate is supported. The native 'H2Dialect' is included in Hibernate version 3.2.0 (according to the Hibernate bug tracking system, issue HHH-1558, actually I didn't check myself yet). For Hibernate versions where the H2 dialect is not included, the HSQLDB dialect works for most cases. Or you can add the dialect yourself to Hibernate if you like, it is included in the H2 download.

    Thomas

  • Re: Hibernate support

    by Alex Popescu,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    This is great. I am sure I will find some time to play with it. I was quite happy using HSQLDB for testing/development purposes till now and seeing how many features are already supported in H2 will definitely make me consider it in the near future.

    ./alex
    --
    .w( the_mindstorm )p.
    TestNG co-founder

  • clustering

    by karan malhi,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    I just saw the performance metrics. According to the figures on their website, H2 is probably the fastest of all databases out there. Would it be possible to cluster H2 in embedded and server mode. I am thinking that the embedded mode could be useful for transactional caching which could be synchronized with the database in server mode.

  • xml support and xquery

    by karan malhi,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    It would be nice to have features like exporting and importing data in XML format. Also, XQuery support would be a nice feature to have. Does H2 have any such plans to support these features? If yes, then how soon can we see these features in H2

  • Re: clustering

    by Thomas Mueller,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    > H2 is probably the fastest of all databases

    While doing performance tests, I came to the conclusion that H2, HSQLDB, MySQL and PostgreSQL all perform about the same when doing the same things (there are some exceptions, PostgreSQL is a little slower, Derby is a lot slower). The problem is that for MySQL and PostgreSQL, the request always needs to jump over the 'language barrier' (transforming data to/from Java) and over the TCP/IP barrier. Even if you could use MySQL embedded in a Java app (without TCP/IP), there would be the 'native call' overhead. And caching is usually done in the database layer, so Java databases have an advantage in Java apps. So the advantage of H2 is: The base performance is good, it is written in Java, and the optimizer is good. All aspects can and need to be improved of course.

    > Would it be possible to cluster H2 in embedded and server mode.

    The 'mixed clustering mode' you described sounds very interesting! Currently this mode is not possible (clustering always goes over TCP/IP at the moment) but I will add this to the feature request list; it should be quite easy to implement.

  • Re: xml support and xquery

    by Thomas Mueller,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    What exactly to you have in mind? What are the use cases? There are some XML functions implemented in H2 to create XML documents from relational data. Do you want to 'shred' XML into tables/rows? Or store it as a CLOB (I'm currently working on automatic CLOB/BLOB compression by the way)? Or store it in some other way? What about versioning? Maybe JDBC and SQL are not the best APIs to store/manipulate/query XML, did you have a look at the JCR API, and specially Jackrabbit (jackrabbit.apache.org)?

  • Re: clustering

    by karan malhi,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Even if you could use MySQL embedded in a Java app (without TCP/IP), there would be the 'native call' overhead.


    How about if you compile the drive and application it to native code. I think GCJ has that option. In that case, the difference should not be visible , right?

  • Re: xml support and xquery

    by karan malhi,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    What exactly to you have in mind?

    Storing XML as a CLOB
    What are the use cases?

    I want to author training materials and store them as XML CLOB's in the database. Each book has chapters and I want the ability to query the XML and retrieve some chapters depending on the clients requirement (not the whole book). I dont want to read the whole XML document and then filter out the information I need. XQuery could be a possible solution. I saw something in DB2 9 which supports XQuery

    There are some XML functions implemented in H2 to create XML documents from relational data.

    Could you provide the link to those functions
    Do you want to 'shred' XML into tables/rows? Or store it as a CLOB (I'm currently working on automatic CLOB/BLOB compression by the way)?

    As a CLOB

    Or store it in some other way? What about versioning?

    Once I have retrieved the selected chapters and customized the book for the client, I will use SVN to version the pdf format of the book

    Maybe JDBC and SQL are not the best APIs to store/manipulate/query XML,

    Please have a look at this link . this is a blog entry by Ilya Stern
    ilya.typepad.com/enteprise_software/2006/08/xml...

    did you have a look at the JCR API, and specially Jackrabbit (jackrabbit.apache.org)?

    Not yet. But I will look into it. Thanks

  • Re: clustering

    by karan malhi,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Sorry, I had typos in the above. Here is what I originally meant to say :

    How about if you compile the driver and the application to native code. I think GCJ has that option. In that case, the difference should not be visible , right?

  • Re: clustering

    by Thomas Mueller,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Yes, if you compile everything to native code, there is no (at least less) 'language' overhead. My experience is that GCJ is not ready yet to be widely used, and is slower than the Sun JVM. But probably in the future the difference between Java / C# / C++ /... will get smaller and things will get easier to port. Also, it will be easier to access Java applications from other languages.

  • Re: xml support and xquery

    by Thomas Mueller,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    H2 currently support XMLATTR, XMLNODE, XMLCOMMENT, XMLCDATA, XMLSTARTDOC, XMLTEXT. See also www.h2database.com/html/functions.html under 'String functions'. I will have a look how easy it would be to implement XQuery functionality. But probably fulltext search should be implemented first.

  • using H2 with TopLink

    by David Coldrick,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Hi Thomas,

    I notice when using H2 with TopLink - where I need to specify database location in preferences.xml - if I use an embedded specification of:

    jdbc:h2:file:~/.mydir/myDB

    than the database does not get created on access.

    If I use:

    jdbc:h2:file:/home/myHome/.mydir/MyDB

    it all works.

    Any way I can make the specification a subdirectory of my home directory without hard-coding it? This is on Ubuntu 6.06.

    Thanks and regards,
    David

  • Re: using H2 with TopLink

    by Thomas Mueller,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    Hi David,

    > jdbc:h2:file:~/.mydir/myDB

    This is a good idea. I wonder why Java doesn't support this syntax directly. Anyway, I will add support for this feature if possible (not sure when yet).

    Thomas

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

BT