java修改数据语句 java修改sql语句

转载

编程小天才 2023-06-12 22:29:27

文章标签 java修改数据语句 sql JSON 表名 文章分类 Java 后端开发

场景

前台传入的所有sql语句，都需要对表名做解析，添加前后缀。
一开始考虑使用正则匹配，但是sql语句表名的场景太多，select、join都会有表名。最后确定使用Druid中的Vistor来解析SQL语句
Druid中主要使用访问者模式来解析sql，并且封装了对sql语句解析生成的sql树的一系列操作。
访问者模式，是行为型设计模式之一。访问者模式是一种将数据操作与数据结构分离的设计模式。
Druid中默认已经支持标准sql，只要看一下通用的sql实现方式即可。

实现

1.添加依赖

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.22</version>
            </dependency>

2.代码开发

Druid的accept方法会调用visitor，看了SQLASTVisitor，里面默认实现了上面那些对于ast语法树操作的接口，没有符合我需求的，需要自己实现，只要继承了这个类以后，重写visitor方法就行。大致代码如下

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlASTVisitorAdapter;
import com.alibaba.druid.util.JdbcConstants;

import java.util.List;

/**
 * @Description TsqlTablePrefixVisitor
 * @Author lcc
 * @Date 2021/6/22
 */
public class TsqlTablePrefixVisitor extends MySqlASTVisitorAdapter {
    private final static String TABLE_PREFIX_DS = "ods_ds_";
    private final static String TABLE_SUFFIX_DS = "_all";
    private final static String dbType = JdbcConstants.CLICKHOUSE;
    
    @Override
    public boolean visit(SQLExprTableSource x) {
        String table = x.getExpr().toString();
        if (table.contains("`")){
            table = table.replace("`","");
        }
       // x.setExpr(TABLE_PREFIX_DS+table+TABLE_SUFFIX_DS);
        x.setExpr(table+TABLE_SUFFIX_DS);

        return true;
    }
  
    public static void main(String[] args) {
        String chsql = "";
        TsqlTablePrefixVisitor prefixVisitor = new TsqlTablePrefixVisitor();
        List<SQLStatement> stmtList = SQLUtils.parseStatements(chsql, dbType);
        for (SQLStatement stmt : stmtList) {
            stmt.accept(prefixVisitor);
        }
        String convertedSql = SQLUtils.toSQLString(stmtList, dbType);
        System.out.println(convertedSql);
    }
}

我重写的visit()方法主要实现两个功能：
1.去除表名的反引号，因为有时候传入的表名会是`tablename`样式。
2.将SQL语句中的所有表名都添加前后缀。

测试

我这里用一个复杂的SQL语句做演示，SQL语句中包括join和子查询等。

