引子
mybatis官方推出了一种新的动态sql的编程方式MyBatis3 Dynamic Sql。官方觉得xml已经过时了,不建议大家在使用了。而注解的方式也不是很优雅,功能比较少,很多sql不好实现,代码不好调试等,所以推荐正确的mybatis食用方式给大家。
MyBatis3 Dynamic Sql使用了 java8的生成器来生成代码,使用流式以及 lambda表达式来构建sql,看起来更简化、优雅,完全使用java代码编程,更加的流畅。
编程风格:
@Test
void testGeneralSelect() {
try (SqlSession session = sqlSessionFactory.openSession()) {
PersonMapper mapper = session.getMapper(PersonMapper.class);
SelectStatementProvider selectStatement = select(id.as("A_ID"), firstName, lastName, birthDate, employed,
occupation, addressId)
.from(person)
.where(id, isEqualTo(1))
.or(occupation, isNull())
.build()
.render(RenderingStrategies.MYBATIS3);
List<PersonRecord> rows = mapper.selectMany(selectStatement);
assertThat(rows).hasSize(3);
}
}
这里我这里简单介绍一下在springboot中如何使用MyBatis3 Dynamic Sql。
创建数据库
这里使用用户、角色、菜单表来描述:
# 用户表
CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` varchar(50) NOT NULL COMMENT '账号',
`describe` varchar(100) DEFAULT NULL COMMENT '描述',
`nick_name` varchar(50) DEFAULT NULL COMMENT '昵称',
`phone` varchar(20) DEFAULT NULL COMMENT '手机号',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`state` int(4) NOT NULL DEFAULT 0 COMMENT '状态0正常',
`avatar_url` varchar(200) DEFAULT NULL COMMENT '头像url',
`created_time` datetime NOT NULL COMMENT '创建时间',
`modified_time` datetime DEFAULT NULL COMMENT '修改时间',
`del_flag` int(4) NOT NULL DEFAULT 0 COMMENT '删除标记',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_sys_user_account` (`account`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
# 用户角色表
CREATE TABLE `sys_user_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` varchar(50) NOT NULL COMMENT '用户账号',
`role_name` varchar(50) NOT NULL COMMENT '角色名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
# 角色表
CREATE TABLE `sys_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '角色名称',
`display_name` varchar(50) NOT NULL COMMENT '角色名称中文',
`state` int(4) DEFAULT 0 COMMENT '状态',
`describe` varchar(100) DEFAULT NULL COMMENT '描述',
`platform_code` varchar(50) DEFAULT NULL COMMENT '平台code',
`created_time` datetime NOT NULL,
`modified_time` datetime DEFAULT NULL,
`del_flag` int(2) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_sys_role_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
# 菜单表
CREATE TABLE `sys_menu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT '菜单名称',
`display_name` varchar(50) NOT NULL COMMENT '菜单显示名称',
`url` varchar(255) DEFAULT NULL COMMENT '菜单url',
`menu_index` int(4) DEFAULT 0 COMMENT '菜单排序',
`state` int(4) DEFAULT 0 COMMENT '状态0正常1禁用',
`pid` int(11) NOT NULL COMMENT '上一级菜单id',
`icon` varchar(50) DEFAULT NULL COMMENT '图标',
`display` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否显示',
`platform_code` varchar(50) DEFAULT NULL COMMENT '平台code',
`del_flag` int(4) DEFAULT 0,
`created_time` datetime DEFAULT NULL,
`modified_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_sys_menu` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
# 角色菜单表
CREATE TABLE `sys_role_menu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(50) NOT NULL COMMENT '角色名称',
`menu_name` varchar(50) NOT NULL COMMENT '菜单名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
创建springboot项目
创建一个springboot项目
引入对应的包
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- MyBatis 生成器 -->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.4.0</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- MyBatis 动态SQL支持 -->
<dependency>
<groupId>org.mybatis.dynamic-sql</groupId>
<artifactId>mybatis-dynamic-sql</artifactId>
<version>1.3.0</version>
</dependency>
自动生成表的对象
在resources下创建generatorConfig.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--Mybatis Generator目前有5种运行模式,分别为:MyBatis3DynamicSql、MyBatis3Kotlin、MyBatis3、MyBatis3Simple、MyBatis3DynamicSqlV1。-->
<context id="springboot-base" targetRuntime="MyBatis3DynamicSql">
<commentGenerator>
<!-- 是否去除自动生成的注释 true:是 : false:否 -->
<property name="suppressAllComments" value="true" />
</commentGenerator>
<jdbcConnection
connectionURL="jdbc:mysql://127.0.0.1:3306/test?tinyInt1isBit=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true"
driverClass="com.mysql.jdbc.Driver"
userId="root"
password="123456"/>
<javaTypeResolver>
<!-- 默认false,把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer, 为 true时把JDBC DECIMAL
和 NUMERIC 类型解析为java.math.BigDecimal -->
<property name="forceBigDecimals" value="false" />
<!--是否试用jdk8时间类-->
<property name="useJSR310Types" value="true"/>
</javaTypeResolver>
<!-- targetProject:生成PO类的位置 -->
<javaModelGenerator
targetPackage="com.example.dynamic.entity"
targetProject="src/main/java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
<!-- 从数据库返回的值被清理前后的空格 -->
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- targetPackage:mapper接口生成的位置 -->
<javaClientGenerator
targetPackage="com.example.dynamic.mapper"
targetProject="src/main/java" type="ANNOTATEDMAPPER">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!--生成全部表tableName设为%-->
<!-- 指定数据库表 -->
<table tableName="sys_%" >
<domainObjectRenamingRule searchString="^Sys_" replaceString="Sys_"/>
</table>
<!-- 指定数据库表
schema:数据库的schema,可以使用SQL通配符匹配。如果设置了该值,生成SQL的表名会变成如schema.tableName的形式。
domainObjectName:生成对象的基本名称。如果没有指定,MBG会自动根据表名来生成名称。
-->
<!--<table schema="" tableName="" domainObjectName=""/>-->
</context>
</generatorConfiguration>
Mybatis Generator目前有5种运行模式,分别为:MyBatis3DynamicSql、MyBatis3Kotlin、MyBatis3、MyBatis3Simple、MyBatis3DynamicSqlV1,这里使用的是MyBatis3DynamicSql,只生成pojo和一个mapper类,不会生成xml。
在java下创建generator包和MybatisGenerator.java文件
public class MybatisGenerator {
public static void main(String[] args) throws IOException, XMLParserException, InvalidConfigurationException, SQLException, InterruptedException {
//MBG 执行过程中的警告信息
List<String> warnings = new ArrayList<>();
//读取我们的 MBG 配置文件
InputStream is = MybatisGenerator.class.getResourceAsStream("/generatorConfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(is);
is.close();
//当生成的代码重复时,覆盖原代码
DefaultShellCallback callback = new DefaultShellCallback(true);
//创建 MBG
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
//执行生成代码
myBatisGenerator.generate(null);
//输出警告信息
for (String warning : warnings) {
System.out.println(warning);
}
}
}
直接运行此java类,会生成对应的文件:
实体类就是表字段下划线转驼峰。
mapper就是注解形式的mapper接口,包含一些常用的方法:
package com.example.dynamic.mapper;
import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;
import static org.mybatis.dynamic.sql.SqlBuilder.*;
import com.example.dynamic.entity.SysUser;
import java.util.Collection;
import java.util.List;
import java.util.Optional;
import javax.annotation.Generated;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.dynamic.sql.BasicColumn;
import org.mybatis.dynamic.sql.delete.DeleteDSLCompleter;
import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
import org.mybatis.dynamic.sql.insert.render.InsertStatementProvider;
import org.mybatis.dynamic.sql.insert.render.MultiRowInsertStatementProvider;
import org.mybatis.dynamic.sql.select.CountDSLCompleter;
import org.mybatis.dynamic.sql.select.SelectDSLCompleter;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.update.UpdateDSL;
import org.mybatis.dynamic.sql.update.UpdateDSLCompleter;
import org.mybatis.dynamic.sql.update.UpdateModel;
import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
import org.mybatis.dynamic.sql.util.SqlProviderAdapter;
import org.mybatis.dynamic.sql.util.mybatis3.MyBatis3Utils;
@Mapper
public interface SysUserMapper {
@Generated("org.mybatis.generator.api.MyBatisGenerator")
BasicColumn[] selectList = BasicColumn.columnList(id, account, describe, nickName, phone, email, state, avatarUrl, createdTime, modifiedTime, delFlag);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
@SelectProvider(type=SqlProviderAdapter.class, method="select")
long count(SelectStatementProvider selectStatement);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
@DeleteProvider(type=SqlProviderAdapter.class, method="delete")
int delete(DeleteStatementProvider deleteStatement);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
@InsertProvider(type=SqlProviderAdapter.class, method="insert")
int insert(InsertStatementProvider<SysUser> insertStatement);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
@InsertProvider(type=SqlProviderAdapter.class, method="insertMultiple")
int insertMultiple(MultiRowInsertStatementProvider<SysUser> multipleInsertStatement);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@ResultMap("SysUserResult")
Optional<SysUser> selectOne(SelectStatementProvider selectStatement);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
@SelectProvider(type=SqlProviderAdapter.class, method="select")
@Results(id="SysUserResult", value = {
@Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
@Result(column="account", property="account", jdbcType=JdbcType.VARCHAR),
@Result(column="describe", property="describe", jdbcType=JdbcType.VARCHAR),
@Result(column="nick_name", property="nickName", jdbcType=JdbcType.VARCHAR),
@Result(column="phone", property="phone", jdbcType=JdbcType.VARCHAR),
@Result(column="email", property="email", jdbcType=JdbcType.VARCHAR),
@Result(column="state", property="state", jdbcType=JdbcType.INTEGER),
@Result(column="avatar_url", property="avatarUrl", jdbcType=JdbcType.VARCHAR),
@Result(column="created_time", property="createdTime", jdbcType=JdbcType.TIMESTAMP),
@Result(column="modified_time", property="modifiedTime", jdbcType=JdbcType.TIMESTAMP),
@Result(column="del_flag", property="delFlag", jdbcType=JdbcType.INTEGER)
})
List<SysUser> selectMany(SelectStatementProvider selectStatement);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
@UpdateProvider(type=SqlProviderAdapter.class, method="update")
int update(UpdateStatementProvider updateStatement);
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default long count(CountDSLCompleter completer) {
return MyBatis3Utils.countFrom(this::count, sysUser, completer);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int delete(DeleteDSLCompleter completer) {
return MyBatis3Utils.deleteFrom(this::delete, sysUser, completer);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int deleteByPrimaryKey(Integer id_) {
return delete(c ->
c.where(id, isEqualTo(id_))
);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int insert(SysUser record) {
return MyBatis3Utils.insert(this::insert, record, sysUser, c ->
c.map(id).toProperty("id")
.map(account).toProperty("account")
.map(describe).toProperty("describe")
.map(nickName).toProperty("nickName")
.map(phone).toProperty("phone")
.map(email).toProperty("email")
.map(state).toProperty("state")
.map(avatarUrl).toProperty("avatarUrl")
.map(createdTime).toProperty("createdTime")
.map(modifiedTime).toProperty("modifiedTime")
.map(delFlag).toProperty("delFlag")
);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int insertMultiple(Collection<SysUser> records) {
return MyBatis3Utils.insertMultiple(this::insertMultiple, records, sysUser, c ->
c.map(id).toProperty("id")
.map(account).toProperty("account")
.map(describe).toProperty("describe")
.map(nickName).toProperty("nickName")
.map(phone).toProperty("phone")
.map(email).toProperty("email")
.map(state).toProperty("state")
.map(avatarUrl).toProperty("avatarUrl")
.map(createdTime).toProperty("createdTime")
.map(modifiedTime).toProperty("modifiedTime")
.map(delFlag).toProperty("delFlag")
);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int insertSelective(SysUser record) {
return MyBatis3Utils.insert(this::insert, record, sysUser, c ->
c.map(id).toPropertyWhenPresent("id", record::getId)
.map(account).toPropertyWhenPresent("account", record::getAccount)
.map(describe).toPropertyWhenPresent("describe", record::getDescribe)
.map(nickName).toPropertyWhenPresent("nickName", record::getNickName)
.map(phone).toPropertyWhenPresent("phone", record::getPhone)
.map(email).toPropertyWhenPresent("email", record::getEmail)
.map(state).toPropertyWhenPresent("state", record::getState)
.map(avatarUrl).toPropertyWhenPresent("avatarUrl", record::getAvatarUrl)
.map(createdTime).toPropertyWhenPresent("createdTime", record::getCreatedTime)
.map(modifiedTime).toPropertyWhenPresent("modifiedTime", record::getModifiedTime)
.map(delFlag).toPropertyWhenPresent("delFlag", record::getDelFlag)
);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default Optional<SysUser> selectOne(SelectDSLCompleter completer) {
return MyBatis3Utils.selectOne(this::selectOne, selectList, sysUser, completer);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default List<SysUser> select(SelectDSLCompleter completer) {
return MyBatis3Utils.selectList(this::selectMany, selectList, sysUser, completer);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default List<SysUser> selectDistinct(SelectDSLCompleter completer) {
return MyBatis3Utils.selectDistinct(this::selectMany, selectList, sysUser, completer);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default Optional<SysUser> selectByPrimaryKey(Integer id_) {
return selectOne(c ->
c.where(id, isEqualTo(id_))
);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int update(UpdateDSLCompleter completer) {
return MyBatis3Utils.update(this::update, sysUser, completer);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
static UpdateDSL<UpdateModel> updateAllColumns(SysUser record, UpdateDSL<UpdateModel> dsl) {
return dsl.set(id).equalTo(record::getId)
.set(account).equalTo(record::getAccount)
.set(describe).equalTo(record::getDescribe)
.set(nickName).equalTo(record::getNickName)
.set(phone).equalTo(record::getPhone)
.set(email).equalTo(record::getEmail)
.set(state).equalTo(record::getState)
.set(avatarUrl).equalTo(record::getAvatarUrl)
.set(createdTime).equalTo(record::getCreatedTime)
.set(modifiedTime).equalTo(record::getModifiedTime)
.set(delFlag).equalTo(record::getDelFlag);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
static UpdateDSL<UpdateModel> updateSelectiveColumns(SysUser record, UpdateDSL<UpdateModel> dsl) {
return dsl.set(id).equalToWhenPresent(record::getId)
.set(account).equalToWhenPresent(record::getAccount)
.set(describe).equalToWhenPresent(record::getDescribe)
.set(nickName).equalToWhenPresent(record::getNickName)
.set(phone).equalToWhenPresent(record::getPhone)
.set(email).equalToWhenPresent(record::getEmail)
.set(state).equalToWhenPresent(record::getState)
.set(avatarUrl).equalToWhenPresent(record::getAvatarUrl)
.set(createdTime).equalToWhenPresent(record::getCreatedTime)
.set(modifiedTime).equalToWhenPresent(record::getModifiedTime)
.set(delFlag).equalToWhenPresent(record::getDelFlag);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int updateByPrimaryKey(SysUser record) {
return update(c ->
c.set(account).equalTo(record::getAccount)
.set(describe).equalTo(record::getDescribe)
.set(nickName).equalTo(record::getNickName)
.set(phone).equalTo(record::getPhone)
.set(email).equalTo(record::getEmail)
.set(state).equalTo(record::getState)
.set(avatarUrl).equalTo(record::getAvatarUrl)
.set(createdTime).equalTo(record::getCreatedTime)
.set(modifiedTime).equalTo(record::getModifiedTime)
.set(delFlag).equalTo(record::getDelFlag)
.where(id, isEqualTo(record::getId))
);
}
@Generated("org.mybatis.generator.api.MyBatisGenerator")
default int updateByPrimaryKeySelective(SysUser record) {
return update(c ->
c.set(account).equalToWhenPresent(record::getAccount)
.set(describe).equalToWhenPresent(record::getDescribe)
.set(nickName).equalToWhenPresent(record::getNickName)
.set(phone).equalToWhenPresent(record::getPhone)
.set(email).equalToWhenPresent(record::getEmail)
.set(state).equalToWhenPresent(record::getState)
.set(avatarUrl).equalToWhenPresent(record::getAvatarUrl)
.set(createdTime).equalToWhenPresent(record::getCreatedTime)
.set(modifiedTime).equalToWhenPresent(record::getModifiedTime)
.set(delFlag).equalToWhenPresent(record::getDelFlag)
.where(id, isEqualTo(record::getId))
);
}
}
会多出一个XXXDynamicSqlSupport的一个类,这个类在构建动态sql的时候会用到,后面再提。
public final class SysMenuDynamicSqlSupport {
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SysMenu sysMenu = new SysMenu();
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<Integer> id = sysMenu.id;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<String> name = sysMenu.name;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<String> displayName = sysMenu.displayName;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<String> url = sysMenu.url;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<Integer> menuIndex = sysMenu.menuIndex;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<Integer> state = sysMenu.state;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<Integer> pid = sysMenu.pid;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<String> icon = sysMenu.icon;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<Byte> display = sysMenu.display;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<String> platformCode = sysMenu.platformCode;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<Integer> delFlag = sysMenu.delFlag;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<LocalDateTime> createdTime = sysMenu.createdTime;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final SqlColumn<LocalDateTime> modifiedTime = sysMenu.modifiedTime;
@Generated("org.mybatis.generator.api.MyBatisGenerator")
public static final class SysMenu extends SqlTable {
public final SqlColumn<Integer> id = column("id", JDBCType.INTEGER);
public final SqlColumn<String> name = column("name", JDBCType.VARCHAR);
public final SqlColumn<String> displayName = column("display_name", JDBCType.VARCHAR);
public final SqlColumn<String> url = column("url", JDBCType.VARCHAR);
public final SqlColumn<Integer> menuIndex = column("menu_index", JDBCType.INTEGER);
public final SqlColumn<Integer> state = column("state", JDBCType.INTEGER);
public final SqlColumn<Integer> pid = column("pid", JDBCType.INTEGER);
public final SqlColumn<String> icon = column("icon", JDBCType.VARCHAR);
public final SqlColumn<Byte> display = column("display", JDBCType.TINYINT);
public final SqlColumn<String> platformCode = column("platform_code", JDBCType.VARCHAR);
public final SqlColumn<Integer> delFlag = column("del_flag", JDBCType.INTEGER);
public final SqlColumn<LocalDateTime> createdTime = column("created_time", JDBCType.TIMESTAMP);
public final SqlColumn<LocalDateTime> modifiedTime = column("modified_time", JDBCType.TIMESTAMP);
public SysMenu() {
super("sys_menu");
}
}
创建业务代码
配置springboot的启动文件app:
server:
servlet:
context-path: mybatis
port: 8082
spring:
datasource:
url: jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: 123456
web层
对sys_user表的查新、新增、修改、分页查询、count:
package com.example.dynamic.web;
import com.example.dynamic.entity.SysMenu;
import com.example.dynamic.entity.SysUser;
import com.example.dynamic.service.SysMenuService;
import com.example.dynamic.service.SysUserService;
import com.example.dynamic.utils.PageBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("test")
public class WebController {
@Autowired
private SysUserService sysUserService;
@Autowired
private SysMenuService sysMenuService;
/**
* 查询用户
* @param account
* @return
*/
@GetMapping("getSysUserList")
public List<SysUser> getSysUserList(String account) {
return sysUserService.getSysUserList(account);
}
/**
* 新增
* @param account
* @param name
* @return
*/
@GetMapping("insert")
public String insert(String account, String name) {
sysUserService.insert(account, name);
return "ok";
}
/**
* 修改
* @param account
* @param name
* @param state
* @return
*/
@GetMapping("update")
public String update(String account, String name, Integer state) {
sysUserService.update(account, name, state);
return "ok";
}
/**
* 删除
* @param account
* @return
*/
@GetMapping("delete")
public String delete(String account) {
sysUserService.delete(account);
return "ok";
}
/**
* count
* @return
*/
@GetMapping("getCount")
public long getCount() {
return sysUserService.getCount();
}
/**
* 分页查询
* @param account account 可空
* @param pageIndex 页号
* @param pageSize 页数量
* @return
*/
@GetMapping("getSysUsersByPage")
public PageBean<SysUser> getSysUsersByPage(String account, Integer pageIndex, Integer pageSize) {
return sysUserService.getSysUsersByPage(account, pageIndex, pageSize);
}
/**
* 查询用户的角色
* @param account account
* @return List<SysMenu>
*/
@GetMapping("getMenuList")
public List<SysMenu> getMenuList(String account) {
return sysMenuService.getMenuList(account);
}
}
service层
由于业务比较简单,直接把拼sql写在service层,实际开发中建议使用一个dao层。
package com.example.dynamic.service;
import com.example.dynamic.entity.SysUser;
import com.example.dynamic.mapper.SysUserMapper;
import com.example.dynamic.utils.PageBean;
import com.github.pagehelper.PageHelper;
import org.mybatis.dynamic.sql.SqlBuilder;
import org.mybatis.dynamic.sql.delete.render.DeleteStatementProvider;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.mybatis.dynamic.sql.update.render.UpdateStatementProvider;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;
import static org.mybatis.dynamic.sql.SqlBuilder.*;
import static org.mybatis.dynamic.sql.select.SelectDSL.select;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Objects;
@Service
public class SysUserService {
@Resource
private SysUserMapper sysUserMapper;
public List<SysUser> getSysUserList(String account1) {
SelectStatementProvider select = SqlBuilder.select(sysUser.allColumns())
.from(sysUser)
.where(account, isLike(account1).filter(Objects::isNull).map(s -> "%" + s + "%"))
.and(state, isEqualTo(0))
.and(delFlag, isEqualTo(0))
.orderBy(createdTime.descending())
.build()
.render(RenderingStrategies.MYBATIS3);
return sysUserMapper.selectMany(select);
}
@Transactional(rollbackFor = Exception.class)
public boolean insert(String account, String name) {
SysUser sysUser = new SysUser();
sysUser.setAccount(account);
sysUser.setNickName(name);
sysUser.setCreatedTime(LocalDateTime.now());
sysUser.setState(0);
sysUser.setDelFlag(0);
return sysUserMapper.insert(sysUser) > 0;
}
@Transactional(rollbackFor = Exception.class)
public void delete(String account1) {
DeleteStatementProvider del = deleteFrom(sysUser)
.where(account, isEqualTo(account1))
.build()
.render(RenderingStrategies.MYBATIS3);
sysUserMapper.delete(del);
}
@Transactional(rollbackFor = Exception.class)
public boolean update(String account1, String nickName1, Integer state1) {
UpdateStatementProvider update = SqlBuilder.update(sysUser)
.set(nickName).equalTo(nickName1)
.set(state).equalTo(state1)
.set(modifiedTime).equalTo(LocalDateTime.now())
.where(account, isEqualTo(account1))
.build()
.render(RenderingStrategies.MYBATIS3);
return sysUserMapper.update(update) > 0;
}
public PageBean<SysUser> getSysUsersByPage(String account1, Integer pageIndex, Integer pageSize) {
PageHelper.startPage(pageIndex, pageSize);
return new PageBean<SysUser>(sysUserMapper.selectMany(select(sysUser.allColumns())
.from(sysUser)
.where(account, isLike(account1).filter(Objects::isNull).map(x -> "%" + x + "%"))
.and(state, isEqualTo(0))
.orderBy(createdTime.descending())
.build()
.render(RenderingStrategies.MYBATIS3)));
}
public long getCount() {
SelectStatementProvider selectStatement = select(count(sysUser.id))
.from(sysUser)
.where(state, isEqualTo(0))
.build()
.render(RenderingStrategies.MYBATIS3);
return sysUserMapper.count(selectStatement);
}
}
例子中基本完成对表的基本操作,看起来十分的优雅。基本上普通的业务逻辑都包含了,分页使用PageHelper插件和mybatis其他的用法类似。
基本就是lambda的方式在java中编写sql。
静态导入一些mapper类,可以简化代表的编写:
import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;import static org.mybatis.dynamic.sql.SqlBuilder.*;
import static org.mybatis.dynamic.sql.select.SelectDSL.select;
关联查询
直接在java中使用实体对象来关联
package com.example.dynamic.service;
import com.example.dynamic.entity.SysMenu;
import com.example.dynamic.mapper.SysMenuDynamicSqlSupport;
import com.example.dynamic.mapper.SysMenuMapper;
import org.mybatis.dynamic.sql.render.RenderingStrategies;
import org.mybatis.dynamic.sql.select.render.SelectStatementProvider;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import static org.mybatis.dynamic.sql.SqlBuilder.*;
import static org.mybatis.dynamic.sql.select.SelectDSL.select;
import static com.example.dynamic.mapper.SysMenuDynamicSqlSupport.*;
import static com.example.dynamic.mapper.SysRoleMenuDynamicSqlSupport.*;
import static com.example.dynamic.mapper.SysUserRoleDynamicSqlSupport.*;
import static com.example.dynamic.mapper.SysUserDynamicSqlSupport.*;
import java.util.List;
@Service
public class SysMenuService {
@Resource
private SysMenuMapper sysMenuMapper;
public List<SysMenu> getMenuList(String account1) {
return sysMenuMapper.selectMany(select(sysMenu.allColumns())
.from(sysMenu, "m")
.leftJoin(sysRoleMenu, "mr").on(sysMenu.name, equalTo(sysRoleMenu.menuName))
.leftJoin(sysUserRole, "ur").on(sysRoleMenu.roleName, equalTo(sysUserRole.roleName))
.leftJoin(sysUser, "u").on(sysUserRole.account, equalTo(sysUser.account))
.where(sysUser.account, isEqualTo(account1))
.and(sysUser.state, isEqualTo(0))
.build()
.render(RenderingStrategies.MYBATIS3));
}
}
基本上对sql中的关键字都有支持,详细请查看官方文档。
https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html