How do I include my embedded SQLite db in or with my executable Jar?

I'm really struggling to with my Intellij jar (executable). I'm ready to hand it out for some friendly  user testing but Intellij won't handle the embedded SQLite db. So I was hoping someone would have a suggestion or solution.

The embeded DB is called research.db. Here are my details:

Code referencing the DB:
private static final String SQLConn = "jdbc:sqlite:./src/research.db";
Where it is located:
src > research.db

Problem:
I cannot get the Jar to run because it cannot access the DB. What I thought I could do was create a "sqlite" folder under the src folder and Intellij would embed (literally) the DB within the JAR. I don't think that is possible  now. So I moved it where it was previously, back within the src folder.

What I've done:
I've been on a number of sites each with similar steps using Intellij (see attached image). I reached out to Intellij Community support then eventually was asked to enter a ticket, and they found the jar works but the database is the issue causing it not to launch.

I've never, ever, created an installer in my short coding career, but I don't know if that is what needs to happen and edit the SQLConn (above code) in my application code. I did something similar but smaller than this in NetBeans with a DB and had no issue, but the DB was outside the JAR and had to be within the same folder. So I'm not sure what is different about Intellij.

I'm open to any suggestion or pointer in the right direction.

Thanks as always!

This is why you don't let your IDE do your thinking for you. In production, you don't have an IDE anymore.

To talk to an embedded SQLite db within an executable jar, you need two things:

1. The SQLite JDBC driver
2.. SQLite implementation code.

I'm not sure if SQLite has a native Java implementation jar or if you have to have a native-code rathole class (which would be part of the JDBC driver). The "rathole" would be to communicate with an SQLite DLL or .so library.

If it's all available in native Java, you'd just need to ensure that both driver and DBMS code were made part of your executable JAR, and here please note that a JAR embedded within a JAR is not part of the outer JAR's classpath - you need special logic to make that possible (Maven has a plugin for it).

If you need to talk to non-Java SQLite code, then the DLL/so has to be part of your library lookup path, and that's a complicated (and OS-dependent) affair that I'll have to ask you to ask Google about.

Just to add to Tim's post: in the "target" system where the executable jar is going to run, the database cannot be inside the jar. It must be in a directory on the target system. So yeah, you just can't send out the jar file and be done.

As for writing an installer, I'd suggest for your first attempt just sending the executable jar and the database and have the person at the target system receive them and put them somewhere. The person could be you and the target system your system, only your test would be to install them in a directory which has nothing at all to do with your IDEA directories.

Hi Paul and Tim!
Based upon your comments, you may or may not approve of this approach, but here is what I have done and provided the code below. It took all day to research how and to create it, but I built an Install Class to verify and/or install the database. Since the JAR file in essence is like a ZIP file, the main class of my app (i.e ResearchApp) will implement the Install class and then verify or install the database to the users home path, validating and managing folder permissions. So far, it has worked on a couple user systems but I'm sure I may run into an issue being new to java and attempting at my best to manage all possible exceptions.

In essence, the first thing the Install will do is capture path where the JAR resides and then call to the users home path and concatenate an application folder, then check if the folder path and file exists. If not, it will iterate through the JAR to find the DB then try to confirm and or manage folder/file permissions if it fails to create. This database will come with some initial data samples. If successful, the later attempts to skip the install if the Install object class validates successfully. Thereafter, database connections are established success or failure.

I'm certainly still open to thoughts or suggestions on this attempt.

