DatabaseComponent.java

/*
 * Copyright (C) 2006-2016 DLR, Germany
 * 
 * All rights reserved
 * 
 * http://www.rcenvironment.de/
 */

package de.rcenvironment.components.database.execution;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.codehaus.jackson.map.ObjectMapper;
import org.codehaus.jackson.type.JavaType;

import de.rcenvironment.components.database.common.DatabaseComponentConstants;
import de.rcenvironment.components.database.common.DatabaseComponentHistoryDataItem;
import de.rcenvironment.components.database.common.DatabaseStatement;
import de.rcenvironment.components.database.common.jdbc.JDBCDriverInformation;
import de.rcenvironment.components.database.common.jdbc.JDBCDriverService;
import de.rcenvironment.core.component.api.ComponentConstants;
import de.rcenvironment.core.component.api.ComponentException;
import de.rcenvironment.core.component.execution.api.ComponentContext;
import de.rcenvironment.core.component.model.api.LocalExecutionOnly;
import de.rcenvironment.core.component.model.spi.DefaultComponent;
import de.rcenvironment.core.datamodel.api.DataType;
import de.rcenvironment.core.datamodel.api.TypedDatum;
import de.rcenvironment.core.datamodel.api.TypedDatumFactory;
import de.rcenvironment.core.datamodel.api.TypedDatumService;
import de.rcenvironment.core.datamodel.types.api.BooleanTD;
import de.rcenvironment.core.datamodel.types.api.FloatTD;
import de.rcenvironment.core.datamodel.types.api.IntegerTD;
import de.rcenvironment.core.datamodel.types.api.ShortTextTD;
import de.rcenvironment.core.datamodel.types.api.SmallTableTD;
import de.rcenvironment.core.utils.common.JsonUtils;
import de.rcenvironment.core.utils.common.StringUtils;

/**
 * Database component execution class.
 *
 * @author Oliver Seebach
 */
@LocalExecutionOnly
public class DatabaseComponent extends DefaultComponent {

    private static final String ERROR_FILLING_SMALL_TABLE = "Error when filling the output '%s' of type small table.";
    
    private static final String FULL_STOP = ".";

    private static final String SAVEPOINT = "RCEdatabaseTrancactionSavepoint";

    private static final int MINUS_ONE = -1;

    private static final String SEMICOLON = ";";

    private static final String JDBC = "jdbc";

    private static final String SLASH = "/";

    private static final String DOUBLE_SLASH = "//";

    private static final String COLON = ":";

    private static TypedDatumService typedDatumService;

    private static JDBCDriverService jdbcDriverService;

    protected final Log logger = LogFactory.getLog(getClass());

    private Savepoint transactionSavepoint;

    private List<Object> inputOrder = new ArrayList<>();

    private ComponentContext componentContext;

    private List<DatabaseStatement> databaseStatements;

    private Connection jdbcConnection = null;

    private DatabaseComponentHistoryDataItem databaseWorkflowDataItem;

    private void initializeNewWorkflowDataItem() {
        if (Boolean.valueOf(componentContext.getConfigurationValue(ComponentConstants.CONFIG_KEY_STORE_DATA_ITEM))) {
            databaseWorkflowDataItem = new DatabaseComponentHistoryDataItem(DatabaseComponentConstants.COMPONENT_ID);
        }
    }

    private void writeFinalWorkflowDataItem() {
        if (Boolean.valueOf(componentContext.getConfigurationValue(ComponentConstants.CONFIG_KEY_STORE_DATA_ITEM))) {
            componentContext.writeFinalHistoryDataItem(databaseWorkflowDataItem);
        }
    }

    @Override
    public void setComponentContext(ComponentContext componentContext) {
        this.componentContext = componentContext;
    }

    @Override
    public void start() throws ComponentException {

        jdbcDriverService = componentContext.getService(JDBCDriverService.class);
        typedDatumService = componentContext.getService(TypedDatumService.class);

        super.start();
        if (componentContext.getInputs().isEmpty()) {
            runDatabaseComponent();
        }
    }

    @Override
    public void processInputs() throws ComponentException {
        runDatabaseComponent();
        super.processInputs();
    }

    @Override
    public boolean treatStartAsComponentRun() {
        return componentContext.getInputs().isEmpty();
    }

