jsqlparser:基于语法分析实现SQL中的CAST函数替换

最近遇到一个问题，应用层提供的SQL语句中有CAST(local_time AS DATE)这样的语句，在MySQL中执行肯定是没问题的，但是后台数据库切换到了HBase,使用apache phoenix 提供的JDBC驱动访问时却报错了，按照phoenix官方的文档，CAST函数是支持，但现实就是报错过不了，应该是我使用的phoenix版本问题，应该是个BUG,暂时无法通过升级版本解决。

解决方案也不复杂就是用phoenix的Native函数TO_DATE,TO_CHAR函数来代替，将CAST(local_time AS DATE)替换为TO_DATE(TO_CHAR("local_time"), 'yyyy-MM-dd')。

那么问题来了，如果让应用层来替换这事很方便，但是我们希望数据存储对应用层是透明的，应用层不需要知道存储是MySQL还是HBase，如果让应用层修改，那应用层就需要知道数据库的类型是MySQL还是HBase，根据不同的数据库使用不同的SQL语句，这太麻烦了----这是下下策。

有没有可能在服务端自动替换呢？有了jsqlparser这个神器，这个想法就可以实现。
jsqlparser是一个java的SQL语句解析器，在我之前的博客：《jsqlparser:基于抽象语法树(AST)遍历SQL语句的语法元素》以及《jsqlparser:实现基于SQL语法分析的SQL注入攻击检查》介绍了如何通过jsqlparser来遍历SQL语句中所有的语法单元实现自己的需求。
那么基于jsqlparser解析出的对象，修改部分语法也是可以实现的。所以一个基本的解决思路就有了：

遍历jsqlparser解析的语法树对象，找到所有CAST函数(Function对象)，将其替换为需要的函数。

以下是实现代码：

import net.sf.jsqlparser.expression.CastExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.util.TablesNamesFinder;

import java.util.function.Consumer;
/**
 * 基于SQL语法对象实现对SQL的修改

 * 对PHOENIX支持有问题的CAST日期函数转换为使用PHOENIX的Native函数TO_DATE,TO_TIME,TO_TIMESTAMP
 * @author guyadong
 *
 */
public class PhoenixNormalizer  extends TablesNamesFinder{
    public PhoenixNormalizer() {
	}
	/**
	 * 根据输入参数创建TO_DATE,TO_TIME,TO_TIMESTAMP函数
	 * @param castLeftExpression CAST的值参数
	 * @param format 时间格式参数
	 * @param targetFunctionName 要创建的Function对象的函数名
	 */
	private Expression castToFunction(Expression castLeftExpression,String format,String targetFunctionName){
		Function toChar = new Function()
				.withName("TO_CHAR")
				.withParameters(new ExpressionList().addExpressions(castLeftExpression));
		Function targetFunction = new Function()
				.withName(targetFunctionName)
				.withParameters(new ExpressionList().addExpressions(toChar,new StringValue(format)));
		return targetFunction;
	}
	/**
	 * 从 Cast 函数中获取对应的参数，将其转为对应的TO_DATE,TO_TIME,TO_TIMESTAMP函数
	 */
	private void onCastExpression(CastExpression castExpression,Consumer<Expression>consumer){
	        Expression newExp = null;
	        switch (castExpression.getType().toString().toLowerCase()) {
	            case "date":{
	                newExp = castToFunction(castExpression.getLeftExpression(),"yyyy-MM-dd","TO_DATE");
	                break;
	            }
	            case "time":{
	                newExp = castToFunction(castExpression.getLeftExpression(),"yyyy-MM-dd HH:mm:ss","TO_TIME");
	                break;
	            }
	            case "timestamp":{
	                newExp = castToFunction(castExpression.getLeftExpression(),"yyyy-MM-dd'T'HH:mm:ss.SSSZ","TO_TIMESTAMP");
	                break;
	            }
	            default:
	                break;
	        }
	        if(null != newExp){
	            consumer.accept(newExp);
	        }
	}
    @Override
    public void visit(SelectExpressionItem item) {
    	/**
    	 * 不同于其他函数，jsqlparser对于CAST函数是单独处理的，定义了一个单独的类 CastExpression
    	 */
        if(item.getExpression() instanceof CastExpression){
            onCastExpression((CastExpression)item.getExpression(),item::setExpression);
        }
        super.visit(item);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

调用示例

Statement stmt;

String sql = "SELECT count(1) AS count, CAST(\"local_time\" AS date) AS datastr, \"device_id\", \"media_id\" FROM \"dc_play_log_hbase\" WHERE \"local_time\" < TO_TIMESTAMP(\'2022-11-30 23:59:59\') AND \"local_time\" >= TO_TIMESTAMP(\'2022-11-22 00:00:00\') GROUP BY datastr,\"device_id\", \"media_id\""
stmt = CCJSqlParserUtil.parse(sql);
// PhoenixNormalizer遍历所有节点，执行替换
stmt.accept(new PhoenixNormalizer());
// 输出替换后的SQL
System.printf("sql = %s\n",stmt);
1
2
3
4
5
6
7
8

输出

sql = SELECT count(1) AS “count”, TO_DATE(TO_CHAR(“local_time”), ‘yyyy-MM-dd’) AS “datastr”, “device_id”, “media_id” FROM “dc_play_log_hbase” WHERE “local_time” < TO_TIMESTAMP(‘2022-11-30 23:59:59’) AND “local_time” >= TO_TIMESTAMP(‘2022-11-22 00:00:00’) GROUP BY “datastr”, “device_id”, “media_id”

完整的代码参见码云仓库：

https://gitee.com/l0km/sql2java/blob/master/sql2java-manager/src/main/java/gu/sql2java/phoenix/PhoenixNormalizer.java

单元测试：

https://gitee.com/l0km/sql2java/blob/master/sql2java-manager/src/test/java/gu/sql2java/pagehelper/parser/JsqlParserTest.java

参考资料：
《apache functions》

jsqlparser系列文章

《jsqlparser(一):基于抽象语法树(AST)遍历SQL语句的语法元素》
《jsqlparser(二):实现基于SQL语法分析的SQL注入攻击检查》
《jsqlparser(三):基于语法分析实现SQL中的CAST函数替换》
《jsqlparser(四):实现MySQL 函数DATE_FORMAT到Phoenix函数TO_CHAR的替换》
《jsqlparser(五):修改语法定义(JSqlParserCC.jjt)实现UPSERT支持Phoenix语法ON DUPLICATE KEY IGNORE》