背景:Mybatis plus 提供 了一个sql 解析器抽象类AbstractJsqlParser
/**
* 解析 SQL 方法
*
* @param metaObject 元对象
* @param sql SQL 语句
* @return SQL 信息
*/
@Override
public SqlInfo parser(MetaObject metaObject, String sql) {
if (this.allowProcess(metaObject)) {
try {
logger.debug("Original SQL: " + sql);
// fixed github pull/295
StringBuilder sqlStringBuilder = new StringBuilder();
Statements statements = CCJSqlParserUtil.parseStatements(sql);
int i = 0;
for (Statement statement : statements.getStatements()) {
if (null != statement) {
if (i++ > 0) {
sqlStringBuilder.append(';');
}
sqlStringBuilder.append(this.processParser(statement).getSql());
}
}
if (sqlStringBuilder.length() > 0) {
return SqlInfo.newInstance().setSql(sqlStringBuilder.toString());
}
} catch (JSQLParserException e) {
throw ExceptionUtils.mpe("Failed to process, please exclude the tableName or statementId.\n Error SQL: %s", e, sql);
}
}
return null;
}
方法里面 Statements statements = CCJSqlParserUtil.parseStatements(sql); 使用了JSQLParser对原始sql 进行解析，本以为一切都很完美的进行，结果调试的时候发现发生几个解析异常
异常一：
Original SQL:
SELECT
1 as query_type,
a.id as bill_id,
a.bill_code,
td.trade_money as bill_money,
td.trade_money,
a.inputer_name,
t.trade_type,
t.status AS trade_status,
if(a.status=2||a.status=4,1,0) as expired,
a.status AS asset_status,
a.output_time,
t.create_time AS trade_date,
a.expire_date,
ABS(TIMESTAMPDIFF(DAY, a.output_time, a.expire_date)) AS days,
t.trade_no,
t.creator_id
FROM asset AS a
JOIN trade_detail AS td
ON a.id = td.pre_bill_id
JOIN trade AS t
ON td.trade_no = t.trade_no
WHERE t.trade_type = 1
AND t.status IN (2, 3, 4, 5)
AND a.outputer_id = ?
and a.bill_code = ?
and a.output_time >= ?
and a.output_time <=?
and a.expire_date >=?
and a.expire_date <=?
and t.create_time >=?
and t.create_time <=?
and t.trade_no = ?
and a.status in (0,1,3,5)
order by output_time asc
when useing jsqlparser parser this sql then throw a exception:Encountered unexpected token: "if" "IF"
Caused by: net.sf.jsqlparser.JSQLParserException
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:137)
at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:60)
... 47 more
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "if" "IF"
at line 10, column 9.
Was expecting one of:
"!"
"("
"*"
"+"
"-"
"?"
"@"
"@@"
"ACTION"
"ANY"
"BYTE"
"CASCADE"
"CASE"
"CAST"
"CHANGE"
"CHAR"
"CHARACTER"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"DESCRIBE"
"DO"
"DUPLICATE"
"ENABLE"
"END"
"EXTRACT"
"FALSE"
"FIRST"
"FN"
"FOLLOWING"
"GROUP_CONCAT"
"INDEX"
"INSERT"
"INTERVAL"
"ISNULL"
"KEY"
"LAST"
"MATERIALIZED"
"NEXTVAL"
"NO"
"NOT"
"NULL"
"NULLS"
"OPEN"
"OVER"
"PARTITION"
"PATH"
"PERCENT"
"PRECISION"
"PRIMARY"
"PRIOR"
"RANGE"
"READ"
"REPLACE"
"ROW"
"ROWS"
"SEPARATOR"
"SESSION"
"SIBLINGS"
"SIZE"
"START"
"TABLE"
"TEMP"
"TEMPORARY"
"TOP"
"TRUE"
"TRUNCATE"
"TYPE"
"UNSIGNED"
"VALUE"
"VALUES"
"XML"
"ZONE"
"{d"
"{t"
"{ts"
"~"
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<K_TIME_KEY_EXPR>
<S_CHAR_LITERAL>
<S_DOUBLE>
<S_HEX>
<S_IDENTIFIER>
<S_LONG>
<S_QUOTED_IDENTIFIER>
if.png
异常二：
Original SQL:
SELECT
b2.id AS node_id,
a.outputer_id,
a.outputer_name,
a.output_time as output_date,
a.factor_id,
b2.owner_id,
b2.owner_name,
t.inputer_id AS pledge_ent_id,
t.inputer_name AS pledge_ent_name,
b2.bill_code,
b2.bill_money,
b.root_id,
td2.bill_id AS parent,
b2.create_time AS owner_date,
td2.trade_no,
b2.bill_level,
t.trade_type,
t.status AS trade_status,
(select pledge_type from bill_pledge_operation s where s.trade_no = t.trade_no AND s.STATUS = 1 ORDER BY create_time desc limit 1) AS pledge_type,
(select date_format(create_time,'%Y-%m-%d') from bill_pledge_operation s where s.trade_no = t.trade_no AND s.STATUS = 1 ORDER BY create_time desc limit 1) AS unbind_pledge_date,
tpe.pledge_date,
(t.outputer_id = b2.owner_id) AS is_remain
FROM
trade_detail AS td
JOIN bill AS b
ON b.id = td.bill_id
JOIN bill AS b2
ON b2.root_id = b.root_id
JOIN trade_detail AS td2
ON td2.pre_bill_id = b2.id
JOIN trade AS t
ON t.trade_no = td2.trade_no
JOIN asset AS a
ON a.id = b.root_id
LEFT JOIN trade_pledge_ext AS tpe
ON tpe.trade_no = td2.trade_no
WHERE td.bill_code = ?
AND td.trade_no = ?
AND t.status in (4,5)
ORDER BY b2.bill_level
when useing jsqlparser parser this sql then throw a exception:Encountered unexpected token: "=" "="
Caused by: net.sf.jsqlparser.JSQLParserException
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:137)
at com.baomidou.mybatisplus.core.parser.AbstractJsqlParser.parser(AbstractJsqlParser.java:60)
... 61 more
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "=" "="
at line 23, column 25.
Was expecting one of:
"&"
")"
"::"
"<<"
">>"
"COLLATE"
"["
"^"
"|"
==.png
由上面异常信息可知 JSQLParser 不支持上面两个语法；
解决方法：
1、针对 if("","","")函数 可以使用case when 代替 ；
2、针对与 = 判断两个数值是否相等其实个人不太愿意使用 case when 来实现，代码太多，然后想到了使用 ^ 异或运算如果两个整型值相等返回0 ，跟= 返回值正好相反，这个使用方式根据自己业务确定；
对于JSQLParser 不支持"if" "IF" "=" 也在GitHub 留了issues 期待各位大佬提出更好的解决方案；
作者建议 if(a.status=2||a.status=4,1,0) as expired, 修改为if(a.status=2 or a.status=4,1,0) as expired,
高版本已经兼容；
Encountered unexpected token: "if" "IF" "=" · Issue #1648 · JSQLParser/JSqlParser (github.com)
