How can I parse and evaluate WHERE expression?

[pal548]

Hello!

I`m trying to use JSqlParser to parse custom sql-syntax user input expressions. I have searched all the internet but there is no info regarding my case.

[manticore-projects]

Greetings.
Please provide and example of your SQL (as simple as possible).

[pal548]

Thx for quick reply, the expressions are like:

"aaa = 1 or bbb = 2 and case when ccc = 3 then 4 else 5 end = 6"

I need to compile it and be able to precess with various values of that variables.

[manticore-projects]

Greetings! There are two different methods to walk through the AST down to the leaves

1. Access the Properties of the Statement explicitly using if ( object instanceof Class )
2. Using the Visitor Interfaces

The code below illustrates both ways:

package com.manticore.jsqlformatter;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitorAdapter;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.StatementVisitorAdapter;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectVisitorAdapter;
import org.junit.Test;

import java.util.logging.Logger;

public class ParserTest {
    Logger LOGGER = Logger.getLogger(ParserTest.class.getName());


    @Test
    public void testExpressionClassInstances() throws JSQLParserException {
        String sqlStr = "SELECT * FROM test WHERE aaa = 1 or bbb = 2 and case when ccc = 3 then 4 else 5 end = 6";
        Statement statement = CCJSqlParserUtil.parse(sqlStr);

        if (statement instanceof Select) {
            Select select = (Select) statement;
            SelectBody selectBody = select.getSelectBody();

            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) selectBody;
                Expression whereExpression = plainSelect.getWhere();

                if (whereExpression instanceof AndExpression) {
                    AndExpression andExpression = (AndExpression) whereExpression;
                    LOGGER.info("Found AND Expression: " + andExpression);
                } else if (whereExpression instanceof OrExpression) {
                    OrExpression orExpression = (OrExpression) whereExpression;
                    LOGGER.info("Found OR Expression: " + orExpression);
                }
            }
        }
    }

    @Test
    public void testExpressionVisitor() throws JSQLParserException {
        String sqlStr = "SELECT * FROM test WHERE aaa = 1 or bbb = 2 and case when ccc = 3 then 4 else 5 end = 6";
        Statement statement = CCJSqlParserUtil.parse(sqlStr);

        ExpressionVisitorAdapter expressionVisitorAdapter = new ExpressionVisitorAdapter() {
            @Override
            public void visit(OrExpression orExpression) {
                super.visit(orExpression);
                LOGGER.info("Found OR Expression: " + orExpression);
            }

            @Override
            public void visit(AndExpression andExpression) {
                super.visit(andExpression);
                LOGGER.info("Found AND Expression: " + andExpression);
            }
        };

        SelectVisitorAdapter selectVisitorAdapter = new SelectVisitorAdapter() {
            @Override
            public void visit(PlainSelect plainSelect) {
                plainSelect.getWhere().accept(expressionVisitorAdapter);
            }
        };

        StatementVisitorAdapter statementVisitorAdapter = new StatementVisitorAdapter() {
            @Override
            public void visit(Select select) {
                select.getSelectBody().accept(selectVisitorAdapter);
            }
        };

        statement.accept(statementVisitorAdapter);
    }
}

[pal548]

How can I evaluate the expression? I need to write some tree manually? And then code all possible operators in where?

[manticore-projects]

When you use the ExpressionVisitor then you will need to implement all its methods to cover each possible expression.
You can also look into the method appendExpressions(...) for an illustration.

[pal548]

OK, thanks! But I decided to use H2 in-memory instance to evaluate sql expressions)