单表：Mybatis-Plus分页

单表查询时建议使用：借助的是Page类与Wrapper接口实现
单表分页：在IService里有分页查询接口叫Page<T> selectPage(Page<T> page);和Page<T> selectPage(Page<T> page, Wrapper<T> wrapper);

  1. /**
  2.  * <p>
  3.  * 顶级 Service
  4.  * </p>
  5.  *
  6.  * @author hubin
  7.  * @Date 2016-04-20
  8.  */
  9. public interface IService<T> {
  13.     /**
  14.      * <p>
  15.      * 翻页查询
  16.      * </p>
  17.      *
  18.      * @param page 翻页对象
  19.      * @return
  20.      */
  21.     Page<T> selectPage(Page<T> page);
  25.     /**
  26.      * <p>
  27.      * 翻页查询
  28.      * </p>
  29.      *
  30.      * @param page    翻页对象
  31.      * @param wrapper 实体包装类 {@link Wrapper}
  32.      * @return
  33.      */
  34.     Page<T> selectPage(Page<T> page, Wrapper<T> wrapper);
  36. }
  38. 由于是单表查询，不需定义连接查询的Mapper，用IService的Page<T> selectPage(Page<T> page, Wrapper<T> wrapper);即可
  40.     @Override
  41.     public PageInfo<MsgLogConditionResponse> msgLogs(MsgLogConditionRequest msgLogConditionRequest) {
  42.         Page page = new Page<>((msgLogConditionRequest.getCurrent() == null ? 1 : msgLogConditionRequest.getCurrent()), (msgLogConditionRequest.getSize() == null ? 10 : msgLogConditionRequest.getSize()));
  43.         Boolean unRead = msgLogConditionRequest.getUnRead();
  44.         String sceneType = msgLogConditionRequest.getSceneType();
  45.         System.out.println("sceneType:"+sceneType);
  46.         //分页列表SQL,if-test控制是否执行过滤
  47.         Page resPage = selectPage(page, new EntityWrapper<MsgLog>()
  48.                 .eq(unRead, MsgLog.CONST_MSG_STATE, MsgLog.HAVED_NO_READ)
  49.                 .eq(StringUtils.isNotBlank(sceneType), MsgLog.CONST_SCENE_TYPE, sceneType)
  50.                 .eq(MsgLog.CONST_STATUS, MsgLog.DELETE_STATUS_VALIDATED));
  51.         List<MsgLog> records = resPage.getRecords();
  53.         ArrayList<MsgLogConditionResponse> responses = new ArrayList<>();
  54.         for (MsgLog msgLog : records) {
  55.             MsgLogConditionResponse msgLogConditionResponse = new MsgLogConditionResponse();
  56.             msgLogConditionResponse.setMsgContent(msgLog.getMsgContent());
  57.             msgLogConditionResponse.setReceiveTime(msgLog.getReceiveTime());
  58.             msgLogConditionResponse.setSceneType(msgLog.getSceneType());
  59.             msgLogConditionResponse.setMsgStatus(msgLog.getMsgState());
  60.             responses.add(msgLogConditionResponse);
  61.         }
  62.         return new PageInfo<>(page.getCurrent(), page.getSize(), page.getTotal(), responses);
  63.     }

