Apache Derby Database Tutorials With examples

In this blog post, learn Apache Derby Database tutorials with examples.

Apache Derby Database tutorials

It is an Opensource relational Database developed completely in Java language. It supports the ANSI-SQL standard. It uses as Embedded in java applications or can act as an independent database server.

Features

It is relatively small in size around 4MB. It supports JDBC and ANSI-SQL Standards It is simple to install and Setup

Embedded Derby Database

This database runs inside the application in the same JVM. Application.

Uses JDBC code to connect to Database. When an application is stopped, the Database also stops its instance. Data will be saved in memory and data is gone once the application is stopped.

The database is configured to save data to the file system instead of memory.

How do you configure the derby in-memory database in the spring boot application?

It is easy to configure derby as an embedded database in the spring boot application.

First, add below 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

The property spring.jpa.hibernate.ddl-auto needs to be specified, create-drop - creates the database when the application starts, and drop the database during the application stops.

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

if you want to persist the database, you can 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 the configuration is added, You can add controller, services, and repository classes to interact with the derby database.

Apache Server Derby Database

This run as a separate server, You can assign a port number and hostname, This will be accessed by any application using the hostname.

apache derby installation

Derby is based on the java version, to install it, JDK needs to be installed first.

First, run the below command, to check whether java is installed or not.

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 zip file from here

DB-derby-10.14.2.0-bin.zip file downloaded into your computer.

zip the above file and copy it to the c drive

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

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

Please run the below startNetworkServer command to start derby in server mode

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

It starts with default port 1527

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 interactive mode,

Please run the below command to 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

To sum up, Derby is an open-source database written in java language, It is useful to store the configuration data in java and spring applications, and the tutorial covered how to install it in embedded and server mode.