How to use Apache Derby from JDBC

In this blog, I explain the steps required to use Apache Derby from Java using JDBC.

Watch my video tutorial on Using Apache Derby from JDBC at YouTube.

Apache Derby is a small RDBMS entirely developed in Java, ideal for small databases. Unlike Oracle and other large Database systems, Apache Derby doesn't need any installation. All that we need to have is its JDBC driver to get started as Derby engine runs inside JVM. However, there is an another version of Derby that runs on its own allowing multiple connections from different applications.

This blog shows how to connect to Derby using embedded driver.

For more information about Apache Derby and its features, visit http://db.apache.org/derby

Downloading JDBC Driver

In order to connect to Apache Derby using JDBC, first download JDBC Driver for Apache Derby.

Follow the steps given below to download Apache Derby Driver:

Go to http://db.apache.org/derby
Select Download tab
Select link 10.15.2.0 under For Java 9 and Higher section
Click on db-derby-10.15.2.0-bin.zip to download ZIP file that contains Derby driver
Extract ZIP file into a folder in your system
Inside that folder we find lib folder in which we have derby.jar, which is JDBC driver for Derby

Program to Connect to Apache Derby

The following program is used to connect to Apache Derby using JDBC. We are using embedded driver provided by Derby, which supports only one connection at a time to Derby database. Here we don't have to run Database separately as it is run within Java application.

We can use client/server driver which connects to Derby running outside Java Application. It does allow multiple connections to database.

// Connect to Apache Derby database using Embedded driver 
import java.sql.Connection;
import java.sql.DriverManager;

public class TestConnection {
	public static void main(String[] args) throws Exception {
		Connection con = DriverManager.getConnection("jdbc:derby:c:\\dev\\java\\testdb;create=true");
		System.out.println("Connected To Derby Database!");
		con.close();
	}
}

The above program connects to Derby database using Embedded driver.

Option create=true creates testdb database in c:\dev\java folder if database is not present.

If we want to use Derby Network Client JDBC driver, follow the steps explained in Configure environment to use Derby Network Client JDBC driver.

Program to create table

The following program is used to create table Books in app schema in the database.

// Create BOOKS table in APP schema 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CreateTable {
    public static void main(String[] args) throws Exception {
		Connection con = DriverManager.getConnection("jdbc:derby:c:\\dev\\java\\testdb");
		Statement st = con.createStatement();
		st.executeUpdate("create table app.books (id int primary key GENERATED ALWAYS AS IDENTITY, title varchar(50),  price int)");
		con.close();
	}
}

It is also possible to use Data Source Explorer in Eclipse to connect to an existing database or create new database and create required schema objects.

Program to execute DML command

The following program is used to insert a new row into Books table using PreparedStatement.

// Insert a row into BOOKS table
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class AddBook {
    public static void main(String[] args) throws Exception {
		Connection con = DriverManager.getConnection("jdbc:derby:c:\\dev\\java\\testdb");
		PreparedStatement ps = con.prepareStatement("insert into app.books(title,price) values(?,?)");
		ps.setString(1,"Java Comp. Ref");
		ps.setInt(2,500);
		ps.executeUpdate();
		con.close();
	}
}

Retrieve rows using CachedRowSet

The following program is used to retrieve rows from Books table using CachedRowSet. The recommended way to get CachedRowSet is to create it using RowSetFactory object, as shown in this program.

// List titles from BOOKS table 
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class ListBooks {
     public static void main(String[] args) throws Exception {
		RowSetFactory factory = RowSetProvider.newFactory();
		CachedRowSet crs = factory.createCachedRowSet();
		
		crs.setUrl("jdbc:derby:c:\\dev\\java\\testdb");
		crs.setCommand("select * from app.books");
		crs.execute();
		
		while(crs.next())
			 System.out.println(crs.getString("title"));
		
		crs.close();
	}
}

What appeals to me the most about Derby is the fact that we can use it without any installation and administration. Unlike other big databases like Oracle or MySQL, Derby uses very small footprint.

For more information about Apache Derby database, visit Apache Derby official website.