遇到连表分页就自定义连接查询的Mapper，即手写SQL

  1. public interface VmInstanceApplicationMapper extends BaseMapper<VmInstanceApplication> {
  2.     /**
  3.      * 申请列表
  4.      * @param page
  5.      * @param params
  6.      * @return
  7.      */
  8.     List<VmInstanceApplicationResponse> applicationList(Page page, VmInstanceApplicationRequest params);
  9. }
  11. #SQL无需手动分页了
  12.     <select id="applicationList" parameterType="com.iwhalecloud.aiFactory.aiDevCenter.application.vo.VmInstanceApplicationRequest" resultType="com.iwhalecloud.aiFactory.aiDevCenter.application.vo.VmInstanceApplicationResponse">
  13.         SELECT
  14.             vm.name vmName,
  15.             t.id, t.vm_instance, t.duration, t.application_desc, t.create_user_id, t.create_time, t.auditor, t.audit_desc, t.audit_time, t.com_acct_id, t.application_status
  16.         FROM
  17.             vm_instance_application t
  18.         LEFT JOIN vm_instance vm ON t.vm_instance = vm.id
  19.         WHERE
  20.             1 = 1
  21.         <if test="createUserId != null">
  22.             AND t.create_user_id = #{createUserId}
  23.         </if>
  24.         <if test="applicationStatus != null and applicationStatus != ''">
  25.             AND t.application_status = #{applicationStatus}
  26.         </if>
  27.         <if test="vmName != null and vmName != ''">
  28.             AND vm.name LIKE concat('%', #{vmName}, '%')
  29.         </if>
  30.         <if test="vmInstance != null">
  31.             AND t.vm_instance = #{vmInstance}
  32.         </if>
  33.         <if test="comAcctId != null">
  34.             AND t.com_acct_id = #{comAcctId}
  35.         </if>
  36.         <if test="applicationStatusList != null and applicationStatusList.size() > 0">
  37.             AND t.application_status in
  38.             <foreach collection="applicationStatusList" item="one" open="(" close=")" separator=",">
  39.                 #{one}
  40.             </foreach>
  41.         </if>
  42.         ORDER BY
  43.             create_time DESC
  44.     </select>

单表：Plus的CRUD规范

单操作|条件操作|批量操作使用规范：

  • BaseMapper只有单条插入，在IService里有批量插入接口叫insertBatch
  • BaseMapper只有单条删除，在IService里有批量删除接口叫deleteBatchIds
  • BaseMapper只有单条修改，在IService里有批量删除接口叫updateBatchById
  • 其实BaseMapper里面的条件删除，条件修改也可以构造批量操作条件，但是直接使用IService里面现成的批量操作更方便，推荐！
  • BaseMapper里面的条件查询十分好用List<T> selectList(@Param("ew") Wrapper<T> wrapper);

  • IService里的批量ids查询很方便 List<T> selectBatchIds(Collection<? extends Serializable> idList);

    增

    单增

    1. public interface BaseMapper<T> {
    3.   /**
    4.    * <p>
    5.    * 插入一条记录
    6.    * </p>
    7.    *
    8.    * @param entity 实体对象
    9.    * @return int
    10.    */
    11.   Integer insert(T entity);
    12. }

    返回ID

    ```java MyBatis-Plus 新增插入成功并返回自增Id：调用完insert方法后，直接 entity.getId() 即可

taiModelChangeMapper.insert(taiModelChange); //得到mybatis-plus的自增id Integer id = taiModelChange.getId(); System.out.println(“得到mybatis-plus的自增id “+id);

  1. <a name="MWnGV"></a>
  2. ### 批量增
  3. `BaseMapper`只有单条插入，在`IService`里有批量插入接口叫`insertBatch`
  4. ```shell
  5. public interface IService<T> {
  8.     /**
  9.      * <p>
  10.      * 插入（批量），该方法不适合 Oracle
  11.      * </p>
  12.      *
  13.      * @param entityList 实体对象列表
  14.      * @return boolean
  15.      */
  16.     boolean insertBatch(List<T> entityList);
  17. }

删

单删deleteById

  1. public interface BaseMapper<T> {
  3.     /**
  4.      * <p>
  5.      * 根据 ID 删除
  6.      * </p>
  7.      *
  8.      * @param id 主键ID
  9.      * @return int
  10.      */
  11.     Integer deleteById(Serializable id);
  12. }

条件删

  1. public interface BaseMapper<T> {
  3.     /**
  4.      * <p>
  5.      * 根据 entity 条件，删除记录
  6.      * </p>
  7.      *
  8.      * @param wrapper 实体对象封装操作类（可以为 null）
  9.      * @return int
  10.      */
  11.     Integer delete(@Param("ew") Wrapper<T> wrapper);
  12. }

批量删

BaseMapper只有单条删除，在IService里有批量删除接口叫deleteBatchIds

  1. public interface IService<T> {
  4.     /**
  5.      * <p>
  6.      * 删除（根据ID 批量删除）
  7.      * </p>
  8.      *
  9.      * @param idList 主键ID列表
  10.      * @return boolean
  11.      */
  12.     boolean deleteBatchIds(Collection<? extends Serializable> idList);
  14. }

改

单改updateById

  1. public interface BaseMapper<T> {
  3.     /**
  4.      * <p>
  5.      * 根据 ID 修改
  6.      * </p>
  7.      *
  8.      * @param entity 实体对象
  9.      * @return int
  10.      */
  11.     Integer updateById(@Param("et") T entity);
  12. }

条件改

  1. public interface BaseMapper<T> {
  3.     /**
  4.      * <p>
  5.      * 根据 whereEntity 条件，更新记录
  6.      * </p>
  7.      *
  8.      * @param entity  实体对象
  9.      * @param wrapper 实体对象封装操作类（可以为 null）
  10.      * @return
  11.      */
  12.     Integer update(@Param("et") T entity, @Param("ew") Wrapper<T> wrapper);
  14. }

批量改

BaseMapper只有单条修改，在IService里有批量删除接口叫updateBatchById

  1. public interface IService<T> {
  4.     /**
  5.      * <p>
  6.      * 根据ID 批量更新
  7.      * </p>
  8.      *
  9.      * @param entityList 实体对象列表
  10.      * @return boolean
  11.      */
  12.     boolean updateBatchById(List<T> entityList);
  14. }

查

单查

  2.     /**
  3.      * <p>
  4.      * 根据 ID 查询
  5.      * </p>
  6.      *
  7.      * @param id 主键ID
  8.      * @return T
  9.      */
  10.     T selectById(Serializable id);
  13.     /**
  14.      * <p>
  15.      * 根据 entity 条件，查询一条记录
  16.      * </p>
  17.      *
  18.      * @param entity 实体对象
  19.      * @return T
  20.      */
  21.     T selectOne(@Param("ew") T entity);

条件查

  2. public interface BaseMapper<T> {
  4.     /**
  5.      * <p>
  6.      * 根据 entity 条件，查询全部记录
  7.      * </p>
  8.      *
  9.      * @param wrapper 实体对象封装操作类（可以为 null）
  10.      * @return List<T>
  11.      */
  12.     List<T> selectList(@Param("ew") Wrapper<T> wrapper);
  15. }

批量查

  2. public interface IService<T> {
  5.     /**
  6.      * <p>
  7.      * 查询（根据ID 批量查询）
  8.      * </p>
  9.      *
  10.      * @param idList 主键ID列表
  11.      * @return List<T>
  12.      */
  13.     List<T> selectBatchIds(Collection<? extends Serializable> idList);
  16. }

单表：补充计数方法

Plus计数可以不用分组

  1.     @Override
  2.     public MsgTaskListResponse getTaskCountsGroupByMsgType(){
  3.         MsgTaskListResponse msgTaskListResponse = new MsgTaskListResponse();
  4.         //平台消息计数
  5.         Integer msgTaskPlatformCounts = msgTaskMapper.selectCount(new EntityWrapper<MsgTask>().eq(MsgTask.CONST_MSG_TYPE, MsgTask.CONST_MSGTYPE_PLATFORM));
  6.         msgTaskListResponse.setPlatformCounts(msgTaskPlatformCounts);
  7.         //企业消息计数
  8.         Integer msgTaskEntCounts = msgTaskMapper.selectCount(new EntityWrapper<MsgTask>().eq(MsgTask.CONST_MSG_TYPE, MsgTask.CONST_MSGTYPE_ENT));
  9.         msgTaskListResponse.setEntCounts(msgTaskEntCounts);
  10.         // 所有消息计数
  11.         Integer sumCounts = msgTaskMapper.selectCount(new EntityWrapper<>());
  12.         msgTaskListResponse.setSumCounts(sumCounts);
  13.         return msgTaskListResponse;
  14.     }

单表：条件构造器Wrapper

情况说明：<mybatisplus-starter.version>2.1.9</mybatisplus-starter.version>

  • Wrapper是EntityWrapper的父类
  • 用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件
  • 注意: entity 生成的 where 条件与 使用各个 api 生成的 where 条件没有任何关联行为 ```java /**
    • Copyright (c) 2011-2014, hubin (jobob@qq.com).

    • Licensed under the Apache License, Version 2.0 (the “License”); you may not
    • use this file except in compliance with the License. You may obtain a copy of
    • the License at

    • http://www.apache.org/licenses/LICENSE-2.0

    • Unless required by applicable law or agreed to in writing, software
    • distributed under the License is distributed on an “AS IS” BASIS, WITHOUT
    • WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
    • License for the specific language governing permissions and limitations under
    • the License. */ package com.baomidou.mybatisplus.mapper;

import com.baomidou.mybatisplus.toolkit.StringUtils;

/**

  • Entity 对象封装操作类，定义T-SQL语法

  • *
  • @author hubin , yanghu , Dyang , Caratacus

  • @Date 2016-11-7 */ @SuppressWarnings(“serial”) public class EntityWrapper extends Wrapper {

    /**

    • 数据库表映射实体类 */ protected T entity = null;

      public EntityWrapper() { / 注意，传入查询参数 / }

      public EntityWrapper(T entity) { this.entity = entity; }

      public EntityWrapper(T entity, String sqlSelect) { this.entity = entity; this.sqlSelect = sqlSelect; }

      @Override public T getEntity() { return entity; }

      public void setEntity(T entity) { this.entity = entity; }

      /**

    • SQL 片段 / @Override public String getSqlSegment() { /

      • 无条件 */ String sqlWhere = sql.toString(); if (StringUtils.isEmpty(sqlWhere)) { return null; }

        /*

      • 根据当前实体判断是否需要将WHERE替换成 AND 增加实体不为空但所有属性为空的情况 */ return isWhere != null ? (isWhere ? sqlWhere : sqlWhere.replaceFirst(“WHERE”, AND_OR)) : sqlWhere.replaceFirst(“WHERE”, AND_OR); }

}

  1. 使用示例：
  2. ```java
  3.  Date date = new Date();
  4.  List<MsgTask> msgTasks = msgTaskMapper.selectList(new EntityWrapper<MsgTask>().lt(MsgTask.CONST_SEND_TIME,date).eq(MsgLog.CONST_SEND_STATE,MsgLog.SEND_STATUS_FAILED));

使用示例：if-test
Wrapper的第一个入参boolean condition表示该条件是否加入最后生成的sql中，类似于MybatisXML的if-test标签，例如：

  1.     @Override
  2.     public List<MsgLogConditionResponse> msgLogs(MsgLogConditionRequest msgLogConditionRequest) {
  3.         Page<MsgLog> page = new Page<>();
  4.         if (msgLogConditionRequest.getSize() != null) {
  5.             page.setSize(msgLogConditionRequest.getSize());
  6.         }
  7.         if (msgLogConditionRequest.getCurrent() != null) {
  8.             page.setCurrent(msgLogConditionRequest.getCurrent());
  9.         }
  10.         Boolean unRead = msgLogConditionRequest.getUnRead();
  11.         String sceneType = msgLogConditionRequest.getSceneType();
  12.         //分页列表SQL,if-test控制是否执行过滤
  13.         Page<MsgLog> pageResult = selectPage(page, new EntityWrapper<MsgLog>()
  14.                 .eq(unRead, MsgLog.CONST_MSG_STATE, MsgLog.HAVED_NO_READ)
  15.                 .eq(StringUtils.isNotBlank(sceneType), MsgLog.CONST_SCENE_TYPE, sceneType));
  18.         List<MsgLog> msgLogs = pageResult.getRecords();
  19.         ArrayList<MsgLogConditionResponse> responses = new ArrayList<>();
  20.         for (MsgLog msgLog : msgLogs) {
  21.             MsgLogConditionResponse msgLogConditionResponse = new MsgLogConditionResponse();
  22.             msgLogConditionResponse.setMsgContent(msgLog.getMsgContent());
  23.             msgLogConditionResponse.setReceiveTime(msgLog.getReceiveTime());
  24.             msgLogConditionResponse.setSceneType(msgLog.getSceneType());
  25.             msgLogConditionResponse.setMsgStatus(msgLog.getMsgState());
  26.             responses.add(msgLogConditionResponse);
  27.         }
  28.         return responses;
  29.     }

警告:

  • wrapper 很重，不支持以及不赞成在 RPC 调用中把 Wrapper 进行传输
  • 传输 wrapper 可以类比为你的 controller 用 map 接收值(开发一时爽,维护火葬场)
  • 正确的 RPC 调用姿势是写一个 DTO 进行传输,被调用方再根据 DTO 执行相应的操作
  • 我们拒绝接受任何关于 RPC 传输 Wrapper 报错相关的 issue 甚至 pr

#allEq

  1. allEq(Map<R, V> params)
  2. allEq(Map<R, V> params, boolean null2IsNull)
  3. allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
  • 全部eq(或个别isNull)个别参数说明:
    params : key为数据库字段名,value为字段值
    null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的
  • 例1: allEq({id:1,name:"老王",age:null})—->id = 1 and name = '老王' and age is null
  • 例2: allEq({id:1,name:"老王",age:null}, false)—->id = 1 and name = '老王'
  1. allEq(BiPredicate<R, V> filter, Map<R, V> params)
  2. allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
  3. allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)