    private void runDatabaseComponent() throws ComponentException {

        initializeNewWorkflowDataItem();
        databaseStatements = parseAndValidateStatements();

        Map<String, TypedDatum> inputValues = new HashMap<>();
        if (componentContext != null && componentContext.getInputsWithDatum() != null) {
            for (String inputName : componentContext.getInputsWithDatum()) {
                inputValues.put(inputName, componentContext.readInput(inputName));
            }
        }

        String statementPart = " statement";
        if (databaseStatements.size() > 1) {
            statementPart = " statements";
        }
        componentContext.getLog().componentInfo("Executing " + databaseStatements.size() + statementPart + FULL_STOP);
        boolean autoCommit = false;
        try {
            try {
                // GET CONNECTION AND SET SAVEPOINT FOR POTENTIAL ROLLBACK
                if (jdbcConnection == null || jdbcConnection.isClosed()) {
                    jdbcConnection = getConnection();
                }
                autoCommit = jdbcConnection.getAutoCommit();
                jdbcConnection.setAutoCommit(false);
                transactionSavepoint = jdbcConnection.setSavepoint(SAVEPOINT);
            } catch (SQLException e) {
                throw new ComponentException("Failed to initialize database connection. Database response: " + e.getMessage());
            }

            // PREPARE AND EXECUTE STATEMENTS
            for (DatabaseStatement databaseStatement : databaseStatements) {
                if (!databaseStatement.getStatement().isEmpty()) {
                    boolean statementTypeSupported = statementTypeIsSupportedGeneral(databaseStatement.getStatement());
                    if (statementTypeSupported) {
                        boolean statementContainsSmalltablePlaceholder =
                            statementContainsSmalltablePlaceholder(databaseStatement.getStatement(), inputValues);

                        // WITHOUT SMALLTABLE INPUTS
                        if (!statementContainsSmalltablePlaceholder) {
                            // replace placeholders except for small tables
                            String preparedStatementString = replaceStringAndFillInputOrder(databaseStatement.getStatement(), inputValues);
                            databaseStatement.setStatement(preparedStatementString);
                            try {
                                runSqlStatementUsingPreparedStatement(databaseStatement);
                            } catch (SQLException e) {
                                throw new ComponentException("Failure during execution of database statement '"
                                    + databaseStatement.getName() + "' (" + databaseStatement.getStatement() + "). Database response: "
                                    + e.getMessage());
                            } finally {
                                inputOrder.clear();
                            }
                        } else {
                            // WITH SMALLTABLE INPUTS
                            if (statementTypeIsSupportedForSmalltable(databaseStatement.getStatement())) {
                                // replace placeholders except for small tables
                                String preparedStatementString =
                                    replaceStringAndFillInputOrder(databaseStatement.getStatement(), inputValues);
                                databaseStatement.setStatement(preparedStatementString);
                                try {
                                    runSqlStatementUsingPreparedStatementForSmalltables(databaseStatement, inputValues);
                                } catch (SQLException e) {
                                    throw new ComponentException("Failure during execution of database statement '"
                                        + databaseStatement.getName() + "' (" + databaseStatement.getStatement() + "). Database response: "
                                        + e.getMessage());
                                } finally {
                                    inputOrder.clear();
                                }
                            } else {
                                componentContext.getLog().componentWarn("Database query '" + databaseStatement.getName()
                                    + "' could not be matched to the allowed query types that use small table inputs. "
                                    + "The query will be skipped. Currently 'Insert' is supported for small table inputs.");
                            }
                        }

                    } else {
                        componentContext.getLog().componentWarn("Database query '" + databaseStatement.getStatement()
                            + "' could not be matched to the allowed query types and will be skipped. "
                            + "Currently 'Select', 'Insert', 'Update' and 'Delete' are supported.");
                    }
                } else {
                    componentContext.getLog().componentWarn(
                        "Database query with name '" + databaseStatement.getName() + "' is empty and will be skipped.");
                }
            }
            // COMMIT TRANSACTION
            try {
                jdbcConnection.commit();
            } catch (SQLException e) {
                throw new ComponentException("Failed to commit transaction. Database response: " + e.getMessage());
            }
        } catch (ComponentException e) {
            try {
                jdbcConnection.rollback(transactionSavepoint);
            } catch (SQLException e2) {
                throw new ComponentException("Failed to rollback database. " + e2.getMessage());
            }
            throw e;
        } finally {
            if (jdbcConnection == null) {
                String host = componentContext.getConfigurationValue(DatabaseComponentConstants.DATABASE_HOST);
                String port = componentContext.getConfigurationValue(DatabaseComponentConstants.DATABASE_PORT);
                String scheme = componentContext.getConfigurationValue(DatabaseComponentConstants.DATABASE_SCHEME);
                throw new ComponentException(
                    "Failed to establish database connection. Is the database, username and password correctly defined? "
                        + "You entered -> Host: '" + host + "'; Port: '" + port + "'; Default Scheme: '" + scheme + "'. "
                        + "Note that username and password are not stated here for security reasons.");
            } else {
                try {
                    if (!jdbcConnection.isClosed()) {
                        if (transactionSavepoint != null) {
                            jdbcConnection.releaseSavepoint(transactionSavepoint);
                        }
                        jdbcConnection.setAutoCommit(autoCommit);
                        jdbcConnection.close();
                    }
                } catch (SQLException e) {
                    throw new ComponentException("Failed to release database resources. Database response: " + e.getMessage());
                }
            }
        }

        // WRITE SUCCESS OUTPUT AND WORKFLOW DATA ITEMS
        componentContext.writeOutput(DatabaseComponentConstants.OUTPUT_NAME_SUCCESS, typedDatumService.getFactory().createBoolean(true));
        writeFinalWorkflowDataItem();
    }

