Apache Derby Database Tutorials With examples

In this blog post, you’ll explore Apache Derby Database tutorials with illustrative examples.

Apache Derby Database tutorials

Apache Derby is an open-source relational database developed entirely in Java. It supports the ANSI-SQL standard and can function as an embedded database within Java applications or as an independent database server.

Features

  • Relatively small in size, around 4MB.
  • Supports JDBC and ANSI-SQL Standards.
  • Simple to install and set up.

Embedded Derby Database

  • This database runs inside the application within the same JVM, utilizing JDBC code to connect to the database.
  • When an application is stopped, the Database also stops its instance.
  • Data will be saved in memory, So any changes made to the data is gone once the application is stopped.

The database can be configured to save data to the file system instead of memory. How to Configure Derby In-Memory Database in a Spring Boot Application?

Configuring Derby as an embedded database in a Spring Boot application is straightforward.

Start by adding the following Maven dependencies

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

In the application.properties, specify the property spring.jpa.hibernate.ddl-auto. For example, update/create-drop creates the database on application start and drops it on application stop.

spring.jpa.hibernate.ddl-auto=update/create-drop

To persist the database, set spring.jpa.hibernate.ddl-auto=update.

Here are complete spring boot application properties

spring.datasource.url=jdbc:derby:mydb;create=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DerbyTenSevenDialect
spring.jpa.hibernate.ddl-auto=update

Once configured, you can add controller, services, and repository classes to interact with the Derby database.

Apache Server Derby Database

This runs as a separate server, allowing you to assign a port number and hostname accessible by any application.

apache derby installation

Derby is Java version-dependent, requiring JDK installation first.

Check Java installation using

A:\Java>java -version
java version "1.8.0_102"
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)

Download the here🔗 file from here and unzip it to a directory, e.g., c:\db-derby-10.14.2.0-bin. Create an environment variable DERBY_HOME.

set DERBY_HOME=c:\db-derby-10.14.2.0-bin

Or in Windows,

  • Go to windows +R command - Edit system/User environment variable

Got to Environment variables- create a new environment variable as shown below

Derby installation on windows tutorials

In the same way, EDIT PATH environment variable add %DERBY_HOME%\bin

Once installation is done, you can verify the installation using the derby ij command.

C:\Users\Kiran>ij
ij version 10.14
ij>

This gives the version an open interactive mode, which means installation is successful.

Now derby installation is done, and ready to start the server

After installation, start the server using startNetworkServer:

C:\>startNetworkServer
Mon Apr 26 17:04:35 IST 2021: Security manager installed using the Basic server security policy.
Mon Apr 26 17:04:37 IST 2021: Apache Derby Network Server - 10.14.2.0 - (1828579) started and ready to accept connections on port 1527

This starts the server on the default port 1527. Y

You can pass command-line arguments -p for port number change and -h for hostname change

startNetworkServer -p [portno] -h [hostname]

You can write a java code to access using the below URL

jdbc:derby://localhost:1527/derbydb;create=true

How to Create a sample database in derby?

From the interactive mode, connect to the database server:

ij> > connect 'jdbc:derby://localhost:1527/derbydb;create=true'

This creates derbydb, There is no command to list out databases directly

You can create and run SQL queries for creating and inserting tables in a database

Conclusion

In conclusion, Apache Derby, being an open-source Java-based database, proves beneficial for storing configuration data in Java and Spring applications. This tutorial covered installation in both embedded and server modes.