Using Apache Derby in Java application

Download and unzip. Let’s call the root directory $DERBY_HOME. It is a good idea to add Derby installation directory to the path:

$ export DERBY_HOME=/path/to/db-derby-10.5.3.0-bin

Derby can run in embedded mode or as a server. In embedded mode, DB is integrated with your java application and runs with the same JVM as your application. If Derby is running in server mode, it can be installed on some other computer and runs in its own JVM (other than your java application).

Basics of Derby

Go to $DERBY_HOME/lib directory where Derby jar files are located before running following commands.

See usage:

$ java -jar derbyrun.jar

See version:

$ java -jar derbyrun.jar sysinfo

Run ij tool:

$ java -jar derbyrun.jar ij

Prompt changes to ij>

Type exit; to exit.

If you added Derby directory to the path, you can also start ij tool from $DERBY_HOME/bin directory (./ij).

Dump data to console or file

$ java -jar derbyrun.jar dblook -d connectionURL

Run Derby using the embedded driver

Create new test directory where your DB will be created.

Run derby ij tool:

$ java -jar $DERBY_HOME/lib/derbyrun.jar ij

Connect and create new database:

ij> CONNECT 'jdbc:derby:animalsdb;create=true';

Create table:

ij> CREATE TABLE ZEBRAS (id INT PRIMARY KEY, name VARCHAR(10));

Add some entries:

ij> INSERT INTO ZEBRAS VALUES (1, 'Wilma'),(2, 'Lucy'),(3, 'Fred');

Show table content:

ij> SELECT * FROM ZEBRAS;
ij> SELECT * FROM ZEBRAS WHERE id=3;

Create another table:

ij> CREATE TABLE MONKEYS (name VARCHAR(10) PRIMARY KEY, age INT, sex VARCHAR(1));
ij> INSERT INTO MONKEYS VALUES ('Bruce', 7, 'm'),('Nancy', 10, 'f');
ij> SELECT * FROM MONKEYS;

Create tables with sql script:

ij> run 'ToursDB_schema.sql';
run 'loadTables.sql';

Run Derby as server

In first console window start server:

$ java -jar $DERBY_HOME/lib/derbyrun.jar server start

Run ij:

$ $DERBY_HOME/bin/ij

Connect to DB:

ij> CONNECT 'jdbc:derby://localhost:1527/employeesdb;create=true';

Create table:

ij> CREATE TABLE EMPLOYEES (id INT, name VARCHAR(10), age INT);

Insert data:

ij> INSERT INTO EMPLOYEES VALUES (65286, 'Bob', 35), (35421, 'John', 40);

Set primary key:

ij> ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_PK Primary Key (id);

Exit from ij

ij> exit;

Shutdown server:

$ java -jar derbyrun.jar server shutdown

If running your Derby in embedded mode, the database will be created inside current directory (could be different than $DERBY_HOME/lib). It is good practice to specify full path to your DB.

If running Derby in server mode, the DB will be created in the same directory where Derby server was started (usually $DERBY_HOME/lib on remote server).

Run JDBC program using embedded driver

Example on using embedded driver in Java application: load Derby JDBC driver and connect to Derby DB, create table if it doesn’t exist already, insert some data into DB, retrieve data from DB and finally disconnect. Add derby.jar to the build path.