    private String buildInsertPlaceholderWithSize(int size) {
        String placeholder = " (";
        for (int i = 0; i < size; i++) {
            placeholder += "?";
            placeholder += ",";
        }
        placeholder = placeholder.substring(0, placeholder.length() - 1); // remove last ","
        placeholder += ") ";
        return placeholder;
    }

    /**
     * Return a configured database connection.
     * 
     * @return the database connection
     * @throws SQLException Thrown when the connection could not be established.
     * @throws ComponentException Thrown when the driver cannot be loaded.
     */
    public Connection getConnection() throws SQLException, ComponentException {

        final String databaseHost = componentContext.getConfigurationValue(DatabaseComponentConstants.DATABASE_HOST);
        final String databasePort = componentContext.getConfigurationValue(DatabaseComponentConstants.DATABASE_PORT);
        String databaseConnector = componentContext.getConfigurationValue(DatabaseComponentConstants.DATABASE_CONNECTOR);
        final String databaseScheme = componentContext.getConfigurationValue(DatabaseComponentConstants.DATABASE_SCHEME);
        final String databaseUser = componentContext.getConfigurationValue(DatabaseComponentConstants.CONFIG_KEY_AUTH_USER);
        String databasePassword = componentContext.getConfigurationValue(DatabaseComponentConstants.CONFIG_KEY_AUTH_PHRASE);

        if (jdbcDriverService.getRegisteredJDBCDrivers().isEmpty()) {
            String noRegisteredDriversWarning = "Failed to establish connection because no JDBC driver is registered. "
                + "Please make sure the subfolder '.../configuration/jdbc' in your "
                + "installation directory contains the desired driver file";
            componentContext.getLog().componentError(noRegisteredDriversWarning);
            throw new ComponentException(noRegisteredDriversWarning);
        }

        String urlScheme = "";
        for (JDBCDriverInformation driverInformation : jdbcDriverService.getRegisteredJDBCDrivers()) {
            if (databaseConnector.equals(driverInformation.getDisplayName())) {
                urlScheme = driverInformation.getUrlScheme();
                break;
            }
        }
        if (urlScheme.isEmpty()) {
            String urlSchemeEmptyWarning = "Failed to establish connection because no JDBC driver for the selected connector was found. "
                + "Please make sure the subfolder '.../extras/database_connectors' in your "
                + "installation directory contains the desired driver file";
            componentContext.getLog().componentError(urlSchemeEmptyWarning);
            throw new ComponentException(urlSchemeEmptyWarning);
        }

        String url = JDBC + COLON + urlScheme + COLON + DOUBLE_SLASH + databaseHost + COLON + databasePort + SLASH + databaseScheme;

        Connection connection = null;
        if (databasePassword.isEmpty()) {
            databasePassword = null;
        }

        connection = jdbcDriverService.getConnectionWithCredentials(url, databaseUser, databasePassword);

        return connection;
    }

