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

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Spring tutorials.