Replace multi appearance in sql statement using jsqlparser

Question

I am using jsqlparser to parse a SQL string and replace table names in the string.
My input is

 SELECT id, test 
 FROM test1 JOIN test2 
 ON test1.aa = test2.bb 
 WHERE test1.conf = \"test\"
 LIMIT 10"

and my goal output is

SELECT id, test 
FROM test1_suffix 
JOIN test2_suffix 
ON test1_suffix.aa = test2_suffix.bb 
WHERE test1_suffix.conf = \"test\"
LIMIT 10"

And I managed to replace table name by extend the TablesNamesFinder, but it gave me this:

SELECT id, test
FROM test1_suffix 
JOIN test2_suffix 
ON test1.aa = test2.bb 
WHERE test1.conf = \"test\"
LIMIT 10

I say that's half of the job done, but how can I do the rest of my job?

score 2 · Accepted Answer · answered Aug 10 '18 at 08:00

So here is a complete (hopefully) example to replace all occurances of table names. The problem is, that JSqlParser does not differ between aliases and table names. There has to be some logic to skip aliases of your sqls, if you do not want to correct those.

The usage of TableNamesFinder does not do the full job, because it traverses the AST only as far as it is needed to find table names and stops then. That is why my example uses the deparsers.

This code transforms

select id, test from test where name = "test"

to

SELECT id, test FROM test_mytest WHERE name = "test"

and

select * from t2 join t1 on t1.aa = t2.bb where t1.a = "someCondition" limit 10

to

SELECT * FROM t2_mytest JOIN t1_mytest ON t1_mytest.aa = t2_mytest.bb WHERE t1_mytest.a = "someCondition" LIMIT 10

I think that solves your problem.

public class SimpleSqlParser24 {

    public static void main(String args[]) throws JSQLParserException {
        replaceTableName("select id, test from test where name = \"test\"");
        replaceTableName("select * from t2 join t1 on t1.aa = t2.bb where t1.a = \"someCondition\" limit 10");
    }

    private static void replaceTableName(String sql) throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse(sql);

        StringBuilder buffer = new StringBuilder();
        ExpressionDeParser expressionDeParser = new ExpressionDeParser() {
            @Override
            public void visit(Column tableColumn) {
                if (tableColumn.getTable() != null) {
                    tableColumn.getTable().setName(tableColumn.getTable().getName() + "_mytest");
                }
                super.visit(tableColumn);
            }

        };
        SelectDeParser deparser = new SelectDeParser(expressionDeParser, buffer) {
            @Override
            public void visit(Table tableName) {
                tableName.setName(tableName.getName() + "_mytest");
                super.visit(tableName);
            }
        };
        expressionDeParser.setSelectVisitor(deparser);
        expressionDeParser.setBuffer(buffer);
        select.getSelectBody().accept(deparser);

        System.out.println(buffer.toString());
    }
}

Thank you very much, I have to point out it would be better if `tableColumn.getTable() != null` can be `tableColumn.getTable().getName() != null`, I need some knowledge about deparser later on.. — Jeremy Len, Aug 10 '18 at 08:38

score 0 · Answer 2 · answered Aug 08 '18 at 09:05

0

Add an alias when parsing that gets used instead of the table name.

SELECT * 
FROM test a
WHERE a.conf = 'something'

Then this should be changed to, that is the where clause can be the same

SELECT * 
FROM test_suffix a
WHERE a.conf = 'something'

answered Aug 08 '18 at 09:05

Joakim Danielson

43,251
5
22
52

That's a clever solution, but I have to make sure user inputs a alias, or I'll face the same problem replacing user's alias... – Jeremy Len Aug 08 '18 at 09:18
We discussed all that stuff already: https://github.com/JSQLParser/JSqlParser/issues/655. Within the included stackoverflow link there are code examples. – wumpz Aug 09 '18 at 09:43
@wumpz alias strategy won't work for me, I have to work out a common way for all sql situations to solve the problem, which is use parser to modify table name – Jeremy Len Aug 09 '18 at 14:58

Replace multi appearance in sql statement using jsqlparser

2 Answers2