    // NO SMALLTABLE INPUT
    private void runSqlStatementUsingPreparedStatement(DatabaseStatement databaseStatement) throws SQLException, ComponentException {
        // declare the jdbc assets to be able to close them in the finally-clause
        PreparedStatement preparedStatement = null;
        String sqlStatement = databaseStatement.getStatement();

        if (!sqlStatement.endsWith(SEMICOLON)) {
            sqlStatement += SEMICOLON;
        }
        preparedStatement = jdbcConnection.prepareStatement(sqlStatement);
        if (!inputOrder.isEmpty()) {
            for (int i = 1; i < inputOrder.size() + 1; i++) {
                if (inputOrder.get(i - 1) instanceof String) {
                    String inputToSet = (String) inputOrder.get(i - 1);
                    preparedStatement.setString(i, inputToSet);
                } else if (inputOrder.get(i - 1) instanceof Long) {
                    Integer inputToSet = Integer.valueOf(((Long) inputOrder.get(i - 1)).intValue());
                    preparedStatement.setInt(i, inputToSet);
                } else if (inputOrder.get(i - 1) instanceof Float) {
                    Float inputToSet = (Float) inputOrder.get(i - 1);
                    preparedStatement.setFloat(i, inputToSet);
                } else if (inputOrder.get(i - 1) instanceof Double) {
                    Double inputToSet = (Double) inputOrder.get(i - 1);
                    preparedStatement.setDouble(i, inputToSet);
                } else if (inputOrder.get(i - 1) instanceof Boolean) {
                    Boolean inputToSet = (Boolean) inputOrder.get(i - 1);
                    preparedStatement.setBoolean(i, inputToSet);
                }
            }
        }
        String effectiveQuery = preparedStatement.toString().substring(preparedStatement.toString().indexOf(COLON) + 2);

        if (databaseWorkflowDataItem != null) {
            databaseWorkflowDataItem.addDatabaseStatementHistoryData(
                databaseStatement.getIndex(), databaseStatement.getName(), sqlStatement, effectiveQuery);
        }

        componentContext.getLog().componentInfo("Sending query '" + effectiveQuery + "' to database.");
        ResultSet resultSet = null;
        boolean isManipulation =
            (effectiveQuery.toLowerCase().startsWith(DatabaseComponentConstants.INSERT)
                || effectiveQuery.toLowerCase().startsWith(DatabaseComponentConstants.UPDATE)
                || effectiveQuery.toLowerCase().startsWith(DatabaseComponentConstants.DELETE));
        if (isManipulation) {
            preparedStatement.executeUpdate();
        } else {
            resultSet = preparedStatement.executeQuery();
        }

        // check if result set is not empty
        boolean hasResultSet = false;
        if (resultSet != null) {
            if (resultSet.isBeforeFirst()) {
                if (resultSet.next()) {
                    hasResultSet = true;
                }
                resultSet.beforeFirst();
            }
        }

        if (resultSet != null && hasResultSet && databaseStatement.isWillWriteToOutput()
            && !databaseStatement.getOutputToWriteTo().isEmpty()) {
            distributeResults(databaseStatement.getOutputToWriteTo(), resultSet);
            if (!resultSet.isClosed()) {
                resultSet.close();
            }
        }
        if (!preparedStatement.isClosed()) {
            preparedStatement.close();
        }

    }

