com.krawler.database.DbPool.java Source code

Java tutorial

Introduction

Here is the source code for com.krawler.database.DbPool.java

Source

/*
 * Copyright (C) 2012  Krawler Information Systems Pvt Ltd
 * All rights reserved.
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program 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 General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/
package com.krawler.database;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Properties;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

import com.krawler.common.service.ServiceException;
import com.krawler.common.util.KrawlerLog;
import com.krawler.common.util.Log;
import com.krawler.common.util.LogFactory;
import com.krawler.common.util.SystemUtil;
import com.krawler.common.util.ValueCounter;
import com.krawler.esp.stats.KrawlerPerf;
import com.krawler.esp.utils.ConfigReader;

public class DbPool {

    private static int sConnectionPoolSize = 200;
    private static Log sLog = LogFactory.getLog(DbPool.class);
    private static PoolingDataSource sPoolingDataSource;
    private static String sRootUrl = null;
    private static GenericObjectPool sConnectionPool;
    private static ValueCounter sConnectionStackCounter = new ValueCounter();

    public static class Connection {
        private java.sql.Connection mConnection;
        private Throwable mStackTrace;

        private Connection(java.sql.Connection conn) {
            mConnection = conn;
        }

        public java.sql.Connection getConnection() {
            return mConnection;
        }

        public void setTransactionIsolation(int level) throws ServiceException {
            try {
                mConnection.setTransactionIsolation(level);
            } catch (SQLException e) {
                throw ServiceException.FAILURE("setting database connection isolation level", e);
            }
        }

        /**
         * Disable foreign key constraint checking for this Connection. Used by
         * the mailbox restore code so that it can do a LOAD DATA INFILE without
         * hitting foreign key constraint troubles.
         * 
         * @throws ServiceException
         */
        public void disableForeignKeyConstraints() throws ServiceException {
            PreparedStatement stmt = null;
            try {
                stmt = mConnection.prepareStatement("SET FOREIGN_KEY_CHECKS=0");
                stmt.execute();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("disabling foreign key constraints", e);
            } finally {
                DbPool.closeStatement(stmt);
            }
        }

        public void enableForeignKeyConstraints() throws ServiceException {
            PreparedStatement stmt = null;
            try {
                stmt = mConnection.prepareStatement("SET FOREIGN_KEY_CHECKS=1");
                stmt.execute();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("disabling foreign key constraints", e);
            } finally {
                DbPool.closeStatement(stmt);
            }
        }

        public PreparedStatement prepareStatement(String sql) throws ServiceException {
            KrawlerPerf.incrementPrepareCount();
            try {
                return mConnection.prepareStatement(sql);
            } catch (SQLException e) {
                throw ServiceException.FAILURE("preparing database statement", e);
            }
        }

        public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws ServiceException {
            KrawlerPerf.incrementPrepareCount();
            try {
                return mConnection.prepareStatement(sql, autoGeneratedKeys);
            } catch (SQLException e) {
                throw ServiceException.FAILURE("preparing database statement", e);
            }
        }

        public void close() throws ServiceException {
            try {
                if (!mConnection.isClosed())
                    mConnection.close();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("closing database connection", e);
            } finally {
                // Connection is being returned to the pool. Decrement its stack
                // trace counter. Null check is required for the stack trace in
                // case this is a maintenance/logger connection, or if dbconn
                // debug logging was turned on between the getConnection() and
                // close() calls.
                if (mStackTrace != null && KrawlerLog.dbconn.isDebugEnabled()) {
                    String stackTrace = SystemUtil.getStackTrace(mStackTrace);
                    synchronized (sConnectionStackCounter) {
                        sConnectionStackCounter.decrement(stackTrace);
                    }
                }
            }
        }

        public void rollback() throws ServiceException {
            try {
                mConnection.rollback();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("rolling back database transaction", e);
            }
        }

        public void commit() throws ServiceException {
            try {
                mConnection.commit();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("committing database transaction", e);
            }
        }

        /**
         * Sets the stack trace used for detecting connection leaks.
         */
        private void setStackTrace(Throwable t) {
            mStackTrace = t;
        }
    }

    /**
     * Initializes the connection pool.
     */
    static {
        String drivers = System.getProperty("jdbc.drivers");
        if (drivers == null)
            System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");

        String myAddress = ConfigReader.getinstance().get("serverip", "192.168.0.132");
        String myPort = ConfigReader.getinstance().get("mysql_port", "3306");
        String dbname = ConfigReader.getinstance().get("dbname", "krawlerwebapp");
        sRootUrl = "jdbc:mysql://" + myAddress + ":" + myPort + "/";
        String url = sRootUrl + dbname;

        Properties props = getKrawlerDbProps();
        // TODO: need to tune these
        String maxActive = (String) props.get("maxActive");
        if (maxActive != null) {
            try {
                sConnectionPoolSize = Integer.parseInt(maxActive);
            } catch (NumberFormatException nfe) {
                sLog.warn("exception parsing maxActive", nfe);
            }
        }
        KrawlerLog.misc.debug("Setting mysql connection pool size to " + sConnectionPoolSize);

        sConnectionPool = new GenericObjectPool(null, sConnectionPoolSize, GenericObjectPool.WHEN_EXHAUSTED_BLOCK,
                -1, sConnectionPoolSize);
        ConnectionFactory cfac = new DriverManagerConnectionFactory(url, props);

        boolean defAutoCommit = false;
        boolean defReadOnly = false;

        new PoolableConnectionFactory(cfac, sConnectionPool, null, null, defReadOnly, defAutoCommit);

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Class.forName("org.apache.commons.dbcp.PoolingDriver");
            sPoolingDataSource = new PoolingDataSource(sConnectionPool);
        } catch (ClassNotFoundException e) {
            sLog.fatal("can't init Pool", e);
            System.exit(1);
        }

        KrawlerPerf.addStatsCallback(new DbStats());
    };

    private static Properties getKrawlerDbProps() {
        Properties props = new Properties();

        props.put("cacheResultSetMetadata", "true");
        props.put("cachePrepStmts", "true");
        props.put("prepStmtCacheSize", "25");
        props.put("autoReconnect", "true");
        props.put("useUnicode", "true");
        props.put("characterEncoding", "UTF-8");
        props.put("dumpQueriesOnException", "true");

        String user = ConfigReader.getinstance().get("mysql_user", "root");
        String pwd = ConfigReader.getinstance().get("mysql_passwd", "root");
        props.put("user", user);
        props.put("password", pwd);

        return props;
    }

    /**
     * return a connection to use for the Krawler database.
     * 
     * @param
     * @return
     * @throws ServiceException
     */
    public static Connection getConnection() throws ServiceException {
        java.sql.Connection conn = null;

        long start = KrawlerPerf.STOPWATCH_DB_CONN.start();

        try {
            conn = sPoolingDataSource.getConnection();

            if (conn.getAutoCommit() != false)
                conn.setAutoCommit(false);

            // We want READ COMMITTED transaction isolation level for duplicate
            // handling code in BucketBlobStore.newBlobInfo().
            conn.setTransactionIsolation(java.sql.Connection.TRANSACTION_READ_COMMITTED);
        } catch (SQLException e) {
            throw ServiceException.FAILURE("getting database connection", e);
        }

        // If the connection pool is overutilized, warn about potential leaks
        int numActive = sConnectionPool.getNumActive();
        int maxActive = sConnectionPool.getMaxActive();

        if (numActive > maxActive * 0.75) {
            String stackTraceMsg = "Turn on debug logging for KrawlerLog.dbconn to see stack "
                    + "traces of connections not returned to the pool.";
            if (KrawlerLog.dbconn.isDebugEnabled()) {
                StringBuffer buf = new StringBuffer();
                synchronized (sConnectionStackCounter) {
                    Iterator i = sConnectionStackCounter.iterator();
                    while (i.hasNext()) {
                        String stackTrace = (String) i.next();
                        int count = sConnectionStackCounter.getCount(stackTrace);
                        if (count == 0) {
                            i.remove();
                        } else {
                            buf.append(count + " connections allocated at " + stackTrace + "\n");
                        }
                    }
                }
                stackTraceMsg = buf.toString();
            }
            KrawlerLog.dbconn.warn("Connection pool is 75% utilized.  " + numActive
                    + " connections out of a maximum of " + maxActive + " in use.  " + stackTraceMsg);
        }

        if (KrawlerLog.sqltrace.isDebugEnabled() || KrawlerLog.perf.isDebugEnabled()) {
            // conn = new DebugConnection(conn); //TODO: uncomment later[BS]
        }
        Connection krawlerCon = new Connection(conn);

        // If we're debugging, update the counter with the current stack trace
        if (KrawlerLog.dbconn.isDebugEnabled()) {
            Throwable t = new Throwable();
            krawlerCon.setStackTrace(t);

            String stackTrace = SystemUtil.getStackTrace(t);
            synchronized (sConnectionStackCounter) {
                sConnectionStackCounter.increment(stackTrace);
            }
        }

        KrawlerPerf.STOPWATCH_DB_CONN.stop(start);
        return krawlerCon;
    }

    /**
     * Returns a new database connection for maintenance operations, such as
     * restore. Does not specify the name of the default database. This
     * connection is created outside the context of the database connection
     * pool.
     */
    public static Connection getMaintenanceConnection() throws ServiceException {
        try {
            String user = ConfigReader.getinstance().get("mysql_user", "root");
            String pwd = ConfigReader.getinstance().get("mysql_passwd", "root");
            java.sql.Connection conn = DriverManager.getConnection(sRootUrl + "?user=" + user + "&password=" + pwd);
            conn.setAutoCommit(false);
            return new Connection(conn);
        } catch (SQLException e) {
            throw ServiceException.FAILURE("getting database maintenance connection", e);
        }
    }

    /**
     * closes the specified connection (if not null), and catches any exceptions
     * on close, and logs them.
     * 
     * @param conn
     */
    public static void quietClose(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (ServiceException e) {
                if (sLog.isWarnEnabled())
                    sLog.warn("quietClose caught exception", e);
            }
        }
    }

    /**
     * Does a rollback the specified connection (if not null), and catches any
     * exceptions and logs them.
     * 
     * @param conn
     */
    public static void quietRollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (ServiceException e) {
                if (sLog.isWarnEnabled())
                    sLog.warn("quietRollback caught exception", e);
            }
        }
    }

    /**
     * Closes a statement and wraps any resulting exception in a
     * ServiceException.
     * 
     * @param stmt
     * @throws ServiceException
     */
    public static void closeStatement(Statement stmt) throws ServiceException {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("closing statement", e);
            }
        }
    }

    /**
     * Closes a ResultSet and wraps any resulting exception in a
     * ServiceException.
     * 
     * @param rs
     * @throws ServiceException
     */
    public static void closeResults(ResultSet rs) throws ServiceException {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                throw ServiceException.FAILURE("closing statement", e);
            }
        }
    }

    /**
     * Returns the number of connections currently in use.
     */
    static int getSize() {
        return sConnectionPool.getNumActive();
    }
}