Java-Mybatis Plus 语法总结【持续更新】

2020年04月15日 12:43 · 阅读(2559) ·

开发环境

名称 版本
操作系统 Windows 10 X64
JDK JDK1.8（jdk-8u151-windows-x64）
IntelliJ IDEA IntelliJ IDEA 2018.3
Maven Maven 3.6.0
com.baomidou mybatis-plus-boot-starter

官网

项目 官网
Mybatis https://blog.mybatis.org/
http://www.mybatis.cn/
Mybatis Plus https://mybatis.plus/

Lambda 表达式常用方法

  1. eq == equal  等于
  2. ne == not equal 不等于
  3. gt == greater than 大于
  4. lt == less than 小于
  5. ge ==  greater than or equal 大于等于
  6. le == less than or equal 小于等于
  7. in == in 包含（数组）
  8. isNull == 等于null
  9. isNotNull == 不等于null
  10. orderByDesc == 倒序排序
  11. orderByAsc == 升序排序
  12. or == 或者
  13. and == 并且
  14. between == 在2个条件之间(包括边界值)
  15. like == 模糊查询
  16. clear == 清除
  17. apply == 拼接sql
  18. lambda == 使用lambda表达式
  19. exists == 临时表

添加数据

这个 insert 是根据对象赋值的属性来生成 sql 语句的。
比如 payApplyHead 对象有 20 个属性，你只赋值了 2 个，最终生成的 insert 语句，只会 insert 两个字段。

  1. public boolean add(PayApplyHead payApplyHead){
  2.     //成功 result = 1，即受影响的行数
  3.     int result = baseMapper.insert(payApplyHead);;
  5.     return result > 0;
  6. }

添加数据返回 Id

application.yml

  1. #mybatis
  2. mybatis-plus:
  3.   typeEnumsPackage:
  4.   global-config:
  5.     #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
  6.     id-type: 0
  7.     #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
  8.     field-strategy: 2

字段

  1. /**
  2.  * 主键ID
  3.  */
  4. @ApiModelProperty(value = "主键")
  5. @TableId(type = IdType.AUTO)
  6. private Long id;

代码

这里 insert param 之后，会自动把 ID 赋值。

  1. /**
  2.  * 添加数据
  3.  * @param param 数据
  4.  * @return      添加成功的数据
  5.  */
  6. @Override
  7. public CostPayPlan add(CostPayPlan param){
  8.     baseMapper.insert(param);
  9.     return param;
  10. }

推荐的批量插入 Batch Insert Support

Mybatis批量插入的几种方式

  1.  try(SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  2.         SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
  3.         List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
  5.         BatchInsert<SimpleTableRecord> batchInsert = insert(records)
  6.                 .into(simpleTable)
  7.                 .map(id).toProperty("id")
  8.                 .map(firstName).toProperty("firstName")
  9.                 .map(lastName).toProperty("lastName")
  10.                 .map(birthDate).toProperty("birthDate")
  11.                 .map(employed).toProperty("employed")
  12.                 .map(occupation).toProperty("occupation")
  13.                 .build()
  14.                 .render(RenderingStrategies.MYBATIS3);
  16.         batchInsert.insertStatements().forEach(mapper::insert);
  18.         session.commit();
  19.     }

修改数据

指定条件，指定列

  1. public boolean getTest() {
  2.     LambdaUpdateWrapper<SmallflowAccountLicense> wrapper = new LambdaUpdateWrapper<>();
  3.     wrapper.eq(SmallflowAccountLicense::getIsDelete, Contents.NOT_DELETE);
  4.     wrapper.eq(SmallflowAccountLicense::getTenantId, Contents.TENCENT_ID);
  5.     List<Long> listIds = Arrays.asList(1000000L,2000000L,4000000L);
  6.     wrapper.in(SmallflowAccountLicense::getId, listIds);
  7.     wrapper.set(SmallflowAccountLicense::getCreateBy,"luoma");
  9.     return update(wrapper);
  10. }
  • 实际效果
  1. UPDATE smallflow_account_license SET create_by='luoma'
  2. WHERE (is_delete = 0 AND tenant_id = '1' AND id IN (1000000,2000000,4000000));

update 方法只修改赋值的列

application.yml

  1. #mybatis
  2. mybatis-plus:
  3.   typeEnumsPackage:
  4.   global-config:
  5.     #主键类型  0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
  6.     id-type: 0
  7.     #字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
  8.     field-strategy: 2

代码

只要字段赋值为 null，就不会去修改对应字段的值

  1. /**
  2.  * 修改数据
  3.  * @param param 数据
  4.  * @return      修改成功返回 true
  5.  */
  6. @Override
  7. public CostPayPlan update(CostPayPlan param){
  8.     updateById(param);
  9.     return param;
  10. }

