[SpringBoot DB series] h2databse integration example demo
H2dabase is a memory based database, which is more common in the use scenario of embedded database. It has small dependence and complete functions; Generally speaking, it is not used in many normal business projects, but it is useful in some special case s, such as unit testing, business caching, some simple example demo s, etc; This article will teach you how to create a project that inherits h2dabase and supports the import of predetermined schema and data from sql
<!-- more -->
1. Project creation
The corresponding example demo of this article adopts springboot 2.2.1 Release + Maven 3.5.3 + idea for development
1. pom configuration
How to create a springboot project will not be introduced in this article. In the project we created, POM The XML file is as follows
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
</dependencies>
<build>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</pluginManagement>
</build>
<repositories>
<repository>
<id>spring-snapshots</id>
<name>Spring Snapshots</name>
<url>https://repo.spring.io/libs-snapshot-local</url>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
<repository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https://repo.spring.io/libs-milestone-local</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
<repository>
<id>spring-releases</id>
<name>Spring Releases</name>
<url>https://repo.spring.io/libs-release-local</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>Focus on COM. In dependency H2database, the other two are not necessary, but will be used in the following test cases. It is recommended to add
From the above introduction, we can also know that we will operate the database with the help of JPA
2. Attribute configuration
Since it is connected to the database, of course, the relevant configuration of the database is indispensable. Under the resource path of the project, create a new configuration file application properties
# Related configuration of database spring.datasource.url=jdbc:h2:~/h2-db spring.datasource.username=test spring.datasource.password= spring.datasource.driverClassName=org.h2.Driver
The above configuration method is nothing special from our mysql database configuration. Please pay attention to the url here
- JDBC: H2: ~ / H2 DB: the embedded usage gesture will generate an H2 dB mv. DB file (database schema and d column exist in it)
- jdbc:h2:mem:DBName;DB_CLOSE_DELAY=-1: in memory mode, the database will be emptied after the application is restarted, so this method can be considered in the test case
In addition to the above embedded usage posture, H2 dabase also supports specifying a remote directory through tcp
- jdbc:h2:tcp://localhost/~/test
The above is the basic configuration of h2dabase. For a more friendly display, we have opened the web console of h2dabase
##h2 web console settings spring.datasource.platform=h2 #After this configuration, h2 web consloe can be accessed remotely. Otherwise, it can only be accessed locally. spring.h2.console.settings.web-allow-others=true #With this configuration, you can use your_ URL / h2visit h2 web consloe spring.h2.console.path=/h2 #With this configuration, h2 web consloe will be started when the program is started spring.h2.console.enabled=true
You'd better start the sql statement of jpa
spring.jpa.show-sql=true spring.jpa.generate-ddl=true
II. Example test
After the above configuration is completed, it can be said that the integration of h2dabase is basically completed
0. Entrance
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class);
}
}The startup entry of SpringBoot application. After the above execution, we can use http://localhost:8080/h2 Access the console of h2dabase and pay attention to the contents in the box below, which is consistent with the previous configuration file
After logging in, it is a recommended database operation console
1. Entity definition
The following knowledge points belong to JPA. For those interested in JPA, you can take a look at the previous ones JPA series tutorials
@Entity
@Table(name = "test")
public class TestEntity {
@Id
private Integer id;
@Column
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}2. Repository interface
The database operation interface can directly use the default curd without additional adding methods
@Repository
public interface TestRepository extends CrudRepository<TestEntity, Integer> {
}3. Test case
Next, let's give some CURD test case s to demonstrate our integration effect
@RestController
public class TestController {
@Autowired
private TestRepository testRepository;
@GetMapping("/save")
public TestEntity save(Integer id, String name) {
TestEntity testEntity = new TestEntity();
testEntity.setId(id);
testEntity.setName(name);
return testRepository.save(testEntity);
}
@GetMapping("/update")
public TestEntity update(Integer id, String name) {
Optional<TestEntity> entity = testRepository.findById(id);
TestEntity testEntity = entity.get();
testEntity.setName(name);
return testRepository.save(testEntity);
}
@GetMapping("/list")
public Iterable list() {
return testRepository.findAll();
}
@GetMapping("/get")
public TestEntity get(Integer id) {
return testRepository.findById(id).get();
}
@GetMapping("/del")
public boolean del(Integer id) {
testRepository.deleteById(id);
return true;
}
}The measured case s are as follows:
# Add a new record curl 'http://localhost:8080/save? Id = 1 & name = one gray ' # Query record curl 'http://localhost:8080/get?id=1' # Modify record curl 'http://localhost:8080/update? Id = 1 & name = a gray Blog ' # Query all curl 'http://localhost:8080/list' # Delete record curl 'http://localhost:8080/del?id=1'
4. sql file import
Note that in all our previous steps, there is no explanation that we need to take the initiative to create a table called test, which is different from mysql we are familiar with;
Sometimes we may want to initialize the database with the prepared sql file. At this time, we can do the following
Corresponding sql file
Table structure schema-h2 sql
DROP TABLE IF EXISTS book_to_book_store;
DROP TABLE IF EXISTS book_store;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS author;
DROP SEQUENCE IF EXISTS s_author_id;
CREATE SEQUENCE s_author_id START WITH 1;
CREATE TABLE author (
id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
year_of_birth INT,
address VARCHAR(50),
CONSTRAINT pk_t_author PRIMARY KEY (ID)
);
CREATE TABLE book (
id INT NOT NULL,
author_id INT NOT NULL,
co_author_id INT,
details_id INT,
title VARCHAR(400) NOT NULL,
published_in INT,
language_id INT,
content_text CLOB,
content_pdf BLOB,
rec_version INT,
rec_timestamp TIMESTAMP,
CONSTRAINT pk_t_book PRIMARY KEY (id),
CONSTRAINT fk_t_book_author_id FOREIGN KEY (author_id) REFERENCES author(id),
CONSTRAINT fk_t_book_co_author_id FOREIGN KEY (co_author_id) REFERENCES author(id)
);
CREATE TABLE book_store (
name VARCHAR(400) NOT NULL,
CONSTRAINT uk_t_book_store_name PRIMARY KEY(name)
);
CREATE TABLE book_to_book_store (
book_store_name VARCHAR(400) NOT NULL,
book_id INTEGER NOT NULL,
stock INTEGER,
CONSTRAINT pk_b2bs PRIMARY KEY(book_store_name, book_id),
CONSTRAINT fk_b2bs_bs_name FOREIGN KEY (book_store_name)
REFERENCES book_store (name)
ON DELETE CASCADE,
CONSTRAINT fk_b2bs_b_id FOREIGN KEY (book_id)
REFERENCES book (id)
ON DELETE CASCADE
);Data file data-h2 sql
INSERT INTO author VALUES (next value for s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null);
INSERT INTO author VALUES (next value for s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null);
INSERT INTO book VALUES (1, 1, null, null, '1984', 1948, 1, 'To know and not to know, to be conscious of complete truthfulness while telling carefully constructed lies, to hold simultaneously two opinions which cancelled out, knowing them to be contradictory and believing in both of them, to use logic against logic, to repudiate morality while laying claim to it, to believe that democracy was impossible and that the Party was the guardian of democracy, to forget, whatever it was necessary to forget, then to draw it back into memory again at the moment when it was needed, and then promptly to forget it again, and above all, to apply the same process to the process itself -- that was the ultimate subtlety; consciously to induce unconsciousness, and then, once again, to become unconscious of the act of hypnosis you had just performed. Even to understand the word ''doublethink'' involved the use of doublethink..', null, 1, '2010-01-01 00:00:00');
INSERT INTO book VALUES (2, 1, null, null, 'Animal Farm', 1945, 1, null, null, null, '2010-01-01 00:00:00');
INSERT INTO book VALUES (3, 2, null, null, 'O Alquimista', 1988, 4, null, null, 1, null);
INSERT INTO book VALUES (4, 2, null, null, 'Brida', 1990, 2, null, null, null, null);
INSERT INTO book_store (name) VALUES
('Orell Füssli'),
('Ex Libris'),
('Buchhandlung im Volkshaus');
INSERT INTO book_to_book_store VALUES
('Orell Füssli', 1, 10),
('Orell Füssli', 2, 10),
('Orell Füssli', 3, 10),
('Ex Libris', 1, 1),
('Ex Libris', 3, 2),
('Buchhandlung im Volkshaus', 3, 1);The above two files are ready. How do we import them next?
With the help of SQL Maven plugin, add the following paragraph to the pom configuration file
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>sql-maven-plugin</artifactId>
<executions>
<execution>
<id>create-database-h2</id>
<phase>generate-sources</phase>
<goals>
<goal>execute</goal>
</goals>
</execution>
</executions>
<configuration>
<driver>org.h2.Driver</driver>
<url>jdbc:h2:~/h2-db</url>
<username>test</username>
<password></password>
<autocommit>true</autocommit>
<srcFiles>
<srcFile>src/main/resources/schema-h2.sql</srcFile>
<srcFile>src/main/resources/data-h2.sql</srcFile>
</srcFiles>
</configuration>
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.200</version>
</dependency>
</dependencies>
</plugin>
</plugins>Then do the following
After the import is successful, go to the h2 console to view the corresponding data
II. other
0. Project
- Project: https://github.com/liuyueyi/spring-boot-demo
- Project source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/100-h2database
1. A gray Blog
The above contents are only the words of one family. Due to limited personal ability, it is inevitable that there are omissions and mistakes. If you find a bug or have better suggestions, you are welcome to criticize and correct and be grateful
The following is a gray personal blog, which records all blog posts in study and work. Welcome to visit
- A personal Blog https://blog.hhui.top
- A gray blog spring special blog http://spring.hhui.top
