cmssqlmanager.java

cms是开源的框架

     * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement 
     * @throws SQLException if a database access error occurs
     */
    public PreparedStatement getPreparedStatement(Connection con, String queryKey) throws SQLException {

        String rawSql = readQuery(0, queryKey);
        return getPreparedStatementForSql(con, rawSql);
    }

    /**
     * Returns a PreparedStatement for a JDBC connection specified by the SQL query.<p>
     * 
     * @param con the JDBC connection
     * @param query the SQL query
     * @return PreparedStatement a new PreparedStatement containing the pre-compiled SQL statement 
     * @throws SQLException if a database access error occurs
     */
    public PreparedStatement getPreparedStatementForSql(Connection con, String query) throws SQLException {

        // unfortunately, this wrapper is essential, because some JDBC driver 
        // implementations don't accept the delegated objects of DBCP's connection pool. 
        return con.prepareStatement(query);
    }

    /**
     * Initializes this SQL manager.<p>
     * 
     * @param driverType the type ID of the driver (vfs,user,project,workflow or backup) from where this SQL manager is referenced
     * @param poolUrl the pool URL to get connections from the JDBC driver manager
     */
    public void init(int driverType, String poolUrl) {

        if (!poolUrl.startsWith(CmsDbPool.DBCP_JDBC_URL_PREFIX)) {
            poolUrl = CmsDbPool.DBCP_JDBC_URL_PREFIX + poolUrl;
        }

        m_driverType = driverType;
        m_poolUrl = poolUrl;

    }

    /**
     * Generates a new primary key for a given database table name.<p>
     * 
     * This method makes only sense for old-style tables where the primary key is NOT a CmsUUID!
     * 
     * @param tableName the table for which a new primary key should be generated.
     * @return int the new primary key
     * @throws CmsDataAccessException if an error occurs
     */
    public int nextId(String tableName) throws CmsDataAccessException {

        return org.opencms.db.CmsDbUtil.nextId(m_poolUrl, tableName);
    }

    /**
     * Searches for the SQL query with the specified key and CmsProject.<p>
     * 
     * @param project the specified CmsProject
     * @param queryKey the key of the SQL query
     * @return the the SQL query in this property list with the specified key
     */
    public String readQuery(CmsProject project, String queryKey) {

        return readQuery(project.getId(), queryKey);
    }

    /**
     * Searches for the SQL query with the specified key and project-ID.<p>
     * 
     * For projectIds &ne; 0, the pattern {@link #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!
     * 
     * @param projectId the ID of the specified CmsProject
     * @param queryKey the key of the SQL query
     * @return the the SQL query in this property list with the specified key
     */
    public String readQuery(int projectId, String queryKey) {

        String key;
        if (projectId != 0) {
            // id 0 is special, please see below
            StringBuffer buffer = new StringBuffer(128);
            buffer.append(queryKey);
            if (projectId == CmsProject.ONLINE_PROJECT_ID || projectId < 0) {
                buffer.append("_ONLINE");
            } else {
                buffer.append("_OFFLINE");
            }
            key = buffer.toString();
        } else {
            key = queryKey;
        }

        // look up the query in the cache
        String query = (String)m_cachedQueries.get(key);

        if (query == null) {
            // the query has not been cached yet
            // get the SQL statement from the properties hash
            query = readQuery(queryKey);

            // replace control chars.
            query = CmsStringUtil.substitute(query, "\t", " ");
            query = CmsStringUtil.substitute(query, "\n", " ");

            if (projectId != 0) {
                // a project ID = 0 is an internal indicator that a project-independent 
                // query was requested - further regex operations are not required then
                query = CmsSqlManager.replaceProjectPattern(projectId, query);
            }

            // to minimize costs, all statements with replaced expressions are cached in a map
            m_cachedQueries.put(key, query);
        }

        return query;
    }

    /**
     * Searches for the SQL query with the specified key.<p>
     * 
     * @param queryKey the SQL query key
     * @return the the SQL query in this property list with the specified key
     */
    public String readQuery(String queryKey) {

        String value = (String)m_queries.get(queryKey);
        if (value == null) {
            if (LOG.isErrorEnabled()) {
                LOG.error(Messages.get().getBundle().key(Messages.LOG_QUERY_NOT_FOUND_1, queryKey));
            }
        }
        return value;
    }

    /**
     * Sets the designated parameter to the given Java array of bytes.<p>
     * 
     * 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. 
     * 
     * @param statement the PreparedStatement where the content is set
     * @param pos the first parameter is 1, the second is 2, ...
     * @param content the parameter value 
     * @throws SQLException if a database access error occurs
     */
    public void setBytes(PreparedStatement statement, int pos, byte[] content) throws SQLException {

        if (content.length < 2000) {
            statement.setBytes(pos, content);
        } else {
            statement.setBinaryStream(pos, new ByteArrayInputStream(content), content.length);
        }
    }

    /**
     * Replaces null or empty Strings with a String with one space character <code>" "</code>.<p>
     * 
     * @param value the string to validate
     * @return the validate string or a String with one space character if the validated string is null or empty
     */
    public String validateEmpty(String value) {

        if (CmsStringUtil.isNotEmpty(value)) {
            return value;
        }

        return " ";
    }

    /**
     * @see java.lang.Object#finalize()
     */
    protected void finalize() throws Throwable {

        try {
            if (m_cachedQueries != null) {
                m_cachedQueries.clear();
            }

            if (m_queries != null) {
                m_queries.clear();
            }
        } catch (Throwable t) {
            // intentionally left blank
        } finally {
            m_cachedQueries = null;
            m_queries = null;
            m_poolUrl = null;
        }

        super.finalize();
    }

    /**
     * Returns a JDBC connection from the connection pool specified by the given project ID.<p>
     * 
     * The project ID is (usually) the ID of the current project.<p>
     * 
     * Use this method to get a connection for reading/writing data either in online or offline projects
     * such as files, folders.<p>
     * 
     * @param projectId the ID of a Cms project (e.g. the current project from the request context)
     * @return a JDBC connection from the pool specified by the project-ID 
     * @throws SQLException if a database access error occurs
     */
    protected Connection getConnection(int projectId) throws SQLException {

        // the specified project ID is not evaluated in this implementation.
        // extensions of this object might evaluate the project ID to return
        // different connections...        

        if (projectId < 0) {
            throw new SQLException(Messages.get().getBundle().key(
                Messages.ERR_JDBC_CONN_INVALID_PROJECT_ID_1,
                new Integer(projectId)));
        }

        // match the ID to a JDBC pool URL of the OpenCms JDBC pools {online|offline|backup}
        return getConnectionByUrl(m_poolUrl);
    }

    /**
     * Loads a Java properties hash containing SQL queries.<p>
     * 
     * @param propertyFilename the package/filename of the properties hash
     */
    protected void loadQueryProperties(String propertyFilename) {

        Properties properties = new Properties();

        try {
            properties.load(getClass().getClassLoader().getResourceAsStream(propertyFilename));
            m_queries.putAll(properties);
            replaceQuerySearchPatterns();
        } catch (Throwable t) {
            if (LOG.isErrorEnabled()) {
                LOG.error(
                    Messages.get().getBundle().key(Messages.LOG_LOAD_QUERY_PROP_FILE_FAILED_1, propertyFilename),
                    t);
            }

            properties = null;
        }
    }

    /**
     * Replaces patterns ${XXX} by another property value, if XXX is a property key with a value.<p>
     */
    protected synchronized void replaceQuerySearchPatterns() {

        String currentKey = null;
        String currentValue = null;
        int startIndex = 0;
        int endIndex = 0;
        int lastIndex = 0;

        Iterator allKeys = m_queries.keySet().iterator();
        while (allKeys.hasNext()) {
            currentKey = (String)allKeys.next();
            currentValue = (String)m_queries.get(currentKey);
            startIndex = 0;
            endIndex = 0;
            lastIndex = 0;

            while ((startIndex = currentValue.indexOf("${", lastIndex)) != -1) {
                endIndex = currentValue.indexOf('}', startIndex);
                if (endIndex != -1 && !currentValue.startsWith(QUERY_PROJECT_SEARCH_PATTERN, startIndex - 1)) {

                    String replaceKey = currentValue.substring(startIndex + 2, endIndex);
                    String searchPattern = currentValue.substring(startIndex, endIndex + 1);
                    String replacePattern = this.readQuery(replaceKey);

                    if (replacePattern != null) {
                        currentValue = CmsStringUtil.substitute(currentValue, searchPattern, replacePattern);
                    }
                }

                lastIndex = endIndex + 2;
            }
            m_queries.put(currentKey, currentValue);
        }
    }
}