1. What is Apache Derby?

Apache Derby is an open source, lightweight, fully transactional, secure, standards-based database server purely written in Java. Java DB is just an Oracle’s distribution of Derby in their JDK and fully supports SQL, JDBC API, and Java EE technology. Apache Derby and Java DB are essentially the same. Apache Derby is the reference implementation for JDBC 4.0 and compatible to ANSI-SQL.

 2. When it can be useful? 

• We mostly use it in an embedded device scenario
• When resources are really tiny
• When resources are sensible.

 3. Installation of Derby

Download the latest Derby version from the Apache website http://db.apache.org/derby/. Choose the bin distribution and extract this zip to a directory of your choice.

Also make the Derby tools available in your path:

• Set the environment variable DERBY_HOME to the Derby installation directory
• Add DERBY_HOME/bin to the “path” environment variable

4. Mode of Derby database

• Server Mode
• Embedded Mode

4.1. Server Mode

An application can also access a Derby database using the more familiar client/server mode. This is achieved via a framework that embeds Derby and handles database requests from applications, including applications running in different JVMs on the same machine or on remote machines.

4.1.1. How to start Derby database in server mode?

Use the following command in the command line to start the Derby network server (located in the Derby installation directory/bin).

startNetworkServer

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

startNetworkServer -p 3301

By default Derby will only accept connections from the localhost. To make the Derby server accept connections also from other hosts 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

 

 4.1.2. Loading Derby JDBC driver

To connect to the network server via Java code you need to have the derbyclient.jar in your class-path.  We use the following network client driver to connect with DB

If you are using Java 5.0 or earlier, you have to load the driver explicitly like this:

 Class.forName("org.apache.derby.jdbc.ClientDriver");

Or

 DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());

 

4.1.3. Making Derby JDBC connection examples

With JDBC, there are three different ways to establishing a connection to the database, corresponding to three version of the method getConnection() of the DriverManager class:

• Using only a database URL:

String dbURL = "jdbc:derby://localhost:1527/dbname;create=true";

Connection conn = DriverManager.getConnection(dbURL);

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

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

• Using a database URL with user and password:

String dbURL = "jdbc:derby://localhost:1527/dbname;create=true";
String user = "tom";
String password = "secret";
Connection conn = DriverManager.getConnection(dbURL, user, password);

• Using a database URL with a Propertiesobject:

String dbURL = "jdbc:derby://localhost/dbname";
Properties properties = new Properties();
properties.put("create", "true");
properties.put("user", "tom");
properties.put("password", "secret");
Connection conn = DriverManager.getConnection(dbURL, properties);

 

4.2. Embedded Mode

When an application accesses a Derby database using the Embedded Derby JDBC driver, the Derby engine does not run in a separate process, database is accessed from one application only and there are no separate database processes to start up and shut down. Instead, the Derby database engine runs inside the same Java Virtual Machine (JVM) as the application.

4.2.1. Loading Derby JDBC driver

To connect to the database via Java code you need to have the derby.jar for embedded driver in your class-path.  We use the following embedded driver to connect with DB

If you are using Java 5.0 or earlier, you have to load the driver explicitly like this:

  Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

Or

 DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());

 

4.2.2 Making Derby JDBC connection examples

With JDBC, there are three different ways to establishing a connection to the database, corresponding to three version of the method getConnection() of the DriverManager class.

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.

• Using only a database URL:

String dbURL = " jdbc:derby:c:\temp\db\FAQ\db;create=true";
Connection conn = DriverManager.getConnection(dbURL);

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

jdbc:derby:c:\temp\db\FAQ\db

• Using a database URL with user and password:

String dbURL = " jdbc:derby:c:\temp\db\FAQ\db;create=true";
String user = "tom";
String password = "secret";
Connection conn = DriverManager.getConnection(dbURL, user, password);

• Using a database URL with a Propertiesobject:

String dbURL = "jdbc:derby:c:\temp\db\FAQ\db ";
Properties properties = new Properties();
properties.put("create", "true");
properties.put("user", "tom");
properties.put("password", "secret");
Connection conn = DriverManager.getConnection(dbURL, properties);