通过 xml

FinancingBankMapper.xml

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  3.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  4. <mapper namespace="com.tencent.finance.mapper.FinancingBankMapper">
  5.     <update id="deleteAll">
  6.         update financing_bank
  7.         set is_delete = 1,update_time=now(), update_by = #{userName,jdbcType=VARCHAR}
  8.         where is_delete = 0
  9.     </update>
  10. </mapper>

FinancingBankMapper

  1. @Repository
  2. public interface FinancingBankMapper extends BaseMapper<FinancingBank> {
  3.     /**
  4.      * 删除所有数据
  5.      * @param userName 操作用户英文名
  6.      * @return 返回修改成功的数据条数
  7.      */
  8.     int deleteAll(@Param("userName") String userName);
  9. }

查询数据

查询指定列-Mapper.xml

  1.   <sql id="Base_Column_List">
  2.     id, operate_info_id, apply_no, pay_type, pay_id, pay_way_code, pay_way_name, apply_reason, 
  3.     payment_postscript, amount, currency_code, contract_no, surplus_principal_amount, 
  4.     our_side_ou_code, our_side_ou_name, product_name_code, financing_product_no, financing_sign_contract_bank, 
  5.     financing_bank_of_deposit, financing_bank_account, financing_bank_swift_code, financing_bank_code, 
  6.     financing_bank_branch_code, transform_bank_country, transform_bank_name, transform_bank_swift_code, 
  7.     transform_bank_liquidation, applicant, application_date, flow_code, flow_version, 
  8.     flow_instance_id, approval_status_code, approval_status_name, approval_current_node, 
  9.     approval_pass_date, task_owner_staff_id, task_owner_english_name, task_owner_full_name, 
  10.     pay_status_code, pay_status_name, pay_date, push_direct_connection, is_temp_date, 
  11.     tenant_id, create_by, create_time, update_by, update_time, is_delete
  12.   </sql>
  13.   <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
  14.     select 
  15.     <include refid="Base_Column_List" />
  16.     from financing_pay_apply_head
  17.     where id = #{id,jdbcType=BIGINT}
  18.   </select>

查询单条数据-根据列名称

  1. QueryWrapper<Contract> wrapper = new QueryWrapper<>();
  2. wrapper.eq("is_delete",Contents.IS_DELETE);
  3. wrapper.eq("tax_account_id",taxAccountId);
  4. //避免上面的条件能查出多条数据导致 getOne 报错
  5. wrapper.last("limit 1");
  6. Contract contract = contractService.getOne(wrapper);

查询单条数据-根据 lambda 表达式

  1. Order order = orderService.getOne(Wrappers.<Order>lambdaQuery()
  2.         .eq(Order::getOrderNo,taxAccount.getOrderId())
  3.         .eq(Order::getTaxAccountId,taxAccount.getTaxAccountId())
  4.         .eq(Order::getIsDelete,Contents.IS_DELETE));
  1. LambdaQueryWrapper<PayApplyApproval> queryWrapper = new LambdaQueryWrapper<>();
  2. queryWrapper.eq(PayApplyApproval::getApplyNo,applyNo);
  3. queryWrapper.eq(PayApplyApproval::getFlowTaskId,flowTaskId);
  4. queryWrapper.eq(PayApplyApproval::getTenantId,Contents.TENCENT_ID);
  5. queryWrapper.eq(PayApplyApproval::getIsDelete,Contents.NOT_DELETE);
  7. return  baseMapper.selectOne(queryWrapper);

获取指定列-通过 select

  1. ForeignLimit limit = baseMapper.selectOne(Wrappers.<ForeignLimit>lambdaQuery().select(
  2.                 ForeignLimit::getBgCode, ForeignLimit::getBgName,
  3.                 ForeignLimit::getOuId, ForeignLimit::getOuName,
  4.                 ForeignLimit::getType, ForeignLimit::getUserId,
  5.                 ForeignLimit::getUserName
  6.         ).eq(ForeignLimit::getUserId, userId));
  1. public List<AccountBankInfo> getAccountBankList(CommonDto param){
  2.     LambdaQueryWrapper<AccountBankInfo> wrapper = new LambdaQueryWrapper<>();
  3.     wrapper.eq(AccountBankInfo::getIsDelete, Contents.NOT_DELETE);
  4.     //wrapper.eq(AccountBankInfo::getTenantId,Contents.TENCENT_ID);
  6.     //因为这个表字段老是变动，我这里只获取我需要的部分
  7.     wrapper.select(AccountBankInfo::getBankAccountId
  8.                   ,AccountBankInfo::getBankAccountName
  9.                   ,AccountBankInfo::getBankAccountNum
  10.                   ,AccountBankInfo::getBic
  11.                   ,AccountBankInfo::getCurrencyCode
  12.             );
  14.     if(CommUtil.isNotNull(param.getNo())){
  15.         wrapper.like(AccountBankInfo::getBankAccountNum,param.getNo());
  16.     }
  17.     if(CommUtil.isNotNull(param.getName())){
  18.         wrapper.like(AccountBankInfo::getBankAccountName, param.getName());
  19.     }
  21.     return baseMapper.selectList(wrapper);
  22. }
  23. }

