Spring EmbeddedDatabaseBuilder tutorial
last modified October 18, 2023
Spring EmbeddedDatabaseBuilder tutorial shows how to use EmbeddedDatabaseBuilder to create an H2 embedded database in a Spring application.
Spring is a popular Java application framework.
EmbeddedDatabaseBuilder
EmbeddedDatabaseBuilder is a Spring builder which provides convenient
API to create an embedded datbase in a Spring application.
Spring EmbeddedDatabaseBuilder example
The following example uses EmbeddedDatabaseBuilder to build
an embedded H2 database. We use Spring JdbcTemplate to interact with the database.
pom.xml
src
├───main
│ ├───java
│ │ └───com
│ │ └───zetcode
│ │ │ Application.java
│ │ ├───config
│ │ │ DBConfig.java
│ │ └───model
│ │ Car.java
│ └───resources
│ │ logback.xml
│ └───db
│ create-db.sql
│ insert-data.sql
└───test
└───java
This is the project structure.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zetcode</groupId>
<artifactId>embeddeddatabasebuilderex</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
<spring-version>5.3.23</spring-version>
</properties>
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.197</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.4.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring-version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>3.1.0</version>
<configuration>
<mainClass>com.zetcode.Application</mainClass>
</configuration>
</plugin>
</plugins>
</build>
</project>
This is the Maven build file for our Spring application.
resources/logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<logger name="org.springframework" level="ERROR"/>
<logger name="com.zetcode" level="INFO"/>
<appender name="consoleAppender" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<Pattern>%d{HH:mm:ss.SSS} %blue(%-5level) %magenta(%logger{36}) - %msg %n
</Pattern>
</encoder>
</appender>
<root>
<level value="INFO" />
<appender-ref ref="consoleAppender" />
</root>
</configuration>
This is the Logback configuration file.
The following two SQL scripts will be used by EmbeddedDatabaseBuilder
to create a cars table and insert data into it.
resources/db/schema.sql
CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(150), price INT);
The schema.sql creates the database table.
resources/db/data.sql
INSERT INTO cars(name, price) VALUES('Audi', 52642);
INSERT INTO cars(name, price) VALUES('Mercedes', 57127);
INSERT INTO cars(name, price) VALUES('Skoda', 9000);
INSERT INTO cars(name, price) VALUES('Volvo', 29000);
INSERT INTO cars(name, price) VALUES('Bentley', 350000);
INSERT INTO cars(name, price) VALUES('Citroen', 21000);
INSERT INTO cars(name, price) VALUES('Hummer', 41400);
INSERT INTO cars(name, price) VALUES('Volkswagen', 21600);
The data.sql inserts data into the table.
com/zetcode/model/Car.java
package com.zetcode.model;
import java.util.Objects;
public class Car {
private Long id;
private String name;
private int price;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Car car = (Car) o;
return price == car.price &&
Objects.equals(id, car.id) &&
Objects.equals(name, car.name);
}
@Override
public int hashCode() {
return Objects.hash(id, name, price);
}
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("Car{");
sb.append("id=").append(id);
sb.append(", name='").append(name).append('\'');
sb.append(", price=").append(price);
sb.append('}');
return sb.toString();
}
}
This is a Car class.
com/zetcode/config/DBConfig.java
package com.zetcode.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import javax.sql.DataSource;
@Configuration
public class DBConfig {
@Bean
public DataSource dataSource() {
var builder = new EmbeddedDatabaseBuilder();
var db = builder
.setType(EmbeddedDatabaseType.H2) // HSQL or DERBY
.addScript("db/schema.sql")
.addScript("db/data.sql")
.build();
return db;
}
@Bean
public JdbcTemplate createJdbcTeamplate() {
var template = new JdbcTemplate();
template.setDataSource(dataSource());
return template;
}
}
The DBConfig uses the EmbeddedDatabaseBuilder to create
an embedded H2 database. We also create a JdbcTemplate bean.
@Bean
public DataSource dataSource() {
var builder = new EmbeddedDatabaseBuilder();
var db = builder
.setType(EmbeddedDatabaseType.H2) // HSQL or DERBY
.addScript("db/schema.sql")
.addScript("db/data.sql")
.build();
return db;
}
The method creates a datasource with EmbeddedDatabaseBuilder's build
method. We specify the databas type with setType and add SQL scripts with
addScript methods.
@Bean
public JdbcTemplate createJdbcTeamplate() {
var template = new JdbcTemplate();
template.setDataSource(dataSource());
return template;
}
This method generates a new JdbcTemplate. We set the generated
datasource to the template with setDataSource.
com/zetcode/Application.java
package com.zetcode;
import com.zetcode.model.Car;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
@ComponentScan(basePackages = "com.zetcode")
public class Application {
private static final Logger logger = LoggerFactory.getLogger(Application.class);
public static void main(String[] args) {
var ctx = new AnnotationConfigApplicationContext(Application.class);
var app = ctx.getBean(Application.class);
app.run();
ctx.close();
}
@Autowired
private JdbcTemplate jdbcTemplate;
private void run() {
var sql = "SELECT * FROM cars";
var cars = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Car.class));
cars.forEach(car -> logger.info("{}", car));
}
}
In the application, we execute a query which finds all cars.
@Autowired private JdbcTemplate jdbcTemplate;
A JdbcTemplate bean is injected.
var cars = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Car.class));
An SQL query is executed with JdbcTemplate's query method.
cars.forEach(car -> logger.info("{}", car));
The retrieved cars are written to the console.
$ mvn -q exec:java
19:13:35.753 INFO com.zetcode.Application - Car{id=1, name='Audi', price=52642}
19:13:35.768 INFO com.zetcode.Application - Car{id=2, name='Mercedes', price=57127}
19:13:35.768 INFO com.zetcode.Application - Car{id=3, name='Skoda', price=9000}
19:13:35.768 INFO com.zetcode.Application - Car{id=4, name='Volvo', price=29000}
19:13:35.768 INFO com.zetcode.Application - Car{id=5, name='Bentley', price=350000}
19:13:35.768 INFO com.zetcode.Application - Car{id=6, name='Citroen', price=21000}
19:13:35.768 INFO com.zetcode.Application - Car{id=7, name='Hummer', price=41400}
19:13:35.768 INFO com.zetcode.Application - Car{id=8, name='Volkswagen', price=21600}
We run the application.
In this article we have used EmbeddedDatabaseBuilder to create an
embedded H2 database in a Spring application.
Author
List all Spring tutorials.