How to use SQLite database inside jar file?

Question

I can't figure out how to include a SQLite database within a Java .jar file for deployment (via Java WebStart).

The database does not need to be updated at runtime... it is essentially a glorified configuration file.

Using Eclipse, by the way.

Georginageorgine

Tricky. Seems to be much easier with Apache Derby or similar embeddable DBs. – Balefire

Answer 1

Another good option to consider could be derby, javadb. It's bundled with java now and is pretty fast to setup and has good performances.

Regards, Stéphane

Tailband

this, at least so far, seems to be the right solution. thanks. – Georginageorgine

Answer 2

SQLite requires the separate files because of how the engine ensures locking and ACID. Even without the need to update the database, SQLite is only designed to work with direct file access (or memory backing, but not one supplied from Java or another host. SQLite is showing it's not a pure Java solution here nor is it designed for systems not utilizing the basic system IO subsystem(s).

The SQLite data files could be extracted from the JAR to a temporary location at start (and put back in there at the end, if needed) - but other than something similar to this approach, not possible. The task itself isn't complicated, but is another thing that needs to be done and may require additional privileges not available in some environments.

Insphere

Answer 3

Have you considered HSQLDB? It may very well be faster to get HSQLDB working than to figure how to do this with sqlite. I know you said it's read only, but this is obviously not usually the way you'd configure sqlite.

Other option is storing a dump of the sqlite database

sqlite3 .dump

in your jar file, reading it in at startup from the classpath via getResourceAsStream(), and creating the database on application startup. obviously this adds startup overhead and is not ideal, but might work in a pinch.

edit: just found code to actually create the DB programatically here. depending on the size of your db, storing the SQL (compressed if necessary) and creating the DB on startup might not be that horrendous.

Lasley

This is, at least, a clever solution, and seems straightforward. For this application, startup times have some flexibility. I'll give this a shot and get back to you. Thanks! – Georginageorgine

Answer 4

Put the SQLite database into a resource folder (package) within the project.

Connect like this:

public class SQLiteProject extends javax.swing.JFrame {
    :
private void formWindowOpened(java.awt.event.WindowEvent evt) {                                  
    CModel.connectDB(getClass().getResource("/resources/database.sqlite").toString());
}                                 


public class CModel {
    :
public static Connection connectDB(String cstr) {
    try {
        Class.forName("org.sqlite.JDBC");
        Conn = DriverManager.getConnection("jdbc:sqlite::resource:" + cstr);
        JOptionPane.showMessageDialog(null, "Connect!");
        return Conn;
    } catch (ClassNotFoundException | SQLException | HeadlessException e) {
        JOptionPane.showMessageDialog(null, e);
    }
    return null;
}

Download and add the sqlite-jdbc-3.8.7.jar library using Add JAR/Folder ...

Use Clean and Build to build the project (creates dist folder und copies library)

Make a fat-jar by using: http://www.oracle.com/technetwork/articles/javase/single-jar-141905.html

Candiecandied