sql不变的情况下，如何动态替换（多表查询）SQL中的表名

78 阅读 0 评论 52 点赞

我是靠谱客的博主整齐毛豆，最近开发中收集的这篇文章主要介绍sql不变的情况下，如何动态替换（多表查询）SQL中的表名，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

需求场景

因为业务场景是在网页上运维人员写SQL抽数，但是每天查询的SQL一样，就是表名根据时间不同，会加后缀（比如今天是user_20220331,明天是user_20220401），后期替换配置表名就行了。如果replace的话，表名相似就会受影响。

多表SQL
解决方案：
利用jsqlparser的源码包，写一个方法，继承jsqlparser中的一个类，并重写这个类中的某些方法，做到表名替换

1. 导入jar

<dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>0.9.6</version>
        </dependency>

2. 重写方法


import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.collections.CollectionUtils;

import java.io.StringReader;
import java.util.*;

public class Test4 extends TablesNamesFinder{

    private List<String> tables;
    private List<String> otherItemNames;
    private PlainSelect plainSelect;
    private static List<Map> change_tables = new ArrayList<Map>();
    private String table_now;
    private String alias;

    public static void main(String[] args) {
        try {

            Map map = new HashMap();
            map.put("user","user_001");
            map.put("grade","grade_001");
            map.put("dwrep.student@dl_link","student_001");
            
            change_tables.add(map);
            System.out.println("原来的SQL是：");
            String sql = "select * from user s where id=(select u.id from dwrep.student@dl_link  s inner join grade g on u.id=g.id where name = (select name from user s2 where id =2))";

            System.out.println(sql);
            
            CCJSqlParserManager parserManager = new CCJSqlParserManager();
            Select select = (Select) (parserManager.parse(new StringReader(sql)));
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            Statement statement = CCJSqlParserUtil.parse(sql);
            TablesNamesFinder tablesNamesFinder = new Test4();
            List<String> tableNameList = tablesNamesFinder.getTableList(statement);
            System.out.println("改变后的SQL是：");
            System.out.println(statement.toString());
            if (!CollectionUtils.isEmpty(tableNameList)) {
                StringBuffer allTableNames = new StringBuffer();
                tableNameList.forEach(tableName -> {
                    allTableNames.append(tableName + ",");
                });
                String allTableName = allTableNames.toString().substring(0, allTableNames.toString().length() - 1);
            }
        }catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void visit(Table tableName) {
        String tableWholeName = tableName.getFullyQualifiedName();
        alias=tableName.getAlias().toString();
        table_now = tableWholeName;
        if (!this.otherItemNames.contains(tableWholeName.toLowerCase()) && !this.tables.contains(tableWholeName)) {
            this.tables.add(tableWholeName);
        }
    }

    @Override
    public List<String> getTableList(Statement statement) {
        this.init();
        statement.accept(this);
        return this.tables;
    }

    protected void init() {
        this.otherItemNames = new ArrayList();
        this.tables = new ArrayList();
        this.plainSelect = new PlainSelect();
    }

    @Override
    public void visit(PlainSelect plainSelect) {
        Iterator var2;
        if (plainSelect.getSelectItems() != null) {
            var2 = plainSelect.getSelectItems().iterator();
            while(var2.hasNext()) {
                SelectItem item = (SelectItem)var2.next();
                item.accept(this);
            }
        }

        if (plainSelect.getFromItem() != null) {
            plainSelect.getFromItem().accept(this);

            Table table = new Table((String) change_tables.get(0).get(table_now));
            table.setAlias(new Alias(alias));
            plainSelect.setFromItem(table);

        }

        if (plainSelect.getJoins() != null) {
            var2 = plainSelect.getJoins().iterator();

            while(var2.hasNext()) {
                Join join = (Join)var2.next();
                String alias = join.getRightItem().getAlias().toString();
                String table_tmp=join.getRightItem().toString();
                table_now = table_tmp.substring(0,table_tmp.indexOf(" "));
                Table joinTable = new Table();
                joinTable.setName((String) change_tables.get(0).get(table_now));
                joinTable.setAlias(new Alias(alias));
                join.setRightItem(joinTable);
                join.getRightItem().accept(this);
            }
        }

        if (plainSelect.getWhere() != null) {
            plainSelect.getWhere().accept(this);
        }

        if (plainSelect.getOracleHierarchical() != null) {
            plainSelect.getOracleHierarchical().accept(this);
        }
    }

}

3. 运行

表替换是：

 			Map map = new HashMap();
            map.put("user","user_001");
            map.put("grade","grade_001");
            map.put("dwrep.student@dl_link","student_001");

运行结果

原来的SQL是：
select * from user s where id=(select u.id from dwrep.student@dl_link  s inner join grade g on u.id=g.id where name = (select name from user s2 where id =2))

改变后的SQL是：
SELECT * FROM user_001 AS  s WHERE id = (SELECT u.id FROM student_001 AS  s INNER JOIN grade_001 AS  g ON u.id = g.id WHERE name = (SELECT name FROM user_001 AS  s2 WHERE id = 2))