LS Price Increase Launch

The Price of all “Learn Spring” course packages will increase by $40 on next Friday:

>> GET ACCESS NOW

1. Overview

In this tutorial, we'll learn about the Apache Derby database engine, which is a Java-based relational database engine developed by the Apache Software Foundation as an open-source project.

We'll start with installing and configuring it and then look at the tools it provides for interacting with it. After creating a sample database, we'll learn how to execute SQL commands using Derby's command-line tools. Finally, we'll see how to connect to the database programmatically using plain JDBC and through a Spring Boot application.

2. Deployment Modes

Apache Derby has two basic deployment options: a simple embedded option and a client/server option.

In the embedded mode, Derby runs inside of the same Java virtual machine (JVM) as a simple one-user Java application. Due to its automated startup and shutdown, it is usually invisible to end-users and does not require administrator intervention. In this mode, we can set up a temporary database without having to manage it.

In the client/server mode, Derby runs in the Java virtual machine (JVM) that hosts the server when started by an application that provides multi-user connectivity across a network.

3. Installation And Configuration

Let's see how we can install Apache Derby.

3.1. Installation

First, we can download the latest version of Apache Derby from here. After that, we extract the file downloaded. The extracted installation contains several subdirectories:

$ ls 
bin  demo  docs  index.html  javadoc  KEYS  lib  LICENSE  NOTICE  RELEASE-NOTES.html  test
  • bin contains the scripts for executing utilities and setting up the environment
  • demo contains example programs
  • javadoc contains the API documentation
  • docs contain the Apache Derby documentation
  • lib contains the Apache Derby .jar files
  • test contains regression tests for Apache Derby

3.2. Configuration

We'll need to configure a couple of things before we start the database engine.

First, we'll set the DERBY_HOME environment variable to where we extracted the Apache Derby bin. For example, we can use the below command if the Apache Derby is installed in the /opt/derby-db directory:

export DERBY_HOME=/opt/derby-db

Then, we add the DERBY_HOME/bin directory to the PATH environment variable so that we can run the Derby scripts from any directory:

export PATH="$DERBY_HOME/bin:$PATH"

3.3. Derby Libraries

There are various libraries provided by Apache Derby in the lib directory:

$ ls
derbyclient.jar     derbynet.jar            derbyshared.jar
derby.jar           derbyoptionaltools.jar  derbytools.jar
derbyrun.jar        derby.war               derbyLocale_XX.jar ...

Each of the libraries is described below:

  • derby.jar: Needed for embedded environments. For client/server environments, we only need this library on the server.
  • derbyshared.jar: Required by all configurations, regardless of whether we are running an embedded engine, a network server, a remote client, or the database tools.
  • derbytools.jar: Required to run all the Apache Derby tools (IJ, DBLook, and import/export). Also required if we are running a network server or if our application directly references the JDBC drivers.
  • derbyrun.jar: Used to start the Apache Derby tools
  • derbynet.jar: Required to start the Apache Derby Network Server
  • derbyclient.jar: Required to use the Apache Derby network client driver
  • derbyLocale_XX.jar: Required to localize the messages of Apache Derby

4. Tools

Apache Derby provides many tools for different applications. We can run the Apache Derby tools and utilities with derbyrun.jar using shortened names, and we do not need to set the java CLASSPATH environment variable. The derbyrun.jar file must be in the same folder as the other Derby JAR files.

4.1. IJ

IJ is a JDBC-based Java command-line application. Its primary purpose is to allow the execution of Derby SQL statements interactively or through scripts.

First, let's run the IJ tool:

$ $DERBY_HOME/bin/ij
ij version 10.13
ij> 

We can also use the following command to execute it:

$ java -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.13
ij> 

Note that all commands end with a semicolon. If we execute a command without a semicolon, the command line will move to the following line.

In the section on using SQL statements, we'll see how to execute several commands using IJ.

In addition, IJ can use properties to execute commands. This can help us save some repetitive work by using the properties supported by the IJ tool.

We can set IJ properties in the following ways:

  • By specifying a properties file using the -p property-file option on the command line
  • By using the -D option on the command line

We can create a properties file, add all properties that are needed, and then run the following command:

$ java -jar derbyrun.jar ij -p file-name.properties

If the file-name.properties file doesn't exist in the current directory, we'll get a java.io.FileNotFoundException.

