Embedded java databases

Question

I would like to get opinions or suggestions regarding embedded databases in Java. In particular I was looking at H2, HSQLDB and Derby. Have you use any of these in a production project? Do you have comment or recommendations to select one over the others?

Thanks

Edit: I am currently evaluating these options to use in our internal developments, so I don't have a specific use case in mind. One of the possible uses I am evaluating them for is a desktop application that uses the database as a local repository. At some point it synchronizes with a central repository (in this case DB2). It is a store and forward architecture. Anyway, this is just a possibility to guide your answers, Basically I’m looking for your experiences using these tools.

Answer 1

I am a big fan of DB4O for both .Net and Java.

Performance has become much better since the early releases. The licensing model isnt too bad, either. I particularly like the options available for querying your objects. Query by example is very powerful and easy to get used to.

Answer 2

We use HSQLDB in production as a "no-configuration" option for our application. It allows people to trial without the hassle of setting up a real database.

However we do not support it for normal use. The reasons are several:

1. Slows down proportionally to the size of the data.
2. Difficult to access outside of our app (e.g. for custom reports).
3. Transactions / disk-sync is difficult to get right, so it's easy to lose data.

For at least (2) and (3), there are ways around it but it's difficult; it's much easier to e.g. install MySQL.

Answer 3

Java DB (Sun's distribution of Apache Derby) now ships in JDK 6!

I've been wanted to do something like Jason Cohen and have been thinking this looks like the easiest way being in the JDK distro (which of last week is now a requirement for my app). Or maybe I am just lazy that way.

Answer 4

I use Apache Derby for pretty much all of my embedded database needs. You can also use Sun's Java DB that is based on Derby but the latest version of Derby is much newer. It supports a lot of options that commercial, native databases support but is much smaller and easier to embed. I've had some database tables with more than a million records with no issues.

I used to use HSQLDB and Hypersonic about 3 years ago. It has some major performance issues at the time and I switch to Derby from it because of those issues. Derby has been solid even when it was in incubator at Apache.

Answer 5

I have used Derby and i really hate it's data type conversion functions, especially date/time functions. (Number Type)<--> Varchar conversion it's a pain.

So that if you plan use data type conversions in your DB statements consider the use of othe embedded DB, i learn it too late.

Latest Derby Version data type conversions

Answer 6

neo4j is:

an embedded, disk-based, fully transactional Java persistence engine that stores data structured in graphs rather than in tables

I haven't had a chance to try it yet - but it looks very promising. Note this is not an SQL database - your object graph is persisted for you - so it might not be appropriate for your existing app.

Answer 7

H2, derby

but it really depends on what you're using it for.

Answer 8

I'd go with H2, the performance is meant to much better than Derby. Read http://www.h2database.com/html/performance.html for more info.

Answer 9

I guess I'm a little late (a lot late;-)) to this post, but I'd like to add Perst, an open source, object-oriented embedded database for Java &.NET. for your consideration. Perst is an open source / dual license embedded database for Java. The distribution is compatible with Google's Android platform, and also includes Perst Lite for Java ME. We've even built an Android benchmark and produced a whitepaper on the subject...you can take a look here: http://www.mcobject.com/index.cfm?fuseaction=download&amp;pageid=581&amp;sectionid=133

All the best, Chris