JsqlParser how to rewrite a expression

Question

I have a sql where cluase:

a > 1 and b < 1 and c = 3

In this case I want to remove the a>1, how could I rewrite it to

1 = 1 and b < 1 and c =3

I have try the ExpressionVisitor, but couldn't rewrite the Expression

Answer 1

You can simply replace the where part of a SQL by using something like:

((PlainSelect)selectBody).setWhere(CCJSqlParserUtil.parseCondExpression("mycol = 10"));

but if you want to use the where expression itself and want to find in it the expression a > 1, then you have to use something like

Select stmt = (Select) CCJSqlParserUtil.parse("select * from a where a > 1 and b < 1 and c = 3");
System.out.println("before " + stmt.toString());

((PlainSelect) stmt.getSelectBody()).getWhere().accept(new ExpressionVisitorAdapter() {
    @Override
    public void visitBinaryExpression(BinaryExpression expr) {
        if ("a > 1".equals(expr.getLeftExpression().toString())) {
            try {
                expr.setLeftExpression(CCJSqlParserUtil.parseCondExpression("1=1"));
            } catch (JSQLParserException ex) {
                Logger.getLogger(SimpleSqlParser39ReplaceExpressionInWhere.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        super.visitBinaryExpression(expr);
    }
});

System.out.println("after " + stmt.toString());

This is not a complete solution since you have to check the right expression as well. Since you are indeed not able to modify the binary expression a > 1 itself you have to look for the enclosing expression and replace it there. So that's the main idea.

By the way this is the output of this little snippet:

before SELECT * FROM a WHERE a > 1 AND b < 1 AND c = 3
after SELECT * FROM a WHERE 1 = 1 AND b < 1 AND c = 3