Fluent MyBatis使用入门

稻草江南
15,800 阅读10分钟

引言

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三者功能对比

FluentMybatis原理

初识Fluent MyBatis

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 '逻辑删除标识'
);

初始化 SpringBoot 项目

  • 设置项目依赖
  1. spring boot: 基于spring boot开发，肯定是必须的
  2. lombok: 省略get, set, toString代码的神器，个人比较喜欢；你也可以手动生成get set方法
  3. mysql-connector-java: 数据库驱动
  4. fluent-mybatis: fluent-mybatis运行时依赖
  5. fluent-mybatis-processor: fluent-mybatis代码生成&编译时依赖
  6. 测试依赖的jar包: spring-test, junit

maven pom具体配置

  • 配置数据库信息
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

properties具体配置

  • 创建实体类

可以手工创建Entity类，或者任何手段创建的Entity类，然后加上注解

  1. 在Entity类上加上 @FluentMybatis注解
  2. 在主键字段加 @TableId注解
  3. 在一般字段加 @TableField注解

这里直接使用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个特殊字段

  1. gmt_create, 记录创建时间，会设置记录插入的默认值，对应生成Entity字段上的注解 @TableField(insert="now()")
  2. gmt_modified, 记录最后更新时间，会设置记录插入和更新默认值，对应生成代码Entity字段上注解 @TableField(insert="now()", update="now()")
  3. is_deleted, 记录逻辑删除标识，字段类型为Boolean，且设置记录插入的默认值，对应注解 @TableField(insert="0")

执行生成代码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 extends RichEntity{
    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 final Class entityClass() {
      return YourEntity.class;
    }
}

生成的Dao文件，引用到了YourTableBaseDao类，这个类需要根据Entity类编译生成，在重新编译前会有编译错误，所以生成代码后需要重新Rebuild下

@Repository
public class YourDaoImpl extends YourBaseDao implements YourDao {
    // 在这里添加你自己的业务逻辑代码
}

-w100

在Rebuild后，会在target目录下就会多出几个文件, 重新刷新一下工程把target/generated-sources加到源目录上即可。

这些文件时生成在target/generated-sources, 代码中时可以直接引用和打包的，不需要拷贝到src目录下，也不需要维护这些代码，Entity如果发生变化，重新编译打包即可

image.png

  • 启动SpringBoot测试，验证效果

这时工程已经具备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);
        }
    }
}

你可以手工往数据库中插入几条记录，验证一下效果。

Entity对应的Mapper提供的数据操作方法

下面我们分别介绍FluentMybatis提供的insert, select, update和delete方法，内容的介绍基本按4部分解析

  1. 方法的Mapper定义（编译生成的代码）
  2. Mapper对应的动态SQL组装SQLProvider（编译生成的代码）
  3. 一个验证测试例子
  4. 根据例子打印的SQL语句和信息输出，对照查看

FluentMybatis提供的insert方法

insert

单条插入操作

  • Mapper方法
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);
}
  • 动态SQL组装
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做了特殊判断。

  • 编写insert test验证下
@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);
    }
}
  • 执行insert测试方法, 查看控制台输出log信息
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)
  • 这里有几个需要注意的地方
  1. Entity主键值的自增和回写 根据控制台输出，可以看到Entity的id属性已经是根据数据库自增主键值回写过的。 自增主键的设置是通过 @TableId 注解来的，其属性方法auto()默认值是true。
  2. fluent mybatis根据@TableId注解生成的Mapper类上@Options注解如下：
@Options(
  useGeneratedKeys = true,
  keyProperty = "id",
  keyColumn = "id"
)
  1. gmt_created, gmt_modified, is_deleted 默认值插入处理 我们先看一下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语句。

insertBatch

  • 批量插入, 查看Mapper对应的SqlProvider中insertBatch动态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)]

FluentMybatis提供的select查询方法

findById

根据id查找单条数据

  • 系统生成的Mapper方法定义
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对象时也会用到。

  • 系统生成的动态sql构造方法
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拼接比较简单

  1. 根据Entity字段拼接了查询字段列表
  2. 设置 id = #{id}
  • 写个测试实际使用下
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
    @Autowired
    private YourMapper yourMapper;

    @Test
    void findById(){
        YourEntity entity = yourMapper.findById(8L);
        System.out.println(entity);
    }
}
  • 查看控制台输出log
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)

listByIds

根据id列表批量查询实例

  • Mapper定义
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。

  • 动态SQL提供方法
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();
    }
}
  1. 根据Entity字段拼接了查询字段列表
  2. 根据传入的id数量(size), 设置 id IN (#{coll[0]}, ..., #{coll[size - 1]})
  • 写测试验证下
@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)]

findOne

根据自定义条件查询单条记录

  • Mapper方法定义
