Search in sources :

Example 11 with JdbcParameter

use of net.sf.jsqlparser.expression.JdbcParameter in project JSqlParser by JSQLParser.

the class InsertTest method testRegularInsert.

@Test
public void testRegularInsert() throws JSQLParserException {
    String statement = "INSERT INTO mytable (col1, col2, col3) VALUES (?, 'sadfsd', 234)";
    Insert insert = (Insert) parserManager.parse(new StringReader(statement));
    assertEquals("mytable", insert.getTable().getName());
    assertEquals(3, insert.getColumns().size());
    assertEquals("col1", insert.getColumns().get(0).getColumnName());
    assertEquals("col2", insert.getColumns().get(1).getColumnName());
    assertEquals("col3", insert.getColumns().get(2).getColumnName());
    assertEquals(3, ((ExpressionList) insert.getItemsList()).getExpressions().size());
    assertTrue(((ExpressionList) insert.getItemsList()).getExpressions().get(0) instanceof JdbcParameter);
    assertEquals("sadfsd", ((StringValue) ((ExpressionList) insert.getItemsList()).getExpressions().get(1)).getValue());
    assertEquals(234, ((LongValue) ((ExpressionList) insert.getItemsList()).getExpressions().get(2)).getValue());
    assertEquals(statement, "" + insert);
    assertDeparse(new Insert().withTable(new Table("mytable")).addColumns(Arrays.asList(new Column("col1"), new Column("col2"), new Column("col3"))).withItemsList(new ExpressionList(new JdbcParameter(), new StringValue("sadfsd"), new LongValue().withValue(234))), statement);
    statement = "INSERT INTO myschema.mytable VALUES (?, ?, 2.3)";
    insert = (Insert) parserManager.parse(new StringReader(statement));
    assertEquals("myschema.mytable", insert.getTable().getFullyQualifiedName());
    assertEquals(3, insert.getItemsList(ExpressionList.class).getExpressions().size());
    assertTrue(((ExpressionList) insert.getItemsList()).getExpressions().get(0) instanceof JdbcParameter);
    assertEquals(2.3, ((DoubleValue) insert.getItemsList(ExpressionList.class).getExpressions().get(2)).getValue(), 0.0);
    assertEquals(statement, "" + insert);
}
Also used : Table(net.sf.jsqlparser.schema.Table) Column(net.sf.jsqlparser.schema.Column) JdbcParameter(net.sf.jsqlparser.expression.JdbcParameter) StringReader(java.io.StringReader) LongValue(net.sf.jsqlparser.expression.LongValue) StringValue(net.sf.jsqlparser.expression.StringValue) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) MultiExpressionList(net.sf.jsqlparser.expression.operators.relational.MultiExpressionList) Test(org.junit.jupiter.api.Test)

Example 12 with JdbcParameter

use of net.sf.jsqlparser.expression.JdbcParameter in project spanner-jdbc by olavloite.

the class CloudSpannerPreparedStatement method setPlainSelectParameters.

