--Try sample code that works with Spring Boot + Spring JDBC --Use H2 Database for database
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.
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("");
}
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 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
--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 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
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"}]
Recommended Posts