效果

  1. SELECT bank_account_id,bank_account_name,bank_account_num,bic,currency_code
  2.  FROM account_bank_info
  3.  WHERE (is_delete = ? AND bank_account_name LIKE ?);

获取指定列-通过 map 映射并赋值

  1. /**
  2.  * 获取多条数据
  3.  *
  4.  * @param param 查询参数
  5.  * @return 数据列表
  6.  */
  7. @Override
  8. public List<BaseSysOuInfo> getList(OUDTO param) {
  9.     LambdaQueryWrapper<BaseSysOuInfo> wrapper = getLambdaQueryWrapper(param);
  10.     return baseSysOuInfoService.getBaseMapper().selectList(wrapper);
  11. }
  13. /**
  14.  * 获取公共 LambdaQuery 查询参数
  15.  *
  16.  * @param param 查询参数
  17.  * @return LambdaQueryWrapper
  18.  */
  19. public LambdaQueryWrapper<BaseSysOuInfo> getLambdaQueryWrapper(OUDTO param) {
  20.     LambdaQueryWrapper<BaseSysOuInfo> wrapper = new LambdaQueryWrapper<>();
  21.     //wrapper.eq(BaseSysOuInfo::getTenantId, Contents.TENCENT_ID);//这个表没有启动这个字段
  22.     wrapper.eq(BaseSysOuInfo::getIsDelete, Contents.NOT_DELETE);
  23.     wrapper.eq(BaseSysOuInfo::getEnabledFlag, Contents.Y);
  24.     if (CommUtil.isNotNull(param.getOuId())) {
  25.         wrapper.eq(BaseSysOuInfo::getOrgId, param.getOuId());
  26.     }
  27.     if (CommUtil.isNotNull(param.getOuCode())) {
  28.         wrapper.eq(BaseSysOuInfo::getOuCode, param.getOuCode());
  29.     }
  30.     if (CommUtil.isNotNull(param.getOuName())) {
  31.         wrapper.eq(BaseSysOuInfo::getOuShortNameZhs, param.getOuName());
  32.     }
  34.     return wrapper;
  35. }
  37. /**
  38.  * 获取 OU 数据
  39.  *
  40.  * @return OU 列表数据
  41.  */
  42. @Override
  43. public List<OuInfoVO> getOuInfo() {
  44.     OUDTO param = new OUDTO();
  46.     List<OuInfoVO> listData = getList(param).stream()
  47.             .filter(t -> t.getIsDelete().equals(Contents.NOT_DELETE)
  48.                       && Contents.Y.equals(t.getEnabledFlag()))
  49.             .sorted(Comparator.comparingInt(BaseSysOuInfo::getOrgId))
  50.             .map(item ->
  51.                     new OuInfoVO(item.getOuShortNameZhs(),
  52.                                  String.valueOf(item.getOuCode()),
  53.                                  item.getIsDomesticInvest()))
  54.             .collect(Collectors.toList());
  56.     listData.forEach(p -> {
  57.         if (null != p.getIsDomesticInvest()) {
  58.             if (p.getIsDomesticInvest().equals("Y")) {
  59.                 p.setIsDomesticInvest("境内");
  60.             } else if (p.getIsDomesticInvest().equals("N")) {
  61.                 p.setIsDomesticInvest("境外");
  62.             }
  63.         }
  64.     });
  66.     return listData;
  67. }

获取指定列-distinct,selectObjs

  1. QueryWrapper<UserRole> wrapper = new QueryWrapper<>();
  2. wrapper.select("distinct user_id,role_id,authentication");
  3. wrapper.eq("is_delete", 1);
  4. wrapper.eq("user_id", Long.parseLong(userId));
  5. wrapper.orderByAsc("role_id");
  6. List<UserRole> list = baseMapper.selectList(wrapper);