package derbytest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DerbyEmbededTest {

public static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
public static String dbName = "testDB";
public static String connectionURL = "jdbc:derby:" + dbName
+ ";create=true";

public static Connection connection = null;
public static Statement stat = null;
public static PreparedStatement psInsert = null;

public static String createTableString = "CREATE TABLE PERSONS_LIST "
+ "(PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY "
+ " CONSTRAINT PERSON_PK PRIMARY KEY, "
+ " ENTRY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
+ " PERSON VARCHAR(32) NOT NULL) ";

/**
* Main method
*/
public static void main(String[] args) {
loadDriver();
getConnection();
boolean b = checkTable();
if (!b) {
createTable();
}
insertEntries();
getEntries();
disconnect();
}

/**
* Load Derby driver.
*/
public static void loadDriver() {
try {
Class.forName(driver);
System.out.println(driver + " loaded. ");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

/**
* Make connection to DB with connectionURL
*/
public static void getConnection() {
try {
connection = DriverManager.getConnection(connectionURL);
System.out.println("Connected to database " + dbName);
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
* Check if table already exists otherwise create one.
*
* @return true if table exists
*/
public static boolean checkTable() {
try {
stat = connection.createStatement();
stat.execute("update PERSONS_LIST set ENTRY_DATE = CURRENT_TIMESTAMP, PERSON = 'TEST ENTRY' where 1=3");
} catch (SQLException sqle) {
String theError = (sqle).getSQLState();
// If table exists will get - WARNING 02000: No row was found
if (theError.equals("42X05")) {
// Table does not exist
System.out.println("Table does not exist");
return false;
} else if (theError.equals("42X14") || theError.equals("42821")) {
System.out
.println("Incorrect table definition. Drop table PERSONS_LIST and rerun this program");
} else {
System.out.println("Unhandled SQLException");
}
}
System.out.println("Table already exist");
return true;
}

/**
* Create table.
*/
public static void createTable() {
try {
Statement s = connection.createStatement();
System.out.println(" . . . . creating table PERSONS_LIST");
s.execute(createTableString);
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
* Insert some dummy records.
*/
public static void insertEntries() {
String[] names = { "Frank", "Lucy", "Bobby", "Nancy" };

try {
psInsert = connection
.prepareStatement("insert into PERSONS_LIST(PERSON) values (?)");
for (int i = 0; i < names.length; i++) {
psInsert.setString(1, names[i]);
psInsert.executeUpdate();

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

/**
* Get and display all DB records.
*/
public static void getEntries() {

try {
ResultSet persons;

persons = stat
.executeQuery("select ENTRY_DATE, PERSON from PERSONS_LIST order by ENTRY_DATE");

while (persons.next()) {
System.out.println("At " + persons.getTimestamp(1)
+ " I thought of " + persons.getString(2));
}
persons.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

/**
* Close and disconnect from DB.
*/
public static void disconnect() {
try {
psInsert.close();
stat.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Closed connection");

try {
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException se) {
if (se.getSQLState().equals("XJ015")) {
System.out.println("Database shut down normally");
} else {
System.out.println("Database did not shut down normally");
}
}
}

}

Run JDBC program using the client driver

This example shows usage of Derby driver in server mode. There is no other difference as previous example except first three lines, where driver, db name and connection url is defined. Add derbyclient.jar to the build path.

Before running this piece of code, make sure that Derby DB server is running:

$ java -jar derbyrun.jar server start

package derbytest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DerbyClientTest {

public static String driver = "org.apache.derby.jdbc.ClientDriver";
public static String dbName="/Users/matjaz/Documents/workspace/DerbyTest/clientTestDB";
public static String connectionURL = "jdbc:derby://localhost:1527/" + dbName + ";create=true";

public static Connection connection = null;
public static Statement stat = null;
public static PreparedStatement psInsert = null;

public static String createTableString = "CREATE TABLE PERSONS_LIST "
+ "(PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY "
+ " CONSTRAINT PERSON_PK PRIMARY KEY, "
+ " ENTRY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
+ " PERSON VARCHAR(32) NOT NULL) " ;


/**
* Main method
*/
public static void main(String[] args) {
loadDriver();
getConnection();
boolean b = checkTable();
if (!b) {
createTable();
}
insertEntries();
getEntries();
disconnect();
}


/**
* Load Derby driver.
*/
public static void loadDriver() {
try {
Class.forName(driver);
System.out.println(driver + " loaded. ");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}


/**
* Make connection to DB with connectionURL
*/
public static void getConnection() {
try {
connection = DriverManager.getConnection(connectionURL);
System.out.println("Connected to database " + dbName);
} catch (SQLException e) {
e.printStackTrace();
}
}


/**
* Check if table already exists otherwise create one.
* @return true if table exists
*/
public static boolean checkTable() {
try {
stat = connection.createStatement();
stat.execute("update PERSONS_LIST set ENTRY_DATE = CURRENT_TIMESTAMP, PERSON = 'TEST ENTRY' where 1=3");
} catch (SQLException sqle) {
String theError = (sqle).getSQLState();
// If table exists will get - WARNING 02000: No row was found
if (theError.equals("42X05")) {
// Table does not exist
System.out.println("Table does not exist");
return false;
} else if (theError.equals("42X14") || theError.equals("42821")) {
System.out.println("Incorrect table definition. Drop table PERSONS_LIST and rerun this program");
} else {
System.out.println("Unhandled SQLException");
}
}
System.out.println("Table already exist");
return true;
}


/**
* Create table.
*/
public static void createTable() {
try {
Statement s = connection.createStatement();
System.out.println(" . . . . creating table PERSONS_LIST");
s.execute(createTableString);
} catch (SQLException e) {
e.printStackTrace();
}
}


/**
* Insert some dummy records.
*/
public static void insertEntries() {
String[] names = {
"Frank", "Lucy", "Bobby", "Nancy"
};

try {
psInsert = connection.prepareStatement("insert into PERSONS_LIST(PERSON) values (?)");
for (int i = 0; i < names.length; i++) {
psInsert.setString(1, names[i]);
psInsert.executeUpdate();

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


/**
* Get and display all DB records.
*/
public static void getEntries() {

try {
ResultSet persons;

persons = stat.executeQuery("select ENTRY_DATE, PERSON from PERSONS_LIST order by ENTRY_DATE");

while (persons.next()) {
System.out.println("At " + persons.getTimestamp(1) + " I thought of " + persons.getString(2));
}
persons.close();
} catch (SQLException e) {
e.printStackTrace();
}
}


/**
* Close and disconnect from DB.
*/
public static void disconnect() {
try {
psInsert.close();
stat.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("Closed connection");

try {
DriverManager.getConnection("jdbc:derby:;shutdown=true");
} catch (SQLException se) {
if (se.getSQLState().equals("XJ015")) {
System.out.println("Database shut down normally");
} else {
System.out.println("Database did not shut down normally");
}
}
}

}

When you are finished, stop the server:

$ java -jar derbyrun.jar server shutdown