gov.nih.nci.security.upt.util.JDBCHelper.java Source code

Java tutorial

Introduction

Here is the source code for gov.nih.nci.security.upt.util.JDBCHelper.java

Source

/*L
 *  Copyright Ekagra Software Technologies Ltd.
 *  Copyright SAIC, SAIC-Frederick
 *
 *  Distributed under the OSI-approved BSD 3-Clause License.
 *  See http://ncip.github.com/common-security-module/LICENSE.txt for details.
 */

package gov.nih.nci.security.upt.util;

import gov.nih.nci.security.authorization.domainobjects.ApplicationContext;
import gov.nih.nci.security.exceptions.CSException;
import gov.nih.nci.security.upt.constants.DisplayConstants;
import gov.nih.nci.security.upt.forms.ApplicationForm;
import gov.nih.nci.security.upt.forms.BaseDBForm;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cache.CacheException;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Projections;
import org.hibernate.exception.GenericJDBCException;
import org.hibernate.exception.JDBCConnectionException;
import org.hibernate.exception.SQLGrammarException;

import com.mysql.jdbc.CommunicationsException;

/**
 * JDBC Helper class is created to test the database connection parameters
 * available from UPT's ApplicationForm.
 *
 * @author parmarv
 *
 */
public class JDBCHelper {

