How to add new condition with JSqlParser?

Question

I want to add new condition to my sql. For example If query is;

SELECT EMP_ID, FIRST_NAME FROM EMPLOYEES;

I can add new where cause with this codes;

@Override
protected void setLimit(final PlainSelect ps,final long rowLimit) {
    Expression where = ps.getWhere();
    if(where == null) {
         try {
            where = CCJSqlParserUtil.parseCondExpression("ROWNUM < " + (rowLimit+1) );
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }
    ps.setWhere(where);
}

This codes changes the query to

SELECT EMP_ID, FIRST_NAME FROM EMPLOYEES WHERE ROWNUM < 10;

...

But the problem is when query is like;

 SELECT EMP_ID, FIRST_NAME FROM EMPLOYEES WHERE EMP_ID = 10 AND EMP_ID = 10 AND FIRST_NAME = 'Steven';

I cannot add new condition to current condition. Is there a way to change this query to like;

 SELECT EMP_ID, FIRST_NAME FROM EMPLOYEES WHERE EMP_ID = 10 AND EMP_ID = 10 AND FIRST_NAME = 'Steven' AND ROWNUM < 10;

Is there a way to do that with JSqlParser?

score 2 · Accepted Answer · answered Jun 24 '20 at 13:35

So here are two possible solutions for this (this time a bit more than my previous comment :) ):

    var sql = "SELECT EMP_ID, FIRST_NAME FROM EMPLOYEES WHERE EMP_ID = 10 AND EMP_ID = 10 AND FIRST_NAME = 'Steven'";
    var select = (Select) CCJSqlParserUtil.parse(sql);
    var plainselect = (PlainSelect) select.getSelectBody();
    
    //1. add another condition by deparsing the old one and integrate it into
    //a new expression, which is parsed and then set into your statement.        
    var where = CCJSqlParserUtil.parseCondExpression(plainselect.getWhere() 
           + " and ROWNUM < 10");
   
    //2. second is building a new AndExpression and sets the left item to the
    //original where condition and adds a new condition as the right item.
    var where2 = new AndExpression(plainselect.getWhere(),
           CCJSqlParserUtil.parseCondExpression("ROWNUM < 10"));
    
    //output both versions. in fact they should and are identical.
    plainselect.setWhere(where);
    System.out.println(select.toString());
    
    plainselect.setWhere(where2);
    System.out.println(select.toString());

This code outputs the following twice:

SELECT EMP_ID, FIRST_NAME FROM EMPLOYEES WHERE EMP_ID = 10 AND EMP_ID = 10 AND FIRST_NAME = 'Steven' AND ROWNUM < 10

This is a Java >=V10 code. However with slight adjustments it runs using JDK 8 as well.

This variant just transforms the actual where statement into a string and then build with it what condition you want. This resulting string expression is then parsed using JSqlParser (CCJSqlParserUtil.parseCondExpression) and this resulting expression is set into the parsed plain select statement (plainselect.setWhere)
The second variant uses the parsed objects and build an AndExpression around it. The expression to add is constructed as well or parsed using CCJSqlParserUtil.parseCondExpression again and added to the constructed AndExpression as well.

It is important to know, that this constructs some kind of JSqlParser object tree items and does not map one to one to the actual semantics of your SQL. For this you should parenthesis to ensure this, again using type 1 or 2. By the way parenthesis are constructed using the Parenthesis object.

So using this technique you can do all kinds of changes to JSqlParsers object tree.

How to add new condition with JSqlParser?

1 Answers1

Linked