How to work with Derby in Embedded Mode using JDBC

In this JDBC tutorial, you will learn how to write a Java program that connects to a Derby database in embedded mode.

As Derby supports JDBC driver, you can use JDBC API to write Java code to work with Derby as usual. The main difference lies in the database URL and the way to shutdown the database.

In embedded mode, the database URL doesn’t contain hostname and port number. For example:

String databaseURL = "jdbc:derby:booksdb;create=true";

This will connect to a database named booksdb in embedded mode. The database will be created if it doesn’t exist. Note that the database’s directory is relative to the application’s directory (specified by the system property user.dir).

If you want to specify the database by an absolute path, include the path in the database URL like this:

String databaseURL = "jdbc:derby:/d:/projects/booksdb;create=true";

This connects to the database located in the directory d:/projects/booksdb in embedded mode on Windows. On Linux, use the absolute path like this:

String databaseURL = "jdbc:derby:/home/username/projects/booksdb;create=true";

The following code snippet connects to a Derby database in embedded mode, creates it in the current directory if it doesn’t exist, and then executes SQL statements to create a table and insert rows into the table:

String databaseURL = "jdbc:derby:booksdb;create=true";

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

String sql = "CREATE TABLE book (book_id int primary key, title varchar(128))";
statement.execute(sql);


sql = "INSERT INTO book VALUES (1, 'Effective Java'), (2, 'Core Java')";
statement.execute(sql);

Note that in embedded mode, you should shutdown the database by calling this statement:

DriverManager.getConnection("jdbc:derby:;shutdown=true");

This throws SQException even if the database was shutdown normally, so you should catch the exception and check SQL state like this:

try {
	DriverManager.getConnection("jdbc:derby:;shutdown=true");

} catch (SQLException ex) {
	if (ex.getSQLState().equals("XJ015")) {
		System.out.println("Derby shutdown normally");
	} else {
		// could not shutdown the database
		// handle appropriately
	}
}

In case the database and table do exist, the statement to create the table will fail. So you can write a method for checking the existence of a table like this:

boolean doesTableExists(String tableName, Connection conn)
		throws SQLException {
	DatabaseMetaData meta = conn.getMetaData();
	ResultSet result = meta.getTables(null, null, tableName.toUpperCase(), null);

	return result.next();
}

This method returns true if the table specified by the given name does exist, or false otherwise.

The following is full source code of a demo program:

import java.sql.*;

/**
 * This program demonstrates how to connect to a Derby database in embedded mode.
 *
 * @author www.codejava.net
 */
public class DerbyEmbeddedDemo {

	public static void main(String[] args) {
		String databaseURL = "jdbc:derby:booksdb;create=true";

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

			if (!doesTableExists("book", conn)) {
				String sql = "CREATE TABLE book (book_id int primary key, title varchar(128))";
				statement.execute(sql);
				System.out.println("Created table book.");

				sql = "INSERT INTO book VALUES (1, 'Effective Java'), (2, 'Core Java')";
				statement.execute(sql);
				System.out.println("Inserted 3 rows.");
			}

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

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


			DriverManager.getConnection("jdbc:derby:;shutdown=true");

		} catch (SQLException ex) {
			if (ex.getSQLState().equals("XJ015")) {
				System.out.println("Derby shutdown normally");
			} else {
				ex.printStackTrace();
			}
		}
	}

	private static boolean doesTableExists(String tableName, Connection conn)
			throws SQLException {
		DatabaseMetaData meta = conn.getMetaData();
		ResultSet result = meta.getTables(null, null, tableName.toUpperCase(), null);

		return result.next();
	}
}

As you can see, this program connects to a Derby database in embedded mode, creates it if it doesn’t exist. Then it creates a table and inserts a couple of rows into the table. Then it selects all rows from the table. Finally the program shuts down the database.

To run this program, you have to specify the derby.jar file in the classpath like this:

java -cp %DERBY_HOME%\lib\derby.jar;. DerbyEmbeddedDemo

Replace %DERBY_HOME% by the directory where you extracted the Derby distribution.

Run this program the first time, you can see the output like this:

The database’s directory has been created in the same directory as the program. You can use the ij tool to try to connect to this database.

Run the program again, you can see the following output because the database already exists:

That’s how to write a Java program that works with a Derby database in embedded mode. You can use the JDBC API normally, the only difference lies in the database URL and database shutdown.

References:

• Apache Derby Homepage
• Download Apache Derby

Related Apache Derby Tutorials:

• How to Get Started with Apache Derby (JavaDB)
• How to work with Derby database in network client server mode

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.