Add statements to sql query in jsqlparser

Question

I was wondering if it is possible to add an expression to a sql query using jsqlparser. For example I would like to add a column and a value to a sql statement:

original query: "INSERT INTO frontend(in_reply_to) VALUES (email);"

modified query: "INSERT INTO frontend(in_reply_to, user_id) VALUES (email, 123)"

I managed to modify a column name, but not to add one.

Here is the code that I have:

        ExtendedItemsListVisitor visitor = new ExtendedItemsListVisitor() {
            private List expressions = null;

            public List getExpressions() {
                return this.expressions;
            }

            public void setExpressions(ExpressionList expressionList) {
                this.expressions = expressionList.getExpressions();
            }

            public void visit(SubSelect subSelect) {
            }

            public void visit(ExpressionList expressionList) {
                this.expressions = expressionList.getExpressions();
            }
        };

        ItemsList itemsList = ((Insert)statement).getItemsList();
        itemsList.accept(visitor);
        ExpressionList expressions = (ExpressionList)visitor1.getExpressions();

        Expression expression = new StringValue(newValue);
        ExpressionVisitor visitor = new SetValueExpressionVisitor(newValue);

score 2 · Answer 1 · answered Feb 05 '14 at 23:09

I know a little bit late. But here is a solution. First we create a test insert statement:

Insert insert = (Insert)CCJSqlParserUtil.parse("insert into mytable (col1) values (1)");
System.out.println(insert.toString());

Now the variant using the visitor pattern to add a new value:

insert.getColumns().add(new Column("col2"));
insert.getItemsList().accept(new ItemsListVisitor() {

        public void visit(SubSelect subSelect) {
            throw new UnsupportedOperationException("Not supported yet.");
        }

        public void visit(ExpressionList expressionList) {
            expressionList.getExpressions().add(new LongValue(5));
        }

        public void visit(MultiExpressionList multiExprList) {
            throw new UnsupportedOperationException("Not supported yet.");
        }
});
System.out.println(insert.toString());

But using the visitor pattern is a little bit of overkill to achieve it. Here is the simple solution:

insert.getColumns().add(new Column("col3"));
((ExpressionList)insert.getItemsList()).getExpressions().add(new LongValue(10));
System.out.println(insert.toString());

So yes one can modify the SQL hierarchy of JSQLParser. I am using JSQLParser 0.8.9 SNAPSHOT. There are modification in progress to improve this modifiability.

When I tested similar implementation for the shorter version you ave, a ClassCastException was thrown - "net.sf.jsqlparser.expression.operators.conditional.AndExpression cannot be cast to net.sf.jsqlparser.expression.operators.relational.ExpressionList". I believe the compact version is not the best fit. Note: I am using the first time this library and may not understood it deeply. My SQL is: UPDATE MyTable SET COL1 = CURRENT_TIMESTAMP, COL2 = 'some_value' WHERE ID = 22331 AND PARENT = 11220; — Victor, Aug 20 '18 at 09:52
The short version is only for insert. You have to modify it working for updates. — wumpz, Aug 20 '18 at 12:08

Add statements to sql query in jsqlparser

1 Answers1