Unable to find Derby database, how to connect in Java

Question

I have a MySQL database with some tables and records in them. I have written a Java application which, during development, used the MySQL database. Now since development is finished, I wish to make an executable JAR of my program together with a Derby Embedded database.

I have read on the internet that a tool called SQuirrel SQL is capable of copying and transforming a MySQL database to a Derby database and converting the datatypes to the correct ones. So now I have been able to copy a MySQL table to the Derby database using the SQuirrel SQL GUI. However, I do not know how I can access this data in my Java application.

Here you find a screenshot of the Alias in SQuirrel SQL. I have been looking on my computer where these files are stored, but haven't found them...

So can someone explain how this works and how I can properly access the Derby database in my Java application? I have imported all the necessary Derby jars in my project and have written a small test class that creates a table, inserts data and reads it back out. This all works perfectly though.

public static final String URL = "jdbc:derby:";
public static final String USER = "root";
public static final String PASSWORD = "root";
public static final String DRIVER_CLASS = "org.apache.derby.jdbc.EmbeddedDriver";

// private constructor
private ConnectionFactory() {
    try {
        startNetworkServerControl();
        Class.forName(DRIVER_CLASS).newInstance();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private Connection createConnection() {
    Connection connection = null;
    try {
        connection = DriverManager.getConnection("jdbc:derby:AppleDB;create=true", USER, PASSWORD);
    } catch (SQLException e) {
        System.out.println("ERROR: Unable to Connect to Database.");
    }
    return connection;
}

Answer 1

Since you specified the database as "AppleDB", without a full absolute path name, it was stored in a folder named AppleDB in the home directory of SquirrelSQL when it ran.

Search your hard disk for a folder named AppleDB and you will find your database.

If you wish, you can move it to a more appropriate location (simply rename the folder), and then update your JDBC Connection URL to specify the full absolute path name to the folder so that you don't lose track of it in the future.

In your Java program, you simply created another database named AppleDB, in a different folder on your hard disk, in the current directory of your Java program when you ran it. Since that program doesn't contain the data you imported with Squirrel, you could just discard that folder (put it in the trash).

If you don't specify ";create=true" in your JDBC Connection URL, Derby will not automatically create a new database, but will instead give you an error message if you specify the wrong path to your database when you try to open it.