Java Embedded Databases Comparison

Question

I intend to develop a small (Java) application for managing my finances. I believe I need to use an embedded database, but I have no experience regarding this issue. I tried to look at some of the available products, but I can't decide which one would be more suitable for me. H2, HSQLDB, Derby and Berkeley DB seem to be good candidates, but I still don't see how they compare to each other. I appreciate your help comparing them and helping me decide which one to use.

I intend to use Hibernate for my application (unless you would recommend using DBMS-provided API), but I also want to have the ability to edit the database easily using a SQL browsing tool (modifying schema and changing data).

Thank you.

Answer 1

I'd go with MySQL. I use it extensively for personal use and it's both really easy to work with and fast. There are GUI tools available on the MySQL website for browsing/modifying/administrating databases.

Answer 2

I realize you mentioned SQL browsing, but everything else in your question makes me want to suggest you also consider DB4O, which is a great, simple object DB.

Answer 3

I personally favor HSQLDB, but mostly because it was the first I tried.

H2 is said to be faster and provides a nicer GUI frontend (which is generic and works with any JDBC driver, by the way).

At least HSQLDB, H2 and Derby provide server modes which is great for development, because you can access the DB with your application and some tool at the same time (which embedded mode usually doesn't allow).

Answer 4

HSQLDB is a good candidate (the fact that it is used in OpenOffice may convinced some of you), but for such a small personnal application, why not using an object database (instead of a classic relationnal database) ?

I used DB4O in one of my projects, and I'm very satisfied with it. Being object-oriented, you don't need the whole Hibernate layer, and can directly insert/update/delete/query objects ! Moreover, you don't need to worry about the schema, you directly work with the objects and DB4O does the rest !

I agree that it may take some time to get used to this new type of database, but check the DB40 tutorial to see how easy it makes working with the DB !

EDIT: As said in the comments, DB4O handles automatically the newer versions of the classes. Moreover, a tool for browsing and updating the database outside of the application is available here : http://code.google.com/p/db4o-om/

Answer 5

Either

• HSQLDB - Used by OpenOffice, tested and stable. It's easy to use. If you want to edit your db-data, you can just open the file and edit the insert statements.

or

• H2 - Said to be faster (by the developer, who originally designed hsqldb, too)

Which one you use is up to you, depending how much performance and how much stability you need.

The developer of H2 has put up a nice performance evaluation:
http://www.h2database.com/html/performance.html

Answer 6

What criteria will you use to evaluate these ? If you don't know yet, then you don't need to decide right now. Try to make your application as database-implementation-agnostic as you can - providing the appropriate wrappers, data access objects etc., and make this decision when you have all the facts to hand and you have to decide.

If you're using relational databases and SQL then the above shouldn't be too hard (using JDBC etc). Make sure you have plenty of surrounding tests so that when you want to switch between databases, you can determine that your application's functionality remains the same.

I ran into the same issue some time ago. I didn't know which database to go for, so my first solution used Derby (or HSQLDB?), and I was later able to switch to HSQLDB (or Derby ? Can't remember which solution worked) once I'd determined where I had issues (relating to performance) and which solution would really work for me.

Answer 7

Most things have been said already, but I can just add that I've used HSQL, Derby and Berkely DB in a few of my pet projects and they all worked just fine. So I don't think it really matters much to be honest. One thing worth mentioning is that HSQL saves itself as a text file with SQL statements which is quite good. Makes it really easy for when you are developing to do tests and setup data quickly. Can also do quick edits if needed. Guess you could easily transfer all that to any database if you ever need to change as well :)

Answer 8

I'd be interested in any opinions with regard to reliability of any of these embedded DBs.

How likely is the DB to get corrupted if the power cable is pulled out in the middle of a write operation? A lot of vendors tout their performance stats, but sometimes the performance is at the cost of reliability, or useful features such as foreign key constraints or transactions.

Answer 9

HSQLDB may cause problems for large applications, its not quite that stable.

The best I've heard (not first hand experience however) is berkleyDB. But unless you opensource it, it will cost you an arm and a leg to use due to licensing...see this http://www.oracle.com/technology/software/products/berkeley-db/htdocs/licensing.html for details.

ps. berkleyDB is not a relational database in case you didnt know.

Answer 10

More useful answers can be found in this previous question:

http://stackoverflow.com/questions/57102/embedded-java-databases