    // WITH SMALLTABLE INPUT
    private void runSqlStatementUsingPreparedStatementForSmalltables(DatabaseStatement databaseStatement,
        Map<String, TypedDatum> inputValues) throws SQLException, ComponentException {
        // Set small table to be considered and make sure there exists only one
        SmallTableTD smallTableToHandle = null;
        String smallTablePlaceholder = "";
        for (String key : inputValues.keySet()) {
            TypedDatum td = inputValues.get(key);
            String possiblePlaceholder = StringUtils.format(DatabaseComponentConstants.INPUT_PLACEHOLDER_PATTERN, key);
            if (td instanceof SmallTableTD && databaseStatement.getStatement().contains(possiblePlaceholder)) {
                if (smallTableToHandle == null) {
                    smallTableToHandle = (SmallTableTD) td;
                    smallTablePlaceholder = possiblePlaceholder;
                } else {
                    throw new ComponentException("Placeholder for small table input '" + key + "' is just allowed once per statement.");
                }
            }
        }
        if (smallTableToHandle == null) {
            throw new ComponentException("Could not find a small table in statement.");
        }

        PreparedStatement preparedStatement = null;
        for (int row = 0; row < smallTableToHandle.getRowCount(); row++) {
            String sqlStatement = databaseStatement.getStatement();
            List<String> smallTableReplacements = new ArrayList<>();
            for (int col = 0; col < smallTableToHandle.getColumnCount(); col++) {
                String value = "";
                TypedDatum cell = smallTableToHandle.getTypedDatumOfCell(row, col);
                DataType dataType = cell.getDataType();
                if (dataType == DataType.Float) {
                    FloatTD floatTD = (FloatTD) cell;
                    value = String.valueOf(floatTD.getFloatValue());
                } else if (dataType == DataType.Integer) {
                    IntegerTD integerTD = (IntegerTD) cell;
                    value = String.valueOf(integerTD.getIntValue());
                } else if (dataType == DataType.ShortText) {
                    ShortTextTD shortTextTD = (ShortTextTD) cell;
                    value = shortTextTD.getShortTextValue();
                } else if (dataType == DataType.Boolean) {
                    BooleanTD booleanTD = (BooleanTD) cell;
                    value = String.valueOf(booleanTD.getBooleanValue());
                } else if (dataType == DataType.Empty) {
                    value = ""; // set explicitly to make clear what is set
                }
                smallTableReplacements.add(value);
            }
            // memorize where placeholder was and remove
            int placeholdersLocation = sqlStatement.indexOf(smallTablePlaceholder);
            sqlStatement =
                sqlStatement.substring(0, placeholdersLocation)
                    + sqlStatement.substring(placeholdersLocation + smallTablePlaceholder.length());
            // insert (?,?,?,? ... ) with according length
            String replace = buildInsertPlaceholderWithSize(smallTableToHandle.getColumnCount());
            sqlStatement = sqlStatement.substring(0, placeholdersLocation) + replace + sqlStatement.substring(placeholdersLocation);
            if (!sqlStatement.trim().endsWith(SEMICOLON)) {
                sqlStatement += SEMICOLON;
            }
            preparedStatement = jdbcConnection.prepareStatement(sqlStatement);
            int index = 1; // insertion is 1 based
            for (String insertion : smallTableReplacements) {
                preparedStatement.setString(index, insertion);
                index++;
            }
            String effectiveQuery = preparedStatement.toString().substring(preparedStatement.toString().indexOf(COLON) + 2);

            if (databaseWorkflowDataItem != null) {
                databaseWorkflowDataItem.addDatabaseStatementHistoryData(
                    databaseStatement.getIndex(), databaseStatement.getName(), sqlStatement, effectiveQuery);
            }

            preparedStatement.executeUpdate();
        }
        if (preparedStatement == null) {
            throw new ComponentException("Error while executing Insert statement. "
                + "Maybe the small table input is empty?");
        }
    }

    private List<DatabaseStatement> parseAndValidateStatements() throws ComponentException {
        List<DatabaseStatement> statementsToValidate = new ArrayList<>();
        // read in statements
        String statementsString = componentContext.getConfigurationValue(DatabaseComponentConstants.DB_STATEMENTS_KEY);
        if (statementsString != null) {
            ObjectMapper mapper = JsonUtils.getDefaultObjectMapper();
            try {
                JavaType javaType = mapper.getTypeFactory().constructCollectionType(List.class, DatabaseStatement.class);
                statementsToValidate = mapper.readValue(statementsString, javaType);
            } catch (IOException e) {
                throw new ComponentException("Failed to parse SQL statements while initializing execution.", e);
            }
        } else {
            throw new ComponentException("An error occured while loading statements from configuration.");
        }

        // check output to write to is really set
        for (DatabaseStatement statement : statementsToValidate) {
            String exceptionMessage =
                "The statement '" + statement.getName()
                    + "' is configured to write to an output but no output is selected.";
            if (statement.isWillWriteToOutput()) {
                if (statement.getOutputToWriteTo() == null) {
                    throw new ComponentException(exceptionMessage);
                } else if (statement.getOutputToWriteTo().isEmpty()) {
                    throw new ComponentException(exceptionMessage);
                }
            }
            boolean statementTypeSupported = statementTypeIsSupportedGeneral(statement.getStatement());
            if (!statementTypeSupported) {
                String errorMessage = "Statement '" + statement.getName() + "'" + " (" + statement.getStatement()
                    + ") could not be matched to the allowed query types. "
                    + "Currently 'Select', 'Insert', 'Update' and 'Delete' are supported.";
                throw new ComponentException(errorMessage);
            }
        }
        componentContext.getLog().componentInfo("Statements validation successfully passed.");
        return statementsToValidate;
    }

