Connect Java to Access DB on network drive

Question

I'm creating a desktop Java application that will connect to an access database, using ucanaccess as a driver.

The entire thing will be located on a shared network drive.

I use an absolute file path to connect to my database. I expect this database to outlast my tenure at the office. What happens when another user moves the database, or changes the name of the folder etc... I'm the only Java geek in the office, so this needs to be somewhat automated or easily doable for someone who is... well let's just say not computer literate.

I'm looking for ideas on how to get around this. I thought of opening a file dialog and having the user select the location of the file, but this seems like too much work for the kind of people I work with. It should just open...

Any help is much appreciated. Code sample below.

package databaseTest;

import java.sql.Connection;
import java.sql.DriverManager;

public class test {

    public test() {
        try {
            String driver = "net.ucanaccess.jdbc.UcanaccessDriver";
            Class.forName(driver);
            Connection cnct = DriverManager.getConnection("jdbc:ucanaccess://c:\\users\\Christopher\\Desktop\\JavaProject\\Database11.accdb", "", "");
            System.out.println("Connected");
        } catch(Exception ex) {System.out.println(ex.getMessage());}
    }

    public static void main(String[] args) {
        System.out.println("connecting...");
        new test();
    }

}

Answer 1

Suggestion:

I would suggest you to store the database related parameters/configurations

• Path to database
• Name of network shared directory
• Database Name

etc. in a properties file. You can then load the properties file using getResourceAsStream. You only need to ensure two things —

• The properties file is in a directory which is in the project build path.
• If any of the parameter(s) are changed(files moved/renamed), the corresponding value(s) in the properties file are changed also.

Here's one concrete example(tested on Ubuntu Linux).

Let's say we have a:

• MS Access database named Foo.mdb stored under a shared directory /path/to/sharedDirectory
• Shared directory named shared_dir

MyDatabaseProperties.properties:

pathToDB = /path/to/sharedDirectory
sharedFolderName = shared_dir
databaseName = Foo.mdb

Table Definition:

Example:

package com.stackoverflow.questions.Q32641670;


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;


/**
 * Initializes a connection to an MS-Access DB using JDBC/UCanAccess API, inserts a record and closes the connection.
 * <p>
 * ADDITIONAL JARS REQUIRED:
 * ------------------------
 * commons-lang-2.6.jar
 * commons-logging-1.1.1.jar
 * hsqldb.jar
 * jackcess-2.1.0.jar
 * ucanaccess-2.0.9.5.jar
 *
 * @see @link {https://stackoverflow.com/questions/32641670/connect-java-to-access-db-on-network-drive}
 * @since 18/9/15.
 */


/**
 * @author Sandeep Chatterjee
 * @version 1.0
 */
public class ConnectRemoteDB {

    /**
     * @param args The command line arguments
     */
    public static void main(String[] args) throws IOException {

        initializeConnection();

    }

    /**
     * Initializes remote database connection and inserts a record and closes the connection.
     */
    private static void initializeConnection() throws IOException {

        System.out.println("Attempting Database Connection...");

        Connection connection = null;

        PreparedStatement preparedStatement;

        try {

            final Properties PROPERTIES = new Properties();

            InputStream inputStream = ConnectRemoteDB.class.getResourceAsStream("/MyDatabaseProperties.properties");

            PROPERTIES.load(inputStream);

            String pathToDB = PROPERTIES.getProperty("pathToDB");

            String sharedFolderName = PROPERTIES.getProperty("sharedFolderName");

            String databaseName = PROPERTIES.getProperty("databaseName");

            String connectionString = "jdbc:ucanaccess:///" + pathToDB + "/" + sharedFolderName + "/" + databaseName;

            connection = DriverManager.getConnection(connectionString, PROPERTIES);
            System.out.println("CONNECTION ESTABLISHED....");
            String insertTableSQL = "INSERT INTO Table1" + "(Name) VALUES"
                    + "(?)";
            preparedStatement = connection.prepareStatement(insertTableSQL);
            preparedStatement.setString(1, "A");
            preparedStatement.executeUpdate();
            System.out.println("RECORD INSERTED...");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
                System.out.println("CONNECTION CLOSED...");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Again:

• If you move the database to a different shared directory, set the value of pathToDB accordingly.
• If you rename the shared directory, set the value of sharedFolderName accordingly.
• If you rename the database, set the value of databaseName accordingly.

Answer 2

The simplest solution is to put the accdb file in a location relative to the java class and not use an absolute path.

For example if the java class (or jar you have packaged your program into as it may be) resides at c:\share\foo.class (or c:\share\foo.jar) then put the acdb file in the same directory structure e.g. c:\share\database\Database11.accdb.

You can then use a relative path in the connection string:

 Connection cnct = DriverManager.getConnection("jdbc:ucanaccess:database/Database11.accdb", "", "")