个别参数说明:
filter : 过滤函数,是否允许字段传入比对条件中
params 与 null2IsNull : 同上

  • 例1: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null})—->name = '老王' and age is null
  • 例2: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null}, false)—->name = '老王'

    #eq

  1. eq(R column, Object val)
  2. eq(boolean condition, R column, Object val)
  • 等于 =
  • 例: eq("name", "老王")—->name = '老王'

    #ne

  1. ne(R column, Object val)
  2. ne(boolean condition, R column, Object val)
  • 不等于 <>
  • 例: ne("name", "老王")—->name <> '老王'

    #gt

  1. gt(R column, Object val)
  2. gt(boolean condition, R column, Object val)
  • 大于 >
  • 例: gt("age", 18)—->age > 18

    #ge

  1. ge(R column, Object val)
  2. ge(boolean condition, R column, Object val)
  • 大于等于 >=
  • 例: ge("age", 18)—->age >= 18

    #lt

  1. lt(R column, Object val)
  2. lt(boolean condition, R column, Object val)
  • 小于 <
  • 例: lt("age", 18)—->age < 18

    #le

  1. le(R column, Object val)
  2. le(boolean condition, R column, Object val)
  • 小于等于 <=
  • 例: le("age", 18)—->age <= 18

    #between

  1. between(R column, Object val1, Object val2)
  2. between(boolean condition, R column, Object val1, Object val2)
  • BETWEEN 值1 AND 值2
  • 例: between("age", 18, 30)—->age between 18 and 30

    #notBetween

  1. notBetween(R column, Object val1, Object val2)
  2. notBetween(boolean condition, R column, Object val1, Object val2)
  • NOT BETWEEN 值1 AND 值2
  • 例: notBetween("age", 18, 30)—->age not between 18 and 30

    #like

  1. like(R column, Object val)
  2. like(boolean condition, R column, Object val)
  • LIKE ‘%值%’
  • 例: like("name", "王")—->name like '%王%'

    #notLike

  1. notLike(R column, Object val)
  2. notLike(boolean condition, R column, Object val)
  • NOT LIKE ‘%值%’
  • 例: notLike("name", "王")—->name not like '%王%'

    #likeLeft

  1. likeLeft(R column, Object val)
  2. likeLeft(boolean condition, R column, Object val)
  • LIKE ‘%值’
  • 例: likeLeft("name", "王")—->name like '%王'

    #likeRight

  1. likeRight(R column, Object val)
  2. likeRight(boolean condition, R column, Object val)
  • LIKE ‘值%’
  • 例: likeRight("name", "王")—->name like '王%'

    #isNull

  1. isNull(R column)
  2. isNull(boolean condition, R column)
  • 字段 IS NULL
  • 例: isNull("name")—->name is null

    #isNotNull

  1. isNotNull(R column)
  2. isNotNull(boolean condition, R column)
  • 字段 IS NOT NULL
  • 例: isNotNull("name")—->name is not null

    #in

  1. in(R column, Collection<?> value)
  2. in(boolean condition, R column, Collection<?> value)
  • 字段 IN (value.get(0), value.get(1), …)
  • 例: in("age",{1,2,3})—->age in (1,2,3)
  1. in(R column, Object... values)
  2. in(boolean condition, R column, Object... values)
  • 字段 IN (v0, v1, …)
  • 例: in("age", 1, 2, 3)—->age in (1,2,3)

    #notIn

  1. notIn(R column, Collection<?> value)
  2. notIn(boolean condition, R column, Collection<?> value)
  • 字段 NOT IN (value.get(0), value.get(1), …)
  • 例: notIn("age",{1,2,3})—->age not in (1,2,3)
  1. notIn(R column, Object... values)
  2. notIn(boolean condition, R column, Object... values)
  • 字段 NOT IN (v0, v1, …)
  • 例: notIn("age", 1, 2, 3)—->age not in (1,2,3)

    #inSql

  1. inSql(R column, String inValue)
  2. inSql(boolean condition, R column, String inValue)
  • 字段 IN ( sql语句 )
  • 例: inSql("age", "1,2,3,4,5,6")—->age in (1,2,3,4,5,6)
  • 例: inSql("id", "select id from table where id < 3")—->id in (select id from table where id < 3)

    #notInSql

  1. notInSql(R column, String inValue)
  2. notInSql(boolean condition, R column, String inValue)
  • 字段 NOT IN ( sql语句 )
  • 例: notInSql("age", "1,2,3,4,5,6")—->age not in (1,2,3,4,5,6)
  • 例: notInSql("id", "select id from table where id < 3")—->id not in (select id from table where id < 3)

    #groupBy

  1. groupBy(R... columns)
  2. groupBy(boolean condition, R... columns)
  • 分组：GROUP BY 字段, …
  • 例: groupBy("id", "name")—->group by id,name

