Best way to access a sqlite database file in a web service.

Question

First question from me on stack overflow.

I have created a java web application containing a web service using netbeans (I hope a web application were the correct choice). I use the web application as is with no extra frameworks. This web service use a sqlite JDBC driver for accessing a sqlite database file.

My problem is that the file path end up incorrect when I try to form the JDBC connection string. Also, the working directory is different when deploying and when running JUnit tests. I read somewhere about including the file as a resource, but examples of this were nowhere to be seen.

In any case, what is the best way to open the sqlite database, both when the web service is deployed and when I test it "locally"?

I don't know much about web services, I just need it to work, so please, help me with the technicalities.

Update To put this a litle bit in context, some "println" code gives this:

Printing the work directory from a simple JUnit test gives

C:\MinaFiler\Work\SOA\BusTimetableWS

Invoking a similar web servic method returns

C:\Program Files\sges-v3\glassfish\domains\domain1

The connection string is formed from prepending "jdbc:sqlite:" to the path which at the moment is absolute:

C:\MinaFiler\Work\SOA\BusTimetableWS\src\java\miun\bustimetable\database\sqlit\BusTimetableWS.db

However, this fails because my tests throws exceptions stating database tables doesn't exist although they really do, I can see them with sqlite3.exe .

Answer 1

One way would be to use a config file that you can read and fetch your connection string from there.

I'm sure the framework you are using has some kind of standard way of saving configurations.

Another option would be to place the db in a known relative path from your main execution files. Then when executed fetch your current directory, and look for the db from that path.

Answer 2

In any case, what is the best way to open the sqlite database, both when the web service is deployed and when I test it "locally"?

The web service should use a DataSource to retrieve a connection from a connection pool configured at the application server level. In your unit test, use whatever you want (a standalone connection pool, a direct JDBC connection).

But in both cases, why don't you use an absolute path to the database file in your jdbc url? From How to Specify Database Files:

jdbc:sqlite:C:/work/mydatabase.db

The working directory wouldn't matter if you do so.