Adding embedded sqlite-db to Netbeans project

Question

I have the following question which is keeping me busy for some time now.
I am building a Java project in Netbeans and I have an embedded sqlite DB which I use in this project.
Currently the DB is located in the package src/release/.

I reference the db from the code the following way:

c = DriverManager.getConnection("jdbc:sqlite:src/release/db.db3");

When I run the project from within Netbeans it works without any problem. But when I try to build it and run the created jar-file (in the dist-folder).

I get the following error message (translated from Dutch description):

Opening connection failed: path to scr/release/db.db3:'C:\users\idxxxxx\Documents\\dist\src' does not exist

When referencing the DB in the code like this:
c = driverManager.getConnection("jdbc:sqlite:db.db3");
and adding the db-file to the root of the output-dir (so not in the jar itself), the application works partly, but some db-data is missing in my application (empty comboboxes).
So there seems to be an issue also.

My question is:

• How can I add an embedded db - sqlite in this case - in netbeans to my project so it will be part of my project?
• Where should I put the db-file and how do I reference it from within my project-code?

I don't want the enduser to see any db-file in the file(s) he will receive.
so I would like the db-file to be part of the .jar if possible.

Tnx

Answer 1

SQLite needs separate files, even without the need to update the database.

Need ensures locking and ensures database ACID properties.

SQLite from inside .jar

• SQLite is designed to work with direct file access.
• SQLite may require additional privileges not available in some environments.

The SQLite data files could be extracted from the JAR to a temporary location at start.

It is not a good choice to write the database url, directly into the program.

getConnection("jdbc:sqlite:src/release/db.db3");

Let your application look for the file. If not found: error: File db.db3 not found message. Then know the user, not the program is going wrong, but it is missing the database file.

Since you are working with java, it is easy to create dynamic url.
e.g. "jdbc:sqlite:"+PathToApp +"/data/db.db3".
Then the application knows where to copy the extracted file (db.db3 from the jar ) .

extract SQlite from .jar

• You can let java doing it for you.
• Use jdbc:sqlite::resource:
• you need the sqlite-jdbc JAR, so extract the JAR file and add into the application JAR.

DB files will be extracted to a temporary folder System.getProperty(“java.io.tmpdir”).

e.g.

 [...]
 import org.sql.*;
 import org.sqlite.*; 
 [...]
 try {
    Class.forName("org.sqlite.JDBC");
    SQLiteConfig config = new SQLiteConfig();
    config.enableFullSync(true);
    config.setReadOnly(false);
    SQLiteDataSource ds = new SQLiteDataSource(config);
    ds.setUrl("jdbc:sqlite::resource:"+
              getClass().getResource("db.db3").toString());
    conn = ds.getConnection();
 }catch(SQLException se)
 {
  System.out.println("SQLError: "...");
 }

Update oct. 2014

org.sqlite SQLiteConfig + SQLiteDataSource

2014 October 8th: sqlite-jdbc-3.8.6.jar Updated to sqlite 3.8.6