Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Proxies for SQL Connections in Java

In this article we will talk about the Proxies for SQL Connections in Java and we will also see how to Implement the Proxy Connection.

Every web or application server provides infrastructure to store objects in their JNDI (Java Naming and Directory Interface) registry. This infrastructure is called JNDI container. The web application code can retrieve these objects from the JNDI container and use them as per the need. The very common use case for this scenario is the use of data sources. Almost every application that needs to query relational databases uses data sources to connect with the underlying database.

Data source in java is nothing but an implementation of an interface javax.sql.DataSource defined in JDBC (Java Database Connectivity). This interface defines methods, those return objects of type java.sql.Connection.

Connections

The connection object returned by DataSource represents physical connection to the underlying database. We can call this connection as a raw connection because the code using it can set important properties on this connection which can directly affect the connection’s relationship with the database. Few of these properties are listed below :

  • autoCommit: This Boolean property of the connection if set to true, the connection will execute each query as a separate transaction. Once the query is executed, we cannot rollback it. This might affect the business logic that wants to execute multiple queries in a single transaction.
  • networkTimeout: This property tells connection object, the time; it should wait for the response from the database for any query executed. If set to a small value, the connection will timeout if the query is taking long. This will affect the business logic where we are querying tables containing large data.
  • Schema: This will change the schema of the connection. If this property is changed, the connection object will start querying another schema which might hamper the business logic.

Apart from setting all the above(and remaining) properties of the connection, the most important thing the code can do is to close the connection. Here the physical connection will be closed and the connection object will become useless and will start throwing exceptions if used to execute queries.

Using the connection object in a correct manner

We can see many side effects if the raw connection is used or managed in a wrong manner. The biggest side effect is the increase in the number of physical connectionsto the database which might slow down the database or force the database to stop providing physical connections to the clients.

To avoid such situation, applications use connection pools. As the name suggests, connection pool is a pool of connections, capacity of which can be decided at the time of configuration. Once loaded and used, the connection pool will take care of the number of connections and will keep the number in limit. Figure 1 below shows how the connection pool works:

Connection Pool Activity Flow

Figure 1: Connection Pool Activity Flow

The application will use connection pool which in turn will use data source to create physical connection. The pool is responsible to maintain the number of connections and each code block where we need database connection, has to deal with the connection pool. The code will get the connection from the connection pool and will return it back to the pool when it is done with the connection but the code is not allowed to close the connection. This is a very good approach of managing connections.

Get rid of connection pool

Is it possible to get rid of the connection pool? If yes, then who will maintain connections for us? We can get the connection object from the data source. But, what if we close the connection and then request for a new one from the data source? Are we seriously loading the database server?

The answers for all the above questions lie within the data source itself. If we design our data source in such a manner that it will act as a connection pool and data source both, then we can say that we have got rid of a separate connection pool.

Such kind of implementation of a data source is calledPooledDataSource.

Designing Pooled DataSource

As we have seen, we should have a data source that will act as a data source and connection pool both. To design such data source we should think about the points below:

  • The data source should maintain a list of available connections
  • The size of the available connections list should not go above the initial configured size.
  • The application, once done with the connection, will close the connection.
  • Once the connection is closed, it should automatically get back into available connections list. Here the physical connection should not be closed.

The magical connection

The most challenging point while designing the pooled data source is the logic that would be used to close the connection. Here we need a magical connection object, if closed; it should not close the actual physical connection. If we manage to design and code such connection, we can say we are done with the pooled data source.

How can we get such connection object? The connection object is actually returned by the JDBC driver and it is very specific to the database. To resolve this, we need to encapsulate the connection object returned by the JDBC driver into some other object. This ‘other object’ should also implement interface java.sql.Connection, as we are going to return this ‘other object’ as a connection object to the application. This other object is called a Proxy connection as it pretends to be the one application is looking for.

Now, if the application wants to close the connection, it will close the proxy connection and not the actual connection returned by the JDBC driver. The close method of the proxy connection object will take care of two things.First, it will make sure that the actual connection is intact and second, it will return itself to the available connections list of the pooled data source. For the methods other than close, the proxy connection will simply delegate the calls to the actual connection. By doing this we can not only manage the close functionality but any other functionality of the actual connection.

The sections below explain how we can create a proxy connection class.

Implementing Proxy Connection

Below are the two ways of implementing proxy connections and using them in the pooled data source.

Writing a Connection class to encapsulate actual connection

Here we will write a class implementing java.sql.Connection interface and encapsulating actual connection inside it.

Listing 1: Writing a full connection class

publicclassConnectionProxyimplements Connection {
	// Physical connection object
	private Connection connection;
	// Pooled data source
	privatePooledDatasourceds;

	publicConnectionProxy(Connection connection, PooledDatasource ds) {
		this.connection = connection;
		this.ds = ds;
	}
	
	@Override
	publicvoid close() throwsSQLException {
		// Here we will not close the physical connection
		// instead we will return this proxy to the datasource
		ds.returnConnection(this);
	}
	
	@Override
	publicPreparedStatementprepareStatement(String sql) throwsSQLException {
		returnthis.connection.prepareStatement(sql);
	}
	
	// Remaining methods of the class
}
publicclassPooledDatasourceimplementsDataSource {

	@Override
	public Connection getConnection() throwsSQLException {
		/*
		 * Logic to check if the connection is available and if not 
		 * whether we can create a new connection 
		 */
		// 1. Create a physical connection
		Connection connection = createConnection();
		// 2. Wrap the physical connection into proxy.
		ConnectionProxy proxy = newConnectionProxy(connection, this);
		// Register the proxy connection into the connections list 
		registerConnection(proxy);
		return proxy;
	}
}

The limitation of this kind of implementation is we have to implement each and every method of the interface java.sql.Connection. If in future, JDBC specifications change or something new is added in the interface java.sql.Connection, we need to add those things in our proxy class. This scenario will force us to maintain our proxy class forever.

Using dynamic proxies in java

The better way is to use dynamic proxies in java to encapsulate actual connection. Here we create a dynamic proxy for the actual connection and return the proxy to the application.

Listing 2: Writing dynamic proxy in java

publicclassPooledDatasourceimplementsDataSource {

	@Override
	public Connection getConnection() throwsSQLException {
		/*
		 * Logic to check if the connection is available and if not 
		 * whether we can create a new connection 
		 */
		// 1. Create a physical connection
		final Connection connection = createConnection();
		// 2. Wrap the physical connection into proxy.
		final Connection proxyConnection = (Connection) Proxy.newProxyInstance(connection.getClass().getClassLoader(), new Class[] {Connection.class}, newInvocationHandler() {
			@Override
			public Object invoke(Object proxy, Method method, Object[] args) throwsThrowable {
				if("close".equals(method.getName())) {
					// Here we wont close the physical connection. Instead we will return the connection to the pool
					returnConnection(proxyConnection);
				} else {
					returnmethod.invoke(connection, args);
				}
				returnnull;
			}
		});
		// Register the proxy connection into the connections list 
		registerConnection(proxyConnection);
		returnproxyConnection;
	}
}

Conclusion

Using dynamic proxies has its own advantages. We can only implement those methods from the actual connection we want to change the behaviour of. Here we don’t need to implement each and every method from the interface java.sql.Connection.



Developing applications in java and java related technologies since 8 years. Worked in different domains like databases, supply chain management, business intelligence etc.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login