private void setPlainSelectParameters(PlainSelect plainSelect, com.google.cloud.spanner.Statement.Builder builder) {
    if (plainSelect.getFromItem() != null) {
        plainSelect.getFromItem().accept(new FromItemVisitorAdapter() {

            private int tableCount = 0;

            @Override
            public void visit(Table table) {
                tableCount++;
                if (tableCount == 1)
                    getParameterStore().setTable(unquoteIdentifier(table.getFullyQualifiedName()));
                else
                    getParameterStore().setTable(null);
            }

            @Override
            public void visit(SubSelect subSelect) {
                if (subSelect.getSelectBody() instanceof PlainSelect) {
                    setPlainSelectParameters((PlainSelect) subSelect.getSelectBody(), builder);
                } else {
                    subSelect.getSelectBody().accept(new SelectVisitorAdapter() {

                        @Override
                        public void visit(PlainSelect plainSelect) {
                            setPlainSelectParameters(plainSelect, builder);
                        }

                        @Override
                        public void visit(SetOperationList setOpList) {
                            for (SelectBody body : setOpList.getSelects()) {
                                setSelectParameters(body, builder);
                            }
                        }
                    });
                }
            }
        });
    }
    if (plainSelect.getSelectItems() != null) {
        for (SelectItem selectItem : plainSelect.getSelectItems()) {
            selectItem.accept(new SelectItemVisitorAdapter() {

                @Override
                public void visit(SelectExpressionItem item) {
                    item.getExpression().accept(new ExpressionVisitorAdapter() {

                        private String currentCol = null;

                        @Override
                        public void visit(Column col) {
                            currentCol = unquoteIdentifier(col.getFullyQualifiedName());
                        }

                        @Override
                        public void visit(JdbcParameter parameter) {
                            parameter.accept(new ValueBinderExpressionVisitorAdapter<>(getParameterStore(), builder.bind("p" + parameter.getIndex()), currentCol));
                            currentCol = null;
                        }

                        @Override
                        public void visit(SubSelect subSelect) {
                            setSelectParameters(subSelect.getSelectBody(), builder);
                        }
                    });
                }
            });
        }
    }
    setWhereParameters(plainSelect.getWhere(), builder);
    if (plainSelect.getLimit() != null) {
        setWhereParameters(plainSelect.getLimit().getRowCount(), builder);
    }
    if (plainSelect.getOffset() != null && plainSelect.getOffset().getOffsetJdbcParameter() != null) {
        ValueBinderExpressionVisitorAdapter<com.google.cloud.spanner.Statement.Builder> binder = new ValueBinderExpressionVisitorAdapter<>(getParameterStore(), builder.bind("p" + getParameterStore().getHighestIndex()), null);
        binder.setValue(getParameterStore().getParameter(getParameterStore().getHighestIndex()), Types.BIGINT);
        getParameterStore().setType(getParameterStore().getHighestIndex(), Types.BIGINT);
    }
}
Also used : Table(net.sf.jsqlparser.schema.Table) JdbcParameter(net.sf.jsqlparser.expression.JdbcParameter) SelectExpressionItem(net.sf.jsqlparser.statement.select.SelectExpressionItem) WriteBuilder(com.google.cloud.spanner.Mutation.WriteBuilder) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) SelectBody(net.sf.jsqlparser.statement.select.SelectBody) SetOperationList(net.sf.jsqlparser.statement.select.SetOperationList) SelectVisitorAdapter(net.sf.jsqlparser.statement.select.SelectVisitorAdapter) SelectItemVisitorAdapter(net.sf.jsqlparser.statement.select.SelectItemVisitorAdapter) Column(net.sf.jsqlparser.schema.Column) SelectItem(net.sf.jsqlparser.statement.select.SelectItem) FromItemVisitorAdapter(net.sf.jsqlparser.statement.select.FromItemVisitorAdapter) ExpressionVisitorAdapter(net.sf.jsqlparser.expression.ExpressionVisitorAdapter) SubSelect(net.sf.jsqlparser.statement.select.SubSelect)

Example 13 with JdbcParameter

use of net.sf.jsqlparser.expression.JdbcParameter in project JSqlParser by JSQLParser.

the class InsertTest method testRegularInsert.

@Test
public void testRegularInsert() throws JSQLParserException {
    String statement = "INSERT INTO mytable (col1, col2, col3) VALUES (?, 'sadfsd', 234)";
    Insert insert = (Insert) parserManager.parse(new StringReader(statement));
    assertEquals("mytable", insert.getTable().getName());
    assertEquals(3, insert.getColumns().size());
    assertEquals("col1", ((Column) insert.getColumns().get(0)).getColumnName());
    assertEquals("col2", ((Column) insert.getColumns().get(1)).getColumnName());
    assertEquals("col3", ((Column) insert.getColumns().get(2)).getColumnName());
    assertEquals(3, ((ExpressionList) insert.getItemsList()).getExpressions().size());
    assertTrue(((ExpressionList) insert.getItemsList()).getExpressions().get(0) instanceof JdbcParameter);
    assertEquals("sadfsd", ((StringValue) ((ExpressionList) insert.getItemsList()).getExpressions().get(1)).getValue());
    assertEquals(234, ((LongValue) ((ExpressionList) insert.getItemsList()).getExpressions().get(2)).getValue());
    assertEquals(statement, "" + insert);
    statement = "INSERT INTO myschema.mytable VALUES (?, ?, 2.3)";
    insert = (Insert) parserManager.parse(new StringReader(statement));
    assertEquals("myschema.mytable", insert.getTable().getFullyQualifiedName());
    assertEquals(3, ((ExpressionList) insert.getItemsList()).getExpressions().size());
    assertTrue(((ExpressionList) insert.getItemsList()).getExpressions().get(0) instanceof JdbcParameter);
    assertEquals(2.3, ((DoubleValue) ((ExpressionList) insert.getItemsList()).getExpressions().get(2)).getValue(), 0.0);
    assertEquals(statement, "" + insert);
}
Also used : JdbcParameter(net.sf.jsqlparser.expression.JdbcParameter) StringReader(java.io.StringReader) Insert(net.sf.jsqlparser.statement.insert.Insert) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) Test(org.junit.Test)

