Use Spring JDBC with Spring Boot

Overview

--Try sample code that works with Spring Boot + Spring JDBC --Use H2 Database for database

Default configuration information when using H2 Database with Spring Framework

Exporting the default settings to application.properties looks like this:

applicatin.properties


spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.initialization-mode=embedded

This setting is applied automatically, so there is no need to describe it in application.properties. If you want to use a value other than this setting value, you need to describe it in application.properties.

The default setting information is described in the following Spring JDBC source code.

spring-framework/H2EmbeddedDatabaseConfigurer.java at v5.2.0.RC1 · spring-projects/spring-framework · GitHub

ClassUtils.forName("org.h2.Driver", H2EmbeddedDatabaseConfigurer.class.getClassLoader()));
@Override
public void configureConnectionProperties(ConnectionProperties properties, String databaseName) {
	properties.setDriverClass(this.driverClass);
	properties.setUrl(String.format("jdbc:h2:mem:%s;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false", databaseName));
	properties.setUsername("sa");
	properties.setPassword("");
}

spring-framework/EmbeddedDatabaseFactory.java at v5.2.0.RC1 · spring-projects/spring-framework · GitHub

public static final String DEFAULT_DATABASE_NAME = "testdb";

The setting items of application.properties are [Spring Boot Reference Documentation --Appendices --Appendice A: Common application properties](https://docs.spring.io/spring-boot/docs/2.2.0.M5/reference/html/appendix] .html # appendix).

schema.sql and data.sql

schema.sql and data.sql are files that describe SQL statements that are executed when Spring Boot starts.

--Specify SQL statements such as DDL (Data Definition Language) table creation in schema.sql --Write SQL statements such as DML (Data Manipulation Language) record addition in data.sql

It is also possible to change the SQL statement executed depending on the database type, such as schema-h2.sql and data-h2.sql.

“How-to” Guides - 10. Database Initialization

Spring Boot can automatically create the schema (DDL scripts) of your DataSource and initialize it (DML scripts). It loads SQL from the standard root classpath locations: schema.sql and data.sql, respectively. In addition, Spring Boot processes the schema-${platform}.sql and data-${platform}.sql files (if present), where platform is the value of spring.datasource.platform. This allows you to switch to database-specific scripts if necessary. For example, you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql, and so on).

It is also possible to change the SQL file to be read by describing it in applicatin.properties.

applicatin.properties


spring.datasource.schema=classpath:foo-schema.sql
spring.datasource.data=classpath:bar-data.sql

Sample code

Source code list

--pom.xml: Maven build configuration file --.java: Java source code file --.sql: File that describes the SQL statement to be executed at startup

├── pom.xml
└── src
    └── main
        ├── java
        │   └── com
        │       └── example
        │           ├── Person.java
        │           ├── SampleController.java
        │           └── SampleService.java
        └── resources
            ├── data.sql
            └── schema.sql

pom.xml

Maven build configuration file.

<?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>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.0.M5</version>
    <relativePath/>
  </parent>

  <groupId>com.example</groupId>
  <artifactId>spring-jdbc-sample</artifactId>
  <version>0.0.1</version>
  <name>spring-jdbc-sample</name>
  <description>Spring JDBC sample</description>

  <properties>
    <java.version>11</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!--Use Spring JDBC-->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!--Use H2 Database-->
    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <scope>runtime</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

  <repositories>
    <repository>
      <id>spring-milestones</id>
      <name>Spring Milestones</name>
      <url>https://repo.spring.io/milestone</url>
    </repository>
  </repositories>
  <pluginRepositories>
    <pluginRepository>
      <id>spring-milestones</id>
      <name>Spring Milestones</name>
      <url>https://repo.spring.io/milestone</url>
    </pluginRepository>
  </pluginRepositories>

</project>

SampleController.java

A class for receiving HTTP requests.

package com.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@SpringBootApplication
@RestController
public class SampleController {

  public static void main(String[] args) {
    SpringApplication.run(SampleController.class, args);
  }

  @Autowired
  private SampleService service;

  //Adds data with the specified name
  @RequestMapping("/add/{name}")
  public List<Person> index(@ModelAttribute Person person) {
    service.save(person);
    return service.findAll();
  }
}

SampleService.java

A class that manipulates the database using the Spring JDBC JdbcTemplate class.

