Mybatis 记录

1. 根据列表批量修改

void secretKeySequence(@Param("list")List<IndustrialShareDto> list);

<update id="secretKeySequence">
   update cloud_industrial_set
       set `index` =
      case
    <foreach collection="list" item="item">
        when id = #{item.id} then #{item.index}
    </foreach>
    end
    where id in(
    <foreach collection="list" item="item" separator=",">
        #{item.id}
    </foreach>
    )
</update>

2. 小于号的处理

 select username from t where id <![CDATA[ <= ]]> #{id}

3. 大小写重复列的问题

出现在 3.0.5 版本独有的问题，当返回值是 resultType = java.util.Map 时，会有两个列

<select id="findDataShowSet" resultType="java.util.Map">
     select sqltext,customid
       from datashowset
      where showname = #{showName}
      order by id
     offset 0 rows fetch next 1 rows only
</select>

查找原因：FastResultSetHandler.loadMappedAndUnmappedColumnNames()

protected void loadMappedAndUnmappedColumnNames(ResultSet rs, ResultMap resultMap, List<String> mappedColumnNames, List<String> unmappedColumnNames) throws SQLException {
  mappedColumnNames.clear();
  unmappedColumnNames.clear();
  final ResultSetMetaData rsmd = rs.getMetaData();
  final int columnCount = rsmd.getColumnCount();
  final Set<String> mappedColumns = resultMap.getMappedColumns();
  for (int i = 1; i <= columnCount; i++) {
    final String columnName = configuration.isUseColumnLabel() ? rsmd.getColumnLabel(i) : rsmd.getColumnName(i);
    //获取大写
    final String upperColumnName = columnName.toUpperCase(Locale.ENGLISH);
    //大写小写都存了一遍
    if (mappedColumns.contains(upperColumnName)) {
      mappedColumnNames.add(upperColumnName);
      mappedColumnNames.add(columnName);
    } else {
      unmappedColumnNames.add(upperColumnName);
      unmappedColumnNames.add(columnName);
    }
  }
}

要么换版本，要么手动去掉

Set<String> upperKeys = new HashSet<>();
dataList.forEach(map -> {
    upperKeys.addAll(map.keySet());
});
dataList.forEach(
        map -> {
            for(String key : upperKeys){
                map.remove(key.toUpperCase(Locale.ENGLISH));
            }
        }
);

4. 递归自连接

1. 实体类

@Data
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class Sect {
    private Integer id;
    @NotEmpty
    private String name;
    private String desc;
    @NotEmpty
    private Integer parentId;
    
    //内部有一个自己类型的列表
    private List<Sect> sectList;
}

2. Mapper 接口

List<Sect> list(@Param("parentId") Integer parentId);

正常的查询

3. Mapper.xml

<resultMap id="sectList" type="com.demo.dto.Sect">
    <id property="id" column="id"></id>
    <result column="name" property="name"></result>
    <result column="desc" property="desc"></result>
    //select 属性，引用下一个自己，column 属性，将上一个查询的 id 作为参数继续查询
    <collection property="sectList" select="list" column="id"></collection>
</resultMap>
 
<select id="list" resultMap="sectList">
    select * from sect where parent_id = #{parentId}
</select>

4. 调用 mapper

@RequestMapping("/list")
public List<Sect> list(){
    return sectMapper.list(-1);
}
 

这里参数传顶级的 id