Example 14 with JdbcParameter

use of net.sf.jsqlparser.expression.JdbcParameter in project JSqlParser by JSQLParser.

the class ReplaceTest method testReplaceSyntax2.

@Test
public void testReplaceSyntax2() throws JSQLParserException {
    String statement = "REPLACE mytable (col1, col2, col3) VALUES ('as', ?, 565)";
    Replace replace = (Replace) PARSER_MANAGER.parse(new StringReader(statement));
    assertEquals("mytable", replace.getTable().getName());
    assertEquals(3, replace.getColumns().size());
    assertEquals("col1", ((Column) replace.getColumns().get(0)).getColumnName());
    assertEquals("col2", ((Column) replace.getColumns().get(1)).getColumnName());
    assertEquals("col3", ((Column) replace.getColumns().get(2)).getColumnName());
    assertEquals("as", ((StringValue) ((ExpressionList) replace.getItemsList()).getExpressions().get(0)).getValue());
    assertTrue(((ExpressionList) replace.getItemsList()).getExpressions().get(1) instanceof JdbcParameter);
    assertEquals(565, ((LongValue) ((ExpressionList) replace.getItemsList()).getExpressions().get(2)).getValue());
    assertEquals(statement, "" + replace);
}
Also used : Replace(net.sf.jsqlparser.statement.replace.Replace) JdbcParameter(net.sf.jsqlparser.expression.JdbcParameter) StringReader(java.io.StringReader) Test(org.junit.Test)

Example 15 with JdbcParameter

use of net.sf.jsqlparser.expression.JdbcParameter in project herddb by diennea.

the class HerdDBCLI method rewriteQuery.