与SQL分组同理：满足“SELECT子句中的列名必须为分组列或列函数”

  1.     @Override
  2.     public MsgTaskSendLogConditionResponse getSendDetailGroup(MsgTaskSendLogConditionRequest msgTaskSendLogConditionRequest) {
  3.         MsgTaskSendLogConditionResponse msgTaskSendLogConditionResponse = new MsgTaskSendLogConditionResponse();
  4.         Integer msgTaskId = msgTaskSendLogConditionRequest.getMsgTaskId();
  7.         //查询
  8.         Page page = new Page<>((msgTaskSendLogConditionRequest.getCurrent() == null ? 1 : msgTaskSendLogConditionRequest.getCurrent()), (msgTaskSendLogConditionRequest.getSize() == null ? 10 : msgTaskSendLogConditionRequest.getSize()));
  10.         msgTaskMapper.getSendDetailPage(page, msgTaskSendLogConditionRequest);
  11.         msgTaskSendLogConditionResponse.setCountsOfSended(page.getTotal());
  13.         //logs表单表，countsOfSuccess;分组
  14.         List<MsgLog> msgLogSuccess = msgLogMapper.selectList(new EntityWrapper<MsgLog>().setSqlSelect(MsgLog.CONST_SEND_STATE + ",count(*) AS counts_of_success")
  15.                 .eq(MsgLog.CONST_SEND_STATE, MsgLog.SEND_STATUS_SUCCESS)
  16.                 .eq(MsgLog.CONST_STATUS, MsgLog.DELETE_STATUS_VALIDATED)
  17.                 .groupBy(MsgLog.CONST_SEND_STATE));
  18.         msgTaskSendLogConditionResponse.setCountsOfSuccess(msgLogSuccess.size() == 0 ? 0 : msgLogSuccess.get(0).getCountsOfSuccess());
  19.         //logs表单表，countsOfFailed;分组
  20.         List<MsgLog> msgLogFailed = msgLogMapper.selectList(new EntityWrapper<MsgLog>().setSqlSelect(MsgLog.CONST_SEND_STATE + ",count(*) AS counts_of_failed")
  21.                 .eq(MsgLog.CONST_SEND_STATE, MsgLog.SEND_STATUS_FAILED)
  22.                 .eq(MsgLog.CONST_STATUS, MsgLog.DELETE_STATUS_VALIDATED)
  23.                 .groupBy(MsgLog.CONST_SEND_STATE));
  24.         msgTaskSendLogConditionResponse.setCountsOfFailed(msgLogFailed.size() == 0 ? 0 : msgLogFailed.get(0).getCountsOfFailed());
  25.         //logs表单表，countsOfReaded;分组
  26.         List<MsgLog> msgLogReaded = msgLogMapper.selectList(new EntityWrapper<MsgLog>().setSqlSelect(MsgLog.CONST_SEND_STATE + ",count(*) AS counts_of_readed")
  27.                 .eq(MsgLog.CONST_MSG_STATE, MsgLog.HAVED_READ)
  28.                 .eq(MsgLog.CONST_STATUS, MsgLog.DELETE_STATUS_VALIDATED)
  29.                 .groupBy(MsgLog.CONST_SEND_STATE));
  30.         msgTaskSendLogConditionResponse.setCountsOfReaded(msgLogReaded.size() == 0 ? 0 : msgLogReaded.get(0).getCountsOfReaded());
  31.         return msgTaskSendLogConditionResponse;
  33.     }

