Search in sources :

Example 1 with ExpressionList

use of net.sf.jsqlparser.expression.operators.relational.ExpressionList in project dbeaver by serge-rider.

the class SQLQueryTransformerCount method transformQuery.

@Override
public SQLQuery transformQuery(SQLDataSource dataSource, SQLQuery query) throws DBException {
    try {
        Statement statement = CCJSqlParserUtil.parse(query.getQuery());
        if (statement instanceof Select && ((Select) statement).getSelectBody() instanceof PlainSelect) {
            PlainSelect select = (PlainSelect) ((Select) statement).getSelectBody();
            List<SelectItem> selectItems = new ArrayList<>();
            Function countFunc = new Function();
            countFunc.setName("count");
            countFunc.setParameters(new ExpressionList(Collections.<Expression>singletonList(new Column("*"))));
            SelectItem countItem = new SelectExpressionItem(countFunc);
            selectItems.add(countItem);
            select.setSelectItems(selectItems);
            return new SQLQuery(dataSource, select.toString(), query, false);
        } else {
            throw new DBException("Query [" + query.getQuery() + "] can't be modified");
        }
    } catch (JSQLParserException e) {
        throw new DBException("Can't transform query to SELECT count(*)", e);
    }
}
Also used : DBException(org.jkiss.dbeaver.DBException) Statement(net.sf.jsqlparser.statement.Statement) SelectExpressionItem(net.sf.jsqlparser.statement.select.SelectExpressionItem) JSQLParserException(net.sf.jsqlparser.JSQLParserException) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) ArrayList(java.util.ArrayList) SQLQuery(org.jkiss.dbeaver.model.sql.SQLQuery) Function(net.sf.jsqlparser.expression.Function) Expression(net.sf.jsqlparser.expression.Expression) Column(net.sf.jsqlparser.schema.Column) SelectItem(net.sf.jsqlparser.statement.select.SelectItem) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) Select(net.sf.jsqlparser.statement.select.Select) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList)

Example 2 with ExpressionList

use of net.sf.jsqlparser.expression.operators.relational.ExpressionList in project herddb by diennea.

the class SQLPlanner method buildInsertStatement.