private static QueryWithParameters rewriteQuery(String query, TableSpaceMapper mapper, boolean frommysqldump) throws ScriptException {
    try {
        List<Object> parameters = new ArrayList<>();
        if (frommysqldump && query.startsWith("INSERT INTO")) {
            // this is faster than CCJSqlParserUtil and will allow the cache to work at "client-side" too
            QueryWithParameters rewriteSimpleInsertStatement = MySqlDumpInsertStatementRewriter.rewriteSimpleInsertStatement(query);
            if (rewriteSimpleInsertStatement != null) {
                query = rewriteSimpleInsertStatement.query;
                parameters.addAll(rewriteSimpleInsertStatement.jdbcParameters);
                String schema = mapper == null ? null : mapper.getTableSpace(rewriteSimpleInsertStatement.tableName);
                return new QueryWithParameters(query, rewriteSimpleInsertStatement.tableName, parameters, schema);
            }
        }
        String _query = query;
        net.sf.jsqlparser.statement.Statement stmt = PARSER_CACHE.get(_query, () -> {
            return CCJSqlParserUtil.parse(_query);
        });
        if (stmt instanceof Insert) {
            boolean somethingdone = false;
            Insert insert = (Insert) stmt;
            ItemsList itemlist = insert.getItemsList();
            if (itemlist instanceof ExpressionList) {
                ExpressionList list = (ExpressionList) itemlist;
                List<Expression> expressions = list.getExpressions();
                for (int i = 0; i < expressions.size(); i++) {
                    Expression e = expressions.get(i);
                    boolean done = false;
                    if (e instanceof StringValue) {
                        StringValue sv = (StringValue) e;
                        parameters.add(sv.getValue());
                        done = true;
                    } else if (e instanceof LongValue) {
                        LongValue sv = (LongValue) e;
                        parameters.add(sv.getValue());
                        done = true;
                    } else if (e instanceof NullValue) {
                        NullValue sv = (NullValue) e;
                        parameters.add(null);
                        done = true;
                    } else if (e instanceof TimestampValue) {
                        TimestampValue sv = (TimestampValue) e;
                        parameters.add(sv.getValue());
                        done = true;
                    } else if (e instanceof DoubleValue) {
                        DoubleValue sv = (DoubleValue) e;
                        parameters.add(sv.getValue());
                        done = true;
                    }
                    if (done) {
                        somethingdone = true;
                        expressions.set(i, new JdbcParameter());
                    }
                }
                if (somethingdone) {
                    StringBuilder queryResult = new StringBuilder();
                    InsertDeParser deparser = new InsertDeParser(new ExpressionDeParser(null, queryResult), null, queryResult);
                    deparser.deParse(insert);
                    query = queryResult.toString();
                }
            } else if (itemlist instanceof MultiExpressionList) {
                MultiExpressionList mlist = (MultiExpressionList) itemlist;
                List<ExpressionList> lists = mlist.getExprList();
                for (ExpressionList list : lists) {
                    List<Expression> expressions = list.getExpressions();
                    for (int i = 0; i < expressions.size(); i++) {
                        Expression e = expressions.get(i);
                        boolean done = false;
                        if (e instanceof StringValue) {
                            StringValue sv = (StringValue) e;
                            parameters.add(sv.getValue());
                            done = true;
                        } else if (e instanceof LongValue) {
                            LongValue sv = (LongValue) e;
                            parameters.add(sv.getValue());
                            done = true;
                        } else if (e instanceof NullValue) {
                            NullValue sv = (NullValue) e;
                            parameters.add(null);
                            done = true;
                        } else if (e instanceof TimestampValue) {
                            TimestampValue sv = (TimestampValue) e;
                            parameters.add(sv.getValue());
                            done = true;
                        } else if (e instanceof DoubleValue) {
                            DoubleValue sv = (DoubleValue) e;
                            parameters.add(sv.getValue());
                            done = true;
                        }
                        if (done) {
                            somethingdone = true;
                            expressions.set(i, new JdbcParameter());
                        }
                    }
                }
                if (somethingdone) {
                    StringBuilder queryResult = new StringBuilder();
                    InsertDeParser deparser = new InsertDeParser(new ExpressionDeParser(null, queryResult), null, queryResult);
                    deparser.deParse(insert);
                    query = queryResult.toString();
                }
            }
            String schema = mapper == null ? null : mapper.getTableSpace(stmt);
            return new QueryWithParameters(query, null, parameters, schema);
        } else {
            String schema = mapper == null ? null : mapper.getTableSpace(stmt);
            return new QueryWithParameters(query, null, Collections.emptyList(), schema);
        }
    } catch (ExecutionException err) {
        System.out.println("error for query: " + query + " -> " + err.getCause());
        return null;
    }
}
Also used : ItemsList(net.sf.jsqlparser.expression.operators.relational.ItemsList) InsertDeParser(net.sf.jsqlparser.util.deparser.InsertDeParser) ArrayList(java.util.ArrayList) Insert(net.sf.jsqlparser.statement.insert.Insert) NullValue(net.sf.jsqlparser.expression.NullValue) TimestampValue(net.sf.jsqlparser.expression.TimestampValue) MultiExpressionList(net.sf.jsqlparser.expression.operators.relational.MultiExpressionList) ItemsList(net.sf.jsqlparser.expression.operators.relational.ItemsList) ArrayList(java.util.ArrayList) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) MultiExpressionList(net.sf.jsqlparser.expression.operators.relational.MultiExpressionList) List(java.util.List) StringValue(net.sf.jsqlparser.expression.StringValue) ExecutionException(java.util.concurrent.ExecutionException) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) MultiExpressionList(net.sf.jsqlparser.expression.operators.relational.MultiExpressionList) JdbcParameter(net.sf.jsqlparser.expression.JdbcParameter) Expression(net.sf.jsqlparser.expression.Expression) DoubleValue(net.sf.jsqlparser.expression.DoubleValue) LongValue(net.sf.jsqlparser.expression.LongValue) ExpressionDeParser(net.sf.jsqlparser.util.deparser.ExpressionDeParser)

Aggregations

JdbcParameter (net.sf.jsqlparser.expression.JdbcParameter)15 Expression (net.sf.jsqlparser.expression.Expression)8 ExpressionList (net.sf.jsqlparser.expression.operators.relational.ExpressionList)8 ArrayList (java.util.ArrayList)6 BinaryExpression (net.sf.jsqlparser.expression.BinaryExpression)6 AndExpression (net.sf.jsqlparser.expression.operators.conditional.AndExpression)6 StringReader (java.io.StringReader)5 List (java.util.List)5 SignedExpression (net.sf.jsqlparser.expression.SignedExpression)5 StringValue (net.sf.jsqlparser.expression.StringValue)5 StatementExecutionException (herddb.model.StatementExecutionException)4 LongValue (net.sf.jsqlparser.expression.LongValue)4 OrExpression (net.sf.jsqlparser.expression.operators.conditional.OrExpression)4 InExpression (net.sf.jsqlparser.expression.operators.relational.InExpression)4 ItemsList (net.sf.jsqlparser.expression.operators.relational.ItemsList)4 MultiExpressionList (net.sf.jsqlparser.expression.operators.relational.MultiExpressionList)4 PlainSelect (net.sf.jsqlparser.statement.select.PlainSelect)4 Map (java.util.Map)3 CaseExpression (net.sf.jsqlparser.expression.CaseExpression)3 DoubleValue (net.sf.jsqlparser.expression.DoubleValue)3