de.sqlcoach.db.jdbc.DBConnection.java Source code

Java tutorial

Introduction

Here is the source code for de.sqlcoach.db.jdbc.DBConnection.java

Source

/*******************************************************************************
 * This file is part of SQLCoach.
 *
 * SQLCoach is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * SQLCoach is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with Foobar.  If not, see <http://www.gnu.org/licenses/>.
 *******************************************************************************/
/*
 * Created on 09.11.2006 at 03:20:58
 *
 * Florian Moritz, Chistoph Gerstle
 *
 * Media Management and Production
 * University of Applied Sciences Kaiserslautern
 * License: LGPL - GNU Lesser General Public License - http://www.gnu.org/licenses/lgpl.html
 */

package de.sqlcoach.db.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
//import org.wildfly.security.manager.action.GetSystemPropertiesAction;

/**
 * The Class DBConnection. This class is a Wrapper for java.util.Connection
 * 
 * @author Bernhard Schiefer
 * @version 0.1
 */
public class DBConnection implements AutoCloseable {

    @Override
    public String toString() {
        return "[dataSource=" + dataSourceName + " connectionCnt=" + connectionCnt + "]";
    }

    /** The log. */
    private static final Log log = LogFactory.getLog(DBConnection.class);

    /** The PROPERTIE s_ FILE. */
    // private static final String PROPERTIES_FILE =
    // "src/de/fhkl/sqlcoach/properties/db.properties";
    private static final String PROPERTIES_FILE = "db.properties";

    /** The SQLCOAC h_ DBA. */
    public static final String SQLCOACH_DBA = "SQLCOACH_DBADS";

    /** The SQLCOAC h_ USER. */
    // public static String SQLCOACH_USER = "SQLUEBDS";

    /** The connection counter. */
    private static Map<String, Integer> connectionCounter = new HashMap<String, Integer>();

    private Connection cn; //
    private int connectionCnt; // Value of connectionCounter when created
    private String dataSourceName;

    /**
     * Instantiates a new DB object.
     */
    private DBConnection(Connection cn, int connectionCnt, String dataSourceName) {
        this.cn = cn;
        this.connectionCnt = connectionCnt;
        this.dataSourceName = dataSourceName;
    }

    /**
     * provides a Connection object to handle database actions.
     * 
     * @return Connection
     * 
     * @throws Exception
     *           *
     * @throws NamingException
     *           the naming exception
     * @throws SQLException
     *           the SQL exception
     */
    public static DBConnection getConnection() throws NamingException, SQLException {
        // decide to use connection pool or a simple connection with a
        // properties file

        return getPoolConnection(SQLCOACH_DBA);
    }

    public static DBConnection getConnection(String dataSourceName) throws NamingException, SQLException {
        // decide to use connection pool or a simple connection with a
        // properties file
        return getPoolConnection(dataSourceName);
    }

    /**
     * Gets the pool connection.
     * 
     * @return the pool connection
     * 
     * @throws NamingException
     *           the naming exception
     * @throws SQLException
     *           the SQL exception
     */
    protected static DBConnection getPoolConnection(String dataSourceName) throws NamingException, SQLException {
        final Context initCtx = new InitialContext();
        final Context envCtx = (Context) initCtx.lookup("java:comp/env");

        final String name = "jdbc/" + dataSourceName;

        DBConnection dbconn = null;

        Integer connectionCnt = DBConnection.connectionCounter.get(dataSourceName);
        if (connectionCnt == null)
            connectionCnt = 0;

        if (log.isInfoEnabled()) {
            log.info("getPoolConnection ENTER [dataSource=" + dataSourceName + " connectionCnt=" + connectionCnt
                    + "]");
        }

        // final DataSource ds = (DataSource)envCtx.lookup(name);
        // if (ds == null) {
        // log.error ("getPoolConnection : No DataSource for "+ name+ " !");
        // return null;
        // } else {
        // if (log.isDebugEnabled())
        // log.debug("getPoolConnection : DataSource: "+ds.toString());
        // }

        // final Connection cn = ds.getConnection();
        final Connection cn = getSimpleConnection(dataSourceName);
        if (cn == null) { // Failed !
            log.error("getPoolConnection : No Connection for " + name + " ! (connectionCnt:" + connectionCnt + ")");
            return null;
        } else { // Success
            DBConnection.connectionCounter.put(dataSourceName, connectionCnt++);
            dbconn = new DBConnection(cn, connectionCnt, dataSourceName);
            if (log.isDebugEnabled())
                log.debug("getPoolConnection : Connection: " + cn.toString());
        }

        cn.setAutoCommit(false);

        // Set Oracle date format to YYYY-MM-DD
        // final java.sql.DatabaseMetaData dm = cn.getMetaData();
        // final String prodname = dm.getDatabaseProductName();
        // if (prodname.equalsIgnoreCase("Oracle")) { // Only for Oracle !!
        // try (Statement s = cn.createStatement()) {
        // s.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
        // if (log.isDebugEnabled())
        // log.debug("getPoolConnection : ProductName=" + prodname + "\nALTER
        // SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
        // } catch (SQLException e) {
        // log.warn("getPoolConnection : ProductName="+prodname+"\nALTER SESSION SET
        // NLS_DATE_FORMAT = 'YYYY-MM-DD': ", e);
        // }
        // } else {
        // if (log.isDebugEnabled())
        // log.debug("getPoolConnection : Kein Oracle cn= "+cn+ "
        // ProductName="+prodname);
        // }

        if (log.isInfoEnabled())
            log.info("getPoolConnection LEAVE dbconn=" + dbconn);

        return dbconn;
    }

