Spring Boot With H2

十毛

发布于 2021-02-04 09:48:52

4160

发布于 2021-02-04 09:48:52

文章被收录于专栏：用户1337634的专栏用户1337634的专栏

在一些快速项目项目中，会使用H2作为数据库

引入jar包

使用mybatis作为ORM

pom.xml

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.200</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>29.0-jre</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

示例代码

对实体类Person以及与对应的数据库表person进行增删改查

实体类Person

package com.tenmao.myh2;

import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * @author tenmao
 * @since 2020/6/2
 */
@Data
public class Person {
    @EqualsAndHashCode.Exclude
    private Integer id;
    private String name;
    private Integer age;
}

示例Application类

@SpringBootApplication
public class Myh2Application implements CommandLineRunner {
    @Resource
    private PersonMapper personMapper;
    public static void main(String[] args) {
        SpringApplication.run(Myh2Application.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        Person tenmao = new Person();
        tenmao.setName("tenmao");
        tenmao.setAge(100);
        personMapper.insertOne(tenmao);
        Person tenmaoInDb = personMapper.selectById(tenmao.getId());
        Preconditions.checkArgument(tenmaoInDb.equals(tenmao));
        System.out.println(tenmao);
    }
}

Mapper

@Mapper
public interface PersonMapper {
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("INSERT INTO person(name, age) VALUES(#{name}, #{age})")
    int insertOne(Person person);

    @Select("SELECT * FROM person WHERE id=#{id}")
    Person selectById(int id);
}

配置文件

resources/application.yml H2不需要用户名和密码

spring:
  datasource:
    url: jdbc:h2:mem:testdb #配置数据库URL
    driver-class-name: org.h2.Driver #设置数据库驱动

初始化建表语句：resources/schema.sql

CREATE TABLE `person`
(
    id   INT(11)     NOT NULL auto_increment primary key,
    name varchar(32) NOT NULL,
    age  INT DEFAULT NULL
);

常见问题

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "([*]"; expected "(, WITH, SELECT, TABLE, VALUES"; SQL statement H2的SQL语法跟MySQL有一点出入，需要检查一下，另外就是不能漏掉建表语句后面的分号";"