How can I specify a RELATIVE JDBC SQLite url (path?)

Question

I've been trying to get this work for two days and tried all suggestions that I could find. Alas, without success, so far.

This is what I'm trying to do: I am building a small (java swing) desktop database application. I'm using Netbeans 7.0 and SQLite, and found the JDBC driver from http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC to work fine as long as I specify an absolute path in the JDBC URL:

jdbc:sqlite://Users.../lib/db/ithildin.db

Here's how it looks in Netbeans' Services -> Databases tab, using an absolute URL:

opening the connection and then the tables node shows the available tables.

But because I intend to distribute this application to other people, using an absolute path is not going to work. From what I have read here and there it seems to be possible to also specify a relative path to the SQLite database file, but I cannot get it to work whatever I try.

The strange thing is that when I create a connection in Netbeans it initially seems to work when I put both the sqlite jdbc driver jar and the sqlite database file in a /db subdirectory in /lib (in the Netbeans project directory):

and when I open the new connection it maintains that illusion -

but when I open the "Tables" node it turns out to be empty:

Similary, this connection seems valid but as soon as I try to use it - for instance in "Generating Entity Classes From Database" it tells me that there aren't any tables in that database.

Does anyone know a way out of this? I'd much appreciate it.

Lúthien

Solution

I've followed the same steps that you have and my findings suggest that the relative path in the New Connection Wizard is relative to NetBeans' working directory which is the directory that NetBeans is installed to.

For example, in your connection string you are "connecting" to a database that resides at <NetBeans-Install-Dir>/ithildin-13-11-11.db. Since this database does not exist, SQLite is creating it for you which is giving you the "illusion" that you are seeing. In my tests, the database that I was trying to connect to with a relative path was created in the NetBeans install directory.

In other words, everything is working as designed.

In your code you can use a relative path and it should be relative to the top of your project's directory. In NetBeans' Databases Service you will need to either use an absolute path to connect to the database that exists (in your project) or you will need to provide a relative path that begins at NetBeans' install directory.

My suggestion is that you use a relative path in your code and an absolute path in the New Connection Wizard.

Comments

[Lúthien]

Hi, thank you so much! I checked for an empty copy of ithildin-13-11-11.dband indeed, I found it - though not in the Netbeans install dir, but in the project root directory. That may be because I'm on a mac, and the "Netbeans install directory" is a packaged .app file. I'll try out your suggestion of the relative path in the PU and the absolute one in the DB connection tab. Thanks again!!

[Lúthien]

I just tried and it works exactly as you describe (apart from that it does not seem to take the install dir) - it turns out that a relative path in the DB connection tab does not work, but it DOES work in the PU in persistence.xml. The application seems to automatically choose the working connection if they are both OK, and otherwise it picks the OK one. So this seems to work fine like this: I hope it still works in the deployed app :)

[Jonathan Spooner]

Glad I could help. Don't forget to mark this answer as accepted so others will know that your question is answered.