Apache Derby Database Tutorials With examples


In this blog post, We are going to learn Apache Derby Database tutorials with examples

Apache Derby Database tutorials

It is Opensource relational Database developed completely in Java language. It supports ANSI-SQL standard. This can be used in Embedded in java application or can act as 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 application is stopped, Database also stop its instance. Data will be saved in memory and data is gone once application is stopped. Database can be configured to save data to file system instead of memory.

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

It is very easy to configure derby as embedded database in 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 that needs to specified, create-drop - creates the database when application starts, drop the database during application stopped.

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

if you want persisted the database, you can set spring.jpa.hibernate.ddl-auto=update

Here is 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 configuration is added, You can add controller,services and repository classes to interact with derby database

Server Derby Database

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

apache derby installation

Derby is based on java version, to install it, jdk needs to be installed first

First run 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 copied to c drive

c:\db-derby-10.14.2.0-bin

create a 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 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 installation using derby ij command

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

This gives version and open interactive mode, that means installation is succesfull.

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 below URL

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

Create a sample database

From interactive mode,

Please run below command to connect to 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 create and insert tables in a database

Conclusion

To sump, Derby is open source database written in java language, This will be useful to store the configuration data in java and spring applications, This tutorial covered how to install in embedded and server mode.

THE BEST NEWSLETTER ANYWHERE
Join 6,000 subscribers and get a daily digest of full stack tutorials delivered to your inbox directly.No spam ever. Unsubscribe any time.

Similar Posts
Subscribe
You'll get a notification every time a post gets published here.