/**
 * This sample code is provided to demonstrate an error in the
 * MySQL Connector/J implementation.
 * 
 * This code is in the public domain.
 * Copyright Joachim Buechse, joachim@buechse.ch
 */

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import javax.sql.*;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import com.mysql.jdbc.jdbc2.optional.*;

/**
 * A pool of mysql DB connections.
 *
 * @author joachim@buechse.ch
 */
public class ConnPool {
    
    private static final Object DUMMY_VALUE= new Object();
    
    private final MysqlConnectionPoolDataSource _dataSource;
    private final boolean _fixTimezone;

    private final LinkedBlockingQueue<PooledConnection> _freeConnections= 
        new LinkedBlockingQueue<PooledConnection>();
    private final Map<PooledConnection, Object> _busyConnections= 
        Collections.synchronizedMap(new WeakHashMap<PooledConnection, Object>());
    private final ConnectionEventListener _connListener= new MyConnectionEventListener();
    
    
    public ConnPool (String dbUrl, boolean fixTimezone)
        throws SQLException
    {
        // -- attempt to connect
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception e) {
            throw new SQLException("Error while instantiating SQL Driver");
        }

        _dataSource= new MysqlConnectionPoolDataSource();
        _dataSource.setUrl(dbUrl.toString());
        _fixTimezone= fixTimezone;
     
        try {
            _freeConnections.put(createNewPooledConnection());
        } catch (InterruptedException ie) {
            Thread.currentThread().interrupt();
        }
    }
    
    /**
     * @return a functioning DB connection
     */
    public Connection getConnection () throws SQLException {
        
        // -- get a master connection
        PooledConnection masterConnection;
        Connection conn= null;
        while (true) {
            try {
                masterConnection= _freeConnections.poll(10, TimeUnit.MILLISECONDS);
            } catch (InterruptedException ie) {
                Thread.currentThread().interrupt();
                return null;
            }
            if (masterConnection == null) {
                break;
            }
        
            // -- check that it is still valid
            masterConnection.removeConnectionEventListener(_connListener);
            try {
                conn= masterConnection.getConnection();
                Statement s= conn.createStatement();
                s.execute("/* ping */ SELECT 1");
                s.close();
                masterConnection.addConnectionEventListener(_connListener);
                break;
            } catch (SQLException e) {
                System.out.println("removing broken DB connection");
                masterConnection.close();
            }
        }
        if (conn == null) {
            masterConnection= createNewPooledConnection();
            conn= masterConnection.getConnection();
        }
        if (_fixTimezone) {
            // -- configure session time zone
            //    work around for BUG in MySQL driver ignoring sessionVariables
            //    url parameter for PooledConnections
            Statement stz= conn.createStatement();
            stz.execute("SET SESSION time_zone = GMT");
            stz.close();
        }
        
        _busyConnections.put(masterConnection, DUMMY_VALUE);
        return conn;
    }
        
    private final PooledConnection createNewPooledConnection () throws SQLException {
        System.out.println("creating new master connection");

        PooledConnection masterConnection;
        synchronized (_dataSource) {
            masterConnection= _dataSource.getPooledConnection();
        }
        
        // -- check the DB connection
        Connection c= masterConnection.getConnection();
        Statement s= c.createStatement();
        s.execute("/* ping */ SELECT 1"); // a simple ping
        c.close();
        
        masterConnection.addConnectionEventListener(_connListener);
        return masterConnection;
    }
    
    private class MyConnectionEventListener implements ConnectionEventListener {
        /**
         * Application has called close on its representation of a pooled connection.
         */
        public void connectionClosed (ConnectionEvent event) {
            PooledConnection conn= (PooledConnection) event.getSource();
            if (_busyConnections.remove(conn) == DUMMY_VALUE) {
                try {
                    _freeConnections.put(conn);
                } catch (InterruptedException ie) {
                    Thread.currentThread().interrupt();
                }
            } else {
                System.err.println("ERR: got connectionClosed for non-existant master connection");
            }
        }

        /**
         * A fatal error has occurred and the pooled connection can no longer be used.
         */
        public void connectionErrorOccurred (ConnectionEvent event) {
            PooledConnection conn= (PooledConnection) event.getSource();
            System.err.println("removing broken master connection");
            _busyConnections.remove(conn);
        }
    }
}