其中setSqlSelect的SQL里的counts_of_success和counts_of_failed和counts_of_readed：注意是下划线

  1. //logs表单表，countsOfSuccess;分组
  2.         List<MsgLog> msgLogSuccess = msgLogMapper.selectList(new EntityWrapper<MsgLog>().setSqlSelect(MsgLog.CONST_SEND_STATE + ",count(*) AS counts_of_success")
  3.                 .eq(MsgLog.CONST_SEND_STATE, MsgLog.SEND_STATUS_SUCCESS)
  4.                 .eq(MsgLog.CONST_STATUS, MsgLog.DELETE_STATUS_VALIDATED)
  5.                 .groupBy(MsgLog.CONST_SEND_STATE));
  6.         msgTaskSendLogConditionResponse.setCountsOfSuccess(msgLogSuccess.size() == 0 ? 0 : msgLogSuccess.get(0).getCountsOfSuccess());

需要在Entity配置额外的统计字段counts_of_success，counts_of_failed，counts_of_readed

  1.     @TableField(exist = false)
  2.     private static final long serialVersionUID = 1L;
  4.     @TableField(exist = false)
  5.     public Integer countsOfSuccess;
  6.     @TableField(exist = false)
  7.     public Integer countsOfFailed;
  8.     @TableField(exist = false)
  9.     public Integer countsOfReaded;

