Package de.businesscode.util.jdbc

Class DatabaseCompatibility

  • public class DatabaseCompatibility
extends java.lang.Object
    This class encapsulates Database specific behavior and settings

    • Constructor Summary

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

    • Method Summary

      All Methods Static Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      java.lang.String castToVarchar​(java.lang.String jdbcResourceName, int origJdbcDataType, java.lang.String expr)
      Wraps the expression so that the result is of type VARCHAR or CHAR
      static void clear()
      clear caches
      java.lang.String dbLikeEscapeBackslash​(java.lang.String jdbcResourceName)
      Returns true if the database of resultingBindingSet supports GROUPING SETS
      boolean dbNeedsColumnListForRecursiveWithClause​(java.lang.String jdbcResourceName)
      If the database used for the selected BindingSet needs an extra column list for with clauses
      boolean dbNeedsRecursiveInWithClause​(java.lang.String jdbcResourceName)
      Some databases need WITH RECURSIVE for recursive queries, some do not understand it
      boolean dbNeedsVarcharCastForConcatInTopN​(java.lang.String jdbcResourceName)  
      int dbOrderByNullsLast​(java.lang.String jdbcResourceName, java.lang.String colExpr, boolean isDesc, java.lang.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
      boolean dbSupportsGroupingSets​(java.lang.String jdbcResourceName)
      Are we allowed to use GROUPING SETs and GROUPING()?
      java.util.Map<java.lang.String,​java.lang.String> getAggrFktMapping​(java.lang.String jdbcResourceName)
      For the simple @aggr shortcut this is the mapping to the corresponding SQL expression
      byte[] getBlob​(java.lang.String bindingSetName, java.sql.ResultSet rs, int column)
      returns the blob column data as a byte array
      java.io.InputStream getBlobInputStream​(java.lang.String bindingSetName, java.sql.ResultSet rs, int column)
      returns the blob column data as an inputstream
      java.util.Map<java.lang.String,​java.lang.String[]> getCalcFktMapping​(java.lang.String jdbcResourceName)  
      java.lang.String getClob​(java.lang.String bindingSetName, java.sql.ResultSet rs, int column)
      returns the clob column data as a string
      java.io.InputStream getClobInputStream​(java.lang.String bindingSetName, java.sql.ResultSet rs, int column)
      returns the clob column data as an inputstream
      java.lang.String getDatabaseProductNameLC​(java.lang.String jdbcResourceName)
      Does not do much checking, but as all BindingSets are tested on start, the risk is low
      java.util.Map<java.lang.String,​java.lang.String[]> getDefaultCalcFktMapping()  
      static DatabaseCompatibility getInstance()  
      java.util.Set<java.lang.String> getReservedDBWords​(java.lang.String jdbcResourceName)  
      java.util.Map<java.lang.String,​java.lang.String> getSetOperators()
      Standard SQL Set Operators
      java.util.Map<java.lang.String,​java.lang.String[]> getSpatialFktMapping​(java.lang.String jdbcResourceName)
      Geo spatial operators differ significantly from database to database

      • Methods inherited from class java.lang.Object

        clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

    • Field Detail

      • sqlKeyWordsOracle

        protected final java.util.Set<java.lang.String> sqlKeyWordsOracle

      • sqlKeyWordsMysql

        protected final java.util.Set<java.lang.String> sqlKeyWordsMysql

      • sqlKeyWordsPostgres

        protected final java.util.Set<java.lang.String> sqlKeyWordsPostgres

      • sqlKeyWordsSqlServer

        protected final java.util.Set<java.lang.String> sqlKeyWordsSqlServer

      • sqlKeyWordsGeneric

        protected final java.util.Set<java.lang.String> sqlKeyWordsGeneric

      • databaseProduct

        protected final java.util.Map<java.lang.String,​java.lang.String> databaseProduct

      • sqlServerCalcFktMapping

        protected final java.util.Map<java.lang.String,​java.lang.String[]> sqlServerCalcFktMapping

      • oracleCalcFktMapping

        protected final java.util.Map<java.lang.String,​java.lang.String[]> oracleCalcFktMapping

      • mysqlCalcFktMapping

        protected final java.util.Map<java.lang.String,​java.lang.String[]> mysqlCalcFktMapping

      • redshiftCalcFktMapping

        protected final java.util.Map<java.lang.String,​java.lang.String[]> redshiftCalcFktMapping

      • aggregationMappingGeneric

        protected final java.util.Map<java.lang.String,​java.lang.String> aggregationMappingGeneric

      • aggregationMappingMySql

        protected final java.util.Map<java.lang.String,​java.lang.String> aggregationMappingMySql

      • spatialFktMapping

        protected final java.util.Map<java.lang.String,​java.lang.String[]> spatialFktMapping

      • oracleSpatialFktMapping

        protected final java.util.Map<java.lang.String,​java.lang.String[]> oracleSpatialFktMapping

      • sqlServerSpatialFktMapping

        protected final java.util.Map<java.lang.String,​java.lang.String[]> sqlServerSpatialFktMapping

      • sqlSetOperators

        protected final java.util.Map<java.lang.String,​java.lang.String> sqlSetOperators

    • Constructor Detail

      • DatabaseCompatibility

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

    • Method Detail

      • clear

        public static void clear()
        clear caches

      • getReservedDBWords

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

      • dbNeedsColumnListForRecursiveWithClause

        public boolean dbNeedsColumnListForRecursiveWithClause​(java.lang.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​(java.lang.String jdbcResourceName)
        Some databases need WITH RECURSIVE for recursive queries, some do not understand it
        Parameters:
        jdbcResourceName -
        Returns:

      • dbSupportsGroupingSets

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

      • dbLikeEscapeBackslash

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

      • dbNeedsVarcharCastForConcatInTopN

        public boolean dbNeedsVarcharCastForConcatInTopN​(java.lang.String jdbcResourceName)
        Parameters:
        jdbcResourceName -
        Returns:

      • dbOrderByNullsLast

        public int dbOrderByNullsLast​(java.lang.String jdbcResourceName,
                              java.lang.String colExpr,
                              boolean isDesc,
                              java.lang.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 java.lang.String castToVarchar​(java.lang.String jdbcResourceName,
                                      int origJdbcDataType,
                                      java.lang.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 java.util.Map<java.lang.String,​java.lang.String[]> getDefaultCalcFktMapping()

      • getCalcFktMapping

        public java.util.Map<java.lang.String,​java.lang.String[]> getCalcFktMapping​(java.lang.String jdbcResourceName)

      • getAggrFktMapping

        public java.util.Map<java.lang.String,​java.lang.String> getAggrFktMapping​(java.lang.String jdbcResourceName)
        For the simple @aggr shortcut this is the mapping to the corresponding SQL expression

      • getSetOperators

        public java.util.Map<java.lang.String,​java.lang.String> getSetOperators()
        Standard SQL Set Operators

      • getSpatialFktMapping

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

      • getDatabaseProductNameLC

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

      • getClob

        public java.lang.String getClob​(java.lang.String bindingSetName,
                                java.sql.ResultSet rs,
                                int column)
                         throws java.lang.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:
        java.lang.Exception

      • getBlob

        public byte[] getBlob​(java.lang.String bindingSetName,
                      java.sql.ResultSet rs,
                      int column)
               throws java.lang.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:
        java.lang.Exception

      • getClobInputStream

        public java.io.InputStream getClobInputStream​(java.lang.String bindingSetName,
                                              java.sql.ResultSet rs,
                                              int column)
                                       throws java.lang.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:
        java.lang.Exception

      • getBlobInputStream

        public java.io.InputStream getBlobInputStream​(java.lang.String bindingSetName,
                                              java.sql.ResultSet rs,
                                              int column)
                                       throws java.lang.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:
        java.lang.Exception