Apache Derby is an open source database written in Java. It is free and performs well. Apache Derby is used in the JDK and is called Java DB. Apache Derby and Java DB are essentially the same. Apache Derby is the reference implementation for JDBC 4.0 and compatible to ANSI-SQL. JDBC is the Java interface to connect to databases.

Derby can be used in a server mode and in so-called embedded mode. If Derby runs in the server mode you start the Derby network server which will be responsible for handling the database requests. In the enbedded mode Derby runs within the JVM (Java Virtual Machine) of the application. In this mode only the application can access the database, e.g. another user / application will not be able to access the database.

Use the following command in the command line to start the Derby network server (located in the Derby installation directory/bin). On Microsoft Windows it is possible to use the .bat version.

startNetworkServer 

This will start the network server which can serve an unlimited number of database. By default the server will be listening on port 1527 but this can be changed via the -p flag.

startNetworkServer -p 3301 

By default Derby will only accept connections from the localhost. To make the Derby server accept connections also from other hosts then use the following start command. Replace "sampleserver.sampledomain.com" with the name or the IP of the server. The server will then accept connections only from other servers as the localhost.

startNetworkServer -h sampleserver.sampledomain.com 

If connections should be allowed from localhost and any other server use the following.

startNetworkServer -h 0.0.0.0 

To connect to the network server via Java code you need to have the derbyclient.jar in your classpath. The network connection string to this database is the IP address of the server:portnumber. For example for a server which is running on localhost you can create a new database via the following string.

jdbc:derby://localhost:1527/dbname;create=true 

If you want to connect to an existing database you can use the following string.

jdbc:derby://localhost:1527/c:\temp\mydatabase 

For example a small Java client might look like the following. This assumes that you have already created a schema called a table users with the columns "name" and "number".

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

public class DerbyTest {
  private Connection connect = null;
  private Statement statement = null;
  private ResultSet resultSet = null;

  public DerbyTest() throws Exception {
    try {

      Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
      connect = DriverManager
          .getConnection("jdbc:derby://localhost/c:/temp/db/FAQ/db");
      PreparedStatement statement = connect
          .prepareStatement("SELECT * from USERS");

      resultSet = statement.executeQuery();
      while (resultSet.next()) {
        String user = resultSet.getString("name");
        String number = resultSet.getString("number");
        System.out.println("User: " + user);
        System.out.println("ID: " + number);
      }
    } catch (Exception e) {
      throw e;
    } finally {
      close();
    }

  }

  private void close() {
    try {
      if (resultSet != null) {
        resultSet.close();
      }
      if (statement != null) {
        statement.close();
      }
      if (connect != null) {
        connect.close();
      }
    } catch (Exception e) {

    }
  }

  public static void main(String[] args) throws Exception {
    DerbyTest dao = new DerbyTest();
  }

} 

ij is Derby's interactive JDBC scripting tool. It is a simple utility for running scripts or interactive queries against a Derby database. To start the tool open a command shell and type in "ij". This will start a shell program which can connect to your database and execute SQL commands .

Stop this tool with typing in "exit;" and pressing enter. In ij every line need to get terminated via a ;

If you want to connect to the Derby database in embedded mode you can use the following command. In this example the database is located at c:\temp\db\FAQ\db

connect 'jdbc:derby:c:\temp\db\FAQ\db'; 

If you want to connect to a Derby database which is running in server mode then you can use the following command.

connect 'jdbc:derby://localhost:1527/c:\temp\db\FAQ\db;create=true'; 

To disconnect from the database.

disconnect; 

To run a SQL script from ij use the following command.

run 'sqlscript.sql' 

You can also used SQL directly, e.g.

select * from SCHEMA1.USERS where NUMBER='lars' 

Use the tool dblook. Type dblook on the console to see the options, the tool is really easy to use. For example to write the schema "myschema" to the file "lars.sql".

dblook -d 'jdbc:derby:c:\temp\db\FAQ\db' -z myschema -o lars.sql