    private boolean statementTypeIsSupportedGeneral(String statement) {
        for (String allowedStatement : Arrays.asList(DatabaseComponentConstants.STATEMENT_PREFIX_WHITELIST_GENERAL)) {
            if (statement.toLowerCase().startsWith(allowedStatement)) {
                return true;
            }
        }
        return false;
    }

    private boolean statementTypeIsSupportedForSmalltable(String statement) {
        for (String allowedStatement : Arrays.asList(DatabaseComponentConstants.STATEMENT_PREFIX_WHITELIST_SMALLTABLE)) {
            if (statement.toLowerCase().startsWith(allowedStatement)) {
                return true;
            }
        }
        return false;
    }

    private boolean statementContainsSmalltablePlaceholder(String databaseStatement, Map<String, TypedDatum> inputValues) {
        for (String key : inputValues.keySet()) {
            if (inputValues.get(key).getDataType() == DataType.SmallTable) {
                String possiblePlaceholder = StringUtils.format(DatabaseComponentConstants.INPUT_PLACEHOLDER_PATTERN, key);
                if (databaseStatement.contains(possiblePlaceholder)) {
                    return true;
                }
            }
        }
        return false;
    }

    @Override
    public void completeStartOrProcessInputsAfterFailure() throws ComponentException {
        writeFinalWorkflowDataItem();
    }

    /**
     * This method is recursively called and has 2 purposes: 1. All "RCE" placeholders (${...}) are replaced by placeholders for the
     * prepared statement 2. The order of the input values is stored. This is important for the replacement with the actual values. Note
     * that the order has to be reset/cleared per component run.
     * 
     * @param originalStatement The original statement as entered in the UI OR the statement with partial replacements during the recursive
     *        call
     * @param inputValues The actual input values for the current component run
     * @return The statement where placeholders have been replaced by question marks.
     */
    private String replaceStringAndFillInputOrder(String originalStatement, Map<String, TypedDatum> inputValues) {
        String currentStatement = originalStatement;
        Map<Integer, String> tempOccuranceToInputMapping = new HashMap<>();
        for (String inputName : inputValues.keySet()) {
            if (inputValues.get(inputName).getDataType() == DataType.ShortText
                || inputValues.get(inputName).getDataType() == DataType.Float
                || inputValues.get(inputName).getDataType() == DataType.Integer
                || inputValues.get(inputName).getDataType() == DataType.Boolean) {
                String possiblePlaceholder = StringUtils.format(DatabaseComponentConstants.INPUT_PLACEHOLDER_PATTERN, inputName);
                int firstIndex = currentStatement.indexOf(possiblePlaceholder);
                if (firstIndex != MINUS_ONE) {
                    // add just if an occurance was found.
                    tempOccuranceToInputMapping.put(firstIndex, inputName);
                }
            }
        }
        if (tempOccuranceToInputMapping.size() > 0) {
            int minIndex = findMin(tempOccuranceToInputMapping.keySet());
            String firstAppearingInputName = tempOccuranceToInputMapping.get(minIndex);
            TypedDatum firstAppearingTypedDatum = inputValues.get(firstAppearingInputName);
            if (firstAppearingTypedDatum.getDataType() == DataType.ShortText) {
                String stringToAdd = ((ShortTextTD) inputValues.get(firstAppearingInputName)).getShortTextValue();
                inputOrder.add(stringToAdd);
            } else if (firstAppearingTypedDatum.getDataType() == DataType.Float) {
                double floatToAdd = ((FloatTD) inputValues.get(firstAppearingInputName)).getFloatValue();
                inputOrder.add(floatToAdd);
            } else if (firstAppearingTypedDatum.getDataType() == DataType.Integer) {
                long integerToAdd = ((IntegerTD) inputValues.get(firstAppearingInputName)).getIntValue();
                inputOrder.add(integerToAdd);
            } else if (firstAppearingTypedDatum.getDataType() == DataType.Boolean) {
                Boolean booleanToAdd = ((BooleanTD) inputValues.get(firstAppearingInputName)).getBooleanValue();
                inputOrder.add(booleanToAdd);
            }

            // TODO review if this is the proper approach, recheck about .replaceFirst(...) -- seeb_ol, November 2015
            String possiblePlaceholder = StringUtils.format(DatabaseComponentConstants.INPUT_PLACEHOLDER_PATTERN, firstAppearingInputName);
            String replacement = "?";
            String replacedStatement =
                currentStatement.substring(0, minIndex) + replacement + currentStatement.substring(minIndex + possiblePlaceholder.length());

            currentStatement = replaceStringAndFillInputOrder(replacedStatement, inputValues);
        }
        return currentStatement;
    }

