How to control SQLite 3 database files directory after installing my app (Packaged using JPackage)-CodePudding

I'm using SQLite 3 as a database in my JavaFX app, and in goal to package everything in one file (so that the user won't need to do anything other than launch the .msi file to install it), I put database files in the resources, and package my whole application using JPackage, the problem is after installing it, the database files are getting saved in C:/Users/username/AppData/Local/Temp which is not a good place to store important data (i should mention that my app directory is as expected in C:/Users/username/Program Files).

I'm really not sure why they are getting separated (app directory, and database directory) and why the database files are getting saved in a temporary file, any way to change their directory?

Details :

My database connector:

 private Connection connection;

    private CashManagementDBConnector(){
        try {
            connection = DriverManager.getConnection("jdbc:sqlite::resource:DataBases/CashManagement.db");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

JDBC

I'm using xerial SQLite JDBC 3.36.0.3

JDK version

java 17

Thought

so far I think the issue is from the JDBC

CodePudding user response：

SQLite treats resource connection jdbc:sqlite::resource:blah.db as read-only, so it makes a copy of the resource it finds as a new database in the directory:

System.getProperty("java.io.tmpdir");

If you don't want this, at start-up you could have your code check for a specific db file in any location such as:

Path mydb = Path.of(System.getenv("LOCALAPPDATA"), "MyAppName", "blah.db");

If above file does not exist you could copy the master resource file over it:

final String resName= "/DataBases/CashManagement.db";
if (!Files.isRegularFile(mydb)) {
    Files.createDirectories(mydb.getParent()); // if using subdir for app
    try(var in = YourClass.class.getResourceAsStream(resName)) {
        Objects.requireNonNull(in, () -> "Not found resource: " resName);
        Files.copy(in, mydb);
    }
}

Then use the JDBC driver for the copy of the resource:

 try(Connection conn = DriverManager.getConnection("jdbc:sqlite:" mydb.toAbsolutePath())) {
 ...
 }