Java tutorial
/* * 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(); } }