    /**
     * Gets the simple connection.
     * 
     * @return the simple connection
     */
    public static Connection getSimpleConnection(String dataSourceName) {

        log.info("getSimpleConnection ENTER ");
        String driver = "com.sap.dbtech.jdbc.DriverSapDB";
        String url = "jdbc:sapdb://localhost/SQLCOACH";
        String user = "SQLCOACH_DBA";
        String password = "sqlcoach";

        Connection cn = null;
        // final Properties props = new Properties();
        // try {
        // props.load(new FileInputStream(PROPERTIES_FILE));
        // } catch (FileNotFoundException e) {
        // log.error("getSimpleConnection : FileNotFoundException: \n" + e);
        // } catch (IOException e) {
        // log.error("getSimpleConnection : IOException: \n" + e);
        // }

        try {
            Class.forName("com.sap.dbtech.jdbc.DriverSapDB").newInstance();
        } catch (InstantiationException e1) {
            log.error("getSimpleConnection : Class.forName InstantiationException:\n" + e1);
        } catch (IllegalAccessException e1) {
            log.error("getSimpleConnection : Class.forName IllegalAccessException:\n" + e1);
        } catch (ClassNotFoundException e1) {
            log.error("getSimpleConnection : Class.forName ClassNotFoundException:\n" + e1);
        }

        /**
         * driver=com.sap.dbtech.jdbc.DriverSapDB
         * url=jdbc:sapdb://localhost/sqlcoach user=SQLCOACH_DBA password=test
         */

        // final String usr = props.getProperty("user");
        // final String pwd = props.getProperty("password");
        // final String url = props.getProperty("url");

        try {
            cn = DriverManager.getConnection(url, user, password);
            // cn = DriverManager.getConnection("jdbc: sapdb: // localhost/SQLCOACH");
            cn.setAutoCommit(false);
        } catch (Exception e) {
            log.error("getSimpleConnection : problems while establishing the connection:\n", e);
        }
        //      int connectionCnt = connectionCounter.get(dataSourceName);
        //      connectionCounter.put(dataSourceName, connectionCnt++);
        return cn;
    }

    /**
     * Close connection.
     * 
     * @param cn
     *          the cn
     */
    @Override
    public void close() throws Exception {
        if (log.isInfoEnabled())
            log.info("close Connection ENTER cn=" + this);

        if (cn == null) {
            log.error("close Connection cn == NULL! this=" + this);
            return;
        }
        int connectionCnt = DBConnection.connectionCounter.get(dataSourceName);
        DBConnection.connectionCounter.put(dataSourceName, connectionCnt--);
        try {
            cn.close();
            cn = null;
        } catch (SQLException sqle) {
            log.error("close Connection() SQLException this=" + this + "\ncn=" + cn, sqle);
        }
    }

    public Connection getCn() {
        return cn;
    }

    public DatabaseMetaData getMetaData() throws SQLException {
        if (log.isInfoEnabled())
            log.info("getMetaData ENTER cn=" + this);
        return cn.getMetaData();
    }

    public void commit() throws SQLException {
        if (log.isInfoEnabled())
            log.info("commit ENTER cn=" + this);
        cn.commit();
    }

    public void rollback() throws SQLException {
        if (log.isInfoEnabled())
            log.info("rollback ENTER cn=" + this);
        cn.rollback();
    }
}