Derby Embedded Database for Spring Boot

In this post, We will see how we can use Apache Derby embedded database with Spring Boot applications with an example.

Introduction

Apache Derby is a pure java implementation of a relational database. Also, it complies with JDBC and RDBMS standards. For this reason, we can easily embed Derby into applications.

Derby Dependencies for Spring Boot

To include derby into the project, All you have to do is to add the Derby and JPA Starter dependencies from Spring Boot.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derby</artifactId>
</dependency>Code language: HTML, XML (xml)

Notice that we did not specify any versions as spring boot takes care of all of its dependencies through its spring-boot-dependencies BOM.

Spring Boot configuration

Spring Boot takes care of most of the configuration. For instance, it generates a memory-based derby connection string, a user with the name “sa” and an empty password. However, You can override this using the following properties.

spring.datasource.url=jdbc:derby:memory:local;create=true
spring.datasource.username=derbyuser
spring.datasource.password=passwordCode language: Properties (properties)

You can run the database in directory mode so that you can store the data in the filesystem. This way, you can persist the data over application restarts.

For example, The following setting would set up a database directory in the application working directory.

spring.datasource.url=jdbc:derby:directory:mydbdirectory;create=trueCode language: Properties (properties)

Unlike the h2 database, Spring Boot doesn’t support any web-based console for Derby.

Initializing Schema

All embedded databases would require initializing Schema. This is because the embedded databases are not persistent and would lose data on restart. To achieve this, Spring Boot expects a schema.sql and data.sql file in the classpath.

If you have different file names, you can use the following properties.

spring.datasource.schema=initial-schema.sql
spring.datasource.data=initial-data.sqlCode language: Properties (properties)

You can also disable initialization by adjusting the mode.

spring.datasource.initialization-mode=neverCode language: Properties (properties)

And if you have the JPA module, then hibernate will initialize the schema. In that case, you only need data.sql. However, since the recent spring boot version, the data.sql will be loaded even before the schema initialization. To get the old behavior, you should add the following flag.

spring.jpa.defer-datasource-initialization=trueCode language: Properties (properties)

Let’s test out our setup. Here is a sample database entity and the data to be loaded for it.

@Entity
public class UserEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String firstName;
    private String lastName;
}Code language: Java (java)

insert into user_entity (id,first_name,last_name) values (1,'Homer','Simpson');
insert into user_entity (id,first_name,last_name) values (2,'Bart','Simpson');
insert into user_entity (id,first_name,last_name) values (3,'Lisa','Simpson');Code language: SQL (Structured Query Language) (sql)

And here is the API call for looking up the initialized data.

@GetMapping("/test")
public List<UserEntity> getUsers(){
     return userRepository.findAll();
}Code language: Java (java)

When starting the application, you may see the following warning.

Caused by: org.apache.derby.iapi.error.StandardException: ‘DROP TABLE’ cannot be performed on ‘USER_ENTITY’ because it does not exist.

It is because the derby instance is refreshed every time and it doesn’t have any table to be dropped. However, spring boot thinks it needs to clear the tables. As this is just a warning, you can ignore this stack trace.

Conclusion

To summarize, We learned how to include derby into a Spring boot application with an example. The sample code for this guide can be found in this github repository.