或者

  1. /**
  2.  * 获取对应用户权限的 OU Code 集合
  3.  * @param userId 用户 Id
  4.  * @return 对应用户权限的 OU Code 集合
  5.  */
  6. public List<String> getOUCodes(String userId){
  7.     if(StringUtils.isEmpty(userId)){
  8.         //获取当前登录用户
  9.         UserInfoVO userInfoVO = LocalProvider.getUser();
  10.         userId = userInfoVO.getCreateBy();
  11.     }
  13.     QueryWrapper<ForeignLimit> wrapper = new QueryWrapper<>();
  14.     wrapper.select("distinct ou_code");
  15.     wrapper.eq("is_delete",Contents.IS_DELETE);
  16.     wrapper.eq("user_id",userId);
  18.     List<String> list = (List<String>)(List)baseMapper.selectObjs(wrapper);
  20.     //List<Object> listObjs = baseMapper.selectObjs(wrapper);
  21.     //List<String> list = null;
  22.     //if(CommonUtil.isNotNull(listObjs)) {
  23.     //    listResult = listObjs.stream().map(String::valueOf).collect(Collectors.toList());
  24.     //}
  26.     return list;
  27. }

获取数据条数

  1. Integer count = detailService.getBaseMapper()
  2.                  .selectCount(Wrappers.<PayDetail>lambdaQuery()
  3.                      .eq(PayDetail::getPayOrderNo, data.getPayOrderNo())
  4.                      .eq(PayDetail::getIsDelete, 1));

获取 List 数据

  1. List<TaxAccountDes> listTaxAccountDes = baseMapper.selectList(Wrappers.<TaxAccountDes>lambdaQuery()
  2.     .eq(TaxAccountDes::getTaxAccountId,taxAccount.getTaxAccountId())
  3.     .eq(TaxAccountDes::getIsDelete,Contents.IS_DELETE));

获取分页数据-使用 sql

Impl 方法

  1. /**
  2.  * 获取实物进口供应商分页数据（根据用户对应 OU 权限）
  3.  * @param param 供应商信息
  4.  * @return 实物进口供应商列表
  5.  */
  6. @Override
  7. public ResponseVO<Page<PhysicalImport>> getPageList(TaxExemptionData param){
  8.     //分页条件
  9.     Page<PhysicalImport> page = new Page<>();
  10.     page.setSize(param.getSize());
  11.     page.setCurrent(param.getCurrent());
  14.     //当前用户存在角色“购汇业务员-子公司”-限制付款方主体为其负责的主体
  15.     String userId = param.getUserId();
  16.     param.setIsPaymentSub(userRoleService.userIsPaymentSub(userId));
  17.     if(param.getIsPaymentSub()){
  18.         List<String> ouIds = foreignLimitService.getOUCodes(userId);
  19.         if(CollectionUtil.isNotEmpty(ouIds)){
  20.             param.setListParamStr(ouIds);
  21.         }
  22.     }
  25.     IPage<PhysicalImport> ipageData = baseMapper.getPageList(page,param);
  27.     Page<PhysicalImport> pageData = toPage(ipageData);
  29.     ResponseVO<Page<PhysicalImport>> responseVO = new ResponseVO<>(ResponseCode.OK);
  30.     responseVO.setData(pageData);
  32.     return responseVO;
  33. }
  35. /**
  36.  * 公共类- IPage 转换为 Page
  37.  *
  38.  * @param ipageData IPage List
  39.  * @param <T>       类型
  40.  * @return PageList
  41.  */
  42. public static <T> Page<T> toPage(IPage<T> ipageData) {
  43.     //IPage 转换为 Page
  44.     Page<T> pageData = new Page<>();
  45.     pageData.setRecords(ipageData.getRecords());
  46.     pageData.setCurrent(ipageData.getCurrent());
  47.     pageData.setSize(ipageData.getSize());
  48.     pageData.setTotal(ipageData.getTotal());
  50.     return pageData;
  51. }

baseMapper

  1. @Repository
  2. public interface PhysicalImportRepository extends BaseMapper<PhysicalImport> {
  4.     /**
  5.      * 获取实物进口供应商分页数据（根据用户对应 OU 权限）
  6.      * @param param 供应商信息
  7.      * @return 实物进口供应商数据
  8.      */
  9.     IPage<PhysicalImport> getPageList(Page<PhysicalImport> page, @Param("param")TaxExemptionData param);
  10. }

Mapper.xml

  1. <select id="getPageList"
  2.         resultType="com.foreign.payment.common.model.PhysicalImport"
  3.         parameterType="com.foreign.payment.common.data.TaxExemptionData">
  4.     select *
  5.     from t_fpts_physical_import
  6.     <where>
  7.         <if test="param.contractId != null and param.contractId.trim() != ''">
  8.             AND contract_no  like "%"#{param.contractId}"%"
  9.         </if>
  10.         <if test="param.supplierName != null and param.supplierName.trim() != ''">
  11.             AND  supplier_name like "%"#{param.supplierName}"%"
  12.         </if>
  13.         <if test="param.isPaymentSub">
  14.             AND (ou_id IN
  15.             <trim suffixOverrides=" OR ou_id IN()">
  16.                 <foreach collection="param.listParamStr" item="ouCode" index="index" open="(" close=")">
  17.                     <if test="index != 0">
  18.                         <choose>
  19.                             <when test="index % 1000 == 999">) OR ou_id IN(</when>
  20.                             <otherwise>,</otherwise>
  21.                         </choose>
  22.                     </if>
  23.                     #{ouCode}
  24.                 </foreach>
  25.             </trim>
  26.             )
  27.         </if>
  28.         AND is_delete = 1
  29.     </where>
  30.     order by update_time desc
  31. </select>

