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>
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. 递归自连接
- 实体类
@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;
}
- Mapper 接口
List<Sect> list(@Param("parentId") Integer parentId);
正常的查询
- 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>
- 调用 mapper
@RequestMapping("/list")
public List<Sect> list(){
return sectMapper.list(-1);
}
这里参数传顶级的 id