Examples with JoinedRow org.knime.core.data.def.JoinedRow used on opensource projects

Example 6 with JoinedRow

use of org.knime.core.data.def.JoinedRow in project knime-core by knime.

the class DBReaderImpl method loopTable.

/**
 * @since 3.2
 */
@SuppressWarnings("resource")
@Override
public BufferedDataTableRowOutput loopTable(final ExecutionContext exec, final CredentialsProvider cp, final RowInput data, final long rowCount, final boolean failIfException, final boolean appendInputColumns, final boolean includeEmptyResults, final boolean retainAllColumns, final String... columns) throws Exception {
    if (m_blobFactory == null) {
        m_blobFactory = new BinaryObjectCellFactory();
    }
    final DatabaseQueryConnectionSettings dbConn = getQueryConnection();
    return getQueryConnection().execute(cp, conn -> {
        /* Get the selected timezone */
        final TimeZone timezone = dbConn.getTimeZone();
        /* Get the input table spec */
        final DataTableSpec inSpec = data.getDataTableSpec();
        /* Create PreparedStatement */
        final String query = dbConn.getQuery();
        LOGGER.debug("Executing SQL preparedStatement as execute: " + query);
        /* Initialize the error table */
        final UniqueNameGenerator errorGenerator = new UniqueNameGenerator(inSpec);
        final DataColumnSpec errorColSpec = errorGenerator.newColumn(DEF_ERROR_COL_NAME, StringCell.TYPE);
        final DataTableSpec errorSpec = new DataTableSpec(inSpec, new DataTableSpec(errorColSpec));
        m_errorContainer = exec.createDataContainer(errorSpec);
        DataTableSpec dbSpec = new DataTableSpec();
        BufferedDataTableRowOutput output = null;
        exec.setMessage("Start reading rows from database...");
        try (final PreparedStatement stmt = conn.prepareStatement(query)) {
            long inDataCounter = 1;
            long rowIdCounter = 0;
            DataRow row;
            while ((row = data.poll()) != null) {
                exec.checkCanceled();
                if (rowCount > 0) {
                    exec.setProgress(1.0 * inDataCounter / rowCount, "Row " + "#" + inDataCounter + " of " + rowCount);
                } else {
                    exec.setProgress("Writing Row " + "#" + inDataCounter);
                }
                final DataCell[] inCells = new DataCell[columns.length];
                for (int i = 0; i < columns.length; i++) {
                    final int dbIdx = i + 1;
                    final int colIdx = inSpec.findColumnIndex(columns[i]);
                    final DataColumnSpec colSpec = inSpec.getColumnSpec(colIdx);
                    inCells[i] = row.getCell(colIdx);
                    fillStatement(stmt, dbIdx, colSpec, inCells[i], timezone, null);
                }
                try (final ResultSet result = stmt.executeQuery()) {
                    /* In the first iteration, create the out DataTableSpec and BufferedDataTableRowOutput */
                    if (output == null) {
                        dbSpec = createTableSpec(result.getMetaData());
                        if (appendInputColumns) {
                            // Create out DataTableSpec for input table
                            final DataTableSpec newInSpec;
                            if (retainAllColumns) {
                                newInSpec = inSpec;
                            } else {
                                final DataColumnSpec[] inColSpecs = new DataColumnSpec[columns.length];
                                for (int i = 0; i < inColSpecs.length; i++) {
                                    inColSpecs[i] = inSpec.getColumnSpec(columns[i]);
                                }
                                newInSpec = new DataTableSpec(inColSpecs);
                            }
                            // Create DataTableSpec for database columns, rename if necessary
                            final UniqueNameGenerator generator = new UniqueNameGenerator(newInSpec);
                            final DataColumnSpec[] dbColSpecs = new DataColumnSpec[dbSpec.getNumColumns()];
                            for (int i = 0; i < dbColSpecs.length; i++) {
                                final DataColumnSpec colSpec = dbSpec.getColumnSpec(i);
                                dbColSpecs[i] = generator.newColumn(colSpec.getName(), colSpec.getType());
                            }
                            dbSpec = new DataTableSpec(dbColSpecs);
                            m_spec = new DataTableSpec(newInSpec, dbSpec);
                        } else {
                            m_spec = dbSpec;
                        }
                        output = new BufferedDataTableRowOutput(exec.createDataContainer(m_spec));
                    }
                    /* Iterate over the result of the database query and put it into the output table*/
                    final RowIterator dbRowIterator = createDBRowIterator(dbSpec, dbConn, m_blobFactory, false, result, rowIdCounter);
                    boolean hasDbRow = false;
                    while (dbRowIterator.hasNext()) {
                        hasDbRow = true;
                        final DataRow dbRow = dbRowIterator.next();
                        if (appendInputColumns) {
                            final DataRow inRow;
                            if (retainAllColumns) {
                                inRow = new DefaultRow(dbRow.getKey(), row);
                            } else {
                                inRow = new DefaultRow(dbRow.getKey(), inCells);
                            }
                            final JoinedRow joinedRow = new JoinedRow(inRow, dbRow);
                            output.push(joinedRow);
                        } else {
                            output.push(dbRow);
                        }
                        rowIdCounter++;
                    }
                    /* Append columns using MissingCell if no result is returned */
                    if (!hasDbRow && appendInputColumns && includeEmptyResults) {
                        final DataCell[] cells = new DataCell[dbSpec.getNumColumns()];
                        Arrays.fill(cells, DataType.getMissingCell());
                        final RowKey rowKey = RowKey.createRowKey(rowIdCounter);
                        final DataRow emptyDbRows = new DefaultRow(rowKey, cells);
                        final DataRow inRow;
                        if (retainAllColumns) {
                            inRow = new DefaultRow(rowKey, row);
                        } else {
                            inRow = new DefaultRow(rowKey, inCells);
                        }
                        final JoinedRow joinedRow = new JoinedRow(inRow, emptyDbRows);
                        output.push(joinedRow);
                        rowIdCounter++;
                    }
                    inDataCounter++;
                } catch (SQLException ex) {
                    LOGGER.debug("SQLException: " + ex.getMessage());
                    if (!failIfException) {
                        if (output == null) {
                            throw new SQLException(ex);
                        }
                        final AppendedColumnRow appendedRow = new AppendedColumnRow(row, new StringCell(ex.getMessage()));
                        m_errorContainer.addRowToTable(appendedRow);
                    } else {
                        throw new SQLException(ex);
                    }
                }
            }
        } finally {
            data.close();
            if (output == null) {
                output = new BufferedDataTableRowOutput(exec.createDataContainer(inSpec));
            }
            output.close();
            if (m_errorContainer != null) {
                m_errorContainer.close();
            }
        }
        return output;
    });
}

Example 7 with JoinedRow

use of org.knime.core.data.def.JoinedRow in project knime-core by knime.

the class JoinerNodeModel method processRemainingLeftRowsInTable.

private void processRemainingLeftRowsInTable(final DataContainer dc, final DataTable leftTable, final DataTable rightTable) throws CanceledExecutionException {
    if (m_leftIt.getIndex() > m_leftRows) {
        m_leftIt = new CounterRowIterator(leftTable.iterator());
        while (m_leftIt.getIndex() < m_leftRows) {
            m_leftIt.next();
            m_exec.checkCanceled();
        }
    }
    assert m_leftIt.getIndex() == (m_leftRows - 1);
    while (m_leftIt.hasNext()) {
        DataRow leftRow = m_leftIt.next();
        m_exec.checkCanceled();
        dc.addRowToTable(new JoinedRow(leftRow, new DefaultRow(leftRow.getKey(), JoinedTable.createMissingCells(rightTable.getDataTableSpec()))));
        m_leftRows++;
        m_outputRows++;
        printProgress(leftRow.getKey());
    }
}