What database would be advisable for a Java program that should operate on a db file of some sort and...?

Question

My 5 primary goals are:

1. export to file (preferably including csv)
2. import from file
3. perform operations on a database file rather than an internal database or a database located in a fixed location on the computer
4. add attributes and tables to any file (a big table with an entry for every file, and several tables per file) [including adding columns to the tables dynamically]
5. operate well on any system without much hassle

I'm not sure what database is out there to let me use a DB in Java without having to install a Database system directly. However, this will also be my first time using a database to organize files rather than a homebrew! So I could be completely off base.
Is there a database solution that addresses all the above well and that's pretty quick to learn? Would MySQL or SQLite be good choices? I want to avoid messing with passwords and stuff and would like users to be able to exchange databases as a single file without any hassle.

Thanks!

Answer 1

I'd suggest one of the pure Java databases. They are generally lightweight, fast, and because they don't have any native code dependencies it will be much easier to build and deploy your application.

They are typically used in an embedded mode which brings quite a few benefits: you just import the right .jar file into your application and then your application gains the capability to act as a databse server (i.e. your application will access the database files on the disk directly, there is no separate server process to configure and manage).

Ones to look at probably include:

• H2 - http://www.h2database.com/html/main.html
• Derby - http://db.apache.org/derby/
• SQLJet - Pure Java version of SQLite - http://sqljet.com/

See also:

• Java Embedded Databases Comparison
• Lightest Database to be packed with an application

Answer 2

For MySql, you need to do an installation. If application doesn't need any big database then SQL Lite looks like a better choice considering you want to exchange database as a single file.

Although I found that MySql is so good documented and its importing/exporting DB operations are just one line command, it shouldn't be any real learning curve if you are already familiar with SQL.