package com.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class SampleService {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  //Returns a list of data
  public List<Person> findAll() {
    //Assemble and execute SQL to be executed
    String query = "SELECT * from person";
    List<Person> persons = jdbcTemplate.query(query, new BeanPropertyRowMapper<>(Person.class));
    return persons;
  }

  //Add data
  public Person save(Person person) {
    //Assemble the SQL to execute
    SqlParameterSource param = new BeanPropertySqlParameterSource(person);
    SimpleJdbcInsert insert =
      new SimpleJdbcInsert(jdbcTemplate)
        .withTableName("person")
        .usingGeneratedKeyColumns("id");
    //Execute SQL and AUTO_Get the value of INCREMENT
    Number key = insert.executeAndReturnKey(param);
    person.setId(key.longValue());
    System.out.println("Add: " + person);
    return person;
  }
}

Person.java

A class that represents one record in the database.

package com.example;

public class Person {

  private Long id; // AUTO_Give ID with INCREMENT
  private String name;

  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;
  }

  @Override
  public String toString() {
    return "Person(id=" + id + ", name=" + name + ")";
  }
}

schema.sql

A file that describes the SQL statements that are executed at startup.

CREATE TABLE IF NOT EXISTS person (
  id   INTEGER      NOT NULL AUTO_INCREMENT,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
);

data.sql

A file that describes the SQL statements that are executed at startup.

INSERT INTO person (name) VALUES('Alice');

Build and launch Spring Boot

Build with mvn command and generate JAR file.

$ mvn package

Start Spring Boot with java command.

$ java -jar target/spring-jdbc-sample-0.0.1.jar

Access the launched Spring Boot

You can see how the data is added to the database.

$ curl http://localhost:8080/add/Bob
[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]
$ curl http://localhost:8080/add/Cindy
[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Cindy"}]

This operation check environment

Reference material

Recommended Posts

Use Spring JDBC with Spring Boot
Beginning with Spring Boot 0. Use Spring CLI
Use cache with EhCashe 2.x with Spring Boot
Download with Spring Boot
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
How to use built-in h2db with spring boot
Generate barcode with Spring Boot
Hello World with Spring Boot
Implement GraphQL with Spring Boot
Get started with Spring boot
Run LIFF with Spring Boot
SNS login with Spring Boot
File upload with Spring Boot
Spring Boot starting with copy
Spring Boot + Springfox springfox-boot-starter 3.0.0 Use
Hello World with Spring Boot
Add module with Spring Boot
Getting Started with Spring Boot
Create microservices with Spring Boot
Use SpatiaLite with Java / JDBC
Send email with spring boot
Settings for connecting to MySQL with Spring Boot + Spring JDBC
File upload with Spring Boot (do not use Multipart File)
Create an app with Spring Boot 2
How to use Spring Data JDBC
Database linkage with doma2 (Spring boot)
Spring Boot programming with VS Code
Until "Hello World" with Spring Boot
Get validation results with Spring Boot
(Intellij) Hello World with Spring Boot
Create an app with Spring Boot
Google Cloud Platform with Spring Boot 2.0.0
Use DBUnit for Spring Boot test
Check date correlation with Spring Boot
How to use ModelMapper (Spring boot)
I tried GraphQL with Spring Boot
[Java] LINE integration with Spring Boot
I tried Flyway with Spring Boot
Message cooperation started with Spring Boot
Spring Boot gradle build with Docker
Use thymeleaf3 with parent without specifying spring-boot-starter-parent in Spring Boot
Processing at application startup with Spring Boot
Hello World with Eclipse + Spring Boot + Maven
Send regular notifications with LineNotify + Spring Boot
Perform transaction confirmation test with Spring Boot
Try using Spring Boot with VS Code
Start web application development with Spring Boot
Launch Nginx + Spring Boot application with docker-compose
Use DynamoDB query method in Spring Boot
Implement CRUD with Spring Boot + Thymeleaf + MySQL
Asynchronous processing with Spring Boot using @Async
Use Spring Security JSP tags with FreeMarker
Implement paging function with Spring Boot + Thymeleaf
Spring Boot Memorandum
gae + spring boot
(IntelliJ + gradle) Hello World with Spring Boot
Form class validation test with Spring Boot
Run WEB application with Spring Boot + Thymeleaf
Achieve BASIC authentication with Spring Boot + Spring Security
Spring Boot environment construction with Docker (January 2021 version)
Create a website with Spring Boot + Gradle (jdk1.8.x)