vogella.de

Apache Derby is an open source database written in Java. It is free and performs well. Apache Derby is also used in the JDK and is called Java DB. Apache Derby and Java DB are essentially the same. Apache Derby used also to be available in a IBM branded version Cloudscape.

Apache Derby is compatible to ANSI-SQL

Apache Derby is also the reference implementation for JDBC 4.0. JDBC is the Java interface to connect to databases.

Derby can be used in a server version and in an embedded version.

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 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 from 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 database. Per standard the server will be listening on port 1527 but this can be changed via the -p flag.

				
startNetworkServer -p 3301
			

To make the Derby server accept connections also from other hosts then the localhost 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
			

In case you would now like to connect to this network server from Java you only need to have the derbyclient.jar in your classpath (not the derby.jar).

The network connection string to this database is the 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 disconned 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