Keeping DB Connection Open

[cornflakeGirl]

Hi All,

My team is building a web site with an Oracle DB backend using Spring MVC.

I hope that I will be able to find some guidance here for my project with some unique requirements:

1) User is authenticated into the website using their database username and password (I attempt to make a connection and run a simple query with their credentials. If connection is fine, then the user is authenticated into the system. Otherwise, login fails with invalid username/password.

2) Once the user connects to the database, that connection must remain OPEN for the entire duration of their website visit -- that is, until they hit "logout" on the system.

3) We *must* have the connection to the database done for each user in the system -- I cannot connect to the DB using a general application ID to run any queries.

So far, here is what I've done:

My configuration file:

	<bean id="dataSourceTarget"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
		<property name="url"
			value="jdbc:oracle:thin:@xxxx:xxx/xxxx" />
	</bean>

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
		<property name="targetDataSource" ref="dataSourceTarget" />
	</bean>

Upon successful login to the system, I call this servlet filter to set my datasource properties using the actual username/password which is absent in my above configuration. This is retained through the entire session (from what I've seen so far).

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter;
import org.springframework.security.context.SecurityContextHolder;

public final class DatabaseConnectionFilter implements Filter {

	@Override
	public void init(FilterConfig arg0) throws ServletException {

	}

	@Override
	public void doFilter(ServletRequest request, ServletResponse response,
			FilterChain chain) throws IOException, ServletException {

		if (SecurityContextHolder.getContext().getAuthentication() != null) {
			String username = (String) SecurityContextHolder.getContext()
					.getAuthentication().getPrincipal();

			String password = (String) SecurityContextHolder.getContext()
					.getAuthentication().getCredentials();

			ApplicationContext ctx = ApplicationContextProvider
					.getApplicationContext();

			UserCredentialsDataSourceAdapter dataSource = (UserCredentialsDataSourceAdapter) ctx
					.getBean("dataSource");

			dataSource.setCredentialsForCurrentThread(username, password);
			dataSource.setUsername(username);
			dataSource.setPassword(password);
		}
	}

	@Override
	public void destroy() {

		ApplicationContext ctx = ApplicationContextProvider
				.getApplicationContext();

		UserCredentialsDataSourceAdapter dataSource = (UserCredentialsDataSourceAdapter) ctx
				.getBean("dataSource");

		dataSource.removeCredentialsFromCurrentThread();

	}

}

The problem with the above solution is that Spring is opening and closing the connection to the DB each time I run a query. I believe this has something to do with my targetDataSource (org.springframework.jdbc.datasource.DriverManager DataSource), but I am not sure what to use in its place. I believe this behavior might occur with any other datasource I will use (c3p0 for instance).

Can someone please give some guidance on how to force my connection to stay open once a database connection for each user has been established?

Also, if something looks wonky with my implementation above, please provide feedback!

Thank you in advance for the help!

[cornflakeGirl]

I did find this other post with a user having pretty similar requirements.

http://forum.springsource.org/showthread.php?t=46067

Unfortunately, I do not follow either proposed implementation (How would an implementation using AbstractRoutingDataSource look? Does d95sld95's code actually make sense? It looks like it would call getConnection each query as well?)

Thanks all!

[cornflakeGirl]

Think I may have solved my problem?

I updated my configuration to the below, and it seems to give me the expected behavior!

	<bean id="dataSourceAdapter" class="org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS">
		<property name="driver" value="oracle.jdbc.OracleDriver" />
		<property name="url"
			value="jdbc:oracle:thin:@xxx:xxx/xxx" />
	</bean>

	<bean id="dataSourceTarget"
		class="org.apache.commons.dbcp.datasources.PerUserPoolDataSource">
		<property name="connectionPoolDataSource" ref="dataSourceAdapter" />
	</bean>

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
		<property name="targetDataSource" ref="dataSourceTarget" />
	</bean>