Auto Configuration of Databases

Spring Boot can auto-configure embedded H2, HSQL, and Derby databases. We don't need to provide any connection URL or register a bean for the DataSource. We only need to include a build dependency of the embedded database.

Spring Boot can also automatically create the schema (DDL scripts) of our DataSource and initialize it. It loads SQL from the standard root classpath locations: schema.sql and data.sql, respectively.

Auto Configuration of JdbcTemplate

JdbcTemplate and NamedParameterJdbcTemplate classes are auto-configured and we can inject them directly using @Autowire i.e.

@Autowire
private JdbcTemplate jdbcTemplate;

Instead of creating an instance ourselves like we do with plain Spring jdbc application, i.e.

@Autowire
private DataSource dataSource;
 .....
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

Example

This example demonstrates the use H2 embedded database with default Boot settings and directly injecting JdbcTemplate in our DAO class.

pom.xml

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
</dependency>

Dao using JdbcTemplate

@Repository
public class JdbcTemplatePersonDao {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  public void save(Person person) {
      String sql = "insert into Person (first_Name, Last_Name, Address) values (?, ?, ?)";
      jdbcTemplate.update(sql, person.getFirstName(), person.getLastName(),
              person.getAddress());
  }

  public List<Person> loadAll() {
      return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
          return toPerson(resultSet);
      });
  }

  private Person toPerson(ResultSet resultSet) throws SQLException {
      Person person = new Person();
      person.setId(resultSet.getLong("ID"));
      person.setFirstName(resultSet.getString("FIRST_NAME"));
      person.setLastName(resultSet.getString("LAST_NAME"));
      person.setAddress(resultSet.getString("ADDRESS"));
      return person;
  }
}

public class Person {
  private long id;
  private String firstName;
  private String lastName;
  private String address;
    .............
}

src/main/resources/schema.sql

CREATE TABLE PERSON(
ID BIGINT  PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);

src/main/resources/data.sql

insert into PERSON (first_Name, Last_Name, Address) values ('Tom', 'Syke', 'Green Valley');

Example client

@Component
public class ExampleClient {
  @Autowired
  private JdbcTemplatePersonDao dao;

  public void run() {
      Person person = Person.create("Dana", "Whitley", "464 Yellow Drive");
      System.out.println("saving person: " + person);
      dao.save(person);

      person = Person.create("Robin", "Cash", "64 Logic Park");
      System.out.println("saving person: " + person);
      dao.save(person);

      System.out.println("-- loading all --");
      List<Person> persons = dao.loadAll();
      persons.forEach(System.out::println);
  }
}

Main class

@SpringBootApplication
public class SpringBootJdbcH2ExampleMain implements CommandLineRunner {

  @Autowired
  private ExampleClient exampleClient;

  public static void main(String[] args) {
      SpringApplication sa = new SpringApplication(SpringBootJdbcH2ExampleMain.class);
      sa.setBannerMode(Banner.Mode.OFF);
      sa.run(args);
  }

  @Override
  public void run(String... args) {
      exampleClient.run();
  }
}

saving person: Person{id=0, firstName='Dana', lastName='Whitley', address='464 Yellow Drive'}
saving person: Person{id=0, firstName='Robin', lastName='Cash', address='64 Logic Park'}
-- loading all --
Person{id=1, firstName='Tom', lastName='Syke', address='Green Valley'}
Person{id=2, firstName='Dana', lastName='Whitley', address='464 Yellow Drive'}
Person{id=3, firstName='Robin', lastName='Cash', address='64 Logic Park'}

We have filtered the Spring Boot logs in above output.

Quick understanding of embedded database auto configuration

DataSourceAutoConfiguration is responsible to initiate data source auto configuration given that DataSource and EmbeddedDatabaseType classes are on the classpath. It enables DataSourceProperties which invokes EmbeddedDatabaseConnection#get(ClassLoader) to find out what driver class(s) are on the classpath and returns the first corresponding EmbeddedDatabaseConnection enum constant. This enum has all information of what database parameters should be used during auto initialization. For example H2 is defined as:

package org.springframework.boot.jdbc;
public enum EmbeddedDatabaseConnection {
   ....
 H2(EmbeddedDatabaseType.H2, "org.h2.Driver", "jdbc:h2:mem:%s;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"),
  ...
}

Example Project

Dependencies and Technologies Used:

• Spring Boot 2.0.2.RELEASE
  Corresponding Spring Version 5.0.6.RELEASE
• spring-boot-starter-jdbc : Starter for using JDBC with the HikariCP connection pool.
  Uses org.springframework:spring-jdbc version 5.0.6.RELEASE
  Uses com.zaxxer:HikariCP version 2.7.9
• h2 1.4.197: H2 Database Engine.
• JDK 1.8
• Maven 3.3.9