Package jetbrains.buildServer.serverSide.db

Class DBFunctions

  • All Implemented Interfaces:
    SQLExceptionConvertor
    public abstract class DBFunctions
extends Object
implements SQLExceptionConvertor
    Database functions. Provides useful functions for interacting with database.

    Connection
    In order to get an instance of DBFunctions class, use a DBFunctionsProvider. It is a safe way to perform database operations.

    Transactions
    DBFunctions doesn nothing with transactions, except when it asked to execute DDL statements. It allows to work in both auto-committed and normal modes.
    When it asked to execute select and DML queries only, it follows by the general rules for working with a jdbc connection. The client should manipulate with transactions by theirself.
    When it asked to execute a DDL statement, it can commit the current transaction (if one exists), then execute the specified DDL statement, and then commit transaction if successfull or roll it back in case of exceptions.
    Really transactions are handled by an implementation of DBFunctionsProvider that provided with an instance of DBFunctions.

    SQL macros
    DBFunctions supports additional tokens inside SQL statements - macros. Each macro starts with the sharp (#) sign and consists of one word wich can be followed by parameters. Parameters are covered with braces and separated with semicolons.

    SQL parameters
    Most of functions support two ways how SQL parameters:

    1. sequential (each one is noted as a query (?) sign in SQL statenment), their values are passed by an array of values;
    2. named (each one is noted by a name prefixed with the colon (:

    Results
    Each function that performing a 'select' query retrieves all result rows internally, and returns a ready collection of values.

    Exceptions
    Each function can produce DBExceptions instead of SQLExceptions.

    Closing
    Don't forget to call the close() function when you've finished to work with DBFunctions instance (to prevent resources loss). Use try..finally for it.

    Author:
    Leonid Bushuev

    • Field Detail

      • DEFAULT_FETCH_SIZE_FOR_MORE_THAN_ONE_ROW

        public static final int DEFAULT_FETCH_SIZE_FOR_MORE_THAN_ONE_ROW

      • myConnection

        @NotNull
protected final Connection myConnection

      • myDialect

        @NotNull
protected final SQLDialect myDialect

    • Method Detail

      • create

        @NotNull
public static DBFunctions create​(@NotNull
                                 SQLDialect dialect,
                                 @NotNull
                                 DataSource dataSource)
        Creates an instance of DBFunctions using the given data source. After using don't forget to call close() to close the taken connection.
        Parameters:
        dataSource - data source to take a connection from.
        Returns:
        an instance of DBFunctions with just taken connection.

      • create

        @NotNull
public static DBFunctions create​(@NotNull
                                 SQLDialect dialect,
                                 @NotNull
                                 DataSource dataSource,
                                 @Nullable
                                 DatabaseAccessChecker databaseAccessChecker)
        Creates an instance of DBFunctions using the given data source. After using don't forget to call close() to close the taken connection.
        Parameters:
        dataSource - data source to take a connection from.
        databaseAccessChecker - database access checker to use for SQL queries
        Returns:
        an instance of DBFunctions with just taken connection.

      • setDefaultDatabaseAccessChecker

        public static void setDefaultDatabaseAccessChecker​(@Nullable
                                                   DatabaseAccessChecker databaseAccessChecker)

      • queryForSingleString

        @Nullable
@Contract("_, !null, _ -> !null; ")
public final String queryForSingleString​(@NotNull
                                         String query,
                                         @Nullable
                                         String defaultValue,
                                         Object... params)
                                  throws DBException
        Performs a "select" query and expects one row with one column.
        Parameters:
        query - the "select" query to execute.
        defaultValue - the value that will be returned when no rows are selected.
        params - sequential parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException

      • queryForSingleString

        @Nullable
@Contract("_, !null, _ -> !null; ")
public final String queryForSingleString​(@NotNull
                                         String query,
                                         @Nullable
                                         String defaultValue,
                                         @NotNull
                                         Map<String,​Object> params)
                                  throws DBException
        Performs a "select" query and expects one row with one column.
        Parameters:
        query - the "select" query to execute.
        defaultValue - the value that will be returned when no rows are selected.
        params - named parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException

      • queryForValue

        @Nullable
public final <T> T queryForValue​(@NotNull
                                 Class<T> clazz,
                                 @NotNull
                                 String query,
                                 Object... params)
                          throws DBException
        Performs a "select" query and expects one row with one column.
        Parameters:
        query - the "select" query to execute.
        params - sequential parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException
        Since:
        9.0

      • queryForValue

        @Nullable
public final <T> T queryForValue​(@NotNull
                                 Class<T> clazz,
                                 @NotNull
                                 String query,
                                 @NotNull
                                 Map<String,​Object> params)
                          throws DBException
        Performs a "select" query and expects one row with one column.
        Parameters:
        query - the "select" query to execute.
        params - named parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException
        Since:
        9.0

      • queryForSingleBoolean

        public final boolean queryForSingleBoolean​(@NotNull
                                           String query,
                                           boolean defaultValue,
                                           Object... params)
                                    throws DBException
        Performs a "select" query and expects one row with one boolean or integer column.

        Integer values also is interpreted as boolean: values 1 and greater means true, 0 and lessser - false.

        Parameters:
        query - the "select" query to execute.
        defaultValue - the value that will be returned when no rows are selected.
        params - sequential parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException
        Since:
        8.0.5

      • queryForSingleInt

        public final int queryForSingleInt​(@NotNull
                                   String query,
                                   int defaultValue,
                                   Object... params)
                            throws DBException
        Performs a "select" query and expects one row with one integer column.
        Parameters:
        query - the "select" query to execute.
        defaultValue - the value that will be returned when no rows are selected.
        params - sequential parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException

      • queryForSingleLong

        public final long queryForSingleLong​(@NotNull
                                     String query,
                                     long defaultValue,
                                     Object... params)
                              throws DBException
        Performs a "select" query and expects one row with one long integer column.
        Parameters:
        query - the "select" query to execute.
        defaultValue - the value that will be returned when no rows are selected.
        params - sequential parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException

      • queryForTwoLongsSingleRecord

        public final Pair<Long,​Long> queryForTwoLongsSingleRecord​(@NotNull
                                                                String query,
                                                                Object... params)
                                                         throws DBException
        Performs a "select" query and expects one row with two long integer columns.
        Parameters:
        query - the "select" query to execute.
        params - sequential parameters for the query.
        Returns:
        a Pair object containing the two (usually minimum and maximum) values of the selected rows, or a Pair object with null values if no rows are selected.
        Throws:
        DBException - if there is an error executing the query.
        Since:
        2023.11

      • queryForSingleTimestamp

        @Nullable
public final Timestamp queryForSingleTimestamp​(@NotNull
                                               String query,
                                               @Nullable
                                               Timestamp defaultValue,
                                               Object... params)
                                        throws DBException
        Performs a "select" query and expects one row with one timestamp column.
        Parameters:
        query - the "select" query to execute.
        defaultValue - the value that will be returned when no rows are selected.
        params - sequential parameters for the query.
        Returns:
        the value of the first row first column, or the defaultValue if no rows selected.
        Throws:
        DBException

      • queryForInts

        @NotNull
public final List<Integer> queryForInts​(@NotNull
                                        String query,
                                        Object... params)
        Executes the specified query and expects rows with integer values in the first column. Returns the first column as a list of integer values. Preserves the order.

        If you're concerned about performance, consider using queryForIntList(String, Object...) instead.

        Parameters:
        query - a query to execute.
        params - sequential parameters.
        Returns:
        values from the first column of result set (db nulls are presented as java nulls).
        See Also:
        queryForIntList(String, Object...)

      • queryForIntList

        @NotNull
public final gnu.trove.list.TIntList queryForIntList​(@NotNull
                                                     String query,
                                                     Object... params)

        Executes the specified query and expects rows with Types.INTEGER values in the first column. Returns the first column as a list of int values. Preserves the order.

        This method ignores any SQL null's encountered (i. e. the size of the list returned may be less than the result of the corresponding SELECT count(*) ... query with the same predicates).

        Parameters:
        query - the query to execute.
        params - sequential parameters.
        Returns:
        the values from the first column of the result set (SQL null's are ignored).
        Since:
        2017.2.4, 2018.1
        See Also:
        queryForInts(String, Object...)

      • queryForInts

        @NotNull
public final List<Integer> queryForInts​(@NotNull
                                        String query,
                                        @Nullable
                                        Integer maxRows,
                                        @NotNull
                                        Map<String,​Object> params)
        Executes the specified query and expects rows with integer values in the first column. Returns the first column as a list of integer values. Preserves the order.

        If you're concerned about performance, consider using queryForIntList(String, Map) instead.

        Parameters:
        query - a query to execute.
        maxRows - max rows to return or null if there is no limit
        params - named parameters.
        Returns:
        values from the first column of result set (db nulls are presented as java nulls).
        See Also:
        queryForIntList(String, Map)

      • queryForIntList

        @NotNull
public final gnu.trove.list.TIntList queryForIntList​(@NotNull
                                                     String query,
                                                     @NotNull
                                                     Map<String,​Object> params)

        Executes the specified query and expects rows with Types.INTEGER values in the first column. Returns the first column as a list of int values. Preserves the order.

        This method ignores any SQL null's encountered (i. e. the size of the list returned may be less than the result of the corresponding SELECT count(*) ... query with the same predicates).

        Parameters:
        query - the query to execute.
        params - named parameters.
        Returns:
        the values from the first column of the result set (SQL null's are ignored).
        Since:
        2017.2.4, 2018.1
        See Also:
        #queryForInts(String, Map)

      • queryForLongs

        @NotNull
public final List<Long> queryForLongs​(@NotNull
                                      String query,
                                      Object... params)
        Executes the specified query and expects rows with long integer values in the first column. Returns the first column as a list of long integer values. Preserves the order.

        If you're concerned about performance, consider using queryForLongList(String, Object...) instead.

        Parameters:
        query - a query to execute.
        params - sequential parameters.
        Returns:
        values from the first column of result set (db nulls are presented as java nulls).
        See Also:
        queryForLongList(String, Object...)

      • queryForLongList

        @NotNull
public final gnu.trove.list.TLongList queryForLongList​(@NotNull
                                                       String query,
                                                       Object... params)

        Executes the specified query and expects rows with Types.BIGINT values in the first column. Returns the first column as a list of long long values. Preserves the order.

        This method ignores any SQL null's encountered (i. e. the size of the list returned may be less than the result of the corresponding SELECT count(*) ... query with the same predicates).

        Parameters:
        query - the query to execute.
        params - sequential parameters.
        Returns:
        the values from the first column of the result set (SQL null's are ignored).
        Since:
        2017.2.4, 2018.1
        See Also:
        queryForLongs(String, Object...)

      • queryForLongs

        @NotNull
public final List<Long> queryForLongs​(@NotNull
                                      String query,
                                      @Nullable
                                      Integer maxRows,
                                      @NotNull
                                      Map<String,​Object> params)
        Executes the specified query and expects rows with long integer values in the first column. Returns the first column as a list of long integer values. Preserves the order.

        If you're concerned about performance, consider using #queryForLongList(String, Map) instead.

        Parameters:
        query - a query to execute.
        maxRows - max rows to return or null if there is no limit
        params - named parameters.
        Returns:
        values from the first column of result set (db nulls are presented as java nulls).
        See Also:
        #queryForLongList(String, Map)

      • queryForLongList

        @NotNull
public final gnu.trove.list.TLongList queryForLongList​(@NotNull
                                                       String query,
                                                       @Nullable
                                                       Integer maxRows,
                                                       @NotNull
                                                       Map<String,​Object> params)

        Executes the specified query and expects rows with Types.BIGINT values in the first column. Returns the first column as a list of long long values. Preserves the order.

        This method ignores any SQL null's encountered (i. e. the size of the list returned may be less than the result of the corresponding SELECT count(*) ... query with the same predicates).

        Parameters:
        query - the query to execute.
        maxRows - maximum number of rows to select
        params - named parameters.
        Returns:
        the values from the first column of the result set (SQL null's are ignored).
        Since:
        2017.2.4, 2018.1
        See Also:
        #queryForLongs(String, Map)

      • queryForStrings

        @NotNull
public final List<String> queryForStrings​(@NotNull
                                          String query,
                                          Object... params)
        Executes the specified query and expects rows with string values in the first column. Returns the first column as a list of strings. Preserves the order.
        Parameters:
        query - a query to execute.
        params - sequential parameters.
        Returns:
        values from the first column of result set (db nulls are presented as java nulls).

      • queryForBigDecimals

        @NotNull
public final List<BigDecimal> queryForBigDecimals​(@NotNull
                                                  String query,
                                                  Object... params)
        Executes the specified query and expects rows with big decimal values in the first column. Returns the first column as a list of big decimals. Preserves the order.
        Parameters:
        query - a query to execute.
        params - sequential parameters.
        Returns:
        values from the first column of result set (db nulls are presented as java nulls).

      • queryForStrings

        public final List<String> queryForStrings​(@NotNull
                                          String query,
                                          @Nullable
                                          Integer maxRows,
                                          @NotNull
                                          Map<String,​Object> params)
        Executes the specified query and expects rows with string values in the first column. Returns the first column as a list of strings. Preserves the order.
        Parameters:
        query - a query to execute.
        maxRows - max rows to return or null if there is no limit
        params - named parameters.
        Returns:
        values from the first column of result set (db nulls are presented as java nulls).

      • queryForListOf

        @NotNull
public final <D> List<D> queryForListOf​(@NotNull
                                        Class<D> clazz,
                                        @NotNull
                                        String query,
                                        Object... params)

      • queryForMap

        @NotNull
public final <K,​V> Map<K,​V> queryForMap​(@NotNull
                                                    String query,
                                                    @NotNull
                                                    Class<K> keyClass,
                                                    @NotNull
                                                    Class<V> valueClass,
                                                    Object... params)
        Executes the specified query and expects rows with two columns: the first one will be threated as a key and the second one - as a value. All keys must be different. Returns a map built on the fetched keys and values.
        Type Parameters:
        K - class of keys.
        V - class of values.
        Parameters:
        query - a query to execute.
        keyClass - class of keys.
        valueClass - class of values.
        params - query parameters.
        Returns:
        an immutable map.

      • queryForMap

        @NotNull
public final <K,​V> SortedMap<K,​V> queryForMap​(@NotNull
                                                          String query,
                                                          @NotNull
                                                          Comparator<K> keyComparator,
                                                          @NotNull
                                                          Class<K> keyClass,
                                                          @NotNull
                                                          Class<V> valueClass,
                                                          Object... params)
        Executes the specified query and expects rows with two columns: the first one will be threated as a key and the second one - as a value. All keys must be different (regarding to the given comparator). Returns a sorted map built on the fetched keys and values.
        Type Parameters:
        K - class of keys.
        V - class of values.
        Parameters:
        query - a query to execute.
        keyComparator - comparator for keys.
        keyClass - class of keys.
        valueClass - class of values.
        params - query parameters.
        Returns:
        an immutable sorted map based on the given comparator.

      • executeDdls

        public final void executeDdls​(@NotNull
                              String... commands)
                       throws DBException
        Executes one or several DDL commands.

        Warning! - executing a DDL command may perform commit before execution, it depends on the DBMS type. Be sure that this session have no active transactions.

        Parameters:
        commands - ddl queries
        Throws:
        DBException

      • executeDdls

        public final void executeDdls​(@NotNull
                              List<String> commands)

      • getAutoCommit

        public final boolean getAutoCommit()

      • setAutoCommit

        public final void setAutoCommit​(boolean autoCommit)

      • executeSimpleInternal

        protected void executeSimpleInternal​(@NotNull
                                     QueryAndParams qnp)

      • executeDml

        public final int executeDml​(@NotNull
                            String query,
                            @NotNull
                            Object... params)
        Executes the given DML statement.
        Parameters:
        query - the DML statement to execute.
        params - sequential parameters.
        Returns:
        count of affected rows.

      • executeDml

        public final int executeDml​(@NotNull
                            String query,
                            @NotNull
                            Map<String,​Object> params)
        Executes the given DML statement.
        Parameters:
        query - the DML statement to execute.
        params - named parameters.
        Returns:
        count of affected rows.

      • executeDmls

        public final int executeDmls​(@NotNull
                             String... queries)
        Executes several DML statements without parameters.
        Parameters:
        queries - DML queries to execute.
        Returns:
        number of affected rows (in all given statements).

      • executeScript

        public final void executeScript​(@NotNull
                                String script)
        Executes the specifies script without parameters. SQL statements in the script should be separated with semicolon that should be the last character in the string (however, spaces and single-line comments are accepted after this semicolon).

        Known problem: a semicolon inside a block comment can be treated incorrectly.

        Parameters:
        script - script to execute.
        See Also:
        executeScript(String, Map)

      • executeScript

        public final void executeScript​(@NotNull
                                String script,
                                @Nullable
                                Map<String,​Object> params)
        Executes the specified script with parameters. See executeScript(String) for details.
        Parameters:
        script - script to execute.
        params - named parameters.

      • executeBatchForInts

        public final void executeBatchForInts​(@NotNull
                                      String command,
                                      Collection<Integer> data)
        Executes the specified DML statement several times (one time for each item given in data), reusing the same jdbc statement object.
        Parameters:
        command - the DML command to execute.
        data - collection of values - the specified statement will be executed one time for each item in this collection.

      • executeBatchForLongs

        public final void executeBatchForLongs​(@NotNull
                                       String command,
                                       Collection<Long> data)
        Executes the specified DML statement several times (one time for each item given in data), reusing the same jdbc statement object.
        Parameters:
        command - the DML command to execute.
        data - collection of values - the specified statement will be executed one time for each item in this collection.

      • executeBatchForStrings

        public final void executeBatchForStrings​(@NotNull
                                         String command,
                                         Collection<String> data)
        Executes the specified DML statement several times (one time for each item given in data), reusing the same jdbc statement object.
        Parameters:
        command - the DML command to execute.
        data - collection of values - the specified statement will be executed one time for each item in this collection.

      • executeBatchForObjects

        public final void executeBatchForObjects​(@NotNull
                                         String command,
                                         Collection<?> data)
        Executes the specified DML statement several times (one time for each item given in data), reusing the same jdbc statement object.
        Parameters:
        command - the DML command to execute.
        data - collection of values - the specified statement will be executed one time for each item in this collection.

      • executeBatchForTuples

        public final <T> void executeBatchForTuples​(@NotNull
                                            String command,
                                            @NotNull
                                            Class<T> clazz,
                                            @NotNull
                                            Collection<? extends T> data,
                                            @Nullable
                                            Object... plainParams)
        Executes a SQL statement many times, one time for each data item. // TODO document how should parameters be named
        Type Parameters:
        T - type of data items.
        Parameters:
        command - SQL statement to execute.
        clazz - class of data item.
        data - data items.
        plainParams - auxiliary parameters (common for each items).

      • checkTableRowsExistence

        public final boolean checkTableRowsExistence​(@NotNull
                                             String tableName,
                                             @Nullable
                                             Map<String,​Object> fields)
        Checks whether the given row exists in the specified table.
        Parameters:
        tableName - name of the table to insert row.
        fields - fields names an values.
        Returns:
        exists or not.

      • checkQueryReturnsRows

        public final boolean checkQueryReturnsRows​(@NotNull
                                           String query,
                                           Object... params)
                                    throws DBException
        Executes the given query and checks whether it returns at least one row.
        Parameters:
        query - the query to execute.
        params - sequential parameters.
        Returns:
        whether it returns at least one row.
        Throws:
        DBException

      • checkQueryReturnsRows

        public final boolean checkQueryReturnsRows​(@NotNull
                                           String query,
                                           @NotNull
                                           Map<String,​Object> params)
                                    throws DBException
        Executes the given query and checks whether it returns at least one row.
        Parameters:
        query - the query to execute.
        params - named parameters.
        Returns:
        whether it returns at least one row.
        Throws:
        DBException

      • checkTableExistence

        public final boolean checkTableExistence​(@NotNull
                                         String tableName)
        Checks whether the specified table exists.
        Parameters:
        tableName - name of the table to check.
        Returns:
        whether the table exists.

      • checkPermTableExists

        protected abstract boolean checkPermTableExists​(@NotNull
                                                String tableName)
        Checks permanent table existence. This function is only to implement it by inheritors. Don't call it directly!
        Parameters:
        tableName - table name already prepared for specific RDBMS.

      • checkTempTableExists

        protected abstract boolean checkTempTableExists​(@NotNull
                                                String tableName)
        Checks temporary table existence. This function is only to implement it by inheritors. Don't call it directly!
        Parameters:
        tableName - table name already prepared for specific RDBMS.

      • retrieveSchemaTableNames

        @NotNull
public abstract Set<String> retrieveSchemaTableNames​(boolean includePerm,
                                                     boolean includeTemp,
                                                     boolean lowerCase)
        Lists all tables of the current schema and returns their names. Method guarantees fetching all permanent tables for all database types. Fetching temporary tables is guaranteed only for Oracle and HSQL (where such kind of tables are true DB schema objects).
        Parameters:
        includePerm - if true the result will include permanent tables as well.
        includeTemp - if true the result will include temporary tables as well.
        lowerCase - if true each table name will be turned to lower case.
        Returns:
        set of table names in db-specific metadata style.

      • preprocessTableNameAfterRetrieved

        @NotNull
protected String preprocessTableNameAfterRetrieved​(@NotNull
                                                   String tableName)

      • queryForTuples

        @NotNull
public final <T,​COLL extends Collection<? super T>> COLL queryForTuples​(@NotNull
                                                                              COLL result,
                                                                              @NotNull
                                                                              Class<T> clazz,
                                                                              @Nullable
                                                                              String methodName,
                                                                              @NotNull
                                                                              String query,
                                                                              Object... params)
        Performs the specified select query and retrieves result rows as tuples of the specified class.

        For each row, it invokes a class's constructor in order to instantiate an instance of tuple and pass result fileds as constructor arguments.

        Which constructor to invoke is depend on the value of the methodName parameter. If the methodName is null then the first constructor that is not annotated as Tuple will be selected. If the methodName is specified then the constructor that annotated as Tuple(Tuple.method() = "methodName") will be used.

        Type Parameters:
        T - type of tuple.
        COLL - type of tuple collection.
        Parameters:
        result - collection where it will put retrieved tuples to.
        clazz - class of tuples.
        methodName - constructor name or null; empty strings are rejected.
        query - the select query.
        params - sequential query params.
        Returns:
        the result collection given as result parameter.

      • queryForTuples

        @NotNull
public final <T,​COLL extends Collection<? super T>> COLL queryForTuples​(@NotNull
                                                                              COLL result,
                                                                              @Nullable
                                                                              Integer maxTuplesToReturn,
                                                                              @NotNull
                                                                              Class<T> clazz,
                                                                              @Nullable
                                                                              String methodName,
                                                                              @NotNull
                                                                              String query,
                                                                              Object... params)
        Sames as queryForTuples(Collection, Class, String, String, Object...) but optionally can limit the results by the specified amount.
        Type Parameters:
        T - type of tuple.
        COLL - type of tuple collection.
        Parameters:
        result - collection where it will put retrieved tuples to.
        maxTuplesToReturn - max number of tuples to retrieve and add to the result (if null, then there is no limit)
        clazz - class of tuples.
        methodName - constructor name or null; empty strings are rejected.
        query - the select query.
        params - sequential query params.
        Returns:
        the result collection given as result parameter.
        Since:
        2017.1.3

      • queryForTuples

        @NotNull
public final <T,​COLL extends Collection<? super T>> COLL queryForTuples​(@NotNull
                                                                              COLL result,
                                                                              @NotNull
                                                                              Class<T> clazz,
                                                                              @Nullable
                                                                              String methodName,
                                                                              @NotNull
                                                                              String query,
                                                                              @NotNull
                                                                              Map<String,​Object> params)
        Performs the specified select query and retrieves result rows as tuples of the specified class.

        For each row, it invokes a class's constructor in order to instantiate an instance of tuple and pass result fileds as constructor arguments.

        Which constructor to invoke is depend on the value of the methodName parameter. If the methodName is null then the first constructor that is not annotated as Tuple will be selected. If the methodName is specified then the constructor that annotated as Tuple(Tuple.method() = "methodName") will be used.

        Type Parameters:
        T - type of tuple.
        COLL - type of tuple collection.
        Parameters:
        result - collection where it will put retrieved tuples to.
        clazz - class of tuples.
        methodName - constructor name or null; empty strings are rejected.
        query - the select query.
        params - named query params.
        Returns:
        the result collection given as result parameter.

      • queryForTuples

        public final <T> void queryForTuples​(@NotNull
                                     T object,
                                     @NotNull
                                     String methodName,
                                     @NotNull
                                     String query,
                                     Object... params)
        Performs the specified select query, retrieves result rows and calls the specified object method for each retrived row.

        The method to call should accept parameters matched ones the given SQL query returns.

        Type Parameters:
        T - type of tuple.
        Parameters:
        object - the object which method is to call.
        methodName - the name of the method to call.
        query - the select query.
        params - sequential query params.

      • queryForTuples

        public final <T> void queryForTuples​(@NotNull
                                     T object,
                                     @NotNull
                                     String methodName,
                                     @NotNull
                                     String query,
                                     @NotNull
                                     Map<String,​Object> params)
        Performs the specified select query, retrieves result rows and calls the specified object method for each retrived row.

        The method to call should accept parameters matched ones the given SQL query returns.

        Type Parameters:
        T - type of tuple.
        Parameters:
        object - the object which method is to call.
        methodName - the name of the method to call.
        query - the select query.
        params - named query params.

      • isIdentifierNameCaseSensitive

        public abstract boolean isIdentifierNameCaseSensitive()
        Checks whether indentifier names (table names, column names) are case sensitive in the current database.

        Case sensitivity means DBMS tells names in different cases written without quotation as different ones.

        Warning: in case of MySQL, it's unknown how to determine this option; so, for MySQL this function always returns true.

      • getDatabaseName

        @Nullable
public abstract String getDatabaseName()
        Returns the name of the current database.

        Note: in case of Oracle it returns the name of schema.

        Returns:
        name of the current database.

      • getSchemaNameForMetadata

        @Nullable
public abstract String getSchemaNameForMetadata()
        Returns the schema for using in jdbc getMetadata.

      • retrieveSchemasWithTables

        @NotNull
public abstract Collection<String> retrieveSchemasWithTables()
        Retrieves all schema with tables, if the database support schemas. System and internal schemas aren't included.
        Returns:
        schemas if they're supported by the database; an empty collection if not.

      • getUserName

        @NotNull
public final String getUserName()
        Determines the database user name.
        Returns:
        user name in the case returned by DBMS.

      • isTableColumnMandatory

        public boolean isTableColumnMandatory​(@NotNull
                                      String tableName,
                                      @NotNull
                                      String columnName)
        Returns true if specified column is mandatory, false otherwise. Does not cache the result.
        Parameters:
        tableName - name of the table.
        columnName - name of the column.
        Returns:
        true if specified column is mandatory, false otherwise.

      • listTableMandatoryColumns

        public final Set<String> listTableMandatoryColumns​(@NotNull
                                                   String tableName,
                                                   boolean toLowerCase)

      • getTableMandatoryColumnsQuery

        @NotNull
protected abstract String getTableMandatoryColumnsQuery()

      • getTableColumnWidth

        public final int getTableColumnWidth​(@NotNull
                                     String tableName,
                                     String columnName)
        Determines width of one table column.

        For text columns - it returns size in chars. For raw byte stream columns - it returns size in bytes. For other columns it can return unpredictable values.

        Parameters:
        tableName - name of the table.
        columnName - name of the column.
        Returns:
        the width.
        See Also:
        getTableColumnWidths(java.lang.String)

      • getTableColumnWidths

        @NotNull
public Map<String,​Integer> getTableColumnWidths​(@NotNull
                                                      String tableName)
                                               throws DBException
        Retrieves column widths for the specified table.

        For text columns - it returns size in chars. For raw byte stream columns - it returns size in bytes. For other columns it can return unpredictable values.

        Parameters:
        tableName - table name to look columns for.
        Returns:
        map [column name -- column width], column names are in lower case, the order of columns in the map is undefined.
        Throws:
        DBException
        See Also:
        getTableColumnWidth(java.lang.String, java.lang.String)

      • isTableColumnExists

        public final boolean isTableColumnExists​(@NotNull
                                         String tableName,
                                         @NotNull
                                         String columnName)
        Checks whether the table exists and has the specified column.
        Parameters:
        tableName - table name.
        columnName - column name (must be in lower case).
        Returns:
        true if column exists, false if table doesn't exist or table has no such column.

      • expandStringColumn

        public final void expandStringColumn​(@NotNull
                                     String tableName,
                                     @NotNull
                                     String columnName,
                                     int newWidth)
        Increases the size of the specified string column. Works only with string (varchar) columns; in other cases the behaviour of this procedure can be undeterminated.

        Determines the current size of the specified column and performs "alter table... modify..." if neccessary.

        Parameters:
        tableName - name of the table, in the lower case.
        columnName - name of the column to change, in the lower case.
        newWidth - the desired size.

      • isMandatorityIsPreservedWhenColumnIsChanged

        protected boolean isMandatorityIsPreservedWhenColumnIsChanged()
        Returns true if column mandatority is preserved when column is changed.
        Returns:
        true if column mandatority is preserved when column is changed.

      • namedIndexExists

        public boolean namedIndexExists​(@NotNull
                                String table,
                                @NotNull
                                String indexName)
        Returns true if index with specified name exists for the specified table
        Parameters:
        table - name of the table
        indexName - index name
        Returns:
        see above

      • dialect

        @NotNull
public final SQLDialect dialect()
        Returns an SQLDialect instance appropriated for the current DBMS type.
        Returns:
        an SQLDialect instance.

      • commit

        public final void commit()
        Performs commit if the connection is in the transactional mode, or does nothing if the connection is in the auto-commit mode.

      • rollback

        public final void rollback()
        Performs roll back if the connection is in the transactional mode, or does nothing if the connection is in the auto-commit mode.

      • close

        public final void close()
        Closes all allocated resources and returns the holded connection back.

      • getDatabaseType

        @NotNull
public final DatabaseType getDatabaseType()
        Returns the type of the database.
        Returns:
        the database type.

      • preprocessQueryUsingConnection

        protected String preprocessQueryUsingConnection​(String query,
                                                QueryKind queryKind)
        Allows to preprocess the query depends on the current connection.
        Parameters:
        query - query to preprocess; static macros already have been processed.
        queryKind - kind of the query.
        Returns:
        processed query.

      • setFetchSize

        public void setFetchSize​(int fetchSize)
        Sets fetch size to use in prepared statements for subsequent SQL queries where more than one row is expected.
        Parameters:
        fetchSize - custom fetch size, use 0 to reset fetch size to default value

      • getConnection

        @NotNull
public final Connection getConnection()
        Please NEVER uses this getter! This getter is very dangerous and provided for backward compatibility only. Please get rid of all usages of this getter and the remove it!

      • getCharacterSet

        @Nullable
public abstract String getCharacterSet()

        Returns the character set of the underlying database schema, or null if none is available.

        Returns:
        the character set of the underlying database schema, or null if none is available.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2017.2

      • getCollation

        @Nullable
public abstract String getCollation()

        Returns the collation of the underlying database schema, or null if none is available.

        Returns:
        the collation of the underlying database schema, or null if none is available.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2017.2

      • getServerCharacterSet

        @Nullable
public String getServerCharacterSet()

        Returns the character set of the database server (which, generally, may be different from that of the database), or null if none is known or the database server doesn't support this feature.

        Returns:
        the character set of the database server, or null if none is known or the database server doesn't support this feature.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2018.1.2

      • getServerCollation

        @Nullable
public String getServerCollation()

        Returns the collation of the database server (which, generally, may be different from that of the database), or null if none is known or the database server doesn't support this feature.

        Returns:
        the collation of the database server, or null if none is known or the database server doesn't support this feature.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2018.1.2

      • getConnectionCharacterSet

        @Nullable
public String getConnectionCharacterSet()

        Returns the character set of the established JDBC connection, or null if none is known or the database server doesn't support this feature.

        Returns:
        the character set of the established JDBC connection, or null if none is known or the database server doesn't support this feature.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2018.1.2

      • getConnectionCollation

        @Nullable
public String getConnectionCollation()

        Returns the collation of the established JDBC connection, or null if none is known or the database server doesn't support this feature.

        Returns:
        the collation of the established JDBC connection, or null if none is known or the database server doesn't support this feature.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2018.1.2

      • getClientCharacterSet

        @Nullable
public String getClientCharacterSet()

        Returns the character set of the JDBC client (i. e. TeamCity) as seen by the database server, or null if none is known or the database server doesn't support this feature.

        Returns:
        the character set of the JDBC client as seen by the database server, or null if none is known or the database server doesn't support this feature.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2018.1.2

      • getServerTimeZone

        @Nullable
public String getServerTimeZone()

        Returns the time zone of the database server, or null if none is known or the database server doesn't support this feature.

        Returns:
        the time zone of the database server, or null if none is known or the database server doesn't support this feature.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2018.1.2
        See Also:
        getParsedServerTimeZone()

      • getParsedServerTimeZone

        @Nullable
public TimeZone getParsedServerTimeZone()

        Returns the Java time zone which corresponds to that of the database server, or null if server time zone is null or unparseable.

        Returns:
        the Java time zone which corresponds to that of the database server, or null if server time zone is null or unparseable.
        Throws:
        DBException - if a database access error occurs.
        Since:
        2018.1.2
        See Also:
        getServerTimeZone()

      • getColumns

        @NotNull
public ResultSet getColumns​(@NotNull
                            String tableNamePattern,
                            @Nullable
                            String columnNamePattern)
                     throws SQLException

        Retrieves a description of table columns available in the specified catalog.

        Parameters:
        tableNamePattern - a table name pattern; must match the table name as it is stored in the database.
        columnNamePattern - a column name pattern; must match the column name as it is stored in the database.
        Returns:
        ResultSet - each row is a column description.
        Throws:
        SQLException - if a database access error occurs.
        Since:
        2017.2.4, 2018.1
        See Also:
        DatabaseMetaData.getColumns(String, String, String, String)

      • getPrimaryKeys

        @NotNull
public ResultSet getPrimaryKeys​(@NotNull
                                String table)
                         throws SQLException

        Retrieves a description of the given table's primary key columns.

        Parameters:
        table - a table name; must match the table name as it is stored in the database
        Returns:
        ResultSet - each row is a primary key column description.
        Throws:
        SQLException - if a database access error occurs.
        Since:
        2017.2.4, 2018.1
        See Also:
        DatabaseMetaData.getPrimaryKeys(String, String, String)

      • getIndexInfo

        @NotNull
public ResultSet getIndexInfo​(@NotNull
                              String table,
                              boolean unique,
                              boolean approximate)
                       throws SQLException

        Retrieves a description of the given table's indices and statistics.

        Parameters:
        table - a table name; must match the table name as it is stored in this database.
        unique - when true, return only indices for unique values; when false, return indices regardless of whether unique or not.
        approximate - when true, result is allowed to reflect approximate or out of data values; when false, results are requested to be accurate.
        Returns:
        ResultSet - each row is an index column description.
        Throws:
        SQLException - if a database access error occurs.
        Since:
        2017.2.4, 2018.1
        See Also:
        DatabaseMetaData.getIndexInfo(String, String, String, boolean, boolean)

      • createStatementTunerForSimpleValuesQuery

        @Nullable
protected DBFunctions.StatementTuner createStatementTunerForSimpleValuesQuery()