Java中常用的ORM框架主要是mybatis, hibernate, JPA等框架。 国内又以Mybatis用的多,基于mybatis上的增强框架,又有mybatis plus和TK mybatis等。 今天我们介绍一个新的mybatis增强框架 fluent mybatis, 那既然JDBC --> Mybatis或Mybatis Plus无疑简化了开发者的工作,而今天我们所讲的 Fluent MyBatis又起到什么作用呢?
Fluent Mybatis, 原生Mybatis, Mybatis Plus三者功能对比
Fluent MyBatis是一个 MyBatis 的增强工具,他只做了mybatis的语法糖封装,没有对mybatis做任何修改。 通过编译手段,提供了一系列辅助类来帮助开发简化开发、提高效率。
创建一个示例的数据库表
DROP TABLE IF EXISTS `your_table`;
create table `your_table`
(
id bigint auto_increment comment '主键ID' primary key,
name varchar(30) charset utf8 null comment '姓名',
age int null comment '年龄',
email varchar(50) charset utf8 null comment '邮箱',
gmt_create datetime null comment '记录创建时间',
gmt_modified datetime null comment '记录最后修改时间',
is_deleted tinyint(2) default 0 null comment '逻辑删除标识'
);
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.url=jdbc:mysql://localhost:3306/fluent_mybatis_demo?useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
可以手工创建Entity类,或者任何手段创建的Entity类,然后加上注解
这里直接使用fluent mybatis提供的工具类生成代码
public class AppEntityGenerator {
static final String url = "jdbc:mysql://localhost:3306/fluent_mybatis_demo?useSSL=false&useUnicode=true&characterEncoding=utf-8";
public static void main(String[] args) {
FileGenerator.build(Abc.class);
}
@Tables(
/** 数据库连接信息 **/
url = url, username = "root", password = "password",
/** Entity类parent package路径 **/
basePack = "cn.org.fluent.mybatis.springboot.demo",
/** Entity代码源目录 **/
srcDir = "spring-boot-demo/src/main/java",
/** Dao代码源目录 **/
daoDir = "spring-boot-demo/src/main/java",
/** 如果表定义记录创建,记录修改,逻辑删除字段 **/
gmtCreated = "gmt_create", gmtModified = "gmt_modified", logicDeleted = "is_deleted",
/** 需要生成文件的表 **/
tables = @Table(value = {"your_table"})
)
static class Abc {
}
}
这里有3个特殊字段
执行生成代码main函数, 在工程main/src/java目录下产出 Entity, DaoIntf, DaoImpl文件; 观察YourEntity的主键 id, gmt_create, gmt_modified, is_deleted这几个字段的注解
@Data
@Accessors(chain = true)
@FluentMybatis(table = "your_table")
public class YourEntity implements IEntity{
private static final long serialVersionUID = 1L;
@TableId(value = "id")
private Long id;
@TableField(value = "gmt_create", insert = "now()")
private Date gmtCreate;
@TableField(value = "gmt_modified", insert = "now()", update = "now()")
private Date gmtModified;
@TableField(value = "is_deleted", insert = "0")
private Boolean isDeleted;
@TableField(value = "age")
private Integer age;
@TableField(value = "email")
private String email;
@TableField(value = "name")
private String name;
@Override
public Serializable findPk() {
return id;
}
}
生成的Dao文件,引用到了YourTableBaseDao类,这个类需要根据Entity类编译生成,在重新编译前会有编译错误,所以生成代码后需要重新Rebuild下
@Repository
public class YourDaoImpl extends YourBaseDao implements YourDao {
// 在这里添加你自己的业务逻辑代码
}
在Rebuild后,会在target目录下就会多出几个文件, 重新刷新一下工程把target/generated-sources加到源目录上即可。
这些文件时生成在target/generated-sources, 代码中时可以直接引用和打包的,不需要拷贝到src目录下,也不需要维护这些代码,Entity如果发生变化,重新编译打包即可
这时工程已经具备fluent mybatis强大的增删改查功能了。我们创建一个测试类来验证一下,在测试类中注入 YourMapper,这里演示一个查询所有的方法,所以使用了 listEntity ,其参数是一个Query对象。
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void contextLoads() {
List<YourEntity> list = yourMapper.listEntity(yourMapper.query());
for (YourEntity entity : list) {
System.out.println(entity);
}
}
}
你可以手工往数据库中插入几条记录,验证一下效果。
下面我们分别介绍FluentMybatis提供的insert, select, update和delete方法,内容的介绍基本按4部分解析
单条插入操作
public interface YourMapper extends IEntityMapper<YourEntity> {
/**
* 插入一条记录
*
* @param entity
* @return
*/
@Override
@InsertProvider(
type = YourSqlProvider.class,
method = "insert"
)
@Options(
useGeneratedKeys = true,
keyProperty = "id",
keyColumn = "id"
)
int insert(YourEntity entity);
}
public class YourSqlProvider {
public String insert(YourEntity entity) {
assertNotNull("entity", entity);
MapperSql sql = new MapperSql();
sql.INSERT_INTO("your_table");
List<String> columns = new ArrayList<>();
List<String> values = new ArrayList<>();
if (entity.getId() != null) {
columns.add("id");
values.add("#{id}");
}
columns.add("gmt_create");
if (entity.getGmtCreate() != null) {
values.add("#{gmtCreate}");
} else {
values.add("now()");
}
columns.add("gmt_modified");
if (entity.getGmtModified() != null) {
values.add("#{gmtModified}");
} else {
values.add("now()");
}
columns.add("is_deleted");
if (entity.getIsDeleted() != null) {
values.add("#{isDeleted}");
} else {
values.add("0");
}
if (entity.getAge() != null) {
columns.add("age");
values.add("#{age}");
}
if (entity.getEmail() != null) {
columns.add("email");
values.add("#{email}");
}
if (entity.getName() != null) {
columns.add("name");
values.add("#{name}");
}
sql.INSERT_COLUMNS(columns);
sql.VALUES();
sql.INSERT_VALUES(values);
return sql.toString();
}
}
组装过程中,对对应了 @TableField(insert="默认值")的3个字段:gmt_crate, gmt_modified, is_deleted做了特殊判断。
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void insert() {
// 构造一个对象
YourEntity entity = new YourEntity();
entity.setName("Fluent Mybatis");
entity.setAge(1);
entity.setEmail("darui.wu@163.com");
entity.setIsDeleted(false);
// 插入操作
int count = yourMapper.insert(entity);
System.out.println("count:" + count);
System.out.println("entity:" + entity);
}
}
DEBUG - ==> Preparing: INSERT INTO your_table(gmt_create, gmt_modified, is_deleted, age, email, name) VALUES (now(), now(), ?, ?, ?, ?)
DEBUG - ==> Parameters: false(Boolean), 1(Integer), darui.wu@163.com(String), Fluent Mybatis(String)
DEBUG - <== Updates: 1
count:1
entity:YourEntity(id=18, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis)
根据控制台输出,可以看到Entity的id属性已经是根据数据库自增主键值回写过的。 自增主键的设置是通过 @TableId 注解来的,其属性方法auto()默认值是true。 2. fluent mybatis根据@TableId注解生成的Mapper类上@Options注解如下:
@Options(
useGeneratedKeys = true,
keyProperty = "id",
keyColumn = "id"
)
我们先看一下Entity上这3个字段的@TableField注解, 他们都定义了一个属性方法insert,设置了insert的默认值(即程序编码insert时,如果没有设置该字段,则使用默认值)
@TableField(value = "gmt_create", insert = "now()")
private Date gmtCreate;
@TableField(value = "gmt_modified", insert = "now()", update = "now()")
private Date gmtModified;
@TableField(value = "is_deleted", insert = "0")
private Boolean isDeleted;
在测试例子中,gmt_created和gmt_modified在初始化Entity时,没有设置任何值; is_deleted设置了值false。 在构建sql是,gmt_created, gmt_modified直接使用默认值 "now()", is_deleted使用预编译变量(?)设置(实际值false)。
INSERT INTO your_table
(gmt_create, gmt_modified, is_deleted, age, email, name)
VALUES
(now(), now(), ?, ?, ?, ?)
我们再看一下对应的SQLProvider的SQL构造, 我们只看着3个字段的构造
public class YourSqlProvider {
public String insert(YourEntity entity) {
List<String> columns = new ArrayList<>();
List<String> values = new ArrayList<>();
// 省略 ... ...
columns.add("gmt_create");
if (entity.getGmtCreate() != null) {
values.add("#{gmtCreate}");
} else {
values.add("now()");
}
columns.add("gmt_modified");
if (entity.getGmtModified() != null) {
values.add("#{gmtModified}");
} else {
values.add("now()");
}
columns.add("is_deleted");
if (entity.getIsDeleted() != null) {
values.add("#{isDeleted}");
} else {
values.add("0");
}
if (entity.getAge() != null) {
columns.add("age");
values.add("#{age}");
}
// 省略... ...
return sql.toString();
}
}
我们看到,没有 insert属性的字段,只判断了是否为空; 有insert属性的字段,如果entity不为空,则把默认值赋值给sql语句。
public class YourSqlProvider {
public String insertBatch(Map map) {
assertNotEmpty("map", map);
MapperSql sql = new MapperSql();
List<YourEntity> entities = getParas(map, "list");
sql.INSERT_INTO("your_table");
sql.INSERT_COLUMNS(ALL_ENTITY_FIELDS);
sql.VALUES();
for (int index = 0; index < entities.size(); index++) {
if (index > 0) {
sql.APPEND(", ");
}
sql.INSERT_VALUES(
"#{list[" + index + "].id}",
entities.get(index).getGmtCreate() == null ? "now()" : "#{list[" + index + "].gmtCreate}",
entities.get(index).getGmtModified() == null ? "now()" : "#{list[" + index + "].gmtModified}",
entities.get(index).getIsDeleted() == null ? "0" : "#{list[" + index + "].isDeleted}",
"#{list[" + index + "].age}",
"#{list[" + index + "].email}",
"#{list[" + index + "].name}"
);
}
return sql.toString();
}
}
SQL构造语句是通过一个for循环遍历实体列表,构造出下列SQL语句, 其中对有insert默认值属性处理方式同单条insert一样, 这里就不再重复。
INSERT INTO your_table ('Entity对应的字段列表') VALUES ('实例1值'), ('实例2值')
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
void insertBatch(){
List<YourEntity> entities = new ArrayList<>();
entities.add(new YourEntity().setName("Fluent Mybatis").setEmail("darui.wu@163.com"));
entities.add(new YourEntity().setName("Fluent Mybatis Demo").setEmail("darui.wu@163.com"));
entities.add(new YourEntity().setName("Test4J").setEmail("darui.wu@163.com"));
int count = yourMapper.insertBatch(entities);
System.out.println("count:" + count);
System.out.println("entity:" + entities);
}
}
DEBUG - ==> Preparing: INSERT INTO your_table(id, gmt_create, gmt_modified, is_deleted, age, email, name) VALUES (?, now(), now(), 0, ?, ?, ?) , (?, now(), now(), 0, ?, ?, ?) , (?, now(), now(), 0, ?, ?, ?)
DEBUG - ==> Parameters: null, null, darui.wu@163.com(String), Fluent Mybatis(String), null, null, darui.wu@163.com(String), Fluent Mybatis Demo(String), null, null, darui.wu@163.com(String), Test4J(String)
DEBUG - <== Updates: 3
count:3
entity:[YourEntity(id=null, gmtCreate=null, gmtModified=null, isDeleted=null, age=null, email=darui.wu@163.com, name=Fluent Mybatis), YourEntity(id=null, gmtCreate=null, gmtModified=null, isDeleted=null, age=null, email=darui.wu@163.com, name=Fluent Mybatis Demo), YourEntity(id=null, gmtCreate=null, gmtModified=null, isDeleted=null, age=null, email=darui.wu@163.com, name=Test4J)]
根据id查找单条数据
public interface YourMapper extends IEntityMapper<YourEntity> {
String ResultMap = "YourEntityResultMap";
@SelectProvider(
type = YourSqlProvider.class,
method = "findById"
)
@Results(
id = ResultMap,
value = {
@Result(column = "id", property = "id", javaType = Long.class, id = true),
@Result(column = "gmt_create", property = "gmtCreate", javaType = Date.class),
@Result(column = "gmt_modified", property = "gmtModified", javaType = Date.class),
@Result(column = "is_deleted", property = "isDeleted", javaType = Boolean.class),
@Result(column = "age", property = "age", javaType = Integer.class),
@Result(column = "email", property = "email", javaType = String.class),
@Result(column = "name", property = "name", javaType = String.class)
}
)
YourEntity findById(Serializable id);
}
在findById上,除了定义了提供动态SQL语句的SQLProvider类和方法外,还定义的数据映射关系 @Results。 这个ResultMap映射在单个Mapper里是通用的,其他的查询方法返回Entity对象时也会用到。
public class YourSqlProvider {
public String findById(Serializable id) {
assertNotNull("id", id);
MapperSql sql = new MapperSql();
sql.SELECT("your_table", ALL_ENTITY_FIELDS);
sql.WHERE("id = #{id}");
return sql.toString();
}
}
这个SQL拼接比较简单
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void findById(){
YourEntity entity = yourMapper.findById(8L);
System.out.println(entity);
}
}
DEBUG - ==> Preparing: SELECT id, gmt_create, gmt_modified, is_deleted, age, email, name FROM your_table WHERE id = ?
DEBUG - ==> Parameters: 8(Long)
DEBUG - <== Total: 1
YourEntity(id=8, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis)
根据id列表批量查询实例
public interface YourMapper extends IEntityMapper<YourEntity> {
String ResultMap = "YourEntityResultMap";
@Override
@SelectProvider(
type = YourSqlProvider.class,
method = "listByIds"
)
@ResultMap(ResultMap)
List<YourEntity> listByIds(@Param(Param_Coll) Collection ids);
}
输入是一个id列表集合,返回是一个Entity列表, 数据的映射复用了findById中定义的ResultMap。
public class YourSqlProvider {
public String listByIds(Map map) {
Collection ids = getParas(map, "coll");
MapperSql sql = new MapperSql();
sql.SELECT("your_table", ALL_ENTITY_FIELDS);
sql.WHERE_PK_IN("id", ids.size());
return sql.toString();
}
}
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void listByIds() {
List<YourEntity> entities = yourMapper.listByIds(Arrays.asList(8L, 9L));
System.out.println(entities);
}
}
DEBUG - ==> Preparing: SELECT id, gmt_create, gmt_modified, is_deleted, age, email, name FROM your_table WHERE id IN (?, ?)
DEBUG - ==> Parameters: 8(Long), 9(Long)
DEBUG - <== Total: 2
[YourEntity(id=8, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis),
YourEntity(id=9, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis)]
根据自定义条件查询单条记录
public interface YourMapper extends IEntityMapper<YourEntity> {
@SelectProvider(
type = YourSqlProvider.class,
method = "findOne"
)
@ResultMap(ResultMap)
YourEntity findOne(@Param(Param_EW) IQuery query);
}
public class YourSqlProvider {
public String findOne(Map map) {
WrapperData data = getWrapperData(map, "ew");
MapperSql sql = new MapperSql();
sql.SELECT("your_table", data, ALL_ENTITY_FIELDS);
sql.WHERE_GROUP_ORDER_BY(data);
return byPaged(DbType.MYSQL, data, sql.toString());
}
}
动态SQL组装做了以下几件事:
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void findOne() {
YourEntity entity = yourMapper.findOne(new YourQuery()
.where.id().eq(4L).end()
);
}
}
查看控制台的输出:
DEBUG - ==> Preparing: SELECT id, gmt_create, gmt_modified, is_deleted, age, email, name FROM your_table WHERE id = ?
DEBUG - ==> Parameters: 4(Long)
DEBUG - <== Total: 1
YourEntity(id=4, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis)
这种情况下,数据库中满足条件的数据有一条或0条;如果符合条件的数据大于一条,情况会怎样呢,我们再写一个测试实验一下。
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void findOne2() {
YourEntity entity = yourMapper.findOne(new YourQuery()
.where.name().eq("Fluent Mybatis").end()
);
System.out.println(entity);
}
}
因为数据库中有多条name='Fluent Mybatis'的数据,调用这个方法会抛出异常
DEBUG - ==> Preparing: SELECT id, gmt_create, gmt_modified, is_deleted, age, email, name FROM your_table WHERE name = ?
DEBUG - ==> Parameters: Fluent Mybatis(String)
DEBUG - <== Total: 14
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions
.TooManyResultsException: Expected one result (or null) to be returned by selectOne(),
but found: 14
public interface YourMapper extends IEntityMapper<YourEntity> {
String ResultMap = "YourEntityResultMap";
@SelectProvider(
type = YourSqlProvider.class,
method = "listByMap"
)
@ResultMap(ResultMap)
List<YourEntity> listByMap(@Param(Param_CM) Map<String, Object> columnMap);
}
入参Map<String, Object>, 用来表示查询数据的条件。具体条件是 key = value 的AND关系。
public class YourSqlProvider {
public String listByMap(Map map) {
Map<String, Object> where = getParas(map, "cm");
MapperSql sql = new MapperSql();
sql.SELECT("your_table", ALL_ENTITY_FIELDS);
sql.WHERE("cm", where);
return sql.toString();
}
}
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void listByMap() {
List<YourEntity> entities = yourMapper.listByMap(new HashMap<String, Object>() {
{
this.put("name", "Fluent Mybatis");
this.put("is_deleted", false);
}
});
System.out.println(entities);
}
}
DEBUG - ==> Preparing: SELECT id, gmt_create, gmt_modified, is_deleted, age, email, name FROM your_table WHERE is_deleted = ? AND name = ?
DEBUG - ==> Parameters: false(Boolean), Fluent Mybatis(String)
DEBUG - <== Total: 5
[YourEntity(id=4, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis),
YourEntity(id=5, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis),
YourEntity(id=6, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis),
YourEntity(id=7, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis),
YourEntity(id=8, gmtCreate=null, gmtModified=null, isDeleted=false, age=1, email=darui.wu@163.com, name=Fluent Mybatis)]
根据自定义条件查询数据,并把数据映射为对应的Entity类
public interface YourMapper extends IEntityMapper<YourEntity> {
@SelectProvider(
type = YourSqlProvider.class,
method = "listEntity"
)
@ResultMap(ResultMap)
List<YourEntity> listEntity(@Param(Param_EW) IQuery query);
}
public class YourSqlProvider {
public String listEntity(Map map) {
WrapperData data = getWrapperData(map, "ew");
MapperSql sql = new MapperSql();
sql.SELECT("your_table", data, ALL_ENTITY_FIELDS);
sql.WHERE_GROUP_ORDER_BY(data);
return byPaged(DbType.MYSQL, data, sql.toString());
}
}
同findOne方法, 动态SQL组装做了下面几件事:
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void listEntity() {
List<YourEntity> entities = yourMapper.listEntity(new YourQuery()
.select.name().age().email().end()
.where.id().lt(6L)
.and.name().like("Fluent").end()
.orderBy.id().desc().end()
);
System.out.println(entities);
}
}
DEBUG - ==> Preparing: SELECT name, age, email FROM your_table WHERE id < ? AND name LIKE ? ORDER BY id DESC
DEBUG - ==> Parameters: 6(Long), %Fluent%(String)
DEBUG - <== Total: 2
[YourEntity(id=null, gmtCreate=null, gmtModified=null, isDeleted=null, age=1, email=darui.wu@163.com, name=Fluent Mybatis),
YourEntity(id=null, gmtCreate=null, gmtModified=null, isDeleted=null, age=1, email=darui.wu@163.com, name=Fluent Mybatis)]
自定义查询定义了
listMaps参数构造和listEntity一样,不同的时返回时不映射为Entity,而且映射成Map对象
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void listMaps() {
List<Map<String,Object>> maps = yourMapper.listMaps(new YourQuery()
.select.name().age().email().end()
.where.id().lt(6L)
.and.name().like("Fluent").end()
.orderBy.id().desc().end()
);
System.out.println(maps);
}
}
DEBUG - ==> Preparing: SELECT name, age, email AS EMail FROM your_table WHERE id < ? AND name LIKE ? ORDER BY id DESC
DEBUG - ==> Parameters: 6(Long), %Fluent%(String)
DEBUG - <== Total: 2
[{name=Fluent Mybatis, EMail=darui.wu@163.com},
{name=Fluent Mybatis, EMail=darui.wu@163.com}]
listObjs查询参数构造和listEntity、listMaps一样,但只返回查询对象的第一列,其余列被舍弃。
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void listObjs() {
List<String> ids = yourMapper.listObjs(new YourQuery()
.select.name().age().email().end()
.where.id().lt(6L)
.and.name().like("Fluent").end()
.orderBy.id().desc().end()
);
System.out.println(ids);
}
}
DEBUG - ==> Preparing: SELECT name, age, email AS EMail FROM your_table WHERE id < ? AND name LIKE ? ORDER BY id DESC
DEBUG - ==> Parameters: 6(Long), %Fluent%(String)
DEBUG - <== Total: 2
[Fluent Mybatis, Fluent Mybatis]
我们看到,控制台只打印出了查询字段的第一列name: [Fluent Mybatis, Fluent Mybatis]
count, 返回符合条件的记录数
public interface YourMapper extends IEntityMapper<YourEntity> {
@SelectProvider(
type = YourSqlProvider.class,
method = "count"
)
Integer count(@Param(Param_EW) IQuery query);
}
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void count() {
int count = yourMapper.count(new YourQuery()
.where.id().lt(1000L)
.and.name().like("Fluent").end()
.limit(0, 10)
);
System.out.println(count);
}
}
DEBUG - ==> Preparing: SELECT COUNT(*) FROM your_table WHERE id < ? AND name LIKE ? LIMIT ?, ?
DEBUG - ==> Parameters: 1000(Long), %Fluent%(String), 0(Integer), 10(Integer)
DEBUG - <== Total: 1
5
使用方法同count,只是SQL语句部分舍弃了limit设置(如果你设置了)
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void countNoLimit() {
int count = yourMapper.countNoLimit(new YourQuery()
.where.id().lt(1000L)
.and.name().like("Fluent").end()
.limit(0, 10)
);
System.out.println(count);
}
}
DEBUG - ==> Preparing: SELECT COUNT(*) FROM your_table WHERE id < ? AND name LIKE ?
DEBUG - ==> Parameters: 1000(Long), %Fluent%(String)
DEBUG - <== Total: 1
5
我们看到打印出的SQL语句和count方法相比,少了limit部分。
updateById 根据Entity id值,更新Entity中非空属性
public interface YourMapper extends IEntityMapper<YourEntity> {
@UpdateProvider(
type = YourSqlProvider.class,
method = "updateById"
)
int updateById(@Param(Param_ET) YourEntity entity);
}
入参是Entity对象, 出参是更新记录数,这里返回值只可能是0: 不存在id记录,更新失败;1: 更新id记录成功。
public class YourSqlProvider {
public String updateById(Map<String, Object> map) {
YourEntity entity = getParas(map, "et");
MapperSql sql = new MapperSql();
sql.UPDATE("your_table");
List<String> sets = new ArrayList<>();
if (entity.getGmtCreate() != null) {
sets.add("gmt_create = #{et.gmtCreate}");
}
if (entity.getGmtModified() != null) {
sets.add("gmt_modified = #{et.gmtModified}");
} else {
sets.add("gmt_modified = now()");
}
if (entity.getIsDeleted() != null) {
sets.add("is_deleted = #{et.isDeleted}");
}
if (entity.getAge() != null) {
sets.add("age = #{et.age}");
}
if (entity.getEmail() != null) {
sets.add("email = #{et.email}");
}
if (entity.getName() != null) {
sets.add("name = #{et.name}");
}
sql.SET(sets);
sql.WHERE("id = #{et.id}");
return sql.toString();
}
}
我们看到,在设置set时,会判断entity对象是否为null;但如果在Entity对象上设置了 @TableField( update = 'update默认值'), 则entity属性是空的情况下,会使用默认值代替,比如上面gmtModified属性
if (entity.getGmtModified() != null) {
sets.add("gmt_modified = #{et.gmtModified}");
} else {
sets.add("gmt_modified = now()");
}
where条件部分则比较简单: id = #{et.id}
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void updateById() {
int count = yourMapper.updateById(new YourEntity()
.setId(2L)
.setName("Powerful Fluent Mybatis")
);
System.out.println(count);
}
}
DEBUG - ==> Preparing: UPDATE your_table SET gmt_modified = now(), name = ? WHERE id = ?
DEBUG - ==> Parameters: Powerful Fluent Mybatis(String), 2(Long)
DEBUG - <== Updates: 1
1
我们看到update set部分,除了设置了name=?,还设置了 gmt_modified = now()
updateBy, 根据自定义set语句,where条件执行更新操作
public interface YourMapper extends IEntityMapper<YourEntity> {
@UpdateProvider(
type = YourSqlProvider.class,
method = "updateBy"
)
int updateBy(@Param(Param_EW) IUpdate update);
}
入参是一个IUpdate对象,出参是更新成功的记录数。
public class YourSqlProvider {
public String updateBy(Map<String, Object> map) {
WrapperData data = getWrapperData(map, "ew");
MapperSql sql = new MapperSql();
Map<String, String> updates = data.getUpdates();
assertNotEmpty("updates", updates);
sql.UPDATE("your_table");
List<String> sets = new ArrayList<>();
if (!updates.containsKey("gmtModified")) {
sets.add("gmt_modified = now()");
}
sets.add(data.getUpdateStr());
sql.SET(sets);
sql.WHERE_GROUP_ORDER_BY(data);
sql.LIMIT(data, true);
return sql.toString();
}
}
动态构造语句中对 @TableField( update = 'update默认值')字段(这里是gmtModified)做了单独判断, 如果条件中不包含gmtModified,则追加默认值更新。
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void updateBy() {
int count = yourMapper.updateBy(new YourUpdate()
.update.name().is("Powerful Fluent mybatis")
.set.email().is("darui.wu@163.com")
.set.age().is(1).end()
.where.id().eq(2).end()
);
System.out.println(count);
}
}
DEBUG - ==> Preparing: UPDATE your_table SET gmt_modified = now(), name = ?, email = ?, age = ? WHERE id = ?
DEBUG - ==> Parameters: Powerful Fluent mybatis(String), darui.wu@163.com(String), 1(Integer), 2(Integer)
DEBUG - <== Updates: 1
1
注意 gmt_modified = now()更新默认值部分
根据主键Id物理删除记录
public class YourSqlProvider {
public String deleteById(Serializable id) {
MapperSql sql = new MapperSql();
sql.DELETE_FROM("your_table");
sql.WHERE("id = #{id}");
return sql.toString();
}
}
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void deleteById(){
int count = yourMapper.deleteById(3L);
System.out.println("count:" + count);
}
}
DEBUG - ==> Preparing: DELETE FROM your_table WHERE id = ?
DEBUG - ==> Parameters: 3(Long)
DEBUG - <== Updates: 1
count:1
按id列表批量删除, 用法同deleteById
@Test
void deleteByIds() {
int count = yourMapper.deleteByIds(Arrays.asList(1L, 2L, 3L));
System.out.println("count:" + count);
}
DEBUG - ==> Preparing: DELETE FROM your_table WHERE id IN (?, ?, ?)
DEBUG - ==> Parameters: 1(Long), 2(Long), 3(Long)
delete, 按自定义Query条件删除记录
public interface YourMapper extends IEntityMapper<YourEntity> {
@DeleteProvider(
type = YourSqlProvider.class,
method = "delete"
)
int delete(@Param(Param_EW) IQuery wrapper);
}
入参是一个IQuery对象,出参是删除记录数
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void delete() {
int count = yourMapper.delete(new YourQuery()
.where.id().in(new int[]{1, 2, 3}).end()
);
System.out.println("count:" + count);
}
}
DEBUG - ==> Preparing: DELETE FROM your_table WHERE id IN (?, ?, ?)
DEBUG - ==> Parameters: 1(Integer), 2(Integer), 3(Integer)
DEBUG - <== Updates: 3
count:3
deleteByMap: 根据map中key=value条件集更新记录
public interface YourMapper extends IEntityMapper<YourEntity> {
@DeleteProvider(
type = YourSqlProvider.class,
method = "deleteByMap"
)
int deleteByMap(@Param(Param_CM) Map<String, Object> cm);
}
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
@Autowired
private YourMapper yourMapper;
@Test
void deleteByMap() {
int count = yourMapper.deleteByMap(new HashMap<String, Object>() {
{
this.put("name", "Fluent Mybatis");
this.put("email", "darui.wu@163.com");
}
});
System.out.println("count:" + count);
}
}
DEBUG - ==> Preparing: DELETE FROM your_table WHERE name = ? AND email = ?
DEBUG - ==> Parameters: Fluent Mybatis(String), darui.wu@163.com(String)
DEBUG - <== Updates: 2
count:2
本篇文章介绍完FluentMuybatis提供Mapper内置方法,我们后面接着介绍如何通过IQuery和IUpdate定义强大的动态SQL语句。 文章中提到示例验证例子可以在
FluentMybatis gitee docs上找到
出处:https://juejin.cn/post/6884799624755249160