public interface YourMapper extends IEntityMapper<YourEntity> {
    @SelectProvider(
        type = YourSqlProvider.class,
        method = "findOne"
    )
    @ResultMap(ResultMap)
    YourEntity findOne(@Param(Param_EW) IQuery query);
}
  • 动态sql组装
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组装做了以下几件事：

  1. 根据query是否显式设置了查询字段，设置select字段列表，如果未设置，则取默认拼装Entity全字段。
  2. 根据query里面的where, group by, having by和order by设置查询条件: sql.WHERE_GROUP_ORDER_BY(data)
  3. 根据是否设置了分页信息和数据库类型，组装分页查询语法: byPaged(DbType.MYSQL, data, sql.toString())
  • 写个测试验证下
@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条；如果符合条件的数据大于一条，情况会怎样呢，我们再写一个测试实验一下。

  • 如果findOne，符合条件数据大于2条
@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

listByMap

  • Mapper方法定义
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关系。

  • 动态SQL拼接
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();
    }
}
  1. 查询Entity所有字段
  2. 组装map条件, (key1 = value1) AND (key2 = value2)
  • 写个测试demo验证下
@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)]

listEntity

根据自定义条件查询数据，并把数据映射为对应的Entity类

  • Mapper方法定义
public interface YourMapper extends IEntityMapper<YourEntity> {
    @SelectProvider(
        type = YourSqlProvider.class,
        method = "listEntity"
    )
    @ResultMap(ResultMap)
    List<YourEntity> listEntity(@Param(Param_EW) IQuery query);
}
  • 动态SQL组装
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组装做了下面几件事:

  1. 根据query是否显式设置了查询字段，设置select字段列表，如果未设置，则取默认拼装Entity全字段。
  2. 根据query里面的where, group by, having by和order by设置查询条件: sql.WHERE_GROUP_ORDER_BY(data)
  3. 根据是否设置了分页信息和数据库类型，组装分页查询语法: byPaged(DbType.MYSQL, data, sql.toString())
  • 写个测试看下效果
@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);
    }
}
  • 查看控制台log
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)]

自定义查询定义了

  1. 要查询的字段: name, age, email3个字段
  2. 定义了具体条件: id < ? AND name LIKE ?
  3. 定义了按id倒序排

listMaps

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

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

count, 返回符合条件的记录数

  • Mapper定义
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

countNoLimit

使用方法同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部分。

FluentMybatis提供的update更新方法

updateById

updateById 根据Entity id值，更新Entity中非空属性

  • Mapper定义
public interface YourMapper extends IEntityMapper<YourEntity> {
  @UpdateProvider(
      type = YourSqlProvider.class,
      method = "updateById"
  )
  int updateById(@Param(Param_ET) YourEntity entity);
}

入参是Entity对象， 出参是更新记录数，这里返回值只可能是0: 不存在id记录,更新失败；1: 更新id记录成功。

  • 动态SQL组装
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

updateBy, 根据自定义set语句，where条件执行更新操作

  • Mapper定义
public interface YourMapper extends IEntityMapper<YourEntity> {
  @UpdateProvider(
      type = YourSqlProvider.class,
      method = "updateBy"
  )
  int updateBy(@Param(Param_EW) IUpdate update);
}

入参是一个IUpdate对象，出参是更新成功的记录数。

  • 动态SQL构造
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()
            .set.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()更新默认值部分

FluentMybatis提供的delete方法

deleteById

根据主键Id物理删除记录

  • 查看deleteById对应的SqlProvider语句构造方法
public class YourSqlProvider {
    public String deleteById(Serializable id) {
        MapperSql sql = new MapperSql();
        sql.DELETE_FROM("your_table");
        sql.WHERE("id = #{id}");
        return sql.toString();
    }
}
  • deleteById的SQL构造比较简单，我们直接看测试演示例子
@SpringBootTest(classes = QuickStartApplication.class)
public class FluentMybatisApplicationTest {
    @Autowired
    private YourMapper yourMapper;

    @Test
    void deleteById(){
        int count = yourMapper.deleteById(3L);
        System.out.println("count:" + count);
    }
}
  • 查看控制台输出log:
DEBUG - ==>  Preparing: DELETE FROM your_table WHERE id = ?  
DEBUG - ==> Parameters: 3(Long) 
DEBUG - <==    Updates: 1 
count:1

deleteByIds

按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

delete, 按自定义Query条件删除记录

  • Mapper定义
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

deleteByMap: 根据map中key=value条件集更新记录

  • Mapper定义
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上找到

Fluent Mybatis介绍系列

Fluent Mybatis文档&示例

Fluent Mybatis源码, github

avatar
文章
阅读
粉丝
相关推荐
Fluent Mybatis 入门介绍二
3.0k阅读
 · 
43点赞
初探 FluentMybatis
1.5k阅读
 · 
6点赞
使用 Fluent Mybatis 实现mybatis动态sql拼装和fluent api语法
4.8k阅读
 · 
43点赞
Fluent Mybatis入门介绍七：批量更新
2.8k阅读
 · 
14点赞
Fluent Mybatis, 原生Mybatis, Mybatis Plus三者功能对比
3.0k阅读
 · 
13点赞