Deconstruct, Modify, Rebuild an SQL statement(Java version)

This article shows how to use General SQL Parser to split an SQL statement into it’s constituent parts and then add/remove select list, add more criteria, an order by clause, etc.

This feature is quite useful if you want to reconstruct an existing SQL statement dynamically in your program.

Take this SQL for example:

SELECT A as A_Alias, B AS B_Alias FROM TABLE_X

If you need to remove the second column “B AS B_Alias” from the select list, just do something like this:

columns.removeResultColumn(1); // 0 is the first column, and 1 is the second column

You will get this new SQL(the , was removed automatically):

SELECT A as A_Alias FROM TABLE_X

OK, now you want to add a new column, let say “x”, just do something like this:

columns.addResultColumn("x");

This time, the new SQL become this:

SELECT A as A_Alias,x  FROM TABLE_X

Replace a column

original SQL:

SELECT * FROM TABLE_X

New SQL:

SELECT TABLE_X.* FROM TABLE_X

Java code use to replace a column name which is reasonably straightforward.:

TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TResultColumnList columns = select.getResultColumnList();
if (columns.getResultColumn(0).toString().equalsIgnoreCase("*")){
    columns.getResultColumn(0).setString("TABLE_X.*");
}

Add criteria (where clause)

original SQL:

SELECT * FROM TABLE_X where f > 0

New SQL:

SELECT * FROM TABLE_X where f > 0 and c>1

Java code use to add criteria :

assertTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.addWhereClause("c>1");

Here is a more advanced demo illustrates how to modify/remove user defined variable in where condition.

Add Order by clause

original SQL:

SELECT * FROM TABLE_X order by a

New SQL:

SELECT * FROM TABLE_X order by a,b

Java code use to add order by item:

assertTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.getOrderbyClause().addOrderByItem("b");

Here is a Java demo with more samples. ANSI C demo

Deconstruct, Modify, Rebuild an SQL statement(Java version, part 2), use low level APIs