转载

h2数据库作为内存型与springboot+mybatis的案例

1）嵌入式模式（使用 JDBC 的本地连接）

2）服务器模式（使用 JDBC 或 ODBC 在 TCP/IP 上的远程连接）

3）混合模式（本地和远程连接同时进行）

该案例为嵌入式模式

二.springboot创建项目

首先，给大家看一下我的项目结构

h2数据库作为内存型与springboot+mybatis的案例

springboot的版本为：2.1.9 具体可以看一下我的pom文件

2.1 依赖pom文件

以下就是我该演示项目的所有依赖，h2的版本交给springboot去进行确定

<properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!--starter-web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--连接H2的mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <!--h2数据库-->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--测试-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <!--把放置在mapper路径下的xml文件编译后放在一起-->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

2.2 application.yml

需要关注 h2 web操作界面的路径和 console打印的sql语句，以下的配置是本文精华部分

spring:
  h2:
    console:
      path: /h2-console #进入h2 web操作界面的路径
      enabled: true #开启h2 web界面
  datasource:
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:h2:mem:test
    username: root
    password: test

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 在console打印sql语句

server:
  port: 8081

2.3 sql语句

data-h2.sql，需要放置在resource/db下

INSERT INTO cat (id, name, age, color,score) VALUES
(1, 'Jone', 18, '黃色',0.4),
(2, 'Jack', 20, '白色',0.5),
(3, 'Tom', 28, '金色',0.1),
(4, 'Sandy', 21, '紅色',0.8),
(5, 'Billie', 24, '綠色',0.7);

schema-h2.sql，需要放置在resource/db下

DROP TABLE IF EXISTS cat;

CREATE TABLE cat
(
    id BIGINT(20) NOT NULL COMMENT '主键ID',
    name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    color VARCHAR(50) NULL DEFAULT NULL COMMENT '颜色',
    score DOUBLE NULL DEFAULT NULL COMMENT '分数',
    PRIMARY KEY (id)
);

2.4 启动类上配上mapper扫描

/**
 * @author CC-CAN
 */
@SpringBootApplication
@MapperScan("com.springboot.*.mapper")
public class MybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisApplication.class, args);
    }

}

2.5 mapper.java和mapper.xml

配置后，由mybatis进行实体类的映射

public interface CatMapper {

    List<Cat> selectAll();

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.springboot.mybatis.mapper.CatMapper">
    <select id="selectAll" resultType="com.springboot.mybatis.entity.Cat">
        SELECT * from cat
    </select>
</mapper>

2.6 案例的entity

与数据库的实体对应

@Data
public class Cat {
    private Long id;
    private String name;
    private Integer age;
    private String color;
    private Double score;
}

2.7 service 和 impl

public interface CatService {

    /**
     * 喵叫
     * @return
     */
    String meow();

    List<Cat> list();

}

@Service
public class CatServiceImpl implements CatService {

    @Autowired
    private CatMapper catMapper;

    @Override
    public String meow() {
        return "瞄";
    }

    @Override
    public List<Cat> list() {
        return catMapper.selectAll();
    }
}

2.8 建立测试类

在test创建 springboot 的测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisApplicationTests {

    @Autowired
    private CatService catService;

    @Test
    public void contextLoads() {
        List<Cat> list = catService.list();
        list.forEach(System.out::println);
    }

}

三测试结果

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6caf7803] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@408543908 wrapping conn0: url=jdbc:h2:mem:test user=ROOT] will not be managed by Spring
==>  Preparing: SELECT * from cat 
==> Parameters: 
<==    Columns: ID, NAME, AGE, COLOR, SCORE
<==        Row: 1, Jone, 18, 黃色, 0.4
<==        Row: 2, Jack, 20, 白色, 0.5
<==        Row: 3, Tom, 28, 金色, 0.1
<==        Row: 4, Sandy, 21, 紅色, 0.8
<==        Row: 5, Billie, 24, 綠色, 0.7
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6caf7803]
Cat(id=1, name=Jone, age=18, color=黃色, score=0.4)
Cat(id=2, name=Jack, age=20, color=白色, score=0.5)
Cat(id=3, name=Tom, age=28, color=金色, score=0.1)
Cat(id=4, name=Sandy, age=21, color=紅色, score=0.8)
Cat(id=5, name=Billie, age=24, color=綠色, score=0.7)

h2数据库作为内存型与springboot+mybatis的案例