ResearchApp (main) call:
public class ResearchApp extends Application {    public static void main(String[] args){        new Install();        launch(args);    }

Install Class:
class Install {    Install(){        String jarPath = new File(Install.class.getProtectionDomain().getCodeSource().getLocation().getPath()).toString();        String destinationPath = System.getProperty("user.home") + "\\ResearchDB";        if(!ValidateInstall(destinationPath)){            RetrieveResources(jarPath, destinationPath);        }    }    private boolean ValidateInstall(String path){        File file = new File(path + File.separator + "research.db");        return file.exists();    }    private void RetrieveResources(String jarFile, String destDir){        try{            JarFile jar = new JarFile(jarFile);            java.util.Enumeration enumEntries = jar.entries();            while (enumEntries.hasMoreElements()) {                JarEntry file = (JarEntry) enumEntries.nextElement();                File jFile = new File(file.toString());                if (jFile.getCanonicalFile().getName().toLowerCase().equals("research.db")) {                    File f = new File(destDir + File.separator);                    if (!f.exists())                        if (!f.mkdir())                            if(SetPermissions(f.toString()))                                if(!f.mkdir())                                    JOptionPane.showMessageDialog(null, "Application setup failed. User may not have " +                                            "folder permissions for the home directory:\n" + f.toString(), "Error: Installation",JOptionPane.ERROR_MESSAGE);                    InputStream is = jar.getInputStream(file); // get the input stream                    FileOutputStream fos = new FileOutputStream(f + File.separator + jFile.getCanonicalFile().getName());                    while (is.available() > 0) {                        fos.write(is.read());                    }                    fos.close();                    is.close();                }            }        }catch(IOException ex){            ex.printStackTrace();            JOptionPane.showMessageDialog(null, "Application setup failed. User may not have " +                    "folder permissions for the home directory:\n" + System.getProperty("user.home"), "Error: Installation",JOptionPane.ERROR_MESSAGE);            System.exit(0);        }    }    private boolean SetPermissions(String path){        File file = new File(path);        if(!file.canExecute() && !file.canRead() && !file.canWrite()){            if(!file.setExecutable(true) || !file.setReadable(true)  || !file.setWritable(true)){                JOptionPane.showMessageDialog(null, "Access Denied: The ResearchDB location could not be created.", "Database Creation Failed", JOptionPane.ERROR_MESSAGE);                System.exit(0);            }        }        return true;    } }

A JAR file isn't "like" a ZIP file. It is a ZIP file. The only differences being the file extension and the presence of the META-INF directory where Java looks for magic properties. You can literally create a JAR using only the ZIP utility.

I think what Paul was getting at is that a ZIP file is not simply a subfile tree, even though in some cases an OS might make it look so. In particular, the only way in Java to "write" to a ZIP (JAR) file is to create a whole new ZIPfile.

Nothing wrong with having logic to look for and initialize a SQLite database if needed. In fact, that's pretty much standard procedure in Android apps.

But please lose the Windows file delimiters. You can almost always use "real" slashes in file paths in Java, even for Windows files. Like so: "C:/Users/myself/databasex.db". It's both more portable (works on Windows, Linux and MacOS) and it's less likely to zap you since you don't have to remember to double-up on backslashes.

And it's easy to play with files in directories:

File myDb = new File( databaseDirectory, "my.db" );

Your file logic is much too complicated. The File class and File Exceptions can make things much simpler if you let them.

Hey, I'm all about simple, efficient and clearly understood! I like your signature line "An IDE is no substitute for an Intelligent Developer." I just need to get there. I'm leaning on Intellij tohelp me find efficiencies at the moment. Your suggestion is good and I agree (for what that is worth) that adding back in the back slashes. However, I did not pickup on the sample code for the file directory and file name you provided. I like that.

Everyone is so helpful on this site. I was attracted to the idea new Java users could learn here with "no question too simple", instead of "why are you doing that stupid".

Thanks again Tim!

I'm afraid that signature is a rather bitter jab at managers who think they can hire the dumbest, cheapest monkeys they can get and expect the IDE to compensate for their deficiencies. But really, you can get into serious trouble using an IDE to do your work for you if you don't know what it's producing. I know that, alas, from bitter personal experience.

One of the problems with many traditional Internet newsgroups was that when people asked "dumb" or often-repeated questions, the Lords of the Forums would typically flame them to the point of physic scarring. One of our most fundamental rules here is that you don't do that.

In more recent times, a certain alternative style of forum has emerged that is frequently used as a copy/paste resource, but it, also has suffered from a reputation for traumatizing what they (sometimes wrongly) interpreted as duplicate questions, dumb questions, and questions that didn't meet the qualifications of the forum even if the answers might have been of interest anyway. They've been trying to remedy that though.

In the mean time, I'll often answer the "same" question over and over and over. Partly because I can't be bothered to put it in the Wiki, but also because everyone has different holes in how they understand things, and sometimes a personalized answer is simplly better.

So we're glad we can be of help!

Tim Holloway wrote:Your file logic is much too complicated.

Example: consider lines 32 to 38 in your posted code. Java 8 has a java.nio.file.Files class which allows you to replace all of that code by one or maybe two lines of code which you don't have to test nearly as much.

Tim Holloway wrote:
In the mean time, I'll often answer the "same" question over and over and over. Partly because I can't be bothered to put it in the Wiki, but also because everyone has different holes in how they understand things, and sometimes a personalized answer is simplly better.

I do that largely because I never have access to any links to the "stock" answer.
Well, that and sometimes I get to find out that the "stock" answer is no longer necessarily true.
Indeed it has happened that it turned out that the "stock" answer was never actually true, and was little more than an IT myth.