Package de.aristaflow.adept2.base.dbaccess

Interface DBAccessProvider

  • public interface DBAccessProvider
    This is a provider interface that must be implemented in a vendor-specific way in order to support a certain DBMS.

    Implementations must have a public default constructor!

    • Field Detail

      • CONF_LOCK_TABLE_POLL_TIME

        static final String CONF_LOCK_TABLE_POLL_TIME
        Configuration key: The time in milliseconds when to poll for a table lock. This is only relevant for DBMS which do not support locking a table with a timeout.
        See Also:
        Constant Field Values

      • CONF_MAX_SEQ_COUNT

        static final String CONF_MAX_SEQ_COUNT
        Configuration key: The amount of IDs (entries) in a sequence table before that table is cleared. This is only relevant for simulated sequences.
        See Also:
        Constant Field Values

    • Method Detail

      • getDatabaseName

        DatabaseName getDatabaseName()
        Gets the name of the underlying database. This avoids the need to parse the product name from the metadata which is rather arbitrary.
        Returns:
        The name of the underlying database.

      • init

        void init​(org.apache.commons.configuration2.Configuration configuration,
          String applicationName)
        Initialises this access provider using the designated configuration and the designated application name. The contents of the configuration may depend on the underlying DBMS. This also applies to how the application name will be used, e. g. for profiling or logging tools. Both parameters are optional.
        Parameters:
        configuration - The configuration for this access provider or null if there is no specific configuration.
        applicationName - The application name used for new connections or null if no application name should be set.

      • isSupportedConnectionURL

        boolean isSupportedConnectionURL​(String subProtocol,
                                 String subName)
        Checks (and returns) if the provided connection URL of the form 'jdbc:<subprotocol>:<subname>' can be handled by this implementation of DBAccess. The URL is already split into its components and supplied in the parameters of the method.
        Parameters:
        subProtocol - the sub-protocol part of the connection URL
        subName - the sub-name part of the connection URL
        Returns:
        whether the connection URL is supported by this DBAccess implementation

      • getEXCEPTKeyword

        String getEXCEPTKeyword()
        Gets the keyword that is interpreted as EXCEPT by the underlying database-management-system. This method is necessary since the systems of some vendors do not understand the SQL-keyword EXCEPT. For an SQL-compliant database-management-system this method returns "EXCEPT".
        Returns:
        The string representing the keyword which is identical to EXCEPT in the underlying database-management-system.

      • getNextIdString

        String getNextIdString​(String seqName)
        Gets the string for usage as column in SELECT retrieving the next ID from the designated sequence.
        Parameters:
        seqName - The name of the sequence from which to get (the string retrieving) the next ID.
        Returns:
        The string retrieving the next ID from the sequence with the designated name.

      • toUpperCase

        String toUpperCase​(String s)
        Gets the designated string converted to upper case as suggested by the underlying database-management system. If a locale is required, Locale.ENGLISH will be used.
        Implementations do not need to ask the DBMS but may create the upper case with best effort.
        Parameters:
        s - The string which to convert to upper case.
        Returns:
        The designated string converted to upper case corresponding to the conversion rules of the underlying database-management system.

      • supportsIntersect

        boolean supportsIntersect()
        Gets whether the underlying DBMS supports intersect. Some DBMS do not support this so callers have to handle this differently, e. g. by using an appropriate WHERE-clause.
        Returns:
        Whether the underlying DBMS supports intersect.

      • supportsSqlXml

        boolean supportsSqlXml()
        Gets whether the underlying DBMS supports the data type Types.SQLXML. Some DBMS do not support this so callers have to use Types.CLOB instead.
        Returns:
        Whether the underlying DBMS supports Types.SQLXML.

      • getStringComparison

        String getStringComparison​(int type,
                           String tableName,
                           String columnName,
                           String tableAlias,
                           int length,
                           boolean equal)
        Gets a string to be used in a WHERE-clause for comparing the designated column having the designated type with another string. Use the returned string in a prepared statement or replace the ? with the real content of the string.
        Depending on the (designated) data type of the column on this, the comparison is determined. For instance, most DBMS do not allow to compare CLOB.

        Do not rely on the data type specified when creating the designated column! For instance long character columns may be CLOB instead of the data type provided when creating the column.

        Make sure that the provided string to compare is never null! While null in the designated column will be handled appropriately, providing a null string will usually not work.

        Parameters:
        type - The JDBC data type of the designated column.
        tableName - The name of the table which contains the column.
        columnName - The name of the column which to compare.
        tableAlias - The table alias to be used as prefix of the column in the statement. Do not provide the separating . as this will be added appropriately by the method. If null is provided, the column name will have no prefix.
        length - The length of the string. When reusing the prepared statement for several strings, use the longest one. This allows the underlying database to cast to VARCHAR for comparing.
        equal - Whether to compare the strings for whether they are equal (or whether they are not).
        Returns:
        The string to be used in the WHERE-clause of a prepared statement for comparing the designated column with a string.

      • getTimeDifference

        String getTimeDifference​(String column,
                         long seconds)
        Gets the string representing the timestamp value (to be calculated within the DBMS) based on the designated column having the designated difference (in seconds). Note that the designated column may need to be fully qualified. It may also be a pseudo-column like CURRENT_TIMESTAMP.
        When using this, make sure the referenced column has been set by the database. If not, you may have the wrong timezone and therefore get the wrong results. For instance if the value of a time/date column has been set by the BPM platform, this will usually be UTC time. Then you must not use CURRENT_TIMESTAMP to compare with that column.
        Parameters:
        column - The (fully-qualified) name of the column which contains the time/date/timestamp. This may also be a pseudo-column.
        seconds - The difference in seconds (!!) which to add or remove from the time in the designated column.
        Returns:
        A string to be inserted into a query specifying the designated time difference with respect to the designated column.

      • getDropForeignKeyConstraintKeyword

        String getDropForeignKeyConstraintKeyword()
        To drop a foreign key constraint from table columns, there are two syntaxes available. Each DBMS supports at least one of them, but none is supported by all:
        • ALTER TABLE &lt;tableName&gt; DROP <b>CONSTRAINT</b> &lt;constraintName&gt;
        • ALTER TABLE &lt;tableName&gt; DROP <b>FOREIGN KEY</b> &lt;constraintName&gt;
        Returns:
        CONSTRAINT or FOREIGN KEY, whichever is appropriate for the DBMS

      • getAddForeignKey

        String getAddForeignKey​(ExtendedConnection con,
                        de.aristaflow.adept2.base.dbaccess.model.ForeignKey fk,
                        boolean quoteIdentifiers)
                 throws SQLException
        Gets an SQL string for ALTER TABLE which adds the designated foreign key.
        Parameters:
        con - The connection for which to retrieve the SQL statement.
        fk - The definition of the foreign key to be added.
        quoteIdentifiers - Whether identifiers should be quoted.
        Returns:
        The SQL string for the ALTER TABLE statement for adding the designated foreign key.
        Throws:
        SQLException - If there are problems retrieving the required meta data or the foreign key cannot be created, an SQLException will be thrown.

      • getCorrespondingDBType

        String getCorrespondingDBType​(int jdbcTypeCode)
        Converts the given JDBC type code and given size constraints into a vendor specific SQL type.

        A call to this method is equivalent to getCorrespondingDBType(jdbcTypeCode, -1, -1). See getCorrespondingDBType(int, int, int).

        Parameters:
        jdbcTypeCode - the JDBC type code that should be mapped to a vendor specific SQL type string
        Returns:
        the best effort mapping of the JDBC type to a vendor specific type

      • getCorrespondingDBType

        String getCorrespondingDBType​(int jdbcTypeCode,
                              int size)
        Converts the given JDBC type code and given size constraints into a vendor specific SQL type.

        A call to this method is equivalent to getCorrespondingDBType(jdbcTypeCode, size, -1). See getCorrespondingDBType(int, int, int).

        Parameters:
        jdbcTypeCode - the JDBC type code that should be mapped to a vendor specific SQL type string
        size - the desired size of the type (i.e. length for string and binary types, precision for number types or fractional seconds for time types); use negative value to indicate that size is not used
        Returns:
        the best effort mapping of the JDBC type to a vendor specific type

      • getCorrespondingDBType

        String getCorrespondingDBType​(int jdbcTypeCode,
                              int size,
                              int scale)
        Converts the given JDBC type code and given size constraints into a vendor specific SQL type. The matching isn't and can't be guaranteed to be exact but is performed on a best effort basis. If e.g. CHAR(300) is requested but the DB only supports a length of up to 254, a VARCHAR(300) might be returned if VARCHAR supports greater lengths.

        Please be aware that binary or character types may be mapped to their LOB variants if the specified size doesn't allow the smaller types. This might however lead to problems when setting or getting the values in the regular way. E.g. ResultSet.getString(int) might not always work for CLOBs or only up to a certain size.

        Character types will be translated to Unicode-capable data types if available. Generally, the following types are supported. Implementations are allowed to also support the remaining JDBC types but the requirements for those aren't very clear. E.g. are size and scale enough for them as parameters? Negative values for size and scale indicate that they are not specified.

        • BOOLEAN: 1 bit
        • BIT: 1 bit
        • TINYINT: 1 byte
        • SMALLINT: 2 bytes
        • INTEGER: 4 bytes
        • BIGINT: 8 bytes
        • REAL: 24 bits for mantissa
        • DOUBLE: 54 bits for mantissa
        • FLOAT(precision): precision of mantissa is in bits (1 <= precision <= 53)
        • DECIMAL(precision, scale): precision and scale are in digits
        • NUMERIC(precision, scale): is used synonymously for DECIMAL
        • CHAR(length): length is mandatory
        • VARCHAR(length): length is mandatory
        • LONGVARCHAR(length): length is optional; most DBs apparently don't support length here, so it's mainly used by DBAccess as an indicator in order to map to CLOB if size constraints make it necessary
        • CLOB(length): length is optional and usually not considered by implementations if the underlying DB doesn't support it
        • BINARY(length): length is mandatory
        • VARBINARY(length): length is mandatory
        • LONGVARBINARY(length): length is optional; most DBs apparently don't support length here, so it's mainly used by DBAccess as an indicator in order to map to BLOB if size constraints make it necessary; DBs that do support a length will use their default (e.g. the maximum) when no explicit length is specified
        • BLOB(length): length is optional; very few databases support it and usually not considered by implementations if the underlying DB doesn't support it
        • DATE
        • TIME(fractional seconds): fractional seconds is in digits and optional; max precision or support at all is not guaranteed, but usually between 0 and 9; if not set an implementation default is used
        • TIMESTAMP(fractional seconds): fractional seconds is in digits and optional; max precision or support at all is not guaranteed, but usually between 0 and 9; if not set an implementation default is used
        Parameters:
        jdbcTypeCode - the JDBC type code that should be mapped to a vendor specific SQL type string
        size - the desired size of the type (i.e. length for string and binary types, precision for number types or fractional seconds for time types); use negative value to indicate that size is not used
        scale - only used for some number types and determines the precision after the decimal point; use negative value to indicate that scale is not used
        Returns:
        the best effort mapping of the JDBC type to a vendor specific type or null if the type is not supported
        Throws:
        IllegalArgumentException - if the combination of JDBC type code, size and scale is illegal, e.g. if scale or size is set for Types.INTEGER

      • formatValue

        String formatValue​(Object value,
                   int typeCode)
        Formats the given value to a string that can be used in SQL statements and queries. The given JDBC type code (see Types) determines the target type. The main intention for this method is to be an alternative where it is not easily possible to use PreparedStatements, e.g. in highly dynamic and complex queries.

        Please be aware that string escaping may not have been implemented in a 100% safe way due to lacking specifications for each database. So for strings it's much safer to use PreparedStatements to prevent any kind of SQL injection.

        This method may throw a runtime exception if object type of the value and type code don't fit together. However it does NOT guarantee to do so, which may result in improperly formatted or even illegal values.

        For all date-related types (i.e. DATE, TIME and TIMESTAMP) not only Date is allowed, but also Date, Time and Timestamp.

        A null value will result in the string "null".

        Booleans are properly formatted to 1 or 0 if the target type is Types.BIT.

        Parameters:
        value - the value to format
        typeCode - the JDBC type code of the target type
        Returns:
        the value formatted according to the given type code

      • renameTable

        void renameTable​(ExtendedConnection con,
                 String oldName,
                 String newName)
          throws SQLException
        Renames the designated table using the designated new name. Usually this will intermediately drop the foreign key constraints.
        Parameters:
        con - The connection on which to execute the operation.
        oldName - The name of the table which to rename.
        newName - The new name of the table.
        Throws:
        SQLException - If a database access error occurs, the specified table name is invalid, a SQLException will be thrown.

      • addColumn

        void addColumn​(ExtendedConnection con,
               String tableName,
               String columnName,
               String correspondingDbType)
        throws SQLException
        Adds an additional column to the designated table of the designated type. Use getCorrespondingDBType(int) or one of the overloaded methods to determine the database-specific type of the column.
        The new column will be nullable. If this is not the desired behaviour, add values for all entries and update the column afterwards.
        Parameters:
        con - The connection on which to execute the operation.
        tableName - The name of the table to which to add a new column.
        columnName - The name of the column which to add.
        correspondingDbType - The string representation of the desired type of the column. Use the string returned by getCorrespondingDBType(int) (or the overloaded methods).
        Throws:
        SQLException - If a database access error occurs, the specified table name or column name is invalid, a SQLException will be thrown.

      • updateColumnType

        void updateColumnType​(ExtendedConnection con,
                      String tableName,
                      String columnName,
                      String correspondingDbType)
               throws SQLException
        Sets the type of the designated column of the designated table to the provided one. Use getCorrespondingDBType(int) or one of the overloaded methods to determine the database-specific type of the column.

        Note that not all DBMS support shortening the type of a column, for instance Derby. This requires creating a new column, copying the data, dropping the old column and renaming the new one. Do not forget to handle indexes and key columns appropriately if required.

        Parameters:
        con - The connection on which to execute the operation.
        tableName - The table containing the column to change the data type.
        columnName - The name of the column to change the data type.
        correspondingDbType - The string representation of the desired type of the column. Use the string returned by getCorrespondingDBType(int) (or the overloaded methods).
        Throws:
        SQLException - If a database access error occurs, the specified table name or column name is invalid, a SQLException will be thrown.

      • updateColumnsType

        void updateColumnsType​(ExtendedConnection con,
                       Map<String,​Collection<String>> columns,
                       String correspondingDbType)
                throws SQLException
        Sets the type of the designated columns of the designated tables to the provided one. Use getCorrespondingDBType(int) or one of the overloaded methods to determine the database-specific type for all columns.

        Note that not all DBMS support shortening the type of a column, for instance Derby. This requires creating a new column, copying the data, dropping the old column and renaming the new one. Do not forget to handle indexes and key columns appropriately if required.

        This is similar to updateColumnType(ExtendedConnection, String, String, String) but allows to updates several columns of different tables at the same type. This is useful for DBMS which have a lot of overhead to change one column, e. g. temporarily remove column constraints, indexes and foreign keys of a changed column. When changing the type of a column referenced by a foreign key, the type of the referencing column also has to be changed.

        Parameters:
        con - The connection on which to execute the operation.
        columns - The table names mapped to the names of the corresponding columns of which to change the data type.
        correspondingDbType - The string representation of the desired type of the columns. Use the string returned by getCorrespondingDBType(int) (or the overloaded methods).
        Throws:
        SQLException - If a database access error occurs, the specified table or column names are invalid, a SQLException will be thrown.

      • updateColumnsNullable

        void updateColumnsNullable​(ExtendedConnection con,
                           String tableName,
                           boolean nullable,
                           String... columns)
                    throws SQLException
        Sets the designated columns of the designated table nullable (not nullable). If the columns are already nullable (not nullable), no change will be made.
        Note that only columns being not part of a key can be nullable. Vice versa only columns having no null value can be set to not nullable. Not respecting this will lead to an SQLException.
        Parameters:
        con - The connection on which to execute the operation.
        tableName - The table containing the columns to change the nullable-state.
        nullable - Whether the columns should allow null values or not.
        columns - The names of the columns to change the nullable-state.
        Throws:
        SQLException - If there are problems checking the metadata or changing the nullable-state, an SQLException will be thrown.

      • updateColumnDefault

        void updateColumnDefault​(ExtendedConnection con,
                         String tableName,
                         String columnName,
                         Object defaultValue)
                  throws SQLException
        Sets the designated value as default value for the designated column.
        Providing null as default value removes the default value.
        Parameters:
        con - The connection on which to execute the operation.
        tableName - The table containing the column to change the default value.
        columnName - The name of the column to change the default value.
        defaultValue - The value which to set as default. This will formatted appropriately.
        Throws:
        SQLException - If there are problems retrieving the metadata (if required) or changing the default value, an SQLException will be thrown.

      • renameColumn

        void renameColumn​(ExtendedConnection con,
                  String tableName,
                  String oldColumnName,
                  String newColumnName)
           throws SQLException
        Renames the designated column from the table which corresponds to an ALTER TABLE RENAME COLUMN statement.
        Parameters:
        con - The connection on which to execute the operation.
        tableName - The name of the table to which to rename a column.
        oldColumnName - The old name of column which to rename.
        newColumnName - The new name of the column.
        Throws:
        SQLException - If a database access error occurs, the specified table name or column name is invalid, a SQLException will be thrown.

      • dropColumn

        void dropColumn​(ExtendedConnection con,
                String tableName,
                String columnName)
         throws SQLException
        Drops the specified column from the table which corresponds to an ALTER TABLE DROP COLUMN statement.

        Do not try to drop columns that are referenced in any (primary key, foreign key, unique, ...) constraints. This may have unpredictable results or even fail (in a data-destroying way).

        Parameters:
        con - The connection on which to execute the operation.
        tableName - The name of the table of which the column type is of interest.
        columnName - The name of column of which the type is of interest.
        Throws:
        SQLException - If a database access error occurs, the specified table name or column name is invalid, a SQLException will be thrown.

      • adaptResultSetType

        int adaptResultSetType​(int resultSetType,
                       int resultSetConcurrency)

      • getColumnType

        int getColumnType​(ExtendedConnection con,
                  String columnName,
                  String tableName)
           throws SQLException
        Gets the type of a specific column as java.sql.Types.
        Parameters:
        con - The connection on which to execute the operation.
        tableName - The name of the table of which the column type is of interest.
        columnName - The name of column of which the type is of interest.
        Returns:
        The SQL-type of the specified column as defined in java.sql.Types.
        Throws:
        SQLException - If a database access error occurs or the specified table name or column name is invalid, a SQLException will be thrown.
        See Also:
        Types

      • isTableMissing

        boolean isTableMissing​(SQLException ex)

      • isUniqueViolation

        boolean isUniqueViolation​(SQLException ex)

      • isForeignKeyViolation

        boolean isForeignKeyViolation​(SQLException ex)

      • isLockTimeout

        boolean isLockTimeout​(SQLException ex)
        Gets whether the designated exception indicates a lock timeout, e. g. while waiting for a table lock.
        Parameters:
        ex - The exception to be tested
        Returns:
        Whether the designated exception was caused by a lock timeout.

      • isSyntaxError

        boolean isSyntaxError​(SQLException ex)
        Gets whether the designated exception indicates syntax error.
        Parameters:
        ex - The exception to be tested
        Returns:
        Whether the designated exception was caused by a syntax error.

      • getCurrentTime

        Timestamp getCurrentTime​(ExtendedConnection con)
                  throws SQLException
        Gets the current time (as timestamp) from the underlying DBMS in UTC timezone. This may differ from the time provided by the current JVM and allows for using the same time across several nodes.
        Parameters:
        con - The connection with which to retrieve the current time.
        Returns:
        The current time (as timestamp) in UTC timezone from the underlying DBMS.
        Throws:
        SQLException - If there are problems retrieving the current time, an SQLException will be thrown.

      • createSequence

        void createSequence​(ExtendedConnection connection,
                    String sequenceName,
                    long start,
                    long increment)
             throws SQLException
        Creates a new sequence in the database. A sequence is a thread-safe way to generate IDs.
        Parameters:
        connection - the connection on which to execute the operation
        sequenceName - the name of the sequence to be created
        start - the initial value of the sequence, i.e. the first value that will be returned by nextID(ExtendedConnection, String); may be negative
        increment - the value by which to increment the ID after a call to nextID(ExtendedConnection, String); may be negative
        Throws:
        SQLException - if a database access error occurs

      • dropSequence

        void dropSequence​(ExtendedConnection connection,
                  String sequenceName)
           throws SQLException
        Drop the sequence with the given name.
        Parameters:
        connection - the connection on which to execute the operation
        sequenceName - the name of the sequence to be dropped
        Throws:
        SQLException - if a database access error occurs or the specified sequence doesn't exist

      • sequenceExists

        boolean sequenceExists​(ExtendedConnection connection,
                       String sequenceName)
                throws SQLException
        Returns whether a sequence with the given name exists.
        Parameters:
        connection - the connection on which to execute the operation
        sequenceName - the sequence to be tested
        Returns:
        whether a sequence with the given name exists
        Throws:
        SQLException - if a database access error occurs

      • nextID

        long nextID​(ExtendedConnection connection,
            String sequenceName)
     throws SQLException
        Returns the next ID from the sequence with the given name.
        Parameters:
        connection - the connection on which to execute the operation
        sequenceName - the sequence from which to return the next ID
        Returns:
        the next ID from the given sequence
        Throws:
        SQLException - if a database access error occurs

      • lockTable

        void lockTable​(ExtendedConnection con,
               String tableName,
               long timeout)
        throws SQLException,
               TimeoutException
        Locks the designated table exclusively until the end of the corresponding transaction. The calling thread will wait for the designated amount of milliseconds for the lock.
        While the wait time is provided in milliseconds, not all databases support this but only full seconds. In such a case if the value is not a full second, the next greater integer value will be used.

        The designated connection needs to have auto-commit disabled. The table lock is held until the end of the transaction! So be sure to not hold any other locks and usually use a separate transaction for locking the table unless modifying the very same table.

        Note that not all DBMS support timed wait. These will throw an UnsupportedOperationException.

        Parameters:
        con - The connection on which to lock the table.
        tableName - The name of the table which to lock. This table has to exist.
        timeout - The wait time in milliseconds. Use 0 to not wait at all (just try to lock) or a negative value to wait forever.
        Throws:
        SQLException - If there are problems locking the designated table, an SQLException will be thrown.
        TimeoutException - If the designated timeout elapsed before the lock can be acquired, a TimeoutException will be thrown.

      • useLockTimeout

        String useLockTimeout​(ExtendedConnection con,
                      String query,
                      long timeout)
               throws SQLException
        Appends the designated lock timeout in milliseconds to the designated query (DDL and DML are not supported!). This may also be a NOWAIT as well as waiting forever.
        While the wait time is provided in milliseconds, not all databases support this but only full seconds. In such a case if the value is not a full second, the next greater integer value will be used.

        Note that not all DBMS support a lock timeout for each query separately. These will return the designated query unchanged. In this case you need to set the lock timeout for the transaction temporarily using setLockTimeout(ExtendedConnection, long).

        DBMS not supporting a lock timeout will also return the designated query unchanged.

        Parameters:
        con - The connection for which to set a lock timeout for the designated query.
        query - The query which should use a specific lock timeout.
        timeout - The lock timeout in milliseconds. Use 0 to not wait at all, use -1 to wait forever.
        Returns:
        The query with the corresponding lock timeout appended.
        Throws:
        SQLException - If there are problems appending the lock timeout, e.g. when accessing the meta data of the designated connection, an SQLException will be thrown.

      • setLockTimeout

        long setLockTimeout​(ExtendedConnection con,
                    long timeout)
             throws SQLException
        Sets the lock timeout for the current transaction or connection in the designated connection to the designated timeout (in milliseconds) and returns the current lock timeout. Depending on the DBMS, the lock timeout either applies until the end of the active transaction or until the end of the connection. So make sure to reset the lock timeout if no longer needed in a finally.
        While the wait time is provided in milliseconds, not all databases support this but only full seconds. In such a case if the value is not a full second, the next greater integer value will be used. However, the return value will be the appropriate amount of milliseconds.

        Note that not all DBMS support a lock timeout for a transaction but require the lock timeout for each query separately. So you need to setLockTimeout(ExtendedConnection, long) as well as useLockTimeout(ExtendedConnection, String, long).

        Note that not all DBMS support a dynamic lock timeout. These will throw an UnsupportedOperationException.

        Parameters:
        con - The connection which contains the transaction for which to set the lock timeout.
        timeout - The lock timeout in milliseconds. Use 0 to not wait at all, use -1 to wait forever.
        Returns:
        The timeout before changing it.
        Throws:
        SQLException - If there are problems setting the lock timeout, an SQLException will be thrown.

      • setDefaultLockTimeout

        void setDefaultLockTimeout​(ExtendedConnection con)
                    throws SQLException
        Sets the default value for the lock timeout. This is required to reset pooled connections.
        Parameters:
        con - The connection for which to set the default lock timeout.
        Throws:
        SQLException - If there are problems setting the lock timeout, an SQLException will be thrown.

      • createRecursionTable

        String createRecursionTable​(ExtendedConnection connection,
                            String[] selectAttributes,
                            String tableName,
                            String startCondition,
                            ParentToChildConnection[] parentToChildConnections,
                            int maxDepth)
                     throws SQLException
        Creates a (temporary) table or view containing tuples that match a recursive/hierarchical SQL-query which is based on the specified parameters. This method is necessary since recursive queries are completely different or even non-existing in some database-management-systems. If no recursion exists (for example in Derby), it will have to be implemented directly in Java.

        Therefore not all features are supported. The main restriction are the SELECT-attributes. They are always from the current recursion step which means a tuple always contains only values from the child tables. There are no tuples containing (joined) parent and child attributes. Further aggregation is not supported. This can be achieved by grouping all resulting tuples by their level.

        Following are examples for translations of the method parameters to DB2- and Oracle-syntax. in DB2: 

           CREATE TABLE return AS
     WITH RecRel(Depth, selectAttributes[]) AS
     (SELECT 1, selectAttributes[]
      FROM tableName
      WHERE startCondition
     UNION ALL
      SELECT Parent.Depth + 1, Child.selectAttributes[]
      FROM RecRel AS Parent, tableName AS Child
      WHERE Parent.parentToChildConnection[0].getParentAttribute()
            parentToChildConnection[0].getOperator()
            Child.parentToChildConnection[0].getChildAttribute()
        AND Parent.parentToChildConnection[1].getParentAttribute()
            parentToChildConnection[1].getOperator()
            Child.parentToChildConnection[1].getChildAttribute()
        AND ...
        AND Parent.Depth < maxDepth
     )
     SELECT *
     FROM RecRel
        in Oracle: 
           CREATE TABLE return AS
     SELECT Depth, selectAttributes[]
     FROM tableName
     START WITH startCondition
     CONNECT BY PRIOR parentToChildConnection[0]
       AND PRIOR parentToChildConnection[1]
       AND ...
       AND Depth <= maxDepth
        Parameters:
        connection - the connection on which to execute the operation
        selectAttributes - Array of the names of the columns to be in the created recursion table. The names have to be valid column names of the specified table.
        tableName - The name of the table which contains the hierarchical data. If the hierarchical data spans several tables, a new temporary table will have to be created by joining these tables. Thus the name of the temporary table is to be used as parameter.
        startCondition - A string forming a valid WHERE-condition which specifies the conditions which have to hold for the top-level tuples respectively the tuples for the start of the recursion. The string may be any valid WHERE-condition, even complex ones, as long as it is understood by all database-management-systems. The names have to be valid column names of the specified table.
        parentToChildConnections - An array consisting of ParentToChildConnections that specify WHERE-conditions for joining the hierarchical data. The individual ParentToChildConnections are logically linked via AND. These can only contain columns that are part of the selectAttributes.
        maxDepth - The maximum depth of the recursion/hierarchical level which is needed. The recursions will stop when this depth is reached even if not all tuples are reached.
        Returns:
        The name of the newly created relational table containing the result set of the recursive/hierarchical query.
        Throws:
        SQLException - If a database access error occurs or the specified attributes, the table name or the conditions are invalid, a SQLException will be thrown.

      • createRecursionTable

        String createRecursionTable​(ExtendedConnection connection,
                            String[] selectAttributes,
                            String[] rootAttributes,
                            String tableName,
                            String startCondition,
                            ParentToChildConnection[] parentToChildConnections)
                     throws SQLException
        Like createRecursionTable(ExtendedConnection, String[], String, String, ParentToChildConnection[], int) but without a maximum depth. Here the recursion stops as soon as there are no more tuples in a recursion step.

        Following are examples for translations of the method parameters to DB2- and Oracle-syntax. in DB2: 

           CREATE TABLE return AS
     WITH RecRel(Depth, selectAttributes[]) AS
     (SELECT 1, selectAttributes[]
      FROM tableName
      WHERE startCondition
     UNION ALL
      SELECT Parent.Depth + 1, Parent.rootAttributes[], Child.selectAttributes[]
      FROM RecRel AS Parent, tableName AS Child
      WHERE Parent.parentToChildConnection[0].getParentAttribute()
            parentToChildConnection[0].getOperator()
            Child.parentToChildConnection[0].getChildAttribute()
        AND Parent.parentToChildConnection[1].getParentAttribute()
            parentToChildConnection[1].getOperator()
            Child.parentToChildConnection[1].getChildAttribute()
        AND ...
     )
     SELECT *
     FROM RecRel
        in Oracle: 
           CREATE TABLE return AS
     SELECT Depth, CONNECT_BY_ROOT rootAttributes[], (additional) selectAttributes[]
     FROM tableName
     START WITH startCondition
     CONNECT BY PRIOR parentToChildConnection[0]
       AND PRIOR parentToChildConnection[1]
       AND ...
        Parameters:
        connection - the connection on which to execute the operation
        selectAttributes - Array of the names of the columns to be in the created recursion table. The names have to be valid column names of the specified table.
        rootAttributes - Array of the names of columns of the root table, that is, these values will be constant in each recursion step and therefore stem from recursion initialisation. This is a subset of the selectAttributes. If this is null, each tuple from a recursion step will have new attributes.
        tableName - The name of the table which contains the hierarchical data. If the hierarchical data spans several tables, a new temporary table will have to be created by joining these tables. Thus the name of the temporary table is to be used as parameter.
        startCondition - A string forming a valid WHERE-condition which specifies the conditions which have to hold for the top-level tuples respectively the tuples for the start of the recursion. The string may be any valid WHERE-condition, even complex ones, as long as it is understood by all database-management-systems. The names have to be valid column names of the specified table.
        parentToChildConnections - An array consisting of ParentToChildConnections that specify WHERE-conditions for joining the hierarchical data. The individual ParentToChildConnections are logically linked via AND. These can only contain columns that are part of the selectAttributes.
        Returns:
        The name of the newly created relational table containing the result set of the recursive/hierarchical query.
        Throws:
        SQLException - If a database access error occurs or the specified attributes, the table name or the conditions are invalid, a SQLException will be thrown.

      • dropRecursionTable

        void dropRecursionTable​(ExtendedConnection connection,
                        String tableName)
                 throws SQLException
        The specified table that has been created via createRecursionTable(String[], String, String, String[], int) is dropped. This needs to be done to clean up the database. If the provided table has not been created by createRecursionTable(String[], String, String, String[], int), the call will be ignored. If the table has been created by createRecursionTable(String[], String, String, String[], int) but does not exists in the database any more, a DBException will be raised.

        Implementors of this method have to take care not to drop the table again in dropAllRecursionTables().

        Parameters:
        connection - the connection on which to execute the operation
        tableName - The name of the created temporary table. If it has not been created by createRecursionTable(String[], String, String, String[], int), the call will be ignored. If the table does otherwise not exist in the database, a DBException will be raised.
        Throws:
        SQLException - If a database access error occurs, a SQLException will be thrown, especially if the provided table does not exist.
        See Also:
        dropAllRecursionTables(ExtendedConnection), createRecursionTable(ExtendedConnection, String[], String, String, ParentToChildConnection[], int)

      • terminate

        void terminate​(ExtendedConnection con)
        Terminates this access provider allowing it to do some clean-up if required. Implementations should log exceptions but go on with the shutdown.
        Parameters:
        con - The connection with which to access the database for clean-up.