Fully parsing where clause with JSqlParser

Question

I would like to completely, and succinctly, parse a SQL where clause using JSqlParser. It's easy to parse it into individual conditional statements like so

String whereClause = "a=3 AND b=4 AND c=5";
Expression expr = CCJSqlParserUtil.parseCondExpression(whereClause);
expr.accept(new ExpressionVisitorAdapter() {

    @Override
    public void visit(AndExpression expr) {
        if (expr.getLeftExpression() instanceof AndExpression) {
            expr.getLeftExpression().accept(this);
        } else if ((expr.getLeftExpression() instanceof EqualsTo)){
            System.out.println(expr.getLeftExpression());
        }
        System.out.println(expr.getRightExpression());
    }
 });

Which will produce the output:

a=3
b=4
c=5

What I want is to get left side, operator and right side of each individual expression so that I can put the values in some existing filter objects.

I know you can override the visit function for every type of operator like so:

expr.accept(new ExpressionVisitorAdapter() {

@Override
public void visit(AndExpression expr) {
    if (expr.getLeftExpression() instanceof AndExpression) {
        expr.getLeftExpression().accept(this);
    } else if ((expr.getLeftExpression() instanceof EqualsTo)){
        expr.getLeftExpression().accept(this);
        System.out.println(expr.getLeftExpression());
    }
    expr.getRightExpression().accept(this);
    System.out.println(expr.getRightExpression());
}
@Override
public void visit(EqualsTo expr) {
    System.out.println(expr.getLeftExpression());
    System.out.println(expr.getStringExpression());
    System.out.println(expr.getRightExpression());
}

});

Which will get you this output:

a
=
3
a=3
b
=
4
b=4
c
=
5
c=5

But that only covers EqualsTo condition statements that are ANDed together. As you can see, you would have to create an if statement for every logical operator and override the visit() function for every comparison operator. Is there a simpler way of doing this?

score 4 · Accepted Answer · answered Oct 18 '17 at 10:59

Using ExpressionVisitorAdapter, you could overwrite

protected void visitBinaryExpression(BinaryExpression expr)

which is called for every of those expressions.

Operations are BinaryExpressions as well. So you need to check for the type by instanceof ComparisonOperator. This will handle all comparators and not operations like + *.

This should do it. I removed the visit for AND and extended your whereClause with some more expressions.

    String whereClause = "a=3 AND b=4 AND c=5 AND d>5 AND x<10";
    Expression expr = CCJSqlParserUtil.parseCondExpression(whereClause);
    expr.accept(new ExpressionVisitorAdapter() {

        @Override
        protected void visitBinaryExpression(BinaryExpression expr) {
            if (expr instanceof ComparisonOperator) {
                System.out.println("left=" + expr.getLeftExpression() + "  op=" +  expr.getStringExpression() + "  right=" + expr.getRightExpression());
            }

            super.visitBinaryExpression(expr); 
        }
    });

This outputs:

left=a  op==  right=3
left=b  op==  right=4
left=c  op==  right=5
left=d  op=>  right=5
left=x  op=<  right=10

Thanks for the answer. If I could up-vote it I would. It was a big help. However, your solution doesn't completely parse the where clause. It only shows the comparisons themselves. I will post a more complete answer that I came to. — codering, Oct 19 '17 at 14:44
Ok. I thought my solution was what you wanted to achieve. Missinterpretation. So I voted you up. :) — wumpz, Oct 19 '17 at 19:58

score 1 · Answer 2 · answered Oct 19 '17 at 15:05

After more development and help from wumpz' answer, I have a solution. The function parseWhereClauseToFilter() will parse the where clause with logical operators and comparisons. I haven't tested operators like BETWEEN and IN, etc. but I think the solution would be similar. I found a SO question here which was also a help.

The code below

public static void parseWhereClauseToFilter(String whereClause ){

    try {
        Expression expr = CCJSqlParserUtil.parseCondExpression(whereClause);
        FilterExpressionVisitorAdapter adapter = new FilterExpressionVisitorAdapter();
        expr.accept(adapter);

    } catch (JSQLParserException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
} 
public class FilterExpressionVisitorAdapter extends ExpressionVisitorAdapter{
    int depth = 0;
    public void processLogicalExpression( BinaryExpression expr, String logic){
        System.out.println(StringUtils.repeat("-", depth) + logic);

        depth++;
        expr.getLeftExpression().accept(this);
        expr.getRightExpression().accept(this);
        if(  depth != 0 ){
            depth--;
        }
    }

    @Override
    protected void visitBinaryExpression(BinaryExpression expr) {
        if (expr instanceof ComparisonOperator) {
            System.out.println(StringUtils.repeat("-", depth) + 
                "left=" + expr.getLeftExpression() + 
                "  op=" +  expr.getStringExpression() + 
                "  right=" + expr.getRightExpression() );
        } 
        super.visitBinaryExpression(expr); 
    }

    @Override
    public void visit(AndExpression expr) {
        processLogicalExpression(expr, "AND");

    }
    @Override
    public void visit(OrExpression expr) {
        processLogicalExpression(expr, "OR");
    }
    @Override
    public void visit(Parenthesis parenthesis) {
        parenthesis.getExpression().accept(this);
    }

}

will produce the following output for the input string of "a=3 and (b=4 and c=5) and d>5"

AND
-AND
--left=a  op==  right=3
--AND
---left=b  op==  right=4
---left=c  op==  right=5
-left=d  op=>  right=5

Fully parsing where clause with JSqlParser

2 Answers2