org.apereo.portal.jdbc.DatabaseMetaDataImpl.java Source code

Java tutorial

Introduction

Here is the source code for org.apereo.portal.jdbc.DatabaseMetaDataImpl.java

Source

/**
 * Licensed to Apereo under one or more contributor license
 * agreements. See the NOTICE file distributed with this work
 * for additional information regarding copyright ownership.
 * Apereo licenses this file to you under the Apache License,
 * Version 2.0 (the "License"); you may not use this file
 * except in compliance with the License.  You may obtain a
 * copy of the License at the following location:
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apereo.portal.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * @author Eric Dalquist <a href="mailto:edalquist@unicon.net">edalquist@unicon.net</a>
 */
public class DatabaseMetaDataImpl implements IDatabaseMetadata, InitializingBean {
    public static final class PostgreSQLDb extends JoinQueryString {
        public PostgreSQLDb(final String testString) {
            super(testString);
        }
    }

    public static final class OracleDb extends JoinQueryString {
        public OracleDb(final String testString) {
            super(testString);
        }
    }

    public static final class JdbcDb extends JoinQueryString {
        public JdbcDb(final String testString) {
            super(testString);
        }
    }

    private static final Log LOG = LogFactory.getLog(DatabaseMetaDataImpl.class);

    /** Define the oracle TO_DATE format */

    //Define the different join queries we know about with the
    //appropriately typed JoinQueryString implementation.
    private static final JoinQueryString jdbcDb = new DatabaseMetaDataImpl.JdbcDb(
            "{oj UP_USER LEFT OUTER JOIN UP_USER_LAYOUT ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID} WHERE");
    private static final JoinQueryString postgreSQLDb = new DatabaseMetaDataImpl.PostgreSQLDb(
            "UP_USER LEFT OUTER JOIN UP_USER_LAYOUT ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID WHERE");
    private static final JoinQueryString oracleDb = new DatabaseMetaDataImpl.OracleDb(
            "UP_USER, UP_USER_LAYOUT WHERE UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID(+) AND");

    /** Array of join tests to perform. */
    private static final JoinQueryString[] joinTests = { oracleDb, postgreSQLDb, jdbcDb };

    /** The {@link DataSource} that represents the server */
    private final DataSource dataSource;
    private final PlatformTransactionManager transactionManager;
    private TransactionTemplate transactionTemplate;

    /** The {@link IJoinQueryString} to use for performing outer joins */
    private IJoinQueryString joinTest = null;
    private boolean dbmdSupportsOuterJoins = false;

    //Database meta information
    private boolean portalTablesExist = false;
    private boolean useTSWrapper = false;
    private boolean useToDate = false;
    private String databaseProductName = null;
    private String databaseProductVersion = null;
    private String driverName = null;
    private String driverVersion = null;
    private String userName = null;
    private String dbUrl = null;

    /**
     * Creates a new {@link DatabaseMetaDataImpl} with the specified
     * {@link DataSource}.
     *
     * @param ds The {@link DataSource} to use as the base for this server interface.
     */
    public DatabaseMetaDataImpl(final DataSource ds, final PlatformTransactionManager transactionManager) {
        if (ds == null)
            throw new IllegalArgumentException("DataSource cannot be null");

        this.dataSource = ds;
        this.transactionManager = transactionManager;
    }

    /* (non-Javadoc)
     * @see org.springframework.beans.factory.InitializingBean#afterPropertiesSet()
     */
    public void afterPropertiesSet() throws Exception {
        this.transactionTemplate = new TransactionTemplate(this.transactionManager);
        this.transactionTemplate.setPropagationBehavior(TransactionTemplate.PROPAGATION_REQUIRES_NEW);
        this.transactionTemplate.setReadOnly(true);
        this.transactionTemplate.afterPropertiesSet();

        this.runDatabaseTests();
        if (LOG.isDebugEnabled())
            LOG.debug(this.toString());
    }