For example, suppose we want to create a connection to a specific database with a specific name. We can do it with the ij.database property:

$ java -jar -Dij.protocol=jdbc:derby: -Dij.database=baeldung derbyrun.jar ij
ij version 10.13
CONNECTION0* - 	jdbc:derby:baeldung
* = current connection

4.2. DBLook

The dblook tool provides the DDL (Data Definition Language) of the database. For example, we can write the DDL of the baeldung database to the console:

$ $DERBY_HOME/bin/dblook -d jdbc:derby:baeldung
-- Timestamp: 2021-08-23 01:29:48.529
-- Source database is: baeldung
-- Connection URL is: jdbc:derby:baeldung
-- appendLogs: false

-- ----------------------------------------------
-- DDL Statements for schemas
-- ----------------------------------------------

CREATE SCHEMA "basic_users";

-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------

CREATE TABLE "APP"."authors" ("id" INTEGER NOT NULL, "first_name" VARCHAR(255) , "last_name" VARCHAR(255));

-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------

-- PRIMARY/UNIQUE
ALTER TABLE "APP"."authors" ADD CONSTRAINT "SQL0000000000-582f8014-017b-6e26-ada1-00000644e000" PRIMARY KEY ("id");

The dblook has other options that are described below.

We can use -o for writing the DDL to a file like baeldung.sql:

$ sudo $DERBY_HOME/bin/dblook -d jdbc:derby:baeldung -o baeldung.sql

We also can specify the schema with -z and the table with -t:

$ sudo $DERBY_HOME/bin/dblook -d jdbc:derby:baeldung -o baeldung.sql -z SCHEMA_NAME -t "TABLE_NAME"

4.3. Sysinfo

The Apache Derby sysinfo tool displays information regarding our Java environment and the Derby version. In addition, the sysinfo tool displays system information on the console:

$ java -jar $DERBY_HOME/lib/derbyrun.jar sysinfo

------------------ Java Information ------------------
Java Version:    11.0.11
Java Vendor:     Ubuntu
Java home:       /usr/lib/jvm/java-11-openjdk-amd64
Java classpath:  /opt/derby-db/lib/derbyrun.jar
OS name:         Linux
OS architecture: amd64
OS version:      5.11.0-27-generic
Java user name:  arash
Java user home:  /home/arash
Java user dir:   /opt/derby-db
java.specification.name: Java Platform API Specification
java.specification.version: 11
java.runtime.version: 11.0.11+9-Ubuntu-0ubuntu2.20.04
--------- Derby Information --------
[/opt/derby-db/lib/derby.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbytools.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbynet.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbyclient.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbyshared.jar] 10.13.1.1 - (1873585)
[/opt/derby-db/lib/derbyoptionaltools.jar] 10.13.1.1 - (1873585)

5. SQL Statements In Apache Derby

Here we'll examine some of the essential SQL statements that Apache Derby provided. We'll look at each statement's syntax with an example.

5.1. Create Database

We can create a new database with the connect command and create=true attribute in our connection string:

ij> connect 'jdbc:derby:databaseName;create=true';

5.2. Connect to Database

IJ automatically loads the appropriate driver based on the URL syntax when we interact interactively with a Derby database:

ij> connect 'jdbc:derby:baeldung' user 'user1' password 'pass123';

5.3. Create Schema

The CREATE SCHEMA statement defines a schema, which is a way to identify a specific namespace for a set of objects:

CREATE SCHEMA schema_name AUTHORIZATION userName;

Schema names cannot exceed 128 characters and must be unique within the database also. In addition, schema names cannot begin with the prefix SYS.

Here is an example of a schema named baeldung_authors:

ij> CREATE SCHEMA baeldung_authors;

In addition, we can create a schema for baeldung_authors that just specific users with ID arash can access:

ij> CREATE SCHEMA baeldung_authors AUTHORIZATION arash;

5.4. Drop Schema

We can drop a schema by using the DROP SCHEMA statement, and also, the target schema must be empty for DROP SCHEMA to succeed. We cannot drop the APP schema (the default user schema) or the SYS schema:

DROP SCHEMA schema_name RESTRICT;

By using the RESTRICT keyword, we can enforce the rule that there cannot be any objects defined in a specified schema for the schema to be deleted from the database.

Let's see an example to drop the schema:

ij> DROP SCHEMA baeldung_authors RESTRICT;

