CODEWITHSUNDEEP
javahsqldbembedded-database
smeeb
smeeb

Reputation: 29507

Initializing an HSQLDB instance and then using it in an app

I am brand new to the concept of embedded databases and have chosen HSQLDB to be the embedded DB for my Java app. I think I am fundamentally not understanding something: nowhere do I see how/where to:

With a non-embedded ("normal") DB, I would first use a DB client to connect to the database, and CREATE the db_myapp DB as well as any tables it should have. My app would then expect those tables to exist at runtime.

But with HSQLDB, I have no such DB server to connect to, so I don't see how/where I can create these databases/tables/credentials ahead of time, before my app runs.

And maybe that's exactly what an "embedded" DB does; perhaps its an entire DB embedded inside a JDBC driver? In any event, I still need a way to accomplish the 3 things listed above.

The only thing I can think of is to run some initialization code every time that my app starts up. This code would check for the existence of these constructs, and if they don't exist, then it would create them.

There are several problems here:

Upvotes: 1

Views: 990

Answers (1)

allTwentyQuestions
allTwentyQuestions

Reputation: 1240

The short answer is that you're pretty much on the right track.

Connecting to the embedded database is really no different from connecting to a normal db server, except that the connection string is a bit different. This section has information on that. The thing is that you don't really have separate 'databases' to choose from, it's just specified in the connection string. For the connection:

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb", "SA", "");

This will give you a connection to an embedded database engine that persists the data in the file at /opt/db/testdb. The default username for an embedded database will always be 'SA' with no password. I honestly don't know if it'll work, but if you really need to set a different password, you can try executing ALTER USER SA SET PASSWORD <newPassword>. It'll probably work...

As far as creating tables and such, there's a couple of way of going about this, depending on whether the database will be persisted as a File or in memory. Often times, embedded dbs get used for pretty simple data, and so the tables get created by executing a statement right after initializing the connection. CREATE TABLE IF NOT EXISTS ... is the usual way of doing things. This allows you to create a table only if it doesn't already exist.

If you're working with a file-base database, then hsqldb gives you another option. Take a look at this documentation about accessing a database using their tools. This would allow you to create a file-base database ahead of time, and set things like username/password and setup all your tables. Then you can just copy over the resultant file to be used by your application. Then everything would be setup before your application connects to it.

So ultimately, you have the option to go either way. You can either have your application set everything up when the connection is initialized, or you can set it up manually ahead of time. My preference is to have the application set it up in code simply because then your table definitions are kept closer to the code that actually uses them. I haven't used an embedded database like that for really complex data, though, so I can't honestly say how well that scales.

Upvotes: 2

Related Questions