How to work with Derby database in network client server mode

In this Java Derby tutorial, you will learn how to write Java code using JDBC API to work with Apache derby databases in network client/server mode. You will also learn how to configure Derby network server to listen on a specific port number, enable client connections from different computers, and how to enable authentication (clients must provide username and password).

1. Start Derby Network Server

You can start Derby network server by typing the following command in a command prompt window:

java -jar %DERBY_HOME%\lib\derbyrun.jar server start

Replace %DERBY_HOME% by the actual path of the directory where you extract Derby distribution. This starts the server to listen for incoming requests from localhost on port number 1527 (default).

Note that the server loads databases it found in the current directory where you typed the command to start the server. You can also start Derby server by executing startNetworkServer.bat in %DERBY_HOME%\bin directory.

2. Derby Client Program Example

Derby supports JDBC driver so you can use JDBC API to write a Java program to connect to Derby server as usual. You have to specify hostname and port number in the database URL like this:

String databaseURL = "jdbc:derby://localhost:2018/booksdb";

As you can see, jdbc:derby is protocol specification, localhost is hostname, 1527 is port number and booksdb is the name of the database you want to work with.

The following sample program demonstrates how to use JDBC API to connect to a Derby server on localhost to select all rows from the book table in the booksdb database:

import java.sql.*;

/**
 * This program demonstrates a client application that connects to a Derby
 * server using JDBC.
 *
 * @author www.codejava.net
 */
public class DerbyClientDemo {

	public static void main(String[] args) {
		String databaseURL = "jdbc:derby://localhost:1527/booksdb";

		try (Connection conn = DriverManager.getConnection(databaseURL)) {
			Statement statement = conn.createStatement();

			String sql = "SELECT * FROM book";
			ResultSet result = statement.executeQuery(sql);

			while (result.next()) {
				System.out.println(result.getString("title"));
			}

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

}

To run this client program, you must specify Derby client JAR file in the classpath like this:

java -cp %DERBY_HOME%\lib\derbyclient.jar;. DerbyClientDemo

To shutdown Derby server, open a command prompt window and type the following command:

java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown

This stops Derby server which is running on localhost with default port number 1527.

3. Enable client connections from outside localhost

By default, Derby server doesn’t allow remote connections from clients on different computers, only connections on localhost (same computer) are allowed.

If you try to run the client program on different computer (of course localhost is changed to server’s hostname or IP address), you will get java.sql.SQLNonTransientConnectionException caused by java.net.ConnectionException.

To enable client connections from outside localhost, you must start Derby server with the -h option followed by hostname or IP address, for example:

java -jar %DERBY_HOME%\lib\derbyrun.jar server start -h namhm-nb

or specify server’s IP address:

java -jar %DERBY_HOME%\lib\derbyrun.jar server start -h 192.168.1.6

Then in the client program, you need to change server’s hostname accordingly in the database URL:

String databaseURL = "jdbc:derby://namhm-nb:1527/booksdb";

Note that if you use the -h option to start the server, you must also use that option to shutdown the server, for example:

java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown -h namhm-nb

Otherwise you will get connection refused error.

4. Change server’s port number

By default, Derby server listens on port number 1527. To use different port number, you have to specify the -p option followed by the port number when starting the server. For example:

java -jar %DERBY_HOME%\lib\derbyrun.jar server start -p 2018

This starts the server listening on port number 2018. Of course you need to change the port number in the database URL in the client program accordingly.

In this case, to shutdown the server you also need to specify the -p option like this:

java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown -p 2018

Therefore, to start Derby server that allows client connections from outside localhost on non-default port number, use the following command for example:

java -jar %DERBY_HOME%\lib\derbyrun.jar server start -h namhm-nb -p 2018

And to shutdown the server accordingly, use the following command:

java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown -h namhm-nb -p 2018

5. Enable Authentication for Derby server

By default, Derby server doesn’t require authentication. That’ why a client program can connect to the server without providing username and password.

To enable authentication for Derby server, you have to create the derby.properties file in the directory where you start the server, with the following content:

derby.connection.requireAuthentication=true
derby.authentication.provider=BUILTIN
derby.user.namhm=password1

Here, you must set the property derby.connection.requireAuthentication to true in order to enable authentication. The second line specifies Derby’s built-in authentication provider which defines usernames and passwords right in this file. You can see a username is namhm with password is password1 in the last line.

Start Derby server in one of the ways above and it will load the derby.properties file automatically.

Then in the client program, you need to specify username and password like this:

import java.sql.*;

/**
 * This program demonstrates a client application that connects to a Derby
 * server using JDBC, with authentication enabled on the server.
 *
 * @author www.codejava.net
 */
public class DerbyClientAuthenticationDemo {

	public static void main(String[] args) {
		String databaseURL = "jdbc:derby://192.168.1.6:2018/booksdb";
		String user = "namhm";
		String pass = "password1";

		try (Connection conn = DriverManager.getConnection(databaseURL, user, pass)) {
			Statement statement = conn.createStatement();

			String sql = "SELECT * FROM app.book";
			ResultSet result = statement.executeQuery(sql);

			while (result.next()) {
				System.out.println(result.getString("title"));
			}

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

To shutdown the server, you must also specify the username and password accordingly, for example:

java -jar %DERBY_HOME%\lib\derbyrun.jar server shutdown -h 192.168.1.6 -p 2018 -user namhm -password password1

And here’s another version of Derby client program that parameterizes hostname, port number, username and password:

import java.sql.*;

/**
 * This program demonstrates a client application that connects to a Derby
 * server using JDBC, with authentication enabled on the server.
 * The connection information are parameterized.
 *
 * @author www.codejava.net
 */
public class DerbyRemoteClientAuthenticationDemo {

	public static void main(String[] args) {
		String hostname = args[0];
		String port = args[1];
		String user = args[2];
		String pass = args[3];

		String databaseURL = "jdbc:derby://" + hostname + ":" + port + "/booksdb";

		try (Connection conn = DriverManager.getConnection(databaseURL, user, pass)) {
			Statement statement = conn.createStatement();

			String sql = "SELECT * FROM app.book";
			ResultSet result = statement.executeQuery(sql);

			while (result.next()) {
				System.out.println(result.getString("title"));
			}

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

Run this program like this:

java -cp %DERBY_HOME%\lib\derbyclient.jar;. DerbyRemoteClientAuthenticationDemo 192.168.1.6 2018 namhm password1

Note that with authentication enabled, you must specify schema name before the table name as you can see in the SQL statement in the above program:

String sql = "SELECT * FROM app.book";

 Here, app is the default schema name of Derby.

That’s how to work with Derby database in network client/server mode. You use the JDBC API as usual, the only new things you have to learn is how to configure, start and stop the server in different ways.

References:

• Apache Derby Homepage
• Download Apache Derby

Related Apache Derby Tutorials:

• How to Get Started with Apache Derby (JavaDB)
• How to work with Derby in Embedded Mode using JDBC

Other JDBC Tutorials:

• How to connect to a database with JDBC
• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to call stored procedure with JDBC
• How to read database metadata in JDBC

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.