Parsing SQL query in Java

Question

I know by using prepared statement we can set the column values. Here what I want is, I already have a list of queries written to execute on same table but with different column values. E.g.

select * from tableName as t1 where t1.tableColumnId=4 and t1.tableColumnName='test' inner join tableName2 as t2 on t1.tableColumnId=t2.tableColumnId

select * from tableName as t1 where t1.tableColumnId=6 and t1.tableColumnName='test' inner join tableName2 as t2 on t1.tableColumnId=t2.tableColumnId

As you can see both the queries are almost same except the tableColumnId value. I want to save this in collection as

select * from tableName as t1 where t1.tableColumnId=? and t1.tableColumnName='test' inner join tableName2 as t2 on t1.tableColumnId=t2.tableColumnId

So that I won't have duplicate queries (where values are not considered).

How can I do this?

user_521799 · Answer

jOOQ has a SQL parser / translator that can do these kinds of things. You can access it over the jOOQ website, for example: https://www.jooq.org/translate.

Fix your SQL query as it uses a wrong syntactic order of WHERE and INNER JOIN
Specify "Bind Variables" to be "Force Indexed", which parses literals and turns them into bind variables (all of them, not just specific ones)
Paste your query in the input textarea

You'll get:

Input

select * 
from tableName as t1 
inner join tableName2 as t2 
on t1.tableColumnId=t2.tableColumnId 
where t1.tableColumnId=4 and t1.tableColumnName='test'

Output

select *
from tableName as t1
  join tableName2 as t2
    on t1.tableColumnId = t2.tableColumnId
where (
  t1.tableColumnId = ?
  and t1.tableColumnName = ?
)

You can also use this API programmatically using:

import org.jooq.*;
import org.jooq.conf.*;
import org.jooq.impl.*;

public class Test {
    public static void main(String[] args) throws Exception {
        DSLContext ctx = DSL.using(
            new DefaultConfiguration().set(
                new Settings().withParamType(ParamType.FORCE_INDEXED)));

        Parser parser = ctx.parser();
        Query query = parser.parseQuery(
            "select * "
          + "from tableName as t1 "
          + "inner join tableName2 as t2 "
          + "on t1.tableColumnId=t2.tableColumnId "
          + "where t1.tableColumnId=4 and t1.tableColumnName='test'");

        System.out.println(ctx
            .renderContext()
            .paramType(ParamType.FORCE_INDEXED)
            .visit(query)
            .render());
    }
}

Disclaimer: I work for the vendor.

Parsing SQL query in Java

2 Answers