select t12.serviceCode as logistics_id, t5.entityName as logistics_name, t12.fulfillmentSite as delivery_warehouse_name, t7.itemCode as item_code, t7.itemName as item_name, t10.brandName as brand_name, CONCAT_WS('/',t8.item_category_level1_name,t8.item_category_level2_name,t8.item_category_level3_name,t8.item_category_level4_name) as categroy_name, t7.year as item_year, t7.season as item_season, substr(t7.launchDate,1,10) as launch_date, t6.skuCode as sku_code, coalesce(if(JSON_EXTRACT(t6.attributes, '$[0].attrKey') like '%颜色%', JSON_EXTRACT(t6.attributes, '$[0].attrVal'), if(JSON_EXTRACT(t6.attributes, '$[1].attrKey') like '%颜色%', JSON_EXTRACT(t6.attributes, '$[1].attrVal'), null))) as sku_color, coalesce(if(JSON_EXTRACT(t6.attributes, '$[1].attrKey') like '%尺码%', JSON_EXTRACT(t6.attributes, '$[1].attrVal'), if(JSON_EXTRACT(t6.attributes, '$[0].attrKey') like '%尺码%', JSON_EXTRACT(t6.attributes, '$[0].attrVal'), null))) as sku_size, t3.tradeQty as item_cnt, t6.salePrc_value as sku_price from trade__trade_base_order_line_b_o t3 left join  item__sku_b_o t6 on JSON_EXTRACT(t3.sku,'$.id') = t6.id left join item__item_b_o t7 on t6.item = t7.id left join ( SELECT CASE WHEN t1.categoryLevel = 1 THEN t1.id WHEN t1.categoryLevel = 2 THEN t2.id WHEN t1.categoryLevel = 3 THEN t3.id WHEN t1.categoryLevel = 4 THEN t4.id ELSE '' END AS item_category_level1_id, CASE WHEN t1.categoryLevel = 1 THEN t1.categoryName WHEN t1.categoryLevel = 2 THEN t2.categoryName WHEN t1.categoryLevel = 3 THEN t3.categoryName WHEN t1.categoryLevel = 4 THEN t4.categoryName ELSE '' END AS item_category_level1_name, CASE WHEN t1.categoryLevel = 2 THEN t1.id WHEN t1.categoryLevel = 3 THEN t2.id WHEN t1.categoryLevel = 4 THEN t3.id ELSE '' END AS item_category_level2_id, CASE WHEN t1.categoryLevel = 2 THEN t1.categoryName WHEN t1.categoryLevel = 3 THEN t2.categoryName WHEN t1.categoryLevel = 4 THEN t3.categoryName ELSE '' END AS item_category_level2_name, CASE WHEN t1.categoryLevel = 3 THEN t1.id WHEN t1.categoryLevel = 4 THEN t2.id ELSE '' END AS item_category_level3_id, CASE WHEN t1.categoryLevel = 3 THEN t1.categoryName WHEN t1.categoryLevel = 4 THEN t2.categoryName ELSE '' END AS item_category_level3_name, CASE WHEN t1.categoryLevel = 4 THEN t1.id ELSE '' END AS item_category_level4_id, CASE WHEN t1.categoryLevel = 4 THEN t1.categoryName ELSE '' END AS item_category_level4_name FROM md__category_b_o AS t1 LEFT JOIN md__category_b_o AS t2 ON t1.parentCategory = t2.id LEFT JOIN md__category_b_o AS t3 ON t2.parentCategory = t3.id LEFT JOIN md__category_b_o AS t4 ON t3.parentCategory = t4.id ) t8 on t7.category = coalesce(t8.item_category_level4_id,t8.item_category_level3_id,t8.item_category_level2_id,t8.item_category_level1_id) left join  md__brand_b_o t10 on t7.brand = t10.id left join trade__fulfillment_progress_line_b_o t13 on t3.id = t13.tradeOrderLine left join  trade__fulfillment_progress_b_o t12 on t13.fulfillmentProgress = t12.id left join  md__entity_b_o t5 on t12.fulfillmentServiceProvider = t5.id inner join  (select * from md__entity_role_b_o where roleType = 'CARRIER') t4 on t4.entity = t5.id

执行后结果为：

SELECT t12.serviceCode AS logistics_id, t5.entityName AS logistics_name, t12.fulfillmentSite AS delivery_warehouse_name, t7.itemCode AS item_code, t7.itemName AS item_name
	, t10.brandName AS brand_name
	, CONCAT_WS('/', t8.item_category_level1_name, t8.item_category_level2_name, t8.item_category_level3_name, t8.item_category_level4_name) AS categroy_name
	, t7.year AS item_year, t7.season AS item_season
	, substr(t7.launchDate, 1, 10) AS launch_date
	, t6.skuCode AS sku_code
	, coalesce(if(JSON_EXTRACT(t6.attributes, '$[0].attrKey') LIKE '%颜色%', JSON_EXTRACT(t6.attributes, '$[0].attrVal'), if(JSON_EXTRACT(t6.attributes, '$[1].attrKey') LIKE '%颜色%', JSON_EXTRACT(t6.attributes, '$[1].attrVal'), NULL))) AS sku_color
	, coalesce(if(JSON_EXTRACT(t6.attributes, '$[1].attrKey') LIKE '%尺码%', JSON_EXTRACT(t6.attributes, '$[1].attrVal'), if(JSON_EXTRACT(t6.attributes, '$[0].attrKey') LIKE '%尺码%', JSON_EXTRACT(t6.attributes, '$[0].attrVal'), NULL))) AS sku_size
	, t3.tradeQty AS item_cnt, t6.salePrc_value AS sku_price
