Creating/Configuring Derby JDBC Client in IntelliJ Idea 13

Question

Sorry for this (maybe) stupid question. I need to create some local DB in my java project so I've decided for Apache Derby Client. I am working with IntelliJ IDEA 13 Ultimate and my problem is that I don't know, how to create local database. Tutorials at Jetbrains websites aren't useful because there are articles only about connecting to the remote DB, not to the local one (or at least I didn't find them yet).

What have I done so far:

1. I've tried to set the DB up by creating new remote derby data source. Screenshot with the settings: DB Settings screen

Username and password are the same: admin

1. After clicking test connection, this error is thrown: error
2. When I click apply and ok, it says that it's connected, but exception is still there.

So do you have any idea where the problem can be? I've got small confiuration class called DatabaseSetting.java

package issuetrackinglite;

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

public class DatabaseSetting {

private String dbURL = "jdbc:derby://localhost:1527/MallDB;create=true";
private String user = "admin";
private String password = "admin";
private Connection connection;



public static final String CREATE_ITEMS_DB = "CREATE TABLE items (item_id INTEGER NOT NULL, item_name VARCHAR(20) NOT NULL, item_price REAL NOT NULL, multiplicity_shop INTEGER NOT NULL, multiplicity_store INTEGER NOT NULL)";
public static final String INSERT_PRODUCT = "INSERT INTO items (item_id, item_name, item_price, multiplicity_shop, multiplicity_store) VALUES (?, ?, ?, ?, ?)";
public static final String CLEAR_ITEMS_DB = "DELETE FROM items";

// -------------------------------------------------------------

protected Connection connectToDB() {

    try {

        connection = DriverManager.getConnection(dbURL, user, password);

        return connection;

    } catch (SQLException ex) {
        System.out.println("SQL exception - connectToDB(): " + ex.getMessage());
        return null;
    }

}

}

EDIT

Simply explained: I just need to create virtual derby database which will be created every time at the program start. I don't know, how to do it in IntelliJ. I've added DERBY_HOME to the enviroment variables and also added path to Derby. Now this error is thrown by IntelliJ: Error window

Thank you very much for your help and time

Answer 1

I've managed to get this work. Here are the steps that I've made to successfully configure local Derby database to work in IntelliJ Idea 13/14.

1. First, you need to manually start derby server (if it is not already running) before using it in IDEA. I usually do it via command prompt by typing:

   C:\Users\PcName>startNetworkServer -noSecurityManager

   This command will start derby server on port number: 1527. Make sure you have correctly set path in enviroment variables

2. Next go to IDEA, open Database window, click on the green plus sign in the upper left corner and in the dropdown menu choose: Data Source -> Derby -> Remote

3. You can inspire with my settings provided in the screenshot in my question. You can also download Derby driver files. IDEA does it just by clicking on the download button.

   Basic template which I always use is something like that:

   Name field should be in the form: Derby - YourDatabaseName;create=true@localhost

   Host field should have localhost inside

   Port has to be 1527

   Database field has to be in form: YourDatabaseName;create=true

   Urc to connect: jdbc:derby://localhost:1527/YourDatabaseName;create=true

4. (Optional) - You can specify your database user name and password. In IDEA 14 there is a checkbox Save on disk with master password protection, which I personally always leave unchecked.

That's it. I hope this little guide will help somebody to configure Derby in Intellij IDEA

Answer 2

For embedded database, use the default/In-Memory/URL only select box on the right side of the connection URL input field. When set to In-Memory, it generates the following URL, which works for me.

 jdbc:derby:memory:MallDB;create=true

Answer 3

I think that for a local (embedded) database, you would leave off the host and port in the connection url.

The documentation says that the url should be of the form: jdbc:derby:MallDB;create=true