获取分页数据-使用 LambdaQueryWrapper

分页插件

  1. @Configuration
  2. public class MybatisPlusConfig {
  3.     @Bean
  4.     public MybatisPlusInterceptor mybatisPlusInterceptor() {
  5.         MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
  6.         interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
  7.         return interceptor;
  8.     }
  9. }

BaseVO

  1. @Getter
  2. @Setter
  3. @ToString
  4. @AllArgsConstructor
  5. @NoArgsConstructor
  6. public class BaseVO<T> {
  8.     @ApiModelProperty("总数")
  9.     private Integer total;
  11.     @ApiModelProperty("当前页")
  12.     private Integer current;
  14.     @ApiModelProperty("分页数")
  15.     private Integer pageSize;
  17.     @ApiModelProperty("结果集")
  18.     private List<T> dataList;
  19. }

DTO

  1. /**
  2.  * 分页基础类
  3.  */
  4. @Getter
  5. @Setter
  6. @ToString
  7. public abstract class WeekdayDto {
  8.     @ApiModelProperty("当前页数")
  9.     private Integer current;
  11.     @ApiModelProperty("每页显示条数")
  12.     private Integer pageSize;
  14.     @ApiModelProperty("开始时间戳，精确到毫秒")
  15.     private String startTime;
  17.     @ApiModelProperty("结束时间戳，精确到毫秒")
  18.     private String endTime;
  20.     /**
  21.      * 国家
  22.      */
  23.     private String country;
  25.     /**
  26.      * 当前日期，格式：yyyy-MM-dd
  27.      */
  28.     private String day;
  29. }

分页

  1. /**
  2.  * 获取分页数据
  3.  * @param param 查询参数
  4.  * @return 分页数据
  5.  */
  6. @Override
  7. public BaseVO<Weekday> getPage(WeekdayDto param){
  8.     //必填项判断
  9.     if (CommUtil.isNull(param)) {
  10.         throw new InternalApiException(-1, "WeekdayDto is null!");
  11.     }
  12.     Integer pageSize = param.getPageSize();
  13.     if (CommUtil.isNull(pageSize)) {
  14.         throw new InternalApiException(-1, "pageSize is null!");
  15.     }
  16.     Integer current = param.getCurrent();
  17.     if (CommUtil.isNull(current)) {
  18.         throw new InternalApiException(-1, "current is null!");
  19.     }
  21.     //查询参数
  22.     LambdaQueryWrapper<Weekday> wrapper = new LambdaQueryWrapper<>();
  23.     wrapper.eq(Weekday::getIsDelete, Contents.NOT_DELETE);
  24.     wrapper.eq(Weekday::getTenantId,Contents.TENCENT_ID);
  25.     if(CommUtil.isNotNull(param.getCountry())){
  26.         wrapper.eq(Weekday::getCountry, param.getCountry());
  27.     }
  28.     if(CommUtil.isNotNull(param.getYear())){
  29.         wrapper.eq(Weekday::getYear, param.getYear());
  30.     }
  31.     if(CommUtil.isNotNull(param.getMonth())){
  32.         wrapper.eq(Weekday::getMonth, param.getMonth());
  33.     }
  35.     //分页参数
  36.     Page<Weekday> page = new Page<>(current,pageSize);
  37.     Page<Weekday> pageList = baseMapper.selectPage(page,wrapper);
  39.     //返回参数
  40.     BaseVO<Weekday> baseVO = new BaseVO<>();
  41.     baseVO.setPageSize(pageSize);
  42.     baseVO.setCurrent(current);
  43.     if(CommUtil.isNotNull(pageList)){
  44.         baseVO.setDataList(pageList.getRecords());
  45.         baseVO.setTotal((int)pageList.getTotal());
  46.     }
  48.     return baseVO;
  49. }
  50. }

获取 Map 数据

Mapper.xml

  1. <select id="selectMap" resultType="com.test.UserInfo">
  2. select id,username,nickname
  3. from t_user
  4. </select>

baseMapper

  1. @MapKey("username")
  2. Map<String, UserInfo> selectMap();

selectMaps

  • 表数据
  1. select current_step,count(*) as count, sum(id) as id_sum
  2. from smallflow_account_license
  3. group by current_step
