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的修改<br>
 * 对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);
    }
}

调用示例

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);

输出

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》

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
以下是一个完整的 Java 程序,可以使用 JSqlParser 提取 SQL 语句的所有函数: ```java import net.sf.jsqlparser.expression.ExpressionVisitorAdapter; import net.sf.jsqlparser.expression.Function; import net.sf.jsqlparser.parser.CCJSqlParserUtil; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.util.TablesNamesFinder; import java.util.List; public class ExtractFunctions { public static void main(String[] args) { String sql = "SELECT COUNT(*) FROM orders WHERE YEAR(order_date) = 2021 AND MONTH(order_date) = 7"; try { Statement statement = CCJSqlParserUtil.parse(sql); // 提取所有函数 FunctionVisitor visitor = new FunctionVisitor(); statement.accept(visitor); // 提取所有表名 TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); List<String> tableList = tablesNamesFinder.getTableList(statement); // 输出结果 System.out.println("Functions:"); for (String function : visitor.getFunctions()) { System.out.println("- " + function); } System.out.println("Tables:"); for (String table : tableList) { System.out.println("- " + table); } } catch (Exception e) { e.printStackTrace(); } } static class FunctionVisitor extends ExpressionVisitorAdapter { private final StringBuilder sb = new StringBuilder(); @Override public void visit(Function function) { sb.append(function.getName().toLowerCase()).append("("); if (function.getParameters() != null) { function.getParameters().accept(this); } sb.append(")"); } public List<String> getFunctions() { return List.of(sb.toString().split(",")); } } } ``` 该程序会输出 SQL 语句的所有函数名以及表名。需要注意的是,该程序使用了 Java 11 的 List.of 方法,如果使用的是低版本的 Java,需要将该方法替换为其他方式来创建 List 对象。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

10km

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值