Preface

This article guides you to integrate H2 memory databases using Spring Boot, Spring Data JPA. For more information on H2 data: http://www.h2database.com/html/tutorial.html

Get ready

• JDK 1.8 or later
• Maven 3 or later

technology stack

• Spring Data JPA
• Spring Boot

directory structure

pom.xml

<?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">
    <parent>
        <artifactId>jpa-example</artifactId>
        <groupId>cn.merryyou</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>h2-webconsole</artifactId>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.196</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.6.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

Entity class

User

@Entity
@Table(name = "t_user")
@Data
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private String url;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", url='" + url + '\'' +
                '}';
    }
}

• The @Table declares that this object maps to the database's data tables by which the names of tables (talbe), catalogs, and schema s can be specified for entities. This annotation is not required, and if not, the system uses the default value (the short class name of the entity).

• Id declares this property as the primary key. This attribute value can be created by itself, but Hibernate recommends that it be generated by Hibernate

• GeneratedValue specifies the primary key generation strategy.

  1. TABLE: Save id values with tables
  2. IDENTITY: identitycolumn
  3. SEQUENCR : sequence
  4. AUTO: The three above are used differently depending on the database.

• Column declares the mapping relationship between the attribute and the database field.

AddressRepository

public interface UserRepository extends JpaRepository<User, Integer> {
}

Spring Data JPA includes some built-in Repository and implements some commonly used methods: findone, findall, save, etc.

application.yml

spring:
  datasource:
    url: jdbc:h2:mem:h2test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    platform: h2
    username: sa
    password:
    driverClassName: org.h2.Driver
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        show_sql: true
        use_sql_comments: true
        format_sql: true
  h2:
    console:
      enabled: true
      path: /console
      settings:
        trace: false
        web-allow-others: false
logging:
  level: debug

Connection configuration

Configuration of database connection in application.yml file

• spring.datasource.url=jdbc:h2:mem:h2test, configure the connection address of H2 database
• spring.datasource.driver-class-name=org.h2.Driver, configure JDBC Driver
• spring.datasource.username=sa, configure database username
• spring.datasource.password=, configure database password

When you complete the two steps of dependency and connection configuration, you can use h2 in the program type. spring will automatically help you complete the injection of DataSource.

Data initialization configuration

If you need to initialize the database at program startup, configure the database access in the application.properties file

• spring.datasource.schema=classpath:db/schema.sql. After this configuration, every time the program is started, the program runs the resources/db/schema.sql file to operate on the structure of the database.
• spring.datasource.data=classpath:db/data.sql. After this configuration, every time the program starts, the program runs the resources/db/data.sql file to operate on the database.

This configuration is very suitable for the development environment. I will put the database structure SQL in resources/db/schema.sql and the data SQL in resources/db/data.sql. So every time I run the program, I get a new database. This eliminates the need for me to modify the data every time for testing.

h2 web consloe configuration

h2 web consloe is a database GUI management application similar to phpMyAdmin. When the program runs, h2 web consloe is automatically started. Of course, you can also configure it as follows.

• spring.h2.console.settings.web-allow-others=true. With this configuration, h2 web consloe can be accessed remotely. Otherwise, it can only be accessed locally.
• spring.h2.console.path=/h2-console. With this configuration, you can access h2 web consloe through YOUR_URL/h2-console. YOUR_URL is the access URl of your program.
• spring.h2.console.enabled=true. With this configuration, the h2 web consloe will start when the program starts. This is the default, of course. If you don't want to start h2 web consloe at startup time, set it to false.

UserRepositoryTest

@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    public void saveTest() throws Exception {
        User user = new User();
        user.setName("Zheng Long Fei");
        user.setUrl("http://merryyou.cn");
        User result = userRepository.save(user);
        log.info(result.toString());
        Assert.assertNotNull(user.getId());
    }

    @Test
    public void findOneTest() throws Exception{
        User user = userRepository.findOne(1l);
        log.info(user.toString());
        Assert.assertNotNull(user);
        Assert.assertTrue(1l==user.getId());
    }
}

h2 web consloe

Code download

Download it from my github. https://github.com/longfeizheng/jpa-example/tree/master/h2-webconsole