current_step count id_sum
作废 1 53
直属Leader审批 5 308
直属上级审核 3 161
结束 6 355
草稿 4 225
  • 代码
  1. public List<TestVO> getTest() {
  2.     QueryWrapper<SmallflowAccountLicense> wrapper = new QueryWrapper<>();
  3.     wrapper.eq("is_delete", Contents.NOT_DELETE);
  4.     wrapper.eq("tenant_id", Contents.TENCENT_ID);
  6.     wrapper.groupBy("current_step");
  8.     wrapper.select("current_step,count(*) as count, sum(id) as id_sum");
  10.     List<TestVO> listResult = new ArrayList<>();
  11.     List<Map<String, Object>> listMap = baseMapper.selectMaps(wrapper);
  12.     for (Map<String, Object> itemMap : listMap) {
  13.         TestVO item = new TestVO();
  14.         for (String name : itemMap.keySet()) {
  15.             String value = String.valueOf(itemMap.get(name));
  16.             if("current_step".equals(name)) {
  17.                 item.setCurrentStep(value);
  18.             }
  19.             if("count".equals(name)) {
  20.                 item.setCount(Integer.parseInt(value));
  21.             }
  22.             if("id_sum".equals(name)) {
  23.                 item.setIdSum(new BigDecimal(value));
  24.             }
  26.         }
  27.         listResult.add(item);
  28.     }
  30.     return listResult;
  32. }
  • 结果
  1. [
  2.   {
  3.     "currentStep": "作废",
  4.     "count": 1,
  5.     "idSum": 53
  6.   },
  7.   {
  8.     "currentStep": "直属Leader审批",
  9.     "count": 5,
  10.     "idSum": 308
  11.   },
  12.   {
  13.     "currentStep": "直属上级审核",
  14.     "count": 3,
  15.     "idSum": 161
  16.   },
  17.   {
  18.     "currentStep": "结束",
  19.     "count": 6,
  20.     "idSum": 355
  21.   },
  22.   {
  23.     "currentStep": "草稿",
  24.     "count": 4,
  25.     "idSum": 225
  26.   }
  27. ]

where 条件

使用 where 标签取消 where 1=1

  1. <select id="selectGroupByEmployeeNum" resultMap="BaseResultMap" parameterType="com.dao.impl.ZcChatGroup">
  2.     select
  3.         *
  4.     from 
  5.         zc_chat_group 
  6.     <where>
  7.         <if test="id!=null">
  8.             id= #{id} 
  9.         </if>
  10.         <if test="leaderNum!=null">
  11.             and leader_num = #{leaderNum} 
  12.         </if>
  13.         <if test="groupType!=null">
  14.             and group_type = #{groupType} 
  15.         </if>
  16.     </where>
  17.   </select>

这条sql执行时，如果 id 这个参数为 null，则这条语句的执行结果为

  1. select * from zc_chat_group where leader_num = ‘xx’ and group_type = ‘xx’

这个 where 标签会知道如果它包含的标签中有返回值的话，它就会插入一个 where。
此外，如果标签返回的内容是以 AND 或 OR 开头的，则会把它去除掉。

and (.. or ..)

  1. //查询条件
  2. QueryWrapper<TaxExemption> wrapper = new QueryWrapper<>();
  3. wrapper.eq("is_delete", Contents.IS_DELETE);
  4. if(StringUtils.isNotEmpty(param.getSupplierName())){
  5.     wrapper.and(wrapperLike->wrapperLike.like("contract_party",param.getSupplierName()).or().like("chinese_name",param.getSupplierName()));
  6. }
  7. if(StringUtils.isNotEmpty(param.getContractId())){
  8.     wrapper.like("contract_no",param.getContractId());
  9. }

结果

  1. where is_delete = 1 
  2.   and (contract_party like '%name%' or chinese_name like '%name%') 
  3.   and contract_no like '%name%'

in

  1. //根据供应商Id获取台账明细 Id
  2. List<BigDecimal> listDesId = listReceivePaySubject.stream().filter(t->t.getVendorId().equals(itemReceivePaySubject.getVendorId())).map(DesReceivePaySubject::getTaxAccountDesId).collect(Collectors.toList());
  3. if(CollectionUtil.isEmpty(listDesId)){
  4.     continue;
  5. }
  6. //根据台账明细 Id 获取明细信息
  7. List<TaxAccountDes> taxAccountDesList = taxAccountDesService.list(Wrappers.<TaxAccountDes>lambdaQuery()
  8.         .eq(TaxAccountDes::getIsDelete,Contents.IS_DELETE)
  9.         .and(wrapperIn->wrapperIn.in(TaxAccountDes::getTaxAccountDesId,listDesId))
  11. );

结果

  1. WHERE is_delete = 1 AND ( tax_account_des_id IN (20200413163224861617922000005,20200413172202617341775000006) )