private ExecutionPlan buildInsertStatement(String defaultTableSpace, Insert s, boolean returnValues) throws StatementExecutionException {
    String tableSpace = s.getTable().getSchemaName();
    String tableName = s.getTable().getName();
    if (tableSpace == null) {
        tableSpace = defaultTableSpace;
    }
    TableSpaceManager tableSpaceManager = manager.getTableSpaceManager(tableSpace);
    if (tableSpaceManager == null) {
        throw new StatementExecutionException("no such tablespace " + tableSpace + " here at " + manager.getNodeId());
    }
    AbstractTableManager tableManager = tableSpaceManager.getTableManager(tableName);
    if (tableManager == null) {
        throw new StatementExecutionException("no such table " + tableName + " in tablespace " + tableSpace);
    }
    Table table = tableManager.getTable();
    ItemsList itemlist = s.getItemsList();
    if (itemlist instanceof ExpressionList) {
        int index = 0;
        List<Expression> keyValueExpression = new ArrayList<>();
        List<String> keyExpressionToColumn = new ArrayList<>();
        List<CompiledSQLExpression> valuesExpressions = new ArrayList<>();
        List<net.sf.jsqlparser.schema.Column> valuesColumns = new ArrayList<>();
        ExpressionList list = (ExpressionList) itemlist;
        if (s.getColumns() != null) {
            for (net.sf.jsqlparser.schema.Column c : s.getColumns()) {
                Column column = table.getColumn(c.getColumnName());
                if (column == null) {
                    throw new StatementExecutionException("no such column " + c.getColumnName() + " in table " + tableName + " in tablespace " + tableSpace);
                }
                Expression expression;
                try {
                    expression = list.getExpressions().get(index++);
                } catch (IndexOutOfBoundsException badQuery) {
                    throw new StatementExecutionException("bad number of VALUES in INSERT clause");
                }
                if (table.isPrimaryKeyColumn(column.name)) {
                    keyExpressionToColumn.add(column.name);
                    keyValueExpression.add(expression);
                }
                valuesColumns.add(c);
                valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
            }
        } else {
            for (Column column : table.columns) {
                Expression expression = list.getExpressions().get(index++);
                if (table.isPrimaryKeyColumn(column.name)) {
                    keyExpressionToColumn.add(column.name);
                    keyValueExpression.add(expression);
                }
                valuesColumns.add(new net.sf.jsqlparser.schema.Column(column.name));
                valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
            }
        }
        RecordFunction keyfunction;
        if (keyValueExpression.isEmpty() && table.auto_increment) {
            keyfunction = new AutoIncrementPrimaryKeyRecordFunction();
        } else {
            if (keyValueExpression.size() != table.primaryKey.length) {
                throw new StatementExecutionException("you must set a value for the primary key (expressions=" + keyValueExpression.size() + ")");
            }
            keyfunction = new SQLRecordKeyFunction(table, keyExpressionToColumn, keyValueExpression);
        }
        RecordFunction valuesfunction = new SQLRecordFunction(table, valuesColumns, valuesExpressions);
        try {
            return ExecutionPlan.simple(new InsertStatement(tableSpace, tableName, keyfunction, valuesfunction).setReturnValues(returnValues));
        } catch (IllegalArgumentException err) {
            throw new StatementExecutionException(err);
        }
    } else if (itemlist instanceof MultiExpressionList) {
        if (returnValues) {
            throw new StatementExecutionException("cannot 'return values' on multi-values insert");
        }
        MultiExpressionList multilist = (MultiExpressionList) itemlist;
        List<InsertStatement> inserts = new ArrayList<>();
        for (ExpressionList list : multilist.getExprList()) {
            List<Expression> keyValueExpression = new ArrayList<>();
            List<String> keyExpressionToColumn = new ArrayList<>();
            List<CompiledSQLExpression> valuesExpressions = new ArrayList<>();
            List<net.sf.jsqlparser.schema.Column> valuesColumns = new ArrayList<>();
            int index = 0;
            if (s.getColumns() != null) {
                for (net.sf.jsqlparser.schema.Column c : s.getColumns()) {
                    Column column = table.getColumn(c.getColumnName());
                    if (column == null) {
                        throw new StatementExecutionException("no such column " + c.getColumnName() + " in table " + tableName + " in tablespace " + tableSpace);
                    }
                    Expression expression;
                    try {
                        expression = list.getExpressions().get(index++);
                    } catch (IndexOutOfBoundsException badQuery) {
                        throw new StatementExecutionException("bad number of VALUES in INSERT clause");
                    }
                    if (table.isPrimaryKeyColumn(column.name)) {
                        keyExpressionToColumn.add(column.name);
                        keyValueExpression.add(expression);
                    }
                    valuesColumns.add(c);
                    valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
                }
            } else {
                for (Column column : table.columns) {
                    Expression expression = list.getExpressions().get(index++);
                    if (table.isPrimaryKeyColumn(column.name)) {
                        keyExpressionToColumn.add(column.name);
                        keyValueExpression.add(expression);
                    }
                    valuesColumns.add(new net.sf.jsqlparser.schema.Column(column.name));
                    valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
                }
            }
            RecordFunction keyfunction;
            if (keyValueExpression.isEmpty() && table.auto_increment) {
                keyfunction = new AutoIncrementPrimaryKeyRecordFunction();
            } else {
                if (keyValueExpression.size() != table.primaryKey.length) {
                    throw new StatementExecutionException("you must set a value for the primary key (expressions=" + keyValueExpression.size() + ")");
                }
                keyfunction = new SQLRecordKeyFunction(table, keyExpressionToColumn, keyValueExpression);
            }
            RecordFunction valuesfunction = new SQLRecordFunction(table, valuesColumns, valuesExpressions);
            InsertStatement insert = new InsertStatement(tableSpace, tableName, keyfunction, valuesfunction);
            inserts.add(insert);
        }
        try {
            return ExecutionPlan.multiInsert(inserts);
        } catch (IllegalArgumentException err) {
            throw new StatementExecutionException(err);
        }
    } else {
        List<Expression> keyValueExpression = new ArrayList<>();
        List<String> keyExpressionToColumn = new ArrayList<>();
        List<CompiledSQLExpression> valuesExpressions = new ArrayList<>();
        List<net.sf.jsqlparser.schema.Column> valuesColumns = new ArrayList<>();
        Select select = s.getSelect();
        ExecutionPlan datasource = buildSelectStatement(defaultTableSpace, select, true, -1);
        if (s.getColumns() == null) {
            throw new StatementExecutionException("for INSERT ... SELECT you have to declare the columns to be filled in (use INSERT INTO TABLE(c,c,c,) SELECT .....)");
        }
        IntHolder holder = new IntHolder(1);
        for (net.sf.jsqlparser.schema.Column c : s.getColumns()) {
            Column column = table.getColumn(c.getColumnName());
            if (column == null) {
                throw new StatementExecutionException("no such column " + c.getColumnName() + " in table " + tableName + " in tablespace " + tableSpace);
            }
            JdbcParameter readFromResultSetAsJdbcParameter = new JdbcParameter();
            readFromResultSetAsJdbcParameter.setIndex(holder.value++);
            if (table.isPrimaryKeyColumn(column.name)) {
                keyExpressionToColumn.add(column.name);
                keyValueExpression.add(readFromResultSetAsJdbcParameter);
            }
            valuesColumns.add(c);
            valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, readFromResultSetAsJdbcParameter));
        }
        RecordFunction keyfunction;
        if (keyValueExpression.isEmpty() && table.auto_increment) {
            keyfunction = new AutoIncrementPrimaryKeyRecordFunction();
        } else {
            if (keyValueExpression.size() != table.primaryKey.length) {
                throw new StatementExecutionException("you must set a value for the primary key (expressions=" + keyValueExpression.size() + ")");
            }
            keyfunction = new SQLRecordKeyFunction(table, keyExpressionToColumn, keyValueExpression);
        }
        RecordFunction valuesfunction = new SQLRecordFunction(table, valuesColumns, valuesExpressions);
        try {
            return ExecutionPlan.dataManipulationFromSelect(new InsertStatement(tableSpace, tableName, keyfunction, valuesfunction).setReturnValues(returnValues), datasource);
        } catch (IllegalArgumentException err) {
            throw new StatementExecutionException(err);
        }
    }
}
Also used : ItemsList(net.sf.jsqlparser.expression.operators.relational.ItemsList) ArrayList(java.util.ArrayList) CompiledSQLExpression(herddb.sql.expressions.CompiledSQLExpression) StatementExecutionException(herddb.model.StatementExecutionException) InsertStatement(herddb.model.commands.InsertStatement) ExecutionPlan(herddb.model.ExecutionPlan) Column(herddb.model.Column) TableSpaceManager(herddb.core.TableSpaceManager) MultiExpressionList(net.sf.jsqlparser.expression.operators.relational.MultiExpressionList) IntHolder(herddb.utils.IntHolder) ItemsList(net.sf.jsqlparser.expression.operators.relational.ItemsList) ArrayList(java.util.ArrayList) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) ColumnsList(herddb.model.ColumnsList) MultiExpressionList(net.sf.jsqlparser.expression.operators.relational.MultiExpressionList) List(java.util.List) RecordFunction(herddb.model.RecordFunction) AutoIncrementPrimaryKeyRecordFunction(herddb.model.AutoIncrementPrimaryKeyRecordFunction) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) MultiExpressionList(net.sf.jsqlparser.expression.operators.relational.MultiExpressionList) Table(herddb.model.Table) CreateTable(net.sf.jsqlparser.statement.create.table.CreateTable) JdbcParameter(net.sf.jsqlparser.expression.JdbcParameter) AbstractTableManager(herddb.core.AbstractTableManager) Expression(net.sf.jsqlparser.expression.Expression) AlterExpression(net.sf.jsqlparser.statement.alter.AlterExpression) BinaryExpression(net.sf.jsqlparser.expression.BinaryExpression) AndExpression(net.sf.jsqlparser.expression.operators.conditional.AndExpression) SignedExpression(net.sf.jsqlparser.expression.SignedExpression) CompiledSQLExpression(herddb.sql.expressions.CompiledSQLExpression) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) Select(net.sf.jsqlparser.statement.select.Select) AutoIncrementPrimaryKeyRecordFunction(herddb.model.AutoIncrementPrimaryKeyRecordFunction)

