Java中使用Mybatis操作H2数据库

H2数据库

H2是一个使用 Java 编写的数据库，有内嵌式和服务两种运行模式。

• 内嵌式： 主要有两种

• 内存模式：不会落地持久化，关闭连接后数据就清空；"jdbc:h2:mem:MyDb"
• 文件模式：将数据持久化到文件中；jdbc:h2:file:./Mydb（保存到当前目录下的Mydb.mv.db中）

• 服务式： 像普通数据库一样，以客户端方式通过tcp远程连接；"jdbc:h2:tcp://localhost/~/MyDb"

依赖库

为了使用H2数据库，只需引入依赖包，并做简单配置即可。
在pom.xml中添加：

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

配置

在yml文件中做数据配置：

server:
  port: 8081
  servlet:
    context-path: /study

spring:
  datasource:
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema-h2.sql  # 每次启动程序，都会运行以对数据库操作
    # data: classpath:db/data-h2.sql  # 每次启动程序，都会运行以对数据库的数据操作
    # url: jdbc:h2:mem:test #配置h2数据库的连接地址
    url: jdbc:h2:file:./dbAccount #配置h2数据库的连接地址
    username: root
    password: test123

#spring:
#  datasource:
#    type: com.alibaba.druid.pool.DruidDataSource
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql://127.0.0.1:3306/rtc_sip?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
#    username: root
#    password: mysql

上面配置的脚本文件需要放到resources/db/目录下，如保证表存在：

CREATE TABLE IF NOT EXISTS accounts(
    account varchar(30) NOT NULL,
    name varchar(30) NULL,
    epochSec bigint(20) NULL,
    PRIMARY KEY(account)
);

通过http://127.0.0.1:8081/study/h2-admin/即可访问数据库：

• JDBC UR：填写上面url，即jdbc:h2:file:./dbAccount；
• 用户名与密码为上面yml中设定的；

远程访问

为了能远程访问，需要增加配置：

h2:
    console:
      path: /h2-admin #进入h2 web操作界面的路径
      enabled: true #开启web console功能
      settings:
        web-allow-others: true

MyBatis框架

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。提供了两种方式实现查询、更新、删除、插入：

• XML形式：数据库操作语句放在XML文件中；
• 注解形式：使用函数名作为唯一标识，所以不能重载，要保证函数名唯一；

依赖库

为了使用MyBatis，在pom.xml中添加：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

Debug输出

为了方便查看SQL语句是否正确，可通过启用Debug输出功能，显示执行的SQL语句：

mybatis:
  #  mapper-locations: classpath:mapper/**.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

注解

在对应接口中增加@Mapper注解后，就可以直接在函数上通过注解进行数据库的操作了。

参数传递

参数传递有两种方式：#{}占位符，${}拼接符

• #{} 为参数占位符?：即sql预编译

变量替换是在DBMS中
变量替换后，对应的变量会自动加上单引号''

• ${} 为字符串替换：即sql拼接

变量替换是在DBMS外
变量替换后，对应的变量不会自动加上单引号

使用建议：

• Dao中函数参数，尽量使用注解@Param("")来自定义参数名；
• 能用#{}的地方就用#{}，不用或少用${}
• 表名、字段名等作参数时，必须用${}。如：

select * from ${tableName}
select * from t_user order by ${columnName}

• 使用${}时，要注意何时加或不加单引号，即${}和'${}'

返回自增列

在插入后，返回新增列的id（自增）。User为包含id、name、age、remark四字段的类：

@Insert(value = { "INSERT INTO user (name, age, remark) VALUES (#{name}, #{age}, #{remark})"})
@Options(useGeneratedKeys=true, keyProperty="id")
public void insertUser(User user);

动态SQL

通过script包含的语句为动态SQL，在最后执行时动态拼接。

@Select({"<script>", 
	"SELECT * FROM user ",
	"<where>",
	"<if test = 'age != null and age != 0' >userAge &lt; #{age}</if>", 
	"</where>", 
	"</script>"})
public List<User> getUserList(@Param(value = "age") Integer age);

foreach用法

foreach用于动态SQL中处理集合（List、Array、Map），包括如下属性；

• collection：要迭代的对象（必选）

传入的是单参数且为List时，默认属性值为list；使用@Param(value = "myList")修改;
传入的是单参数且为Array时，默认属性值为array；使用@Param(value = "myArray")修改;
传入的是Map时，没有默认属性值；使用@Param(value = "myList")修改;

• item：迭代元素的别名（必选）：若item本身为类，可通过点号获取内部字段，如#{item.name}；
• index：迭代元素的序号（数组与List）；
• separator：生成结果元素间的分隔符；
• open：语句开始符号（以什么开始）；
• close：语句的结束符号（以什么结束）；

以map迭代为例（键为字段名，值为字段内容）：

@Insert({
        "<script>",
        "Insert into accounts",
        "<foreach collection='map' item='item' index='key' open='(' close=')' separator=','>",
        "${key}",  // must be $
        "</foreach>",
        " values",
        "<foreach collection='map' item='item' index='key' open='(' close=')' separator=','>",
        "#{item}",
        "</foreach>",
        "</script>"
})
int insertMap(@Param(value = "map") Map<String, Object> map);

if用法

根据条件添加语句，若条件满足则包含对应语句：
<if test='name!=null'>name=#{name}</if>

set标签

更新元素不确定时，直接书写比较困难，此时可方便通过where标签实现；则每个if条件最后都添加一个逗号(,)分隔符，MyBatis会自动处理多余的分隔符。

    @Update({"<script>",
            "Update accounts",
            "<set>",
             "<if test='name!=null'>name=#{name},</if>",
             "<if test='epochSec!=null'>epochSec=#{epochSec},</if>",
            "</set>",
            "Where account=#{account}",
            "</script>"
    })
    int updateAccount(Account acc);

where标签

查询时where语句数量不确定（甚至可能会没有）时，直接书写比较困难，此时可方便通过where标签实现；在每条if语句中都添加一个and，MyBatis会处理多余的and，并且在所有条件为空时自动去掉where子句。

    @Update({"<script>",
            "Select account",
            "From accounts",
            "<where>",
            "<if test='name!=null'>and name=#{name}</if>",
            "<if test='epochSec!=null'>and epochSec=#{epochSec}</if>",
            "</where>",
            "</script>"
    })
    List<String> findAccounts(Account acc);

示例

以account为例说明如何使用mybatis操作h2数据库：

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Account {
    private String account;
    private String name;
    private Long epochSec;
}

数据库操作接口

常用的插入、更新、删除操作为例：

@Repository
@Mapper
public interface AccountDao {
    @Insert({"Insert into accounts(account) values(#{acc})"})
    int insertAccount(String acc);

    @Insert({
            "<script>",
            "Insert into accounts(account) values ",
            "<foreach collection='lstAcc' item='item' separator=','>",
            "(#{item})",
            "</foreach>",
            "</script>"
    })
    int insert(@Param(value = "lstAcc") List<String> lstAcc);

    @Select("Select * from accounts Where epochSec is not NULL")
    List<Account> getUsed();

    // 随机返回一个
    @Select("Select account from accounts where epochSec is NULL order by rand() limit 1")
    String getAccount();

    @Update({"Update accounts set name=#{name}, epochSec=#{epochSec} Where account=#{acc} and epochSec is NULL"})
    int setUsed(String acc, String name, Long epochSec);

    @Delete({"Delete From accounts"})
    int clear();

    @Select("Select count(*) from accounts")
    int getCount();
}

接口调用

在启动时，若数据库中没有数据，则进行初始化：

@Service
public class AccountHandler {
    private static final Logger _logger = LoggerFactory.getLogger(AccountHandler.class);

    @Autowired
    AccountDao dbAccount;

    @PostConstruct
    public void initAccount(){
        if(dbAccount.getCount() == 0){
            List<String> lstAcc = new ArrayList<>(100);
            for(int i=1000; i<1100; ++i){
                lstAcc.add(Objects.toString(i));
            }

            dbAccount.insert(lstAcc);
        }
    }

    public String getAccount(){
        return dbAccount.getAccount();
    }
}

RestAPI接口

对外提供的RestAPI接口：

@RestController
@RequestMapping("acc")
public class AccountController {
    private static final Logger _logger = LoggerFactory.getLogger(AccountController.class);

    @Autowired
    AccountHandler accHandler;

    @GetMapping("getAccount")
    public String getAccount(){
        return Objects.toString(accHandler.getAccount());
    }
}