in 超过 1000 行导致报错问题解决

  1. <select id="getTransByIds" resultType="com.tencent.oa.fm.etms.receipt.server.entity.RecepitTransBean">
  2.     select t.* from CUX.CUX_CE_RECEPIT_TRANSACTION t 
  3.     <where>
  4.         <if test="condition.trxIds != null and condition.trxIds.size >0">
  5.             t.trx_id IN
  6.             <trim suffixOverrides=" OR t.trx_id IN()">
  7.                 <foreach collection="condition.trxIds" item="trxId" index="index" open="(" close=")">
  8.                     <if test="index != 0">
  9.                         <choose>
  10.                             <when test="index % 1000 == 999">) OR t.trx_id IN(</when>
  11.                             <otherwise>,</otherwise>
  12.                         </choose>
  13.                     </if>
  14.                     #{trxId,jdbcType=DECIMAL}
  15.                 </foreach>
  16.             </trim>
  17.         </if>
  18.     </where>
  19. </select>

字符串

  1. <select id="getSmallCategoryCodeByDemision" resultType="java.lang.String">
  2.     SELECT ffv.attribute2 transaction_code
  3.     FROM apps.fnd_flex_values_vl ffv, apps.fnd_flex_value_sets ffvs
  4.     WHERE ffv.enabled_flag = 'Y'
  5.     AND ffv.flex_value_set_id = ffvs.flex_value_set_id
  6.     AND ffvs.flex_value_set_name = 'CUX_CE_EBANK_TRANSACTION_AUTH'
  7.     and (ffv.description in
  8.     <trim suffixOverrides=" OR ffv.description in()">
  9.         <foreach collection="demisionSmallCategorys" item="demisionSmallCategory" index="index" open="(" close=")">
  10.             <if test="index != 0">
  11.                 <choose>
  12.                     <when test="index % 1000 == 999">) OR ffv.description in(</when>
  13.                     <otherwise>,</otherwise>
  14.                 </choose>
  15.             </if>
  16.             #{demisionSmallCategory,jdbcType=VARCHAR}
  17.         </foreach>
  18.     </trim>
  19.     )
  20. </select>

if，else

choose (when,otherwize) ,相当于 java 语言中的 switch ,与 jstl 中 的 choose 很类似。

when 元素表示当 when 中的条件满足的时候就输出其中的内容，跟 JAVA 中的 switch 效果差不多的是按照条件的顺序，当 when 中有条件满足的时候，就会跳出 choose，即所有的 when 和 otherwise 条件中，只有一个会输出

当所有的我很条件都不满足的时候就输出 otherwise 中的内容

  1. <choose>
  2.     <when test="condition.trxId != null and condition.trxId != 0">
  3.         and Trx_id = #{condition.trxId,jdbcType=DECIMAL}
  4.     </when>
  5.     <otherwise>
  6.         <if test="condition.trxIds != null and condition.trxIds.size >0" >
  7.             and Trx_id in
  8.             <trim suffixOverrides=" or Trx_id in()">
  9.                 <foreach collection="condition.trxIds" item="id" index="index" open="(" close=")">
  10.                     <if test="index != 0">
  11.                         <choose>
  12.                             <when test="index % 1000 == 999">) or Trx_id in(</when>
  13.                             <otherwise>,</otherwise>
  14.                         </choose>
  15.                     </if>
  16.                     #{id,jdbcType=DECIMAL}
  17.                 </foreach>
  18.             </trim>
  19.         </if>
  20.     </otherwise>
  21. </choose>

传入参数值 trxIds = 57315893，1592541

解析 sql 为

  1. select *
  2.  FROM cux.cux_ce_payment_write_off
  3.  WHERE Trx_id in ( 57315893 , 1592541 )

同时传入参数值 trxId = ‘778367’， trxIds = 57315893，1592541

解析 sql 为

  1. select *
  2.  FROM cux.cux_ce_payment_write_off
  3.  WHERE Trx_id = 778367;

排序

  1. //查询条件
  2. QueryWrapper<SupplierInfo> wrapper = new QueryWrapper<>();
  3. wrapper.eq("is_delete",1);
  4. if(param.getSupplierId() != null && !StringUtil.isBlank(param.getSupplierId())) {
  5.     wrapper.like("supplier_code", param.getSupplierId());
  6. }
  7. if(param.getSupplierName() != null && !StringUtil.isBlank(param.getSupplierName())){
  8.     wrapper.like("supplier_name",param.getSupplierName());
  9. }
  10. wrapper.orderByDesc("update_time");

通用方法

日期字段格式化

  1. @DateTimeFormat(pattern="yyyy-MM-dd")//页面写入数据库时格式化   
  2. @JSONField(format="yyyy-MM-dd")//数据库导出页面时json格式化

xml 映射文件参数值

方式 说明
取值方式： ${} Statement:必须使用字符串拼接方式操作 sql，一定要注意单引号问题
取值方式：#{} PreparedStatement:可以使用通配符操作 sql，因为在使用 String 赋值时，可以自动加单引号，因此不需要注意单引号问题。
命名参数：@Param("param") 当传输多个参数时，mybatis 会默认将这些参数放在 map 集合中，可以通过 @Param("param") 为 map 集合指定键的名字

例子：

WaitTodoHandRepository.java

  1. public interface WaitTodoHandRepository  extends BaseMapper<WaitTodoHand> {
  2.     /**
  3.      * 待办转交
  4.      * @param page  分页信息
  5.      * @param param 用户信息
  6.      * @return 分页数据
  7.      */
  8.     IPage<WaitTodoHandVO> waitTodoHand(Page<WaitTodoHandVO> page, @Param("param")UserData param);
  9. }

WaitTodoHandMapper.xml

  1. <!-- 查询指定用户的待办转交-->
  2. <select id="waitTodoHand"
  3.         resultType="com.foreign.payment.common.vo.WaitTodoHandVO"
  4.         parameterType="com.foreign.payment.common.data.UserData">
  5.     select distinct handle_user_id,handle_user_name,handle_user_fullname,hand_user_id,hand_user_name,hand_user_fullname, DATE_FORMAT(begin_time,'%Y-%m-%d') as begin_time,DATE_FORMAT(end_time,'%Y-%m-%d') as end_time,create_time
  6.     from t_fpts_wait_todo_hand
  7.     where is_delete = 1
  8.     and is_valid = 1
  9.     <![CDATA[ and DATE_FORMAT(NOW(),'%Y-%m-%d') <=  DATE_FORMAT(end_time,'%Y-%m-%d') ]]>
  10.     and (handle_user_id = #{param.staffId})
  11. </select>

xml 映射文件 if test

不等于

  1. WHERE enable =1
  2. <if test="keyword != null and keyword != ''">
  3.         AND ( mac_id = #{keyword} )
  4.         OR ( user_id = #{keyword} )
  5.  </if>

test="keyword != null and keyword != ''" 中 and 必须小写，否则会报错

  1. mybatis：org.apache.ibatis.ognl.ParseException: Encountered " <IDENT> "AND "" at line 1, column 26

等于

这里要用 ==，单个 = 不行相当于赋值，这个条件会永远成立

  1. <if test="keyword == 'DDDDD'">
  2.   ,AND ( mac_id = #{keyword} )
  3. </if>

另外，当 == 单个字符时，需要 .toString() 一下，否则会转换为数据判断导致报错，例如：

  1. <if test="keyword == 'D'">
  2.   ,AND ( mac_id = #{keyword} )
  3. </if>

会报错

  1. org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
  2. ### Error querying database.  Cause: java.lang.NumberFormatException: For input string: "D"
  3. ### Cause: java.lang.NumberFormatException: For input string: "D"

需要修改为

  1. <if test="keyword == 'D'.toString()">
  2.   ,AND ( mac_id = #{keyword} )
  3. </if>

xml 映射文件返回值为 List String

1.Repository

  1. List<String> getUserRole(@Param("param") String param);

2.Maper xml

  1. <select id="getUserRole"
  2.      resultType="java.lang.String"
  3.         parameterType="java.lang.String">
  4.     select auth_role.code
  5.     from auth_user_role,auth_role
  6.     where auth_user_role.user_id = auth_role.id
  7.       and auth_user_role.user_id = #{param}
  8. </select>

insert values 字符中 () 丢失

mybatis trim标签的使用

mybatis中的#{}和${}

这里我 

  1. lastUpdateLogin = 1
  2. attribute1 = 'luoma(罗马)'
  1. <trim prefix="values(" suffix=")" suffixOverrides=",">
  2.     <if test="lastUpdateLogin != null">
  3.         #{lastUpdateLogin,jdbcType=DECIMAL},
  4.     </if>
  5.     <if test="attribute1 != null">
  6.         #{attribute1,jdbcType=VARCHAR},
  7.     </if>
  8. </trim>

mybatis 解析后的 sql 为

  1. values(10728, luoma );

把我括号弄丢了，解决办法

  1. values(
  2.     <trim suffixOverrides=",">
  3.         <if test="lastUpdateLogin != null">
  4.             #{lastUpdateLogin,jdbcType=DECIMAL},
  5.         </if>
  6.     </trim>
  7.     <if test="attribute1 != null">
  8.         ,'${attribute1}'
  9.     </if>
  10. )

mybatis 解析后的 sql 为

  1. values(10728, 'luoma(罗马)' );

属性不映射数据库表字段

  1. @TableField(exist = false) //不映射数据库表
  2. private String financingCategorySubIsSofr;