    /**
     * This method uses Hibernates SessionFactory to get a Session and using Hibernates Criteria does a sample query
     * to connection and obtain results as part of testing for successful connection.
     *
     * Based on the kind of exceptions this method throws CSException with appropriate message.
     * @param appForm -
     *            The ApplicationForm with application database parameters to
     *            test connection for.
     * @return String - The message indicating that connection and a SQL query
     *         was successful
     * @throws CSException -
     *             The exception message indicates which kind of application
     *             database parameters are invalid.
     */
    public static String testConnectionHibernate(BaseDBForm appForm) throws CSException {
        ApplicationForm appform = (ApplicationForm) appForm;

        SessionFactory sf = null;
        try {

            Configuration configuration = new Configuration();
            configuration.addResource("gov/nih/nci/security/authorization/domainobjects/Application.hbm.xml");
            configuration.setProperty("hibernate.connection.url", appform.getApplicationDatabaseURL());
            configuration.setProperty("hibernate.connection.username", appform.getApplicationDatabaseUserName());
            configuration.setProperty("hibernate.connection.password", appform.getApplicationDatabasePassword());
            configuration.setProperty("hibernate.dialect", appform.getApplicationDatabaseDialect());
            configuration.setProperty("hibernate.connection.driver_class", appform.getApplicationDatabaseDriver());
            configuration.setProperty("hibernate.connection.pool_size", "1");
            sf = configuration.buildSessionFactory();
            Session session = sf.openSession();

            Criteria criteria = session.createCriteria(ApplicationContext.class);
            criteria.add(Expression.eq("applicationName", appform.getApplicationName().trim()));
            criteria.setProjection(Projections.rowCount());

            List results = criteria.list();
            Integer integer = (Integer) results.iterator().next();
            if (integer == null) {
                session.close();
                throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED);
            }

            session.close();
            return DisplayConstants.APPLICATION_DATABASE_CONNECTION_SUCCESSFUL;

        } catch (Throwable t) {
            // Depending on the cause of the exception obtain message and throw a CSException.
            if (t instanceof SQLGrammarException) {
                throw new CSException(
                        DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED + " " + t.getCause().getMessage());
            }
            if (t instanceof JDBCConnectionException) {
                if (t.getCause() instanceof CommunicationsException) {
                    throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL_SERVER_PORT);
                }
                if (t.getCause() instanceof SQLException) {
                    throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL);
                }
                throw new CSException(
                        DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED + " " + t.getMessage());
            }
            if (t instanceof GenericJDBCException) {
                throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL_USER_PASS);
            }
            if (t instanceof HibernateException) {
                throw new CSException(
                        DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED + " " + t.getMessage());
            }

            throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED);
        }

    }

    /**
     * This method uses Hibernates SessionFactory to get a Session and using Hibernates Criteria does a sample query
     * to connection and obtain results as part of testing for successful connection.
     *
     * Based on the kind of exceptions this method throws CSException with appropriate message.
     * @param appForm -
     *            The ApplicationForm with application database parameters to
     *            test connection for.
     * @return String - The message indicating that connection and a SQL query
     *         was successful
     * @throws CSException -
     *             The exception message indicates which kind of application
     *             database parameters are invalid.
     */
    public static String testConnectionHibernate(Configuration configuration) throws CSException {

        SessionFactory sf = null;
        ResultSet rs = null;
        Statement stmt = null;
        Connection conn = null;
        Session session = null;
        try {
            //System.out.println("testConnectionHibernate*****1");
            sf = configuration.buildSessionFactory();
            //System.out.println("testConnectionHibernate*****2");
            session = sf.openSession();
            //System.out.println("testConnectionHibernate*****3");
            conn = session.connection();
            //System.out.println("testConnectionHibernate*****4");
            stmt = conn.createStatement();
            //System.out.println("testConnectionHibernate*****5");
            stmt.execute("select count(*) from csm_application");
            //System.out.println("testConnectionHibernate*****6");
            rs = stmt.getResultSet();
            //System.out.println("testConnectionHibernate*****7");

            //System.out.println(rs.getMetaData().getColumnCount());

            return DisplayConstants.APPLICATION_DATABASE_CONNECTION_SUCCESSFUL;

        } catch (Throwable t) {
            t.printStackTrace();
            // Depending on the cause of the exception obtain message and throw a CSException.
            if (t instanceof SQLGrammarException) {
                throw new CSException(
                        DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED + " " + t.getCause().getMessage());
            }
            if (t instanceof JDBCConnectionException) {
                if (t.getCause() instanceof CommunicationsException) {
                    throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL_SERVER_PORT);
                }
                if (t.getCause() instanceof SQLException) {
                    throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL);
                }
                throw new CSException(
                        DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED + " " + t.getMessage());
            }
            if (t instanceof GenericJDBCException) {
                throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL_USER_PASS + " ");
            }
            if (t instanceof CacheException) {
                throw new CacheException("Please Try Again.\n ");

            }
            if (t instanceof HibernateException) {
                throw new CSException(
                        DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED + " " + t.getMessage());
            }

            throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL_USER_PASS);
        } finally {
            try {
                sf.close();
                rs.close();
                stmt.close();
                conn.close();
                session.close();
            } catch (Exception e) {
            }

        }

    }

    /**
     * testConnection method accepts
     *
     * @param appForm -
     *            The ApplicationForm with application database parameters to
     *            test connection for.
     * @return String - The message indicating that connection and a SQL query
     *         was successful
     * @throws CSException -
     *             The exception message indicates which kind of application
     *             database parameters are invalid.
     */
    public static String testConnectionJDBC(BaseDBForm appForm) throws CSException {
        ApplicationForm appform = (ApplicationForm) appForm;
        String message = "";
        try {
            Driver d = (Driver) Class.forName(appform.getApplicationDatabaseDriver()).newInstance();
            Properties props = new Properties();
            props.setProperty("user", appform.getApplicationDatabaseUserName());
            props.setProperty("password", appform.getApplicationDatabasePassword());
            props.setProperty("dialect", appform.getApplicationDatabaseDialect());
            Connection conn = d.connect(appform.getApplicationDatabaseURL(), props);

            Statement stmt = conn.createStatement();
            stmt.execute("select count(*) from csm_application");
            ResultSet rs = stmt.getResultSet();

            rs.close();

            stmt.close();

            conn.close();
            message = DisplayConstants.APPLICATION_DATABASE_CONNECTION_SUCCESSFUL;

        } catch (ClassNotFoundException cnfe) {
            throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_DRIVER);
        } catch (InstantiationException e) {
            throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_DRIVER);
        } catch (IllegalAccessException e) {
            throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_DRIVER);
        } catch (SQLException e) {
            throw new CSException(DisplayConstants.APPLICATION_DATABASE_CONNECTION_FAILED_URL_USER_PASS);
        }

        return message;
    }

}