#orderByAsc

  1. orderByAsc(R... columns)
  2. orderByAsc(boolean condition, R... columns)
  • 排序：ORDER BY 字段, … ASC
  • 例: orderByAsc("id", "name")—->order by id ASC,name ASC

    #orderByDesc

  1. orderByDesc(R... columns)
  2. orderByDesc(boolean condition, R... columns)
  • 排序：ORDER BY 字段, … DESC

  • 例: orderByDesc("id", "name")—->order by id DESC,name DESC

    #orderBy

    1. orderBy(boolean condition, boolean isAsc, R... columns)

  • 排序：ORDER BY 字段, …

  • 例: orderBy(true, true, "id", "name")—->order by id ASC,name ASC

    #having

  1. having(String sqlHaving, Object... params)
  2. having(boolean condition, String sqlHaving, Object... params)
  • HAVING ( sql语句 )
  • 例: having("sum(age) > 10")—->having sum(age) > 10
  • 例: having("sum(age) > {0}", 11)—->having sum(age) > 11

    #func

  1. func(Consumer<Children> consumer)
  2. func(boolean condition, Consumer<Children> consumer)
  • func 方法(主要方便在出现if…else下调用不同方法能不断链)
  • 例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})

    #or

  1. or()
  2. or(boolean condition)
  • 拼接 OR注意事项:
    主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
  • 例: eq("id",1).or().eq("name","老王")—->id = 1 or name = '老王'
  1. or(Consumer<Param> consumer)
  2. or(boolean condition, Consumer<Param> consumer)
  • OR 嵌套
  • 例: or(i -> i.eq("name", "李白").ne("status", "活着"))—->or (name = '李白' and status <> '活着')

    #and

  1. and(Consumer<Param> consumer)
  2. and(boolean condition, Consumer<Param> consumer)
  • AND 嵌套
  • 例: and(i -> i.eq("name", "李白").ne("status", "活着"))—->and (name = '李白' and status <> '活着')

    #nested

  1. nested(Consumer<Param> consumer)
  2. nested(boolean condition, Consumer<Param> consumer)
  • 正常嵌套 不带 AND 或者 OR
  • 例: nested(i -> i.eq("name", "李白").ne("status", "活着"))—->(name = '李白' and status <> '活着')

    #apply

  1. apply(String applySql, Object... params)
  2. apply(boolean condition, String applySql, Object... params)
  • 拼接 sql注意事项:
    该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!
  • 例: apply("id = 1")—->id = 1
  • 例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")—->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
  • 例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")—->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

    #last

  1. last(String lastSql)
  2. last(boolean condition, String lastSql)
  • 无视优化规则直接拼接到 sql 的最后
  • 注意事项:只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用
  • 例: last("limit 1")

    #exists

  1. exists(String existsSql)
  2. exists(boolean condition, String existsSql)
  • 拼接 EXISTS ( sql语句 )
  • 例: exists("select id from table where age = 1")—->exists (select id from table where age = 1)

    #notExists

  1. notExists(String notExistsSql)
  2. notExists(boolean condition, String notExistsSql)
  • 拼接 NOT EXISTS ( sql语句 )
  • 例: notExists("select id from table where age = 1")—->not exists (select id from table where age = 1)