Example 3 with ExpressionList

use of net.sf.jsqlparser.expression.operators.relational.ExpressionList in project JSqlParser by JSQLParser.

the class UpsertTest method testUpsertDuplicate.

@Test
public void testUpsertDuplicate() throws JSQLParserException {
    String statement = "UPSERT INTO TEST (ID, COUNTER) VALUES (123, 0) ON DUPLICATE KEY UPDATE COUNTER = COUNTER + 1";
    Upsert upsert = (Upsert) parserManager.parse(new StringReader(statement));
    assertEquals("TEST", upsert.getTable().getName());
    assertEquals(2, upsert.getColumns().size());
    assertTrue(upsert.isUseValues());
    assertEquals("ID", ((Column) upsert.getColumns().get(0)).getColumnName());
    assertEquals("COUNTER", ((Column) upsert.getColumns().get(1)).getColumnName());
    assertEquals(2, ((ExpressionList) upsert.getItemsList()).getExpressions().size());
    assertEquals(123, ((LongValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(0)).getValue());
    assertEquals(0, ((LongValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(1)).getValue());
    assertEquals(1, upsert.getDuplicateUpdateColumns().size());
    assertEquals("COUNTER", ((Column) upsert.getDuplicateUpdateColumns().get(0)).getColumnName());
    assertEquals(1, upsert.getDuplicateUpdateExpressionList().size());
    assertEquals("COUNTER + 1", upsert.getDuplicateUpdateExpressionList().get(0).toString());
    assertFalse(upsert.isUseSelectBrackets());
    assertTrue(upsert.isUseDuplicate());
    assertEquals(statement, "" + upsert);
}
Also used : Upsert(net.sf.jsqlparser.statement.upsert.Upsert) StringReader(java.io.StringReader) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) Test(org.junit.Test)

Example 4 with ExpressionList

use of net.sf.jsqlparser.expression.operators.relational.ExpressionList in project JSqlParser by JSQLParser.

the class UpsertTest method testUpsertN.

@Test
public void testUpsertN() throws JSQLParserException {
    String statement = "UPSERT INTO TEST VALUES ('foo', 'bar', 3)";
    Upsert upsert = (Upsert) parserManager.parse(new StringReader(statement));
    assertEquals("TEST", upsert.getTable().getName());
    assertEquals(3, ((ExpressionList) upsert.getItemsList()).getExpressions().size());
    assertTrue(upsert.isUseValues());
    assertEquals("foo", ((StringValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(0)).getValue());
    assertEquals("bar", ((StringValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(1)).getValue());
    assertEquals(3, ((LongValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(2)).getValue());
    assertFalse(upsert.isUseSelectBrackets());
    assertFalse(upsert.isUseDuplicate());
    assertEquals(statement, "" + upsert);
}
Also used : Upsert(net.sf.jsqlparser.statement.upsert.Upsert) StringReader(java.io.StringReader) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) Test(org.junit.Test)

Example 5 with ExpressionList

use of net.sf.jsqlparser.expression.operators.relational.ExpressionList in project JSqlParser by JSQLParser.

the class ExecuteDeParserTest method shouldDeParseExecute.

@Test
public void shouldDeParseExecute() {
    Execute execute = new Execute();
    String name = "name";
    ExpressionList exprList = new ExpressionList();
    List<Expression> expressions = new ArrayList<Expression>();
    Expression expression1 = mock(Expression.class);
    Expression expression2 = mock(Expression.class);
    execute.setName(name);
    execute.setExprList(exprList);
    exprList.setExpressions(expressions);
    expressions.add(expression1);
    expressions.add(expression2);
    executeDeParser.deParse(execute);
    String actual = buffer.toString();
    assertTrue(actual.matches("EXECUTE " + name + " .*?, .*"));
}
Also used : Execute(net.sf.jsqlparser.statement.execute.Execute) Expression(net.sf.jsqlparser.expression.Expression) ArrayList(java.util.ArrayList) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList) Test(org.junit.Test)

Aggregations

ExpressionList (net.sf.jsqlparser.expression.operators.relational.ExpressionList)21 Expression (net.sf.jsqlparser.expression.Expression)16 ArrayList (java.util.ArrayList)11 Test (org.junit.Test)9 MultiExpressionList (net.sf.jsqlparser.expression.operators.relational.MultiExpressionList)6 StringReader (java.io.StringReader)5 JSQLParserException (net.sf.jsqlparser.JSQLParserException)4 Function (net.sf.jsqlparser.expression.Function)4 Statement (net.sf.jsqlparser.statement.Statement)4 DBException (org.jkiss.dbeaver.DBException)4 JdbcParameter (net.sf.jsqlparser.expression.JdbcParameter)3 ItemsList (net.sf.jsqlparser.expression.operators.relational.ItemsList)3 Insert (net.sf.jsqlparser.statement.insert.Insert)3 StatementExecutionException (herddb.model.StatementExecutionException)2 List (java.util.List)2 AnalyticExpression (net.sf.jsqlparser.expression.AnalyticExpression)2 BinaryExpression (net.sf.jsqlparser.expression.BinaryExpression)2 KeepExpression (net.sf.jsqlparser.expression.KeepExpression)2 SignedExpression (net.sf.jsqlparser.expression.SignedExpression)2 InExpression (net.sf.jsqlparser.expression.operators.relational.InExpression)2