[SpringBoot DB series] H2Databse integrated demo

[SpringBoot DB series] H2Databse integrated demo

H2dabase memory based database, more common in the use of embedded database scenarios, small dependence, complete functions; Generally speaking, it is not used in normal commercial projects, but in some special cases, it is useful, such as for unit testing, business cache, some simple sample demo, etc. This article will give you a hands-on guide to creating a project that inherits from H2dabase and supports the import of predefined Schemas and data from SQL

I. Project creation

The corresponding example demo in this article is developed using SpringBoot 2.2.1.RELEASE + Maven 3.5.3 + IDEA

1. The pom configuration

How to create a SpringBoot project is not covered in this article. In the project we created, the pom.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>
Copy the code

Focus on com.H2Database in dependency. The other two are not required but will be used in later test cases

As you can also see from the above introduction, we will use JPA to manipulate the database

2. Configure properties

Since you are connecting to the database, of course you need to configure the database. In the project resource path, create a new configuration file application.properties

# Database configuration
spring.datasource.url=jdbc:h2:~/h2-db
spring.datasource.username=test
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver
Copy the code

The above configuration method, and our mysql database configuration has nothing special, here the URL please pay attention to

• jdbc:h2:~/h2-db: Embedded use posture, will generate a name in the user root directoryh2-db.mv.db(the database’s schema and D column are stored in this file)
• jdbc:h2:mem:DBName; DB_CLOSE_DELAY=-1: Memory mode. The database will be cleared after the application is restarted, so consider using this in your test case

In addition to the embedded gesture above, h2-Dabase also supports specifying a remote directory over TCP

• jdbc:h2:tcp://localhost/~/test

Above is the basic configuration of H2dabase. For a more user-friendly display, we have opened the H2dabase Web Console

##h2 Web Console setup
spring.datasource.platform=h2
# With 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 access h2 Web Consloe via YOUR_URL/h2
spring.h2.console.path=/h2
H2 Web Consloe will start when the program is started
spring.h2.console.enabled=true
Copy the code

It is a good idea to open the JPA SQL statement

spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
Copy the code

II. Example test

With the above configuration done, the INTEGRATION of H2dabase is basically complete

Entrance 0.

@SpringBootApplication
public class Application {
    public static void main(String[] args) { SpringApplication.run(Application.class); }}Copy the code

After the above execution, we can access the h2dabase console at http://localhost:8080/h2. Note that the content in the box below is consistent with the previous configuration file

Once logged in, a recommended database operations console is in place

1. The Entity definition

The following is the knowledge of JPA, for those interested in JPA, you can see the previous JPA series tutorial

@Entity
@Table(name = "test")
public class TestEntity {
    @Id
    private Integer id;
    @Column
    private String name;

    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName(a) {
        return name;
    }

    public void setName(String name) {
        this.name = name; }}Copy the code

2. The Repository interface

Database operation interface, directly use the default curd, there is no additional method to add

@Repository
public interface TestRepository extends CrudRepository<TestEntity.Integer> {}Copy the code

3. The test case

Let’s give a few test cases of CURD to demonstrate our integration

@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可迭代list(a) {
        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; }}Copy the code

Measured cases are as follows

# Add a record
curl 'http://localhost:8080/save? Id =1&name= 1

# Query record
curl 'http://localhost:8080/get? id=1'

# Modify record
curl 'http://localhost:8080/update? Id =1&name= 1

# query all
curl 'http://localhost:8080/list'

# Delete record
curl 'http://localhost:8080/del? id=1'
Copy the code

4. Import SQL files

Note that in none of our previous steps did we proactively create a table named test, unlike mysql;

At some point you might want to initialize the database with the prepared SQL file

The corresponding SQL file

Table structureschema-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
);
Copy the code

The data filedata-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);
Copy the code

With the above two files ready, how do we import them?

Using the SQL-Maven-plugin method, 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>
Copy the code

Then the following operation can be done

Once the import is successful, go to the H2 console and view the corresponding data

II. The other

0. Project

• Project: github.com/liuyueyi/sp…
• Project source: github.com/liuyueyi/sp…

1. A gray Blog

The above content is not as good as the letter, purely the words of a family, due to the limited personal ability, there are inevitably omissions and mistakes, such as found bugs or better suggestions, welcome criticism and correction, not grudging gratitude

Below a gray personal blog, record all study and work in the blog, welcome everyone to visit

• A gray Blog personal Blog blog.hhui. Top
• A Gray Blog-Spring feature Blog Spring.hhui. Top