Examples with StatementManipulator org.knime.core.node.port.database.StatementManipulator used on opensource projects

Example 11 with StatementManipulator

use of org.knime.core.node.port.database.StatementManipulator in project knime-core by knime.

the class DBApplyBinnerNodeModel method createDatabasePortObject.

private DatabasePortObject createDatabasePortObject(final DatabasePortObjectSpec inSpec, DatabaseQueryConnectionSettings connectionSettings, final PMMLPortObject pmmlPortObject) throws InvalidSettingsException {
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    String newQuery = createQuery(connectionSettings.getQuery(), statementManipulator, inSpec.getDataTableSpec(), pmmlPortObject);
    connectionSettings = createDBQueryConnection(inSpec, newQuery);
    DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(connectionSettings, newQuery);
    DBReader reader = querySettings.getUtility().getReader(querySettings);
    try {
        DatabasePortObjectSpec databasePortObjectSpec = new DatabasePortObjectSpec(reader.getDataTableSpec(getCredentialsProvider()), connectionSettings.createConnectionModel());
        DatabasePortObject databasePortObject = new DatabasePortObject(databasePortObjectSpec);
        return databasePortObject;
    } catch (SQLException e) {
        throw new InvalidSettingsException("Failure during query generation. Error: " + e.getMessage());
    }
}

Example 12 with StatementManipulator

use of org.knime.core.node.port.database.StatementManipulator in project knime-core by knime.

the class DBGroupByNodeModel2 method createDbOutSpec.

/**
 * @param inSpec Spec of the input database object
 * @param checkRetrieveMetadata true if the retrieveMetadataInConfigure settings should be respected,
 *            <code>false</code> if the metadata should be retrieved in any case (for execute)
 * @return Spec of the output database object
 * @throws InvalidSettingsException If the current settings are invalid
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpec, final boolean checkRetrieveMetadata) throws InvalidSettingsException {
    DatabaseQueryConnectionSettings connection = inSpec.getConnectionSettings(getCredentialsProvider());
    final StatementManipulator statementManipulator = connection.getUtility().getStatementManipulator();
    final String newQuery = createQuery(connection, connection.getQuery(), statementManipulator);
    connection = createDBQueryConnection(inSpec, newQuery);
    if (checkRetrieveMetadata && !connection.getRetrieveMetadataInConfigure()) {
        return null;
    }
    final DataTableSpec tableSpec = createOutSpec(inSpec.getDataTableSpec(), connection, newQuery, statementManipulator, checkRetrieveMetadata);
    return new DatabasePortObjectSpec(tableSpec, connection.createConnectionModel());
}

Example 13 with StatementManipulator

use of org.knime.core.node.port.database.StatementManipulator in project knime-core by knime.

the class DBPivotNodeModel method createQuery.

private String createQuery(final DatabaseQueryConnectionSettings connectionSettings, final DataTableSpec dataTableSpec, final ExecutionMonitor exec) throws SQLException, CanceledExecutionException {
    final StatementManipulator manipulator = connectionSettings.getUtility().getStatementManipulator();
    final String query = connectionSettings.getQuery();
    exec.setMessage("Getting pivot values.");
    ExecutionMonitor subExec = exec.createSubProgress(0.7);
    final List<String> pivotColumns = m_pivotCols.getIncludeList();
    final Map<DataColumnSpec, Set<Object>> pivotElements = connectionSettings.execute(getCredentialsProvider(), conn -> {
        int counter = 1;
        final Map<DataColumnSpec, Set<Object>> pivotMap = new LinkedHashMap<>();
        for (String pivotColumn : pivotColumns) {
            subExec.setProgress(counter / (double) pivotColumns.size(), "Fetching unique values for column " + pivotColumn + ". There are " + (pivotColumns.size() - counter) + " columns left.");
            DataColumnSpec columnSpec = dataTableSpec.getColumnSpec(pivotColumn);
            final String valueQuery = "SELECT DISTINCT " + manipulator.quoteIdentifier(pivotColumn) + " FROM (" + query + ") T";
            try (ResultSet valueSet = conn.createStatement().executeQuery(valueQuery)) {
                exec.checkCanceled();
                final Set<Object> vals = new HashSet<>();
                while (valueSet.next()) {
                    final Object dbVal = valueSet.getObject(1);
                    if (!valueSet.wasNull()) {
                        vals.add(dbVal);
                    }
                }
                pivotMap.put(columnSpec, vals);
                counter++;
            }
        }
        return pivotMap;
    });
    exec.setProgress(0.8, "Getting aggregation methods and columns.");
    List<String> groupByColumns = m_groupByCols.getIncludeList();
    final List<Pair<String, DBAggregationFunction>> aggValues = new LinkedList<>();
    for (int i = 0; i < m_aggregationFunction2Use.size(); i++) {
        exec.checkCanceled();
        final DBColumnAggregationFunctionRow aggregationFunction = m_aggregationFunction2Use.get(i);
        String colName = aggregationFunction.getColumnSpec().getName();
        DBAggregationFunction function = aggregationFunction.getFunction();
        aggValues.add(new Pair<>(colName, function));
    }
    final ColumnNamePolicy pivotColPoliciy = ColumnNamePolicy.getPolicy4Label(m_columnNamePolicy.getStringValue());
    PivotColumnNameGenerator pivotColName = new PivotColumnNameGenerator() {

        @Override
        public String createColumnName(final String columnName, final DBAggregationFunction function, final List<Object> pivotValues) {
            String vals = "";
            Iterator<Object> iterator = pivotValues.iterator();
            while (iterator.hasNext()) {
                vals = vals + iterator.next() + "_";
            }
            vals = vals.substring(0, vals.length() - 1);
            String method = function.getColumnName();
            switch(pivotColPoliciy) {
                case KEEP_ORIGINAL_NAME:
                    return vals + "+" + columnName;
                case AGGREGATION_METHOD_COLUMN_NAME:
                    return vals + "+" + method + "(" + columnName + ")";
                case COLUMN_NAME_AGGREGATION_METHOD:
                    return vals + "+" + columnName + " (" + method + ")";
                default:
                    throw new IllegalStateException("Unhandled column naming policy: " + pivotColPoliciy);
            }
        }
    };
    exec.setProgress(0.9, "Creating query.");
    exec.checkCanceled();
    return manipulator.getPivotStatement(query, groupByColumns, pivotElements, aggValues, pivotColName);
}