    private int findMin(Set<Integer> indices) {
        int currentMin = Integer.MAX_VALUE;
        for (int index : indices) {
            if (index < currentMin) {
                currentMin = index;
            }
        }
        return currentMin;
    }

    private int determineResultSetsRowCount(ResultSet resultSet) throws ComponentException {
        int rowCount = 0;
        try {
            while (resultSet.next()) {
                rowCount++;
            }
            resultSet.beforeFirst();
        } catch (SQLException e1) {
            throw new ComponentException("Failed to determine result set's row count.", e1);
        }
        return rowCount;
    }

    private int determineResultSetsColumnCount(ResultSet resultSet) throws ComponentException {
        try {
            return resultSet.getMetaData().getColumnCount();
        } catch (SQLException e1) {
            throw new ComponentException("Failed to determine result set's column count.", e1);
        }
    }

    private TypedDatum convertResultSetToTypedDatum(ResultSet resultSet, String outputToWriteTo)
        throws SQLException, ComponentException {
        // Determine resultSet's size
        int columnCount = determineResultSetsColumnCount(resultSet);
        int rowCount = determineResultSetsRowCount(resultSet);
        componentContext.getLog().componentInfo("Processing result set with " + rowCount + " row(s) and " + columnCount + " column(s).");
        DataType dataType = componentContext.getOutputDataType(outputToWriteTo);
        TypedDatumFactory tdFactory = typedDatumService.getFactory();
        TypedDatum result = null;

        if (columnCount == 0 || rowCount == 0) {
            // ################ 0 x 0 ######################
            throw new ComponentException(
                "The database returned an empty result set although writing the result to an output was activated.");
        } else if (columnCount == 1 && rowCount == 1) {
            // ################ 1 x 1 ######################
            if (dataType == DataType.SmallTable) {
                result = convertResultSetToSmallTableTD(rowCount, columnCount, outputToWriteTo, resultSet);
            } else {
                resultSet.next();
                if (dataType == DataType.Float) {
                    if (resultSet.getObject(1) instanceof Float || resultSet.getObject(1) instanceof Double) {
                        result = tdFactory.createFloat(resultSet.getDouble(1));
                    } else if (resultSet.getObject(1) instanceof Integer || resultSet.getObject(1) instanceof Long) {
                        result = tdFactory.createFloat(resultSet.getLong(1));
                    } else {
                        throw new ComponentException("Failed to convert result set to single float value.");
                    }
                } else if (dataType == DataType.Integer) {
                    if (resultSet.getObject(1) instanceof Integer || resultSet.getObject(1) instanceof Long) {
                        result = tdFactory.createInteger(resultSet.getLong(1));
                    } else {
                        throw new ComponentException("Failed to convert result set to single integer value.");
                    }
                } else if (dataType == DataType.ShortText) {
                    if (resultSet.getObject(1) instanceof String) {
                        result = tdFactory.createShortText(resultSet.getString(1));
                    } else {
                        throw new ComponentException("Failed to convert result set to single short text value.");
                    }
                } else if (dataType == DataType.Boolean) {
                    if (resultSet.getObject(1) instanceof Boolean) {
                        result = tdFactory.createBoolean(resultSet.getBoolean(1));
                    } else {
                        throw new ComponentException("Failed to convert result set to single boolean value.");
                    }
                }
            }
        } else if (columnCount > 1 || rowCount > 1) {
            // ################ n x n ######################
            if (dataType == DataType.SmallTable) {
                result = convertResultSetToSmallTableTD(rowCount, columnCount, outputToWriteTo, resultSet);
            } else if (dataType == DataType.Float || dataType == DataType.Integer
                || dataType == DataType.ShortText || dataType == DataType.Boolean) {
                throw new ComponentException("The result set contains " + rowCount + " rows and " + columnCount + " columns "
                    + "and thus cannot be written into selected datatype " + dataType.getDisplayName() + FULL_STOP);
            } else {
                throw new ComponentException("The output's datatype is " + dataType.getDisplayName()
                    + " and is currently not supported for the component.");
            }
        }
        return result;
    }