FROM trade__trade_base_order_line_b_o_all t3
	LEFT JOIN item__sku_b_o_all t6 ON JSON_EXTRACT(t3.sku, '$.id') = t6.id
	LEFT JOIN item__item_b_o_all t7 ON t6.item = t7.id
	LEFT JOIN (
		SELECT CASE 
				WHEN t1.categoryLevel = 1 THEN t1.id
				WHEN t1.categoryLevel = 2 THEN t2.id
				WHEN t1.categoryLevel = 3 THEN t3.id
				WHEN t1.categoryLevel = 4 THEN t4.id
				ELSE ''
			END AS item_category_level1_id
			, CASE 
				WHEN t1.categoryLevel = 1 THEN t1.categoryName
				WHEN t1.categoryLevel = 2 THEN t2.categoryName
				WHEN t1.categoryLevel = 3 THEN t3.categoryName
				WHEN t1.categoryLevel = 4 THEN t4.categoryName
				ELSE ''
			END AS item_category_level1_name
			, CASE 
				WHEN t1.categoryLevel = 2 THEN t1.id
				WHEN t1.categoryLevel = 3 THEN t2.id
				WHEN t1.categoryLevel = 4 THEN t3.id
				ELSE ''
			END AS item_category_level2_id
			, CASE 
				WHEN t1.categoryLevel = 2 THEN t1.categoryName
				WHEN t1.categoryLevel = 3 THEN t2.categoryName
				WHEN t1.categoryLevel = 4 THEN t3.categoryName
				ELSE ''
			END AS item_category_level2_name
			, CASE 
				WHEN t1.categoryLevel = 3 THEN t1.id
				WHEN t1.categoryLevel = 4 THEN t2.id
				ELSE ''
			END AS item_category_level3_id
			, CASE 
				WHEN t1.categoryLevel = 3 THEN t1.categoryName
				WHEN t1.categoryLevel = 4 THEN t2.categoryName
				ELSE ''
			END AS item_category_level3_name
			, CASE 
				WHEN t1.categoryLevel = 4 THEN t1.id
				ELSE ''
			END AS item_category_level4_id
			, CASE 
				WHEN t1.categoryLevel = 4 THEN t1.categoryName
				ELSE ''
			END AS item_category_level4_name
		FROM md__category_b_o_all t1
			LEFT JOIN md__category_b_o_all t2 ON t1.parentCategory = t2.id
			LEFT JOIN md__category_b_o_all t3 ON t2.parentCategory = t3.id
			LEFT JOIN md__category_b_o_all t4 ON t3.parentCategory = t4.id
	) t8
	ON t7.category = coalesce(t8.item_category_level4_id, t8.item_category_level3_id, t8.item_category_level2_id, t8.item_category_level1_id)
	LEFT JOIN md__brand_b_o_all t10 ON t7.brand = t10.id
	LEFT JOIN trade__fulfillment_progress_line_b_o_all t13 ON t3.id = t13.tradeOrderLine
	LEFT JOIN trade__fulfillment_progress_b_o_all t12 ON t13.fulfillmentProgress = t12.id
	LEFT JOIN md__entity_b_o_all t5 ON t12.fulfillmentServiceProvider = t5.id
	INNER JOIN (
		SELECT *
		FROM md__entity_role_b_o_all
		WHERE roleType = 'CARRIER'
	) t4
	ON t4.entity = t5.id

可以看到所有表名都做了转义且不需要修改别名。
如果需要修改SQL语法树其他节点，也与此方法类似。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。