    private void releaseConnection(final Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            LOG.warn("An error occured while closing a connection.", e);
        }
    }

    /**
     * @see IDatabaseMetadata#getJoinQuery()
     */
    public final IJoinQueryString getJoinQuery() {
        return this.joinTest;
    }

    /**
     * @see IDatabaseMetadata#supportsOuterJoins()
     */
    public final boolean supportsOuterJoins() {
        return (this.joinTest != null);
    }

    /**
     * @see IDatabaseMetadata#supportsTransactions()
     */
    public final boolean supportsTransactions() {
        //We never run on DBs that don't support transactions any more
        return true;
    }

    /**
     * @see IDatabaseMetadata#supportsPreparedStatements()
     */
    public final boolean supportsPreparedStatements() {
        //We never run on DBs that don't support prepared statements any more
        return true;
    }

    /* (non-Javadoc)
     * @see org.apereo.portal.jdbc.IDatabaseMetadata#getJdbcDriver()
     */
    public String getJdbcDriver() {
        return this.driverName;
    }

    /* (non-Javadoc)
     * @see org.apereo.portal.jdbc.IDatabaseMetadata#getDatabaseProductName()
     */
    public String getDatabaseProductName() {
        return this.databaseProductName;
    }

    /* (non-Javadoc)
     * @see org.apereo.portal.jdbc.IDatabaseMetadata#getDatabaseProductVersion()
     */
    public String getDatabaseProductVersion() {
        return this.databaseProductVersion;
    }

    /* (non-Javadoc)
     * @see org.apereo.portal.jdbc.IDatabaseMetadata#getJdbcDriverVersion()
     */
    public String getJdbcDriverVersion() {
        return this.driverVersion;
    }

    /* (non-Javadoc)
     * @see org.apereo.portal.jdbc.IDatabaseMetadata#getJdbcUrl()
     */
    public String getJdbcUrl() {
        return this.dbUrl;
    }

    /* (non-Javadoc)
     * @see org.apereo.portal.jdbc.IDatabaseMetadata#getJdbcUser()
     */
    public String getJdbcUser() {
        return this.userName;
    }

    /**
     * @see IDatabaseMetadata#sqlTimeStamp()
     */
    public String sqlTimeStamp() {
        return this.sqlTimeStamp(System.currentTimeMillis());
    }

    /**
     * @see IDatabaseMetadata#sqlTimeStamp(long)
     */
    public String sqlTimeStamp(final long date) {
        final StringBuffer sqlTS = new StringBuffer();

        if (useToDate) {
            final SimpleDateFormat oracleToDate = new SimpleDateFormat("yyyy MM dd HH:mm:ss");
            sqlTS.append("TO_DATE('");
            sqlTS.append(oracleToDate.format(new Date(date)));
            sqlTS.append("', 'YYYY MM DD HH24:MI:SS')");
        } else if (useTSWrapper) {
            sqlTS.append("{ts '");
            sqlTS.append(new Timestamp(date).toString());
            sqlTS.append("'}");
        } else {
            sqlTS.append("'");
            sqlTS.append(new Timestamp(date).toString());
            sqlTS.append("'");
        }

        return sqlTS.toString();
    }

    /**
     * @see IDatabaseMetadata#sqlTimeStamp(java.util.Date)
     */
    public String sqlTimeStamp(final Date date) {
        if (date == null) {
            return "NULL";
        }

        return this.sqlTimeStamp(date.getTime());
    }

    @Override
    public String toString() {
        final StringBuilder dbInfo = new StringBuilder();

        dbInfo.append(this.databaseProductName);
        dbInfo.append(" (");
        dbInfo.append(this.databaseProductVersion);
        dbInfo.append(") / ");
        dbInfo.append(this.driverName);
        dbInfo.append(" (");
        dbInfo.append(this.driverVersion);
        dbInfo.append(") database/driver");
        dbInfo.append("\n");
        dbInfo.append("    Connected To: ");
        dbInfo.append(this.dbUrl);
        dbInfo.append("\n");

        if (this.portalTablesExist) {
            dbInfo.append("    Supports:");
            dbInfo.append("\n");
            dbInfo.append("        Outer Joins:          ");
            dbInfo.append(this.supportsOuterJoins());
            dbInfo.append("\n");
            dbInfo.append("        {ts metasyntax:       ");
            dbInfo.append(this.useTSWrapper);
            dbInfo.append("\n");
            dbInfo.append("        TO_DATE():            ");
            dbInfo.append(this.useToDate);
        } else {
            dbInfo.append("    WARNING: uPortal tables do no exist, not all meta-data tests were executed.");
        }

        return dbInfo.toString();
    }

    /**
     * Run a set of tests on the database to provide better meta data.
     */
    private void runDatabaseTests() {
        Connection conn = null;
        try {
            conn = this.dataSource.getConnection();
            //The order of these tests is IMPORTANT, each may depend on the
            //results of the previous tests.
            this.getMetaData(conn);
            final JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

            this.testDatabaseInitialized(jdbcTemplate);
            if (this.portalTablesExist) {
                this.testOuterJoins(jdbcTemplate);
                this.testTimeStamp(jdbcTemplate);
            }

        } catch (SQLException e) {
            LOG.error("Error during database initialization. ", e);
            /*
             * We must throw a RuntimeException here to avoid starting the portal
             * with incorrect assumptions about what the database supports.
             */
            throw new DataAccessResourceFailureException("Error during database initialization. ", e);
        } finally {
            this.releaseConnection(conn);
        }
    }

    /**
     * Gets meta data about the connection.
     */
    private void getMetaData(final Connection conn) {
        try {
            final DatabaseMetaData dmd = conn.getMetaData();

            this.databaseProductName = dmd.getDatabaseProductName();
            this.databaseProductVersion = dmd.getDatabaseProductVersion();
            this.driverName = dmd.getDriverName();
            this.driverVersion = dmd.getDriverVersion();
            this.userName = dmd.getUserName();
            this.dbUrl = dmd.getURL();
            this.dbmdSupportsOuterJoins = dmd.supportsOuterJoins();
        } catch (SQLException sqle) {
            LOG.error("Error getting database meta data.", sqle);
        }
    }

    /**
     * Tests if the uPortal tables exist that are needed for this test. 
     */
    private void testDatabaseInitialized(final JdbcTemplate jdbcTemplate) {
        try {
            jdbcTemplate.queryForObject("SELECT COUNT(USER_ID) FROM UP_USER", Integer.class);
            this.portalTablesExist = true;
        } catch (BadSqlGrammarException bsge) {
            LOG.warn("The uPortal database is not initialized, the database tests will not be performed.");
        }
    }

    /**
     * Test the database to see if it really supports outer joins.
     * @param conn The connection to use.
     */
    private void testOuterJoins(final JdbcTemplate jdbcTemplate) {
        if (this.dbmdSupportsOuterJoins) {
            for (final JoinQueryString joinQueryString : joinTests) {
                final String joinTestQuery = "SELECT COUNT(UP_USER.USER_ID) " + "FROM "
                        + joinQueryString.getTestJoin() + " UP_USER.USER_ID=0";

                try {
                    transactionTemplate.execute(new TransactionCallbackWithoutResult() {
                        @Override
                        public void doInTransactionWithoutResult(TransactionStatus status) {
                            jdbcTemplate.execute(joinTestQuery);
                        }
                    });

                    this.joinTest = joinQueryString;
                    if (LOG.isDebugEnabled()) {
                        LOG.debug("Using join test: " + this.joinTest.getClass().getName());
                    }
                    break;
                } catch (Exception e) {
                    final String logMessage = "Join test failed: " + joinQueryString.getClass().getName()
                            + " on statement: '" + joinTestQuery + "':";

                    if (LOG.isDebugEnabled()) {
                        LOG.debug(logMessage, e);
                    }
                }
            }
        }
    }

    /**
     * Test the database to find the supported timestamp format
     */
    private void testTimeStamp(final JdbcTemplate jdbcTemplate) {
        try {
            //Try using {ts }
            final String timeStampTestQuery = "SELECT USER_ID " + "FROM UP_USER "
                    + "WHERE LST_CHAN_UPDT_DT={ts '2001-01-01 00:00:00.0'} AND USER_ID = 0";

            jdbcTemplate.queryForList(timeStampTestQuery);
            this.useTSWrapper = true;
        } catch (DataAccessException dae1) {
            final String logMessage1 = "Error running {ts } test.";
            if (LOG.isDebugEnabled()) {
                LOG.debug(logMessage1, dae1);
            }

            //Try using TO_DATE()
            try {
                final String toDateTestQuery = "SELECT USER_ID " + "FROM UP_USER "
                        + "WHERE LST_CHAN_UPDT_DT>TO_DATE('2001 01 01 00:00', 'YYYY MM DD HH24:MI:SS') AND USER_ID=0";

                jdbcTemplate.queryForList(toDateTestQuery);
                this.useToDate = true;
            } catch (DataAccessException dae2) {
                final String logMessage2 = "Error running TO_DATE() test.";
                if (LOG.isDebugEnabled()) {
                    LOG.debug(logMessage2, dae2);
                }
            }
        }
    }

}