文章目录
问题复现
因业务开发需要,组内Java开发工程师写了一个偏复杂的Sql语句,其中Wrapper的接口中传入了两个QueryWrapper对象用于拼接语句,但是在测试的过程中发现第二个QueryWrapper无法正确拼接参数。
Mapper接口方法:
List<SalesExportExcelBO> getSalesHistory(@Param("ew") QueryWrapper queryWrapper,
@Param("ew_1") QueryWrapper queryWrapper1,
@Param("orderType") Integer orderType);
Mapper.xml语句:
...
FROM
sales s
...
${ew.customSqlSegment}
...
${ew_1.customSqlSegment}
...
其中querWrapper中传入第一个参数为LocalDate类型:
QueryWrapper<Object> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("s.sale_date", startTime);
querWrapper1中传入第一个参数为Integer类型:
QueryWrapper<Object> queryWrapper1 = new QueryWrapper<>();
if (Objects.nonNull(goodsLifecycle) {
queryWrapper1.eq("sku.goods_lifecycle", goodsLifecycle);
}
swagger中接口传入参数sale_date=’2020-10-01‘,goods_lifecycle=2。
为最终生成语句:
...
WHERE (s.sale_date >= '2020-10-01'...)
...
WHERE (sku.goods_lifecycle = '2020-10-01')
...
正确的语句应该是:
...
WHERE (s.sale_date >= '2020-10-01'...)
...
WHERE (sku.goods_lifecycle = 2)
---错误的语句---
WHERE (sku.goods_lifecycle = '2020-10-01')
---错误的语句---
...
一、定位问题
看输出的语句,感觉是CustomSqlSegment中语句中拼接出了问题,打印下queryWrapper1中的生成语句。
log.info(queryWrapper1.getCustomSqlSegment())
===输出====
WHERE (sku.goods_lifecycle = #{ew.paramNameValuePairs.MPGENVAL1})
===输出====
不出所料,queryWrapper1中的参数被当做占位中的第一个参数格式化掉,并且取的是ew中的参数,而非ew_1的参数
二、问题分析
1.getCustomSqlSegment源码分析
//获取sql语句
public String getCustomSqlSegment() {
//获取sql片段
MergeSegments expression = this.getExpression();
//片段非空
if (Objects.nonNull(expression)) {
//标准片段集合
NormalSegmentList normal = expression.getNormal();
//获取实现ISqlSegment接口getSqlSegment()的语句
String sqlSegment = this.getSqlSegment();
if (StringUtils.isNotBlank(sqlSegment)) {
if (normal.isEmpty()) {
//语句及标准片段不为空
return sqlSegment;
}
//语句前拼接 where 字段
return "WHERE " + sqlSegment;
}
}
return "";
}
主要的重点就在该语句中:
String sqlSegment = this.getSqlSegment();
通过断点进入AbstractWrapper的getSqlSegment()的方法:
public String getSqlSegment() {
return this.expression.getSqlSegment() + this.lastSql.getStringValue();
}
其中this.expression为MergeSegments类型的对象
2.MergeSegments源码分析
public class MergeSegments implements ISqlSegment {
//普通片段
private final NormalSegmentList normal = new NormalSegmentList();
//分组片段
private final GroupBySegmentList groupBy = new GroupBySegmentList();
//having条件片段
private final HavingSegmentList having = new HavingSegmentList();
//排序片段
private final OrderBySegmentList orderBy = new OrderBySegmentList();
//语句
private String sqlSegment = "";
//是否缓存
private boolean cacheSqlSegment = true;
...
public String getSqlSegment() {
if (this.cacheSqlSegment) {
//有缓存就返回语句
return this.sqlSegment;
} else {
//语句只获取一次即缓存
this.cacheSqlSegment = true;
if (this.normal.isEmpty()) {
//没有普通条件语句
if (!this.groupBy.isEmpty() || !this.orderBy.isEmpty()) {
//语句为分组和having条件排序语句拼接
this.sqlSegment = this.groupBy.getSqlSegment() + this.having.getSqlSegment() + this.orderBy.getSqlSegment();
}
} else {
语句为普通条件和分组和having条件排序语句拼接
this.sqlSegment = this.normal.getSqlSegment() + this.groupBy.getSqlSegment() + this.having.getSqlSegment() + this.orderBy.getSqlSegment();
}
//返回语句
return this.sqlSegment;
}
}
主要的切入语句:
this.sqlSegment = this.normal.getSqlSegment() + this.groupBy.getSqlSegment() + this.having.getSqlSegment() + this.orderBy.getSqlSegment();
出现该问题的sql为普通条件语句,为this.normal.getSqlSegment()返回语句,
3.AbstractISegmentList及NormalSegmentList源码分析
AbstractISegmentList为抽象类是NormalSegmentList的父类,而AbstractISegmentList又继承了ArrayList类以及实现了ISqlSegment和StringPool接口。
AbstractISegmentList已实现getSqlSegment()方法
public String getSqlSegment() {
if (this.cacheSqlSegment) {
return this.sqlSegment;
} else {
this.cacheSqlSegment = true;
//未缓存返回this.childrenSqlSegment()
this.sqlSegment = this.childrenSqlSegment();
return this.sqlSegment;
}
}
childrenSqlSegment()在AbstractISegmentList为一个抽象方法,NormalSegmentList中进行了具体的实现
protected String childrenSqlSegment() {
if (MatchSegment.AND_OR.match(this.lastValue)) {
this.removeAndFlushLast();
}
//返回NormalSegmentList中各ISqlSegment实现类的getSqlSegment()返回的语句按“ ”(空格)拼接
String str = (String)this.stream().map(ISqlSegment::getSqlSegment).collect(Collectors.joining(" "));
return "(" + str + ")";
}
4.函数式接口ISqlSegment
//返回NormalSegmentList中各ISqlSegment实现类的getSqlSegment()返回的语句按“ ”(空格)拼接
String str = (String)this.stream().map(ISqlSegment::getSqlSegment).collect(Collectors.joining(" "));
最终可以定位到普通语句就是各实现了ISqlSegment的实现类中getSqlSegment()按规则拼接的字段
三、Compare接口中sql条件拼接
现在把视线移回到我们的业务代码中
queryWrapper1.eq("sku.goods_lifecycle", goodsLifecycle);
Compare类:
default Children eq(R column, Object val) {
//调用重载方法
return this.eq(true, column, val);
}
Children eq(boolean condition, R column, Object val);
而真实调用的eq方法,是由AbstractWrapper类进行重写
public abstract class AbstractWrapper<T, R, Children extends AbstractWrapper<T, R, Children>> extends Wrapper<T> implements Compare<Children, R>, Nested<Children, Children>, Join<Children>, Func<Children, R> {
//方便链式调用
protected final Children typedThis = this;
//参数定位技术器,线程安全
protected AtomicInteger paramNameSeq;
//参数map
protected Map<String, Object> paramNameValuePairs;
/**
/语句
*/
protected SharedString lastSql;
protected SharedString sqlComment;
protected SharedString sqlFirst;
//实体
private T entity;
//上文中提到的各语句片段集合
protected MergeSegments expression;
//实体对应的class
private Class<T> entityClass;
...
/**
/实现的eq方法
*/
public Children eq(boolean condition, R column, Object val) {
//调用了addCondition()方法
return this.addCondition(condition, column, SqlKeyword.EQ, val);
}
...
需要注意的参数和方法:
//上文中提到的各语句片段集合
protected MergeSegments expression;
public Children eq(boolean condition, R column, Object val) {
//调用了addCondition()方法
return this.addCondition(condition, column, SqlKeyword.EQ, val);
}
方法最终指向了addCondition()
public abstract class AbstractWrapper<T, R, Children>.....
....
protected Children addCondition(boolean condition, R column, SqlKeyword sqlKeyword, Object val) {
//调用doIt方法
return this.doIt(condition, () -> {
return this.columnToString(column);
}, sqlKeyword, () -> {
return this.formatSql("{0}", val);
});
}
protected Children doIt(boolean condition, ISqlSegment... sqlSegments) {
if (condition) {
//添加到sql语句片段
this.expression.add(sqlSegments);
}
return this.typedThis;
}
...
addCondition方法中调用doIt方法,第一个参数为布尔条件值,其余的参数为ISqlSegment接口的实现类
如果第一个参数为真,则将后续的ISqlSegment实现类参数添加到expression成员变量中
public class MergeSegments implements ISqlSegment {
private final NormalSegmentList normal = new NormalSegmentList();
private final GroupBySegmentList groupBy = new GroupBySegmentList();
private final HavingSegmentList having = new HavingSegmentList();
private final OrderBySegmentList orderBy = new OrderBySegmentList();
...
/**
/根据不同的类型添加
*/
public void add(ISqlSegment... iSqlSegments) {
List<ISqlSegment> list = Arrays.asList(iSqlSegments);
ISqlSegment firstSqlSegment = (ISqlSegment)list.get(0);
if (MatchSegment.ORDER_BY.match(firstSqlSegment)) {
this.orderBy.addAll(list);
} else if (MatchSegment.GROUP_BY.match(firstSqlSegment)) {
this.groupBy.addAll(list);
} else if (MatchSegment.HAVING.match(firstSqlSegment)) {
this.having.addAll(list);
} else {
this.normal.addAll(list);
}
this.cacheSqlSegment = false;
}
而addCondition方法中传入了3个ISqlSegment的实现类(两个匿名类)
return this.doIt(condition, () -> {
return this.columnToString(column);
}, sqlKeyword, () -> {
return this.formatSql("{0}", val);
});
//第一个,获取列名
() -> {
return this.columnToString(column);
}
//第二个
sqlKeyword 为一个条件枚举
//第三个,关键,返回格式化sql
() -> {
return this.formatSql("{0}", val);
}
继续跟踪代码
protected final String formatSql(String sqlStr, Object... params) {
//调用formatSqlIfNeed方法
return this.formatSqlIfNeed(true, sqlStr, params);
}
protected final String formatSqlIfNeed(boolean need, String sqlStr, Object... params) {
if (need && !StringUtils.isBlank(sqlStr)) {
if (ArrayUtils.isNotEmpty(params)) {
//sql条件参数不为空
for(int i = 0; i < params.length; ++i) {
//遍历sql条件参数并或者自增的生成的参数名
String genParamName = "MPGENVAL" + this.paramNameSeq.incrementAndGet();
//替换sql占位参数
sqlStr = sqlStr.replace(String.format("{%s}", i), String.format("#{%s.paramNameValuePairs.%s}", "ew", genParamName));
//参数放置到paramNameValuePairs类成员变量中
this.paramNameValuePairs.put(genParamName, params[i]);
}
}
return sqlStr;
} else {
return null;
}
}
最终在改方法可以找到导致出现的bug的地方
sqlStr = sqlStr.replace(String.format("{%s}", i), String.format("#{%s.paramNameValuePairs.%s}", "ew", genParamName));
参数按生成名替换了具体的参数值,并且放置真实的参数值到map中,最终在代理对象执行语句前再替换回真实值
由于默认的Wrapper别名为“ew”,所以会直接读取Mapper接口salesPage方法中第一个 @Param("ew") QueryWrapper queryWrapper 中paramNameValuePairs成员变量的第一个参数,也就是 #{ew.paramNameValuePairs.MPGENVAL1}的值:2020-10-01
四、解决办法
1.使用apply方法拼接语句
queryWrapper1.apply("sku.goods_lifecycle = " + goodsLifecycle);
2.Mapper中只传入一个Wrapper,其余的以方法参数传入,在xml中进行拼接
总结
对于出现的问题,断点进行调试,阅读源码找到问题所在以及提出问题解决方案。
对于出现的问题做出以上总结
或有错误以及未考虑周全之处
望能指出以便小弟技术层面更上一层楼