Getting Started With Apache Derby

$ ls 
bin  demo  docs  index.html  javadoc  KEYS  lib  LICENSE  NOTICE  RELEASE-NOTES.html  test

export DERBY_HOME=/opt/derby-db

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

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

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

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

• 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.