前言
小编在crud业务中会遇到case set的场景,
update `TD_XXX`
set
`status` = case `id`
when 12 Then 1
when 13 Then 2
when 14 Then 1
end
where `id` in (12,13,14)
一般来说都是写在xml中,但是其实mybatis-plus也可以
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
源码分析
此部分跳过,在mybatis-plus 自定义UpdateWrapper(一)实现列自增 已经有详细描述了
思路
此时应该将sql分开看得清楚些
SET `columns` = (
CASE `caseColumns`
WHEN caseMap.keys[0] THEN caseMap.values[0]
END
)
代码实现
/**
* 根据传入map case update 最终实现如下效果
* <p>
* SET `columns` = (
* CASE `caseColumns` WHEN caseMap.keys[0] THEN caseMap.values[0]
* WHEN caseMap.keys[1] THEN caseMap.values[1]
* WHEN caseMap.keys[2] THEN caseMap.values[2]
* END)
* </p>
*/
public MyLambdaUpdateWrapper<T> caseSet(SFunction<T, ?> columns, Map<Object,Object> caseMap, SFunction<T, ?> caseColumns) {
String columnsToString = super.columnToString(columns);
List<Object> params = new ArrayList<>();
StringBuilder stringBuilder = new StringBuilder();
int paramIndex = 0;
for (Object o : caseMap.keySet()) {
stringBuilder.append(String.format("WHEN {%d} THEN {%d}", paramIndex++, paramIndex++)).append("\n");
params.add(o);
params.add(caseMap.get(o));
}
String caseSqlFormat = String.format("(case %s %s end)", super.columnToString(caseColumns), stringBuilder.toString());
String format = String.format("%s = %s", columnsToString, formatSql(caseSqlFormat, params.toArray(new Object[0])));
setSql(format);
return this;
}