Class CmsSqlManager

    • Field Summary

      Fields 
      Modifier and Type Field Description
      protected java.util.concurrent.ConcurrentHashMap<java.lang.String,​java.lang.String> m_cachedQueries
      A map to cache queries with replaced search patterns.
      protected int m_driverType
      The type ID of the driver (vfs, user, project or history) from where this SQL manager is referenced.
      protected java.lang.String m_poolUrl
      The pool URL to get connections from the JDBC driver manager, including DBCP's pool URL prefix.
      protected java.util.Map<java.lang.String,​java.lang.String> m_queries
      A map holding all SQL queries.
      protected static java.lang.String QUERY_PROJECT_SEARCH_PATTERN
      A pattern being replaced in SQL queries to generate SQL queries to access online/offline tables.
    • Constructor Summary

      Constructors 
      Constructor Description
      CmsSqlManager()
      Creates a new, empty SQL manager.
    • Method Summary

      All Methods Static Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      void closeAll​(CmsDbContext dbc, java.sql.Connection con, java.sql.Statement stmnt, java.sql.ResultSet res)
      Attempts to close the connection, statement and result set after a statement has been executed.
      byte[] getBytes​(java.sql.ResultSet res, java.lang.String attributeName)
      Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language.
      java.sql.Connection getConnection​(CmsDbContext dbc)
      Returns a JDBC connection from the connection pool.
      static CmsSqlManager getInstance​(java.lang.String classname)
      Creates a new instance of a SQL manager.
      java.sql.PreparedStatement getPreparedStatement​(java.sql.Connection con, java.lang.String queryKey)
      Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query.
      java.sql.PreparedStatement getPreparedStatement​(java.sql.Connection con, CmsProject project, java.lang.String queryKey)
      Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query and the CmsProject.
      java.sql.PreparedStatement getPreparedStatement​(java.sql.Connection con, CmsUUID projectId, java.lang.String queryKey)
      Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query and the project-ID.
      java.sql.PreparedStatement getPreparedStatementForSql​(java.sql.Connection con, java.lang.String query)
      Returns a PreparedStatement for a JDBC connection specified by the SQL query.
      void init​(int driverType, java.lang.String poolUrl)
      Initializes this SQL manager.
      protected void loadQueryProperties​(java.lang.String propertyFilename)
      Loads a Java properties hash containing SQL queries.
      java.lang.String readQuery​(java.lang.String queryKey)
      Searches for the SQL query with the specified key.
      java.lang.String readQuery​(CmsProject project, java.lang.String queryKey)
      Searches for the SQL query with the specified key and CmsProject.
      java.lang.String readQuery​(CmsUUID projectId, java.lang.String queryKey)
      Searches for the SQL query with the specified key and project-ID.
      protected static java.lang.String replaceProjectPattern​(CmsUUID projectId, java.lang.String query)
      Replaces the project search pattern in SQL queries by the pattern _ONLINE_ or _OFFLINE_ depending on the specified project ID.
      protected void replaceQuerySearchPatterns()
      Replaces patterns ${XXX} by another property value, if XXX is a property key with a value.
      void setBytes​(java.sql.PreparedStatement statement, int pos, byte[] content)
      Sets the designated parameter to the given Java array of bytes.
      java.lang.String validateEmpty​(java.lang.String value)
      Replaces null or empty Strings with a String with one space character " ".
      • Methods inherited from class java.lang.Object

        clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
    • Field Detail

      • m_cachedQueries

        protected java.util.concurrent.ConcurrentHashMap<java.lang.String,​java.lang.String> m_cachedQueries
        A map to cache queries with replaced search patterns.
      • m_driverType

        protected int m_driverType
        The type ID of the driver (vfs, user, project or history) from where this SQL manager is referenced.
      • m_poolUrl

        protected java.lang.String m_poolUrl
        The pool URL to get connections from the JDBC driver manager, including DBCP's pool URL prefix.
      • m_queries

        protected java.util.Map<java.lang.String,​java.lang.String> m_queries
        A map holding all SQL queries.
    • Constructor Detail

      • CmsSqlManager

        public CmsSqlManager()
        Creates a new, empty SQL manager.

    • Method Detail

      • getInstance

        public static CmsSqlManager getInstance​(java.lang.String classname)
        Creates a new instance of a SQL manager.

        Parameters:
        classname - the classname of the SQL manager
        Returns:
        a new instance of the SQL manager
      • replaceProjectPattern

        protected static java.lang.String replaceProjectPattern​(CmsUUID projectId,
                                                                java.lang.String query)
        Replaces the project search pattern in SQL queries by the pattern _ONLINE_ or _OFFLINE_ depending on the specified project ID.

        Parameters:
        projectId - the ID of the current project
        query - the SQL query
        Returns:
        String the SQL query with the table key search pattern replaced
      • closeAll

        public void closeAll​(CmsDbContext dbc,
                             java.sql.Connection con,
                             java.sql.Statement stmnt,
                             java.sql.ResultSet res)
        Attempts to close the connection, statement and result set after a statement has been executed.

        Parameters:
        dbc - the current database context
        con - the JDBC connection
        stmnt - the statement
        res - the result set
      • getBytes

        public byte[] getBytes​(java.sql.ResultSet res,
                               java.lang.String attributeName)
                        throws java.sql.SQLException
        Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language.

        The bytes represent the raw values returned by the driver. Overwrite this method if another database server requires a different handling of byte attributes in tables.

        Parameters:
        res - the result set
        attributeName - the name of the table attribute
        Returns:
        byte[] the column value; if the value is SQL NULL, the value returned is null
        Throws:
        java.sql.SQLException - if a database access error occurs
      • getConnection

        public java.sql.Connection getConnection​(CmsDbContext dbc)
                                          throws java.sql.SQLException
        Returns a JDBC connection from the connection pool.

        Use this method to get a connection for reading/writing project independent data.

        Parameters:
        dbc - the current database context
        Returns:
        a JDBC connection
        Throws:
        java.sql.SQLException - if the project id is not supported
      • getPreparedStatement

        public java.sql.PreparedStatement getPreparedStatement​(java.sql.Connection con,
                                                               CmsProject project,
                                                               java.lang.String queryKey)
                                                        throws java.sql.SQLException
        Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query and the CmsProject.

        Parameters:
        con - the JDBC connection
        project - the specified CmsProject
        queryKey - the key of the SQL query
        Returns:
        PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
        Throws:
        java.sql.SQLException - if a database access error occurs
      • getPreparedStatement

        public java.sql.PreparedStatement getPreparedStatement​(java.sql.Connection con,
                                                               CmsUUID projectId,
                                                               java.lang.String queryKey)
                                                        throws java.sql.SQLException
        Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query and the project-ID.

        Parameters:
        con - the JDBC connection
        projectId - the ID of the specified CmsProject
        queryKey - the key of the SQL query
        Returns:
        PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
        Throws:
        java.sql.SQLException - if a database access error occurs
      • getPreparedStatement

        public java.sql.PreparedStatement getPreparedStatement​(java.sql.Connection con,
                                                               java.lang.String queryKey)
                                                        throws java.sql.SQLException
        Returns a PreparedStatement for a JDBC connection specified by the key of a SQL query.

        Parameters:
        con - the JDBC connection
        queryKey - the key of the SQL query
        Returns:
        PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
        Throws:
        java.sql.SQLException - if a database access error occurs
      • getPreparedStatementForSql

        public java.sql.PreparedStatement getPreparedStatementForSql​(java.sql.Connection con,
                                                                     java.lang.String query)
                                                              throws java.sql.SQLException
        Returns a PreparedStatement for a JDBC connection specified by the SQL query.

        Parameters:
        con - the JDBC connection
        query - the SQL query
        Returns:
        PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement
        Throws:
        java.sql.SQLException - if a database access error occurs
      • init

        public void init​(int driverType,
                         java.lang.String poolUrl)
        Initializes this SQL manager.

        Parameters:
        driverType - the type ID of the driver (vfs,user,project or history) from where this SQL manager is referenced
        poolUrl - the pool URL to get connections from the JDBC driver manager
      • readQuery

        public java.lang.String readQuery​(CmsProject project,
                                          java.lang.String queryKey)
        Searches for the SQL query with the specified key and CmsProject.

        Parameters:
        project - the specified CmsProject
        queryKey - the key of the SQL query
        Returns:
        the the SQL query in this property list with the specified key
      • readQuery

        public java.lang.String readQuery​(CmsUUID projectId,
                                          java.lang.String queryKey)
        Searches for the SQL query with the specified key and project-ID.

        For projectIds ≠ 0, the pattern QUERY_PROJECT_SEARCH_PATTERN in table names of queries is replaced with "_ONLINE_" or "_OFFLINE_" to choose the right database tables for SQL queries that are project dependent!

        Parameters:
        projectId - the ID of the specified CmsProject
        queryKey - the key of the SQL query
        Returns:
        the the SQL query in this property list with the specified key
      • readQuery

        public java.lang.String readQuery​(java.lang.String queryKey)
        Searches for the SQL query with the specified key.

        Parameters:
        queryKey - the SQL query key
        Returns:
        the the SQL query in this property list with the specified key
      • setBytes

        public void setBytes​(java.sql.PreparedStatement statement,
                             int pos,
                             byte[] content)
                      throws java.sql.SQLException
        Sets the designated parameter to the given Java array of bytes.

        The driver converts this to an SQL VARBINARY or LONGVARBINARY (depending on the argument's size relative to the driver's limits on VARBINARY values) when it sends it to the database.

        Parameters:
        statement - the PreparedStatement where the content is set
        pos - the first parameter is 1, the second is 2, ...
        content - the parameter value
        Throws:
        java.sql.SQLException - if a database access error occurs
      • validateEmpty

        public java.lang.String validateEmpty​(java.lang.String value)
        Replaces null or empty Strings with a String with one space character " ".

        Parameters:
        value - the string to validate
        Returns:
        the validate string or a String with one space character if the validated string is null or empty
      • loadQueryProperties

        protected void loadQueryProperties​(java.lang.String propertyFilename)
        Loads a Java properties hash containing SQL queries.

        Parameters:
        propertyFilename - the package/filename of the properties hash
      • replaceQuerySearchPatterns

        protected void replaceQuerySearchPatterns()
        Replaces patterns ${XXX} by another property value, if XXX is a property key with a value.