Connection pooling with my own cofiguration file instead of Initial Context.

[Gopi Chand Maddula]

Hi Java Ranch,

I am working on a web application an i want to implement the Connection Pooling. And i wat to store my database details in a seperate properties file instead of placing them in Context.xml.

I had done some modification to Connection Pool Class for getting details from configuration file.

 
package com.dgsecure.db;
import java.sql.*;
import java.util.*;
public class ConnectionPool implements Runnable {
	private Configuration cfg;
	private String driver, url, username, password;
	private int maxConnections;
	private boolean waitIfBusy;
	private Vector availableConnections, busyConnections;
	private boolean connectionPending = false;
	public ConnectionPool() {
		super();
	}
	public void setConfiguration(Configuration cfg) throws Exception {
		this.cfg = cfg;
		initEngine();
	}
	private void initEngine() throws Exception {
		initJDBC();
	}
	private void initJDBC() throws Exception {
		try {
			Class.forName(cfg.get("jdbc.driver")).newInstance();
		} catch (Exception e) {
			throw new Exception(e);
		}
		driver = cfg.get("jdbc.driver");
		url = cfg.get("jdbc.url");
		username = cfg.get("jdbc.user");
		password = cfg.get("jdbc.password");
		int initialConnections = cfg.getInt("initial-connections");
		maxConnections = cfg.getInt("max-connections");
		waitIfBusy = cfg.getBoolean("wait-if-busy");
		if (initialConnections > maxConnections) {
			initialConnections = maxConnections;
		}
		availableConnections = new Vector(initialConnections);
		busyConnections = new Vector();
		for (int i = 0; i < initialConnections; i++) {
			try {
				availableConnections.addElement(makeNewConnection());
			} catch (SQLException e) {
				throw new Exception(e);
			}
		}
	}
	public ConnectionPool(String driver, String url, String username, String password, int initialConnections, int maxConnections, boolean waitIfBusy) throws SQLException {
		this.driver = driver;
		this.url = url;
		this.username = username;
		this.password = password;
		this.maxConnections = maxConnections;
		this.waitIfBusy = waitIfBusy;
		if (initialConnections > maxConnections) {
			initialConnections = maxConnections;
		}
		availableConnections = new Vector(initialConnections);
		busyConnections = new Vector();
		for(int i=0; i<initialConnections; i++) {
			availableConnections.addElement(makeNewConnection());
		}
	}
	public synchronized Connection getConnection() throws SQLException {
		if (!availableConnections.isEmpty()) {
			Connection existingConnection = (Connection)availableConnections.lastElement();
			int lastIndex = availableConnections.size() - 1;
			availableConnections.removeElementAt(lastIndex);
			if (existingConnection.isClosed()) {
				notifyAll(); // Freed up a spot for anybody waiting
				return(getConnection());
			} else {
				busyConnections.addElement(existingConnection);
				return(existingConnection);
			}
		} else {
			if ((totalConnections() < maxConnections) && !connectionPending) {
				makeBackgroundConnection();
			} else if (!waitIfBusy) {
				throw new SQLException("Connection limit reached");
			}
			try {
				wait();
			} catch(InterruptedException ie) {}
			// Someone freed up a connection, so try again.
			return(getConnection());
		}
	}
        private void makeBackgroundConnection() {
		connectionPending = true;
		try {
			Thread connectThread = new Thread(this);
			connectThread.start();
		} catch(OutOfMemoryError oome) {
			// Give up on new connection
		}
	}
	public void run() {
		try {
			Connection connection = makeNewConnection();
			synchronized(this) {
				availableConnections.addElement(connection);
				connectionPending = false;
				notifyAll();
			}
		} catch(Exception e) { // SQLException or OutOfMemory
			// Give up on new connection and wait for existing one
			// to free up.
		}
	}
        private Connection makeNewConnection() throws SQLException {
		try {
			// Load database driver if not already loaded
			Class.forName(driver);
			// Establish network connection to database
			Connection connection = DriverManager.getConnection(url, username, password);
			return(connection);
		} catch(ClassNotFoundException cnfe) {
			// Simplify try/catch blocks of people using this by
			// throwing only one exception type.
			throw new SQLException("Can't find class for driver: " + driver);
		}
	}
	public synchronized void free(Connection connection) {
		busyConnections.removeElement(connection);
		availableConnections.addElement(connection);
		// Wake up threads that are waiting for a connection
		notifyAll(); 
	}
	public synchronized int totalConnections() {
		return(availableConnections.size() + busyConnections.size());
	}
	public synchronized void closeAllConnections() {
		closeConnections(availableConnections);
		availableConnections = new Vector();
		closeConnections(busyConnections);
		busyConnections = new Vector();
	}
	private void closeConnections(Vector connections) {
		try {
			for(int i=0; i<connections.size(); i++) {
				Connection connection = (Connection)connections.elementAt(i);
				if (!connection.isClosed()) {
					connection.close();
				}
			}
		} catch(SQLException sqle) {
			// Ignore errors; garbage collect anyhow
		}
	}
	public synchronized String toString() {
		String info = "ConnectionPool(" + url + "," + username + ")" + ", available=" + availableConnections.size() + ", busy=" + busyConnections.size() + ", max=" + maxConnections;
		return (info);
	}
}

   

my all queries are in a normal java class. I am trying to get connection in a init() method of a servlet so that i can get connection at any time.

My servlet's Init method has the following code

  
Configuration cfg = null;
		connectionPool = new ConnectionPool();
		
		String dbcppath = config.getServletContext().getInitParameter("DBCPFilePath"); 
		try {
			cfg = new SimpleConfiguration(dbcppath+"test.cfg");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		try {
	    	connectionPool.setConfiguration(cfg);
	    } catch(SQLException sqle) {
	      System.err.println("Error making pool: " + sqle);
	      getServletContext().log("Error making pool: " + sqle);
	      connectionPool = null;
	    } catch (Exception e) {
			e.printStackTrace();
		} 
 

if i use another servlet then i can get the connection object. But through a normal java class i wat to get a connection object. but how can i get the connection object.
Please help me in this issue...

[Paul Sturrock]

You'll need to make it available to your class somehow. For normal ConnectionPools this would be done via the DataSource bound in JNDI. If you are not doing something simmilar your normal class will need to configure its own instance of your pool.

[Sandeep Sanaboyina]

We faced a similar problem as we had to use ConnectionPooling for core java programs. We solved it by writing our own implementation of connection pooling.

The concept is to create a static Connection pool object for each database at startup. During the initialization itself the connections are established. Then the getConnection method can be accessed throughout the application.

[Paul Sturrock]

We faced a similar problem as we had to use ConnectionPooling for core java programs. We solved it by writing our own implementation of connection pooling

I think that is what Gopi is trying to do.

[Sandeep Sanaboyina]

Gopi, Are you using an Oracle database???