    /**
     * Converts a given result set to a small table typed datum of the given size.
     * 
     * @param rowCount The rows of the result set.
     * @param columnCount The columns of the result set.
     * @param outputToWriteTo The output name to write to.
     * @param resultSet The result set.
     * @param tdFactory The typed datum factory.
     * @return
     * @throws ComponentException
     */
    private SmallTableTD convertResultSetToSmallTableTD(int rowCount, int columnCount, String outputToWriteTo, ResultSet resultSet)
        throws ComponentException {
        TypedDatumFactory tdFactory = typedDatumService.getFactory();
        SmallTableTD smallTableTD = tdFactory.createSmallTable(rowCount, columnCount);
        // fill table with data
        try {
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    int rowInTable = resultSet.getRow() - 1;
                    int colInTable = i - 1;
                    if (resultSet.getObject(i) instanceof String) {
                        smallTableTD.setTypedDatumForCell(tdFactory.createShortText(resultSet.getString(i)), rowInTable, colInTable);
                    } else if (resultSet.getObject(i) instanceof Integer) {
                        smallTableTD.setTypedDatumForCell(tdFactory.createInteger(resultSet.getInt(i)), rowInTable, colInTable);
                    } else if (resultSet.getObject(i) instanceof Long) {
                        smallTableTD.setTypedDatumForCell(tdFactory.createInteger(resultSet.getLong(i)), rowInTable, colInTable);
                    } else if (resultSet.getObject(i) instanceof Float) {
                        smallTableTD.setTypedDatumForCell(tdFactory.createFloat(resultSet.getFloat(i)), rowInTable, colInTable);
                    } else if (resultSet.getObject(i) instanceof Double) {
                        smallTableTD.setTypedDatumForCell(tdFactory.createFloat(resultSet.getDouble(i)), rowInTable, colInTable);
                    } else if (resultSet.getObject(i) instanceof Boolean) {
                        smallTableTD.setTypedDatumForCell(tdFactory.createBoolean(resultSet.getBoolean(i)), rowInTable, colInTable);
                    } else if (resultSet.getObject(i) == null) {
                        smallTableTD.setTypedDatumForCell(tdFactory.createEmpty(), rowInTable, colInTable);
                    } else if (resultSet.getObject(i) instanceof BigDecimal) {
                        throw new ComponentException(StringUtils.format(ERROR_FILLING_SMALL_TABLE, outputToWriteTo)
                            + "Note that currently no internal data type represents 'big decimal' values.");
                    } else if (resultSet.getObject(i) instanceof BigInteger) {
                        throw new ComponentException(StringUtils.format(ERROR_FILLING_SMALL_TABLE, outputToWriteTo)
                            + "Note that currently no internal data type represents 'big integer' values.");
                    } else {
                        throw new ComponentException(StringUtils.format(ERROR_FILLING_SMALL_TABLE, outputToWriteTo)
                            + "The given data type '" + resultSet.getObject(i).getClass().getName() + "' is currently not supported.");
                    }
                    // Datetime currently not supported in DB component - seeb_ol, April 2016
                    // else if (resultSet.getObject(i) instanceof Timestamp) {
                    // smallTableTD.setTypedDatumForCell(tdFactory.createDateTime((resultSet.getTimestamp(i).getTime())), rowInTable,
                    // colInTable);
                    // }
                }
            }
        } catch (SQLException e) {
            throw new ComponentException("Failed to distribute result set. Database response: " + e.getMessage());
        }
        return smallTableTD;
    }

    protected void distributeResults(String outputToWriteTo, ResultSet resultSet) throws SQLException, ComponentException {
        TypedDatum convertedTypedDatum = convertResultSetToTypedDatum(resultSet, outputToWriteTo);
        if (convertedTypedDatum != null) {
            componentContext.writeOutput(outputToWriteTo, convertedTypedDatum);
        } else {
            componentContext.getLog().componentError(
                "Failed to convert the database result set into to the given output " + outputToWriteTo + FULL_STOP);
        }
    }

    @Override
    public void dispose() {
        closeConnection();
    }

    @Override
    public void tearDown(FinalComponentState state) {
        switch (state) {
        case FINISHED:
            closeConnection();
        default:
            break;
        }
        super.tearDown(state);
    }

    protected void closeConnection() {
        if (jdbcConnection != null) {
            try {
                if (!jdbcConnection.isClosed()) {
                    jdbcConnection.close();
                }
            } catch (SQLException e) {
                componentContext.getLog().componentError("Database connection could not be closed. " + e.getMessage());
            }
            jdbcConnection = null;
        }
    }

}