Package de.businesscode.util.jdbc

Class DatabaseCompatibility

java.lang.Object
de.businesscode.util.jdbc.DatabaseCompatibility
public class DatabaseCompatibility extends Object
This class encapsulates Database specific behavior and settings

  • Field Details

    • sqlKeyWordsOracle

      protected final Set<String> sqlKeyWordsOracle

    • sqlKeyWordsMysql

      protected final Set<String> sqlKeyWordsMysql

    • sqlKeyWordsPostgres

      protected final Set<String> sqlKeyWordsPostgres

    • sqlKeyWordsSqlServer

      protected final Set<String> sqlKeyWordsSqlServer

    • sqlKeyWordsSnowflake

      protected final Set<String> sqlKeyWordsSnowflake

    • sqlKeyWordsGeneric

      protected final Set<String> sqlKeyWordsGeneric

    • databaseProduct

      protected final Map<String,String> databaseProduct

    • sqlServerCalcFktMapping

      protected final Map<String,String[]> sqlServerCalcFktMapping

    • oracleCalcFktMapping

      protected final Map<String,String[]> oracleCalcFktMapping

    • mysqlCalcFktMapping

      protected final Map<String,String[]> mysqlCalcFktMapping

    • redshiftCalcFktMapping

      protected final Map<String,String[]> redshiftCalcFktMapping

    • snowflakeCalcFktMapping

      protected final Map<String,String[]> snowflakeCalcFktMapping

    • aggregationMappingGeneric

      protected final Map<String,String> aggregationMappingGeneric

    • aggregationMappingMySql

      protected final Map<String,String> aggregationMappingMySql

    • spatialFktMapping

      protected final Map<String,String[]> spatialFktMapping

    • oracleSpatialFktMapping

      protected final Map<String,String[]> oracleSpatialFktMapping

    • sqlServerSpatialFktMapping

      protected final Map<String,String[]> sqlServerSpatialFktMapping

    • lateralJoinMapping

      protected final Map<String,String> lateralJoinMapping

    • applyJoinMapping

      protected final Map<String,String> applyJoinMapping

    • sqlSetOperators

      protected final Map<String,String> sqlSetOperators

  • Constructor Details

    • DatabaseCompatibility

      protected DatabaseCompatibility()
      Retrieve a singleton via getInstance() to make your SQl database type dependent

  • Method Details

    • getInstance

      public static DatabaseCompatibility getInstance() throws RuntimeException
      Returns:
      Throws:
      BindingException
      RuntimeException

    • clear

      public static void clear()
      clear caches

    • getReservedDBWords

      public Set<String> getReservedDBWords(String jdbcResourceName)
      Parameters:
      jdbcResourceName -
      Returns:
      Set of key-words for the database belonging to the BindingSets connection

    • dbNeedsColumnListForRecursiveWithClause

      public boolean dbNeedsColumnListForRecursiveWithClause(String jdbcResourceName)
      If the database used for the selected BindingSet needs an extra column list for with clauses
      Parameters:
      jdbcResourceName -
      Returns:

    • dbNeedsRecursiveInWithClause

      public boolean dbNeedsRecursiveInWithClause(String jdbcResourceName)
      Some databases need WITH RECURSIVE for recursive queries, some do not understand it
      Parameters:
      jdbcResourceName -
      Returns:

    • dbSupportsGroupingSets

      public boolean dbSupportsGroupingSets(String jdbcResourceName)
      Are we allowed to use GROUPING SETs and GROUPING()?
      Parameters:
      jdbcResourceName -
      Returns:

    • paginationClause

      public String paginationClause(String jdbcResourceName, int rowStart, int rowEnd, Supplier<String> getOrderBy)
      Pagination clause We prefer ANSI, while this is the actual support mid 2025: select * from BCDUITEST_DEMO_SHIPMENT order by item_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; -- ANSI, Oracle 19, Postgres 16, SnowFlake 9.31, MS SQLAzure 12 (needs OFFSET if FETCH is given), H2 2.1, DuckDB 0.1 select * from BCDUITEST_DEMO_SHIPMENT order by item_id LIMIT 10 OFFSET 20; -- MySQL 8.4.5, Postgres, SnowFlake, H2, DuckDB, Redshift 1.0.117891 select TOP 10 * from BCDUITEST_DEMO_SHIPMENT order by item_id; -- Teradata 16.2, SnowFlake, MS SQLAzure, H2, Redshift select * from BCDUITEST_DEMO_SHIPMENT order by item_id OFFSET 20 LIMIT 10; -- Postgres, DuckDB, Redshift select * from BCDUITEST_DEMO_SHIPMENT QUALIFY ROW_NUMBER() OVER(ORDER BY cost) BETWEEN 10 AND 20; -- Teradata
      Parameters:
      jdbcResourceName -
      rowStart - according to our Wrq convention
      rowEnd - according to our Wrq convention
      Returns:

    • dbLikeEscapeBackslash

      public String dbLikeEscapeBackslash(String jdbcResourceName)
      Returns true if the database of resultingBindingSet supports GROUPING SETS
      Parameters:
      jdbcResourceName -
      Returns:

    • dbNeedsVarcharCastForConcatInTopN

      public boolean dbNeedsVarcharCastForConcatInTopN(String jdbcResourceName)
      Parameters:
      jdbcResourceName -
      Returns:

    • dbOrderByNullsLast

      public int dbOrderByNullsLast(String jdbcResourceName, String colExpr, boolean isDesc, StringBuffer sql)
      Oracle sorts nulls first in desc (order by and rank order by), tera and sqlserver do this vice versa Sadly, of these, only oracle allows NULLS FIRST/LAST in ORDER BY
      Parameters:
      jdbcResourceName -
      Returns:
      How often bound variables of the item used for ordering are used in the created expression

    • castToVarchar

      public String castToVarchar(String jdbcResourceName, int origJdbcDataType, String expr)
      Wraps the expression so that the result is of type VARCHAR or CHAR
      Parameters:
      jdbcResourceName - Used to derive database specific syntax
      origJdbcDataType - Used to skip wrapping if already CHAR/VARCHAR and keep format YYYY-MMM-DDD for type date
      expr - Expression to be wrapped
      Returns:
      Wrapped SQL expression

    • getDefaultCalcFktMapping

      public Map<String,String[]> getDefaultCalcFktMapping()

    • getCalcFktMapping

      public Map<String,String[]> getCalcFktMapping(String jdbcResourceName)

    • getAggrFktMapping

      public Map<String,String> getAggrFktMapping(String jdbcResourceName)
      For the simple @aggr shortcut this is the mapping to the corresponding SQL expression To check whether @aggr is valid, check with containsKey().

    • getSetOperators

      public Map<String,String> getSetOperators()
      Standard SQL Set Operators

    • getJoinOperator

      public String getJoinOperator(String jdbcResourceName, String op)
      Handling of Lateral joins differ from database to database (LATERAL vs. APPLY key word)
      Parameters:
      jdbcResourceName -
      op -
      Returns:

    • getSpatialFktMapping

      public Map<String,String[]> getSpatialFktMapping(String jdbcResourceName)
      Geo spatial operators differ significantly from database to database

    • getDatabaseProductNameLC

      public String getDatabaseProductNameLC(String jdbcResourceName)
      Does not do much checking, but as all BindingSets are tested on start, the risk is low
      Parameters:
      jdbcResourceName -
      Returns:

    • getClob

      public String getClob(String bindingSetName, ResultSet rs, int column) throws Exception
      returns the clob column data as a string
      Parameters:
      bindingSetName - the name of the currently used binding set
      rs - the current result set
      column - the index of the columns within the result set
      Returns:
      clob data as a string
      Throws:
      Exception

    • getBlob

      public byte[] getBlob(String bindingSetName, ResultSet rs, int column) throws Exception
      returns the blob column data as a byte array
      Parameters:
      bindingSetName - the name of the currently used binding set
      rs - the current result set
      column - the index of the columns within the result set
      Returns:
      byte array with data
      Throws:
      Exception

    • getClobInputStream

      public InputStream getClobInputStream(String bindingSetName, ResultSet rs, int column) throws Exception
      returns the clob column data as an inputstream
      Parameters:
      bindingSetName - the name of the currently used binding set
      rs - the current result set
      column - the index of the columns within the result set
      Returns:
      clob data as an inputstream
      Throws:
      Exception

    • getBlobInputStream

      public InputStream getBlobInputStream(String bindingSetName, ResultSet rs, int column) throws Exception
      returns the blob column data as an inputstream
      Parameters:
      bindingSetName - the name of the currently used binding set
      rs - the current result set
      column - the index of the columns within the result set
      Returns:
      blob data as an inputstream
      Throws:
      Exception