Setting Location for database to be used by javadb

[Yaki Kumar]

Hello,

I am stuck figuring out how to set directory location to be used by javadb. Below is the code I using to create database, a test table and few queries - it uses some other class too to find appropriate location of database.

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author vikesh
 */
public class DBManager {

    private static final String EMBEDDED_JAVADB_DRIVER =
            "org.apache.derby.jdbc.EmbeddedDriver";
    private static final String DERBY_PROTOCOL = "jdbc:derby:";
    private static final String DB_NAME = "db_log_data";

    static {
        //Load Embedded Driver for JAVADB
        try {
            Class.forName(EMBEDDED_JAVADB_DRIVER);

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * 
     * @return Connection to the JavaDB database.
     */
    public static Connection getConnection() {
        Connection connection = null;
        Properties prop = new Properties();
        try {
            String dbDir = LogPreference.getInstance().getDBDir();
            //Set Directory containing the database of JAVADB
            System.setProperty("derby.system.home",
                    dbDir);
            System.out.println("DBLocation: " + dbDir);
            connection = DriverManager.getConnection(DERBY_PROTOCOL
                    + DB_NAME + ";create=true;");
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return connection;
    }

    public static final void shutDownDB() {
        try {
            DriverManager.getConnection(DERBY_PROTOCOL + ";shutdown=true");
        } catch (SQLException ex) {
            /**Tt throws an tests for a SQL exception. 
             * A clean shutdown always throws SQL exception XJ015, 
             * which can be ignored.
             */
//            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String[] args) {
        Connection connection = DBManager.getConnection();
        if (connection != null) {
            System.out.println("Got A Connection: " + connection);
            createDBTest(connection);
            shutDownDB();
        }
    }

    private static void createDBTest(Connection con) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.prepareStatement("create table POWER_HOUSE("
                    + "ID integer, VALUE varchar(20))");
            stmt.execute();
            stmt = con.prepareStatement("insert into POWER_HOUSE values(1,'hmmmm')");
            int executeUpdate = stmt.executeUpdate();
            stmt = con.prepareStatement("select * from POWER_HOUSE");
            rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Got: " + rs.getString(1) + ", " + rs.getString(2));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
                if (!con.isClosed()) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

I have used

System.setProperty("derby.system.home", dbDir);

- Line Number 43-44.
But running this shows me the database directory as DBLocation: /home/vikesh/.logbook/db[/b], whereas it creates the database directory named db_log_data in the same directory from where I run this. Is there any other way I can specify location of database. What I am doing wrong?

Here is the output of above:

DBLocation: /home/vikesh/.logbook/db
Got A Connection: org.apache.derby.impl.jdbc.EmbedConnection40@26977856 (XID = 152), (SESSIONID = 1), (DATABASE = db_log_data), (DRDAID = null)
Got: 1, hmmmm


Thanks for helping.

[Ireneusz Kordal]

Hi,

read this: http://db.apache.org/derby/docs/10.8/ref/rrefproper32066.html
and note that derby.system.hom is a static property - if Derby is running, you must reboot it for changes to take effect.

But an easier method is just to set directory locaction in the connection URL, like this:

   conn = DriverManager.getConnection("jdbc:derby:/home/korkodrilko/test/dbname" 
                     + ";create=true;");  

In this example, Derby will create dbmane subirectory in /home/korkodrilko/test/.

On windows you can use drive letter plus colon in the path, and probably (don't remember) both slashes and backshlashes:

   conn = DriverManager.getConnection("jdbc:derby:c:/directory/dbname" 
                     + ";create=true;");  

[Yaki Kumar]

Thanks a lot, I got it sorted. It seems the problem in my case was that the directory supposed to contain db files was already present and not created by execution of following.

connection = DriverManager.getConnection("jdbc:derby:" + dbDir + ";create=true;");

.
Once I did not have the particular directory in place before above code executed, it all got fine.

Thanks a lot!