5.5. Create Table

We can use the CREATE TABLE statement to create a table, which contains columns and constraints:

CREATE TABLE table_name (
   column_name1 column_data_type1 constraint (optional),
   column_name2 column_data_type2 constraint (optional),
);

Let's see an example:

ij> CREATE TABLE posts(post_id INT NOT NULL, publish_date DATE NOT NULL,
    view_number INT DEFAULT 0, PRIMARY KEY (post_id));

If we do not specify a default value, NULL is inserted in the column as the default value

Other SQL statements like INSERT, UPDATE, DELETE, and SELECT for CRUD actions are similar to standard SQL.

6. JDBC Programming With Apache Derby

Here we're going to learn how to create a Java application that uses Apache Derby as a database engine.

6.1. Maven Dependencies

There are two Derby drivers in Maven: derby and derbynet. The former is used for embedded applications, while the latter is used for client/server applications.

Let's add a Maven dependency for derby:

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
    <version>10.13.1.1</version>
</dependency>

Also, we add Maven dependency for derbyclient:

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.13.1.1</version>
</dependency>

6.2. JDBC URL Connection

We can connect to the database with different connection string parameters for client/server and embedded applications.

In the below syntax, we can use it for embedded mode:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]

And also, we can use the syntax below for client/server mode:

jdbc:derby://server[:port]/databaseName[;attribute=value]

The database URL doesn't contain the hostname and port number in embedded mode. For example:

String urlConnection = "jdbc:derby:baeldung;create=true";

6.3. Use Apache Derby In Embedded Mode

Let's connect to an Apache Derby database in embedded mode, create it in the current directory if it doesn't already exist, create a table, and insert rows into the table using SQL statements:

String urlConnection = "jdbc:derby:baeldung;create=true";
Connection con = DriverManager.getConnection(urlConnection);
Statement statement = con.createStatement();
String sql = "CREATE TABLE authors (id INT PRIMARY KEY,first_name VARCHAR(255),last_name VARCHAR(255))";
statement.execute(sql);
sql = "INSERT INTO authors VALUES (1, 'arash','ariani')";
statement.execute(sql);

6.4. Use Apache Derby In Client/Server Mode

First, we run the command below to start up the Apache Derby in client/server mode:

$ java -jar $DERBY_HOME/lib/derbyrun.jar server start 
Sat Aug 28 20:47:58 IRDT 2021 : Security manager installed using the Basic server security policy.
Sat Aug 28 20:47:58 IRDT 2021 : Apache Derby Network Server - 10.13.1.1 -
(1873585) started and ready to accept connections on port 1527

We use the JDBC API to connect to an Apache Derby server on localhost and select all entries from the authors table in the baeldung database:

String urlConnection = "jdbc:derby://localhost:1527/baeldung";
   try (Connection con = DriverManager.getConnection(urlConnection)) {
       Statement statement = con.createStatement();
       String sql = "SELECT * FROM authors";
       ResultSet result = statement.executeQuery(sql);
         while (result.next()) {
           // We can print or use ResultSet here
         }
   } catch (SQLException ex) {
       ex.printStackTrace();
 }

7. Apache Derby With Spring Boot

This section will not explain how to create an application based on Spring Boot in detail, but only the settings relating to interacting with the Apache Derby database.

7.1. Dependencies for Apache Derby

We just have to add the Spring Data and Apache Derby dependencies from Spring Boot to the project to incorporate Apache Derby into it:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.13.1.1</version>
</dependency>

7.2. Spring Boot Configuration

We can use Derby as our persistent database by adding these application properties:

spring.datasource.url=jdbc:derby://localhost:1527/baeldung 
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DerbyTenSevenDialect 
spring.jpa.hibernate.ddl-auto=update
spring.datasource.driver-class-name=org.apache.derby.jdbc.ClientDriver

8. Conclusion

In this tutorial, we've looked into installing and configuring the Apache Derby. After that, we had an overview of tools and some of the most important SQL statements. We covered JDBC programming with snippet codes in Java and finally learned how to configure Spring Boot to use Apache Derby as a persistent database.

As usual, the examples used in this article are available over on GitHub.

LS Price Increase Launch

The Price of all “Learn Spring” course packages will increase by $40 on next Friday:

>> GET ACCESS NOW
Persistence footer banner
Comments are closed on this article!