TKMapper 在Mybatis的基础上封装了绝大多数的单表操作,基本的CURD,条件查询,统计查询,除非出现多表操作,否则,不需要自己写SQL,请参考通用 MAPPER 3
H2 数据库是一个极小的数据库,支持内存模式,嵌入式模式,只需要引入一个jar包,非常适合Demo的开发,当然也有其他更强大的用法。请参考H2数据库教程
项目结构如下:
引入Mybatis和H2 数据库
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>${HikariCP.version}</version>
</dependency>
<!--mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.0.3</version>
</dependency>
<!--pagehelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
</dependency>
首先看application.yml 配置,springboot官方推荐优先使用yml配置,简单明了,层次感强。
#spring
spring:
devtools:
restart:
enabled: false
# H2 DATABASE CONFIG
datasource:
type: com.zaxxer.hikari.HikariDataSource
schema: classpath:/db/schema-h2.sql
data: classpath:/db/data-h2.sql
url: jdbc:h2:mem:testdb
username: sa
password: sa
platform: h2
initialization-mode: always
continue-on-error: true
h2:
console:
enabled: true
path: /console
#mybatis配置
mybatis:
mapper-locations: classpath:mapper/*.xml
configuration:
mapUnderscoreToCamelCase: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper:
mappers: tk.mybatis.mapper.common.Mapper
not-empty: false
identity: MYSQL
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
server:
port: 9013
servlet:
context-path: /me
使用hikari连接池,hikari连接池是最快的连接池,功能也齐全。H2的初始化通过这两行完成,指定DDL,DML
schema: classpath:/db/schema-h2.sql
data: classpath:/db/data-h2.sql
schema-h2.sql
DROP TABLE IF EXISTS user;
CREATE TABLE user
(
test_id BIGINT(20) NOT NULL COMMENT '主键ID',
tenant_id BIGINT(20) NOT NULL COMMENT '租户ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '名称',
age INT(11) NULL DEFAULT NULL COMMENT '年龄',
test_type INT(11) NULL DEFAULT NULL COMMENT '测试下划线字段命名类型',
test_date DATETIME NULL DEFAULT NULL COMMENT '日期',
role BIGINT(20) NULL DEFAULT NULL COMMENT '测试',
phone VARCHAR(11) NULL DEFAULT NULL COMMENT '手机号码',
PRIMARY KEY (test_id)
);
data-h2.sql
DELETE FROM user;
INSERT INTO user (test_id, tenant_id, name, age, test_type, test_date, role, phone) VALUES
(0, 1, '雷锋', 1, 1, '2017-1-1 1:1:1', 1, '10010'),
(1, 1, '三毛', 2, 1, '2017-2-2 1:1:1', 1, '10086'),
(2, 1, '小马', 1, 1, '2017-3-3 1:1:1', 1, '10000'),
(3, 2, '麻花藤', 1, 1, '2017-3-3 1:1:1', 1, '10000'),
(4, 2, '东狗', 2, 1, '2017-3-3 1:1:1', 1, '10086'),
(5, 1, '王五', 2, 1, '2017-3-3 1:1:1', 1, '10010');
指定DB名称和DB用户名称和密码,用来连接H2数据库
# 内存模式,不会持久化,testdb就是指定的db名称
url: jdbc:h2:mem:testdb
username: sa
password: sa
platform: h2
指定分页插件
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
上面的配置好了之后,就完成了整个springboot,tkMapper,h2数据库的整合操作,下面编写CommonMapper,所有的mapper都继承这个Mapper,得到通用mapper的所有内置单表操作方法。
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
public interface CommonMapper<T> extends Mapper<T>, MySqlMapper<T> {
}
UserMapper里面上面都不用写
import com.didispace.common.CommonMapper;
import com.didispace.table.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.Map;
@Mapper
public interface UserMapper extends CommonMapper<User> {
}
调用demo,实现分页,就只有一行代码。
@Override
public List<User> getPageUser(int page, int size){
PageHelper.startPage(page,size);
List<User> users = userMapper.selectAll();
log.info(users.toString());
return users;
}
输出结果:
Page{
count=true,
pageNum=1,
pageSize=2,
startRow=0,
endRow=2,
total=6,
pages=3,
reasonable=true,
pageSizeZero=false
}[
User(testId=0,
tenantId=1,
name=雷锋,
age=1,
testType=1,
testDate=SunJan0101: 01: 01CST2017,
role=1,
phone=10010),
User(testId=1,
tenantId=1,
name=三毛,
age=2,
testType=1,
testDate=ThuFeb0201: 01: 01CST2017,
role=1,
phone=10086)
]
代码地址:https://gitee.com/nonkey/SpringBoot-TK-mapper-H2-Database
参考:
https://gitee.com/baomidou/mybatisplus-spring-boot
https://mapperhelper.github.io/docs/3.usembg/