Extending UsernamePasswordAuthenticationFilter in Spring Security

So, you are using Spring security, and you want to process some information after a user has logged in (say, load something in session) or after failed login attempts (such as lock an account after a set number of failed logins.. ), here's how it can be done.. Hope this helps.

Before I go on to describe what goes into it, thanks to the following. These links are a bit dated, but they did help me along.

Custom AuthenticationProcessingFilter for spring security to perform actions on login
An example of the security config for a custom auth filter (see last post)
Spring's documentation on adding own filters

Now, here we go. What is described below works for Spring Security 3.0.0.RELEASE.

1. We need to extend UsernamePasswordAuthenticationFilter

It used to be AuthenticationProcessingFilter, which is depricated.

In this example, we just write to the console. You would do something more important.

public class CustomUsernamePasswordAuthenticationFilter extends
     UsernamePasswordAuthenticationFilter {

 @Override
 protected void successfulAuthentication(HttpServletRequest request,
         HttpServletResponse response, Authentication authResult)
         throws IOException, ServletException {
     super.successfulAuthentication(request, response, authResult);
 
     System.out.println("==successful login==");
 }

 @Override
 protected void unsuccessfulAuthentication(HttpServletRequest request,
         HttpServletResponse response, AuthenticationException failed)
         throws IOException, ServletException {
     super.unsuccessfulAuthentication(request, response, failed);
 
     System.out.println("==failed login==");
 }

}

2. In spring security context, we will be overriding the form login filter FORM_LOGIN_FILTER.

This used to be AUTHENTICATION_PROCESSING_FILTER, which is not used now.

<http ...
 <custom-filter position="FORM_LOGIN_FILTER" ref="customUsernamePasswordAuthenticationFilter">

Note: Since we are replacing the default FORM_LOGIN_FILTER, we should not use <form-login login-page... , as that will try to create the default filter and you would get an exception (that there are two filters defined at the same position).

3. Since we are using the custom FORM_LOGIN_FILTER, we need to set the following property on <http ..

<http auto-config="false"

4. Another thing to set at <http is entry-point-ref

Again, since we are altering the default behavior, we need to tell what is the entry point for the form login.

<http entry-point-ref="loginUrlAuthenticationEntryPoint"

And, correspondingly, define the loginUrlAuthenticationEntryPoint bean. Note that LoginUrlAuthenticationEntryPoint used to be AuthenticationProcessingFilterEntry which is depricated.

<beans:bean id="loginUrlAuthenticationEntryPoint"
class="org.springframework.security.web.authentication.LoginUrlAuthenticationEntryPoint">
 <beans:property name="loginFormUrl" value="/login.html">
</beans:bean>

5. Remember the filter 'customUsernamePasswordAuthenticationFilter ' we used in in step 2 and extended in step 1, we have to define it.

<beans:bean id="customUsernamePasswordAuthenticationFilter"
     class="com.yourapp.web.security.CustomUsernamePasswordAuthenticationFilter" >
 <beans:property name="authenticationManager" ref="authenticationManager">
 <beans:property name="authenticationFailureHandler" ref="failureHandler">
 <beans:property name="authenticationSuccessHandler" ref="successHandler">
</beans:bean>

<beans:bean id="successHandler" class="org.springframework.security.web.authentication.SavedRequestAwareAuthenticationSuccessHandler">
 <beans:property name="defaultTargetUrl" value="/login.html">
</beans:bean>
<beans:bean id="failureHandler" class="org.springframework.security.web.authentication.SimpleUrlAuthenticationFailureHandler">
 <beans:property name="defaultFailureUrl" value="/login.html?login_error=true">
</beans:bean>

6. In the definition of 'customUsernamePasswordAuthenticationFilter' we are identifying the 'authenticationManager', so when you define your authentication manager provide an 'alias' for it:

<authentication-manager alias="authenticationManager">
 ...
</authentication-manager>

There you have it. Share and enjoy.

Flash Scope alternative in Spring MVC

Flash scope in Spring MVC has been a topic of discussion, and even though Spring provides infrastructure to create custom scopes, I went with another solution. Hey.. as long as it works.. (for your requirements and within your constraints)...

The goal:
Set a flash error or flash message before redirecting to another view. The next view consumes the flash, displays it and clears it (so that on further redirects or refreshes the message does not appear).

Here we go:

1. Create a SessionHandler (or any other dependency you can inject into your Controller).

public class SessionHandler {

private transient final Log log = LogFactory.getLog(SessionHandler.class);

public static final String FLASH_MESSAGE = "FLASH_MESSAGE";
public static final String FLASH_ERROR = "FLASH_ERROR";
public static final String FLASH_TYPE = "flashType";

public void flashError(HttpServletRequest request, String message){
 flash(request, FLASH_ERROR, message);
}

public void flashMessage(HttpServletRequest request, String message){
 flash(request, FLASH_MESSAGE, message);
}

public String consumeFlash(HttpServletRequest request, String flashType){
 String flash = null;
 try {
     flash = (String) request.getSession().getAttribute(flashType);
     request.getSession().removeAttribute(flashType);
 } catch (Exception e){
     log.warn("Error retrieving flash value of type:" + flashType);
     log.error(e.getMessage());
 }
 return flash;
}


/**
* helper method that sets the  flash message of the type provided in session scope
*/
private void flash(HttpServletRequest request, String flashType, String message){
 request.getSession().setAttribute(flashType, message);
}

This handler accepts the flash error/message and sets in session. When requested for it (using consumeFlash), it gets it from the session and clears it. It returns null if it does not find it in session.Next,

2. Use it in your controller.

if(<error condition>){
sessionHandler.flashError(request, "user.error.user.id.invalid");
return new ModelAndView(cancelView);
}

or on a successful operation,

userManager.save(user);
sessionHandler.flashMessage(request, "user.message.saved.successfully");
return new ModelAndView(getSuccessView());

As of this point we can flash an error or a message. Next step is to show it. Here is where my solution gets a bit different. I created another controller (FlashController) that creates a view of type MappingJacksonJsonView and returns that along with flash messages as redereredAttributes.

3. Create FlashController (which is ResourceLoaderAware) and map it to a url (say /flash.html)

public ModelAndView handleRequest(HttpServletRequest request,
     HttpServletResponse response) throws Exception {

 MappingJacksonJsonView view = new MappingJacksonJsonView();
 Map model = new HashMap();
 Resource resource = null;
 PropertyResourceBundle propertyResourceBundle = null;

 try{
     resource = resourceLoader.getResource(resourceLocation);
     propertyResourceBundle = new PropertyResourceBundle(resource.getInputStream());
 }catch (Exception e){
     log.warn("Error loading resource bundle");
     log.error(e.getMessage());
 }

 String flashError = sessionHandler.consumeFlash(request, SessionHandler.FLASH_ERROR);
 if(!(flashError==null)){
     String resolvedFlashError = flashError;
     if (!(propertyResourceBundle==null)){
         try{
             resolvedFlashError = propertyResourceBundle.getString(flashError);
         }catch(Exception e){}
     }
     model.put(SessionHandler.FLASH_ERROR, resolvedFlashError);
 }


 String flashMessage = sessionHandler.consumeFlash(request, SessionHandler.FLASH_MESSAGE);
 if(!(flashMessage==null)){
     String resolvedFlashMessage = flashMessage;
     if (!(propertyResourceBundle==null)){
         try{
             resolvedFlashMessage = propertyResourceBundle.getString(flashMessage);
         }catch(Exception e){}
     }
     model.put(SessionHandler.FLASH_MESSAGE, resolvedFlashMessage);
 }

 Set renderedAttributes = new HashSet();
 renderedAttributes.add(SessionHandler.FLASH_ERROR);
 renderedAttributes.add(SessionHandler.FLASH_MESSAGE);

 return(new ModelAndView(view, model));

}

Couple of things to note here. Since I've added support for key-value from a resource properties file, if the flash message sent is found in the resource file (classpath:ApplicationResources.properties by default), it uses that, or else it just sends the message sent.

Now the question is, we have a way and place to set the flash error/message and now a way to retrieve the message. How do we show it on a view. That comes next.

4. Invoke FlashController from your view using ajax call.

<script>
$.getJSON("${ctx}/flash.html",
  function(data){
    $.each(data, function(i,item){
        var curDiv = (i=='FLASH_ERROR')?"#flashError":"#flashMessage";
        $(curDiv).html(item).fadeIn('slow');
    });
  });
</script>
<div id="flashError" style="display:none;"></div&gt;
<div id="flashMessage" style="display:none;"></div>

That's it. using jQuery getJSON method, we get the messages, and if received show them.

I'm sure there are limitations and constraints in this method, such as using javascript, ajax may be a constraint. I'm sure workarounds can be found. And if not, then using Spring's custom flash scope is always an option. I would like to hear from you if you find any security issue in this solution. n'joy.

Additional reading:
Custom Scope for Flash Scope discussion
Spring by example Custom ThreadScope

JasperReports in Spring with runtime report and format

This post is after a while. I recently finished a phpCake project, which I want to write about a bit, but in an another post. This post is about something I recently was challenged with and came up with a pretty decent solution. (I think..) hope it helps.

My current project is a Spring based solution, built on Spring MVC, spring Security, Sitemesh, JPA with Hibernate and all other bells and whistles. It also integrates JasperReports. Spring has built in support for JasperReports. You can see it documented (that can be debated) here. It has built in View implementations for different renderings of the JasperReports. (csv, html, pdf, xls).

It works, with a little bit of digging.

But it was not sufficient for my requirement, which was:
Allow for rendering of a jasper report picked at runtime in a format chosen at runtime.

The View resolvers pretty much expect that the report url be defined at configuration time. Spring has another View implementation (JasperReportsMultiFormatView) that allows run time format choice, but the JasperReport url still needs to be defined at configuration time.

I understand where this behavior comes from, as majority of the time the reports are built and packaged with source code and the rendering is wired. But it fell short of allowing picking JasperReport at runtime. So, determined to get it to work within the framework of Spring Jasper View implementations (with its nifty support for both .jrxml and compiled .jasper) , I came up with this solution. Of course, this is one implementation, and can be optimized.. If you do, please drop in a note in comments.

1. Create a JasperReportsViewFactory

protected static final String HEADER_CONTENT_DISPOSITION = "Content-Disposition";

public AbstractJasperReportsSingleFormatView getJasperReportsView(HttpServletRequest httpServletRequest,
        DataSource dataSource, String url, String format, String fileName){
     String viewFormat = format==null?"pdf":format;
 
     // set possible content headers
     Properties availableHeaders = new Properties();
     availableHeaders.put("html", "inline; filename="+fileName+".html");
     availableHeaders.put("csv", "inline; filename="+fileName+".csv");
     availableHeaders.put("pdf", "inline; filename="+fileName+".pdf");
     availableHeaders.put("xls", "inline; filename="+fileName+".xls");
 
     // get jasperView class based on the format supplied
     // defaults to pdf
     AbstractJasperReportsSingleFormatView jasperView = null;
     if(viewFormat.equals("csv")) {
         jasperView = new JasperReportsCsvView();
     }else if(viewFormat.equals("html")){
         jasperView = new JasperReportsHtmlView();
     }else if(viewFormat.equals("xls")){
         jasperView = new JasperReportsXlsView();
     }else{
         jasperView = new JasperReportsPdfView();
     }
 
     // get appContext. required by the view
     WebApplicationContext ctx = WebApplicationContextUtils.getRequiredWebApplicationContext(
              httpServletRequest.getSession().getServletContext());
 
     // set the appropriate content disposition header.
     Properties headers = new Properties();
     headers.put(HEADER_CONTENT_DISPOSITION, availableHeaders.get(viewFormat));
 
     // set the relevant jasperView properties
     jasperView.setJdbcDataSource(dataSource);
     jasperView.setUrl(url);
     jasperView.setApplicationContext(ctx);
     jasperView.setHeaders(headers);
 
     // return view
     return jasperView;
 }

As you can see, the return type is a super class of all the Spring's Jasper View implementations. Another feature in this method is to set content disposition and supply an alternate file name for the downloaded file.

2. Use It
Once you have this , make it a dependency in your controller responsible for launching the report, and provide the url, format and other required elements for the view resolver, and execute your report.

public ModelAndView handleRequest(HttpServletRequest httpServletRequest,
         HttpServletResponse httpServletResponse) throws Exception {
 
     Map model = new HashMap();
 
     String format = httpServletRequest.getParameter("format");
       //Default format to pdf
       if (StringUtils.hasText(format)){
         if (!(format.equalsIgnoreCase("pdf") || format.equalsIgnoreCase("html")
             || format.equalsIgnoreCase("csv") || format.equalsIgnoreCase("xls"))){
             format = "pdf";
         }
       }else{
         format = "pdf";
       }
 
     // get the View that will render the report.
     // in actual controller, based on report id rquestd, get the report name and build URL and use
     AbstractJasperReportsSingleFormatView jasperView = jasperReportsViewFactory.getJasperReportsView(
             httpServletRequest, dataSource, "/WEB-INF/jasper/sample.jrxml",format,"DownloadFileName");
 
     // add parameters used by the report
     model.put("Company","Sample Company");
     // more here...    

     return new ModelAndView(jasperView, model);
 }

Limitations:
I'm sure there are some.. One that I know of is Jasper's sub-reports. For me it is not an issue right now as we are not supporting Jasper sub-Reports in our application.

There you have it.. short and sweet. Here are some other links if this is not what you're looking for.

Matt Raible's blog on JasperReports with Appfuse and Spring (dated)
AppFuseJasperReports
A springOne presentation of Spring and JasperReports
Filename discussion using JasperReportsMultiFormatView using configuration

Baking with cakePHP

Its been a while..

So lately I've been baking with cakePHP. Since I'm new to php and new to this framework, my thoughts might overlap between the two. A comment for the cake might be due to php and vice-versa. Anyway, here it goes. Instead of pro's and con's, I'll write as likes and don't-likes.. in no particular order. As I don't think I can judge php and related technology being a newbie..

Like:
First thoughts of using phpcake reminded me of Grails. very simple, less code and code generator tools.

Don't Like:
Too much reliance on convention over configuration. I'm still a bit 'old-school' if you will on this. My gripe with this is that if you live a framework (meaning that is all that you do), then convention is all ok. But if you switch between projects using different frameworks, configuration is a lifesaver. You know exactly what goes where and why. Personally I fall in the second camp and don't like to rely on too many 'automagic' methods.

Like:
convention overrides. It gives ways to override conventions. Such as /controllers/opportunities_controller.php expects and uses /models/opportunity.php model and will error out if model not present. But if you want to use /models/client.php model in /controllers/opportunities_controller.php, just declare a member:

$uses = array('Client');

or if you want to use other models, declare:

$uses = array('Opportunity','Client');

Like:
bake tool. It generates the models, views, controllers with staple code. The generated code is very workable to customize.

Don't Like:
ACL. Nothing wrong with ACL, but the heavy reliance on ACL for Authorization. I wish they support url pattern for authorization as an alternative. (maybe they do.. but I didn't find a good example). As a comparison see aceigi ( http://www.acegisecurity.org/petclinic-tutorial.html and scroll to block of code just before section 'Start petclinic's database)

Also, there is not much support for 'rapid development' with authorization using ACL. Everyone seems to create a different way to implement the actual authorization permission checking, especially from the views. So, I did the same. I even had to create my own session managed permission map, so that the system does not make tons of db queries on each page request.

And if you need to see at a later date which resource is authorized to whom, you need to be a SQL guru to run Tree/Hierarchical queries to find that out.

Like:
debugging. It helps a lot, and shows what framework is doing internally to get stuff done.

Don't Like:
Lack to true OO. a quick example. It allows you to create a Model class. When you invoke for a model from the Model's find() method, you don't get an instance of the model. You get an array with data?!

Lets say controllerA is using ModelB, you can say:

$model = $this->ModelB->find(...)

This is OO. But any call on

$model->..

fails, as $model is not an instance of a class. It is an array.

Like:
components for controllers and helpers for views concept. This helps create reusable functionality and integration into controllers and views is seamless.

Don't Like:
models with data access methods. I had this gripe with Grails as well. There is a big debate on this issue about should models be aware of loading and fetching themselves or should that functionality be separated in DAO's. I don't want to judge which is better, but I like DAO's.. This helps models to behave as command or transport objects.

Like:
dynamic methods: findBy... and findAllBy.. on models.. that's a life saver.

Like:
$conditions for making complex queries. Build your complex query in an array, and supply it to the find method. This addresses one issue I had talked about earlier. See ORM and search/filtering In effect it uses the method I had talked about called 'search...(criteria)', where a criteria object is built and sent to the search method.

Don't Like:
->, => .. fingers hurt typing. This is php issue though..

There are features that I've not tried yet. Such as behaviors, elements etc. I may write again when I have a fuller grasp of the framework.

So finally, would I use php cake in future? Yes, for a quick prototype or where php is required in a project. But for more enterprise solutions I'd fall back on a java solution.

stuff change on you...

I am sure at some point in time we all make a judgment call to go with a piece of software based on its merits. The criteria for what constitutes a merit differs from one individual to another. We come up with a set of criteria and a set of softwares to choose from. We subject the software to the criteria and make a call. All well and good.

If for some reason the software we choose is the choice of many others in our 'sphere of interest' we have a hit. And if we are an outlier, we end up in one of two scenarios.

1. Either we really have made a good choice and the market around has to 'see' the light and catch on, or
2. We really missed the mark and we have to re-analyze our choice and 'catch-up' with the rest.

Of course, the realization of 'hitting the mark' itself is relative. I'm sure there are some die hard fans of EJB2 who will still say that Hibernate and other ORM's have not got it.

So, what would you say, a person trying to make a call between EJB2 and Hibernate?

One can't say for sure. Hear this argument.

Joe: It does not matter what features EJB2 and Hibernate have, our company policy is not to use open source products. All products have to comply with industry standards.

Mark: But what is 'industry standard'. Isn't it what the 'industry' accepts as a standard.. meaning us, developers/architects. If we say Hibernate is a better choice, so it is a standard.

Joe: Possibly, but our policy writers do not see it like that. If it does not come from the Suns, IBMs, Xs or Ys, its not a standard.

Mark: but..

So, you see, Joe and Mark agree. But still have to disagree due to the criteria used for making a choice.

Then there are re-incarnations.

Remember when you looked at JSF when the world was going with Struts and its sisters. Its too dotnet. Its too component oriented. Its not MVC enough. Its this and its not that. The 'sphere of interest' goes with Struts, Webworks, SpringMVC etc. etc. And then JSF comes with a re-incarnation. It has the blessing of the big names Joe was looking for. It has the blessing of 'us, developers/architects' in open source implementations of JSF that Mark was looking for.

So, what would you say, a person who made the choice of JSF at a time when it did not make the cut.. and stuck with it. Is it because that person 'saw' the promise and potential and had the foresight of its success? Or is it just that the person gambled with it and it paid out?

Its the similar story with EJB in its new EJB3 re-incarnation.

Then there is stuff that changes on you.

How often do you make a choice of software with due diligence. Even the 'sphere of interest' is with you. Things are going fine.. You bet your projects and your raise on it. And then it hits you. The people responsible for your choice software bail out on you. The product is no longer supported.

Recently I was looking for a tag library to use jfreecharts.. Cewolf was the number one choice. Even documented and recommended by jfreechart folks.. But its not supported anymore.

Would you use it in your project? (on a side note, I'm writing my own now.)

Another case in point is Appfuse. I have used it multiple times.. with JSF and SpringMVC. Every time I have to use Appfuse, I've to make a call, which MVC framework to use. And to be frank, I had gotten to like SpringMVC. But it seems appfuse may drop support for springMVC. (http://raibledesigns.com/rd/entry/appfuse_light_converted_to_maven). I hope I read it wrong.

So, what would you say, a person who made a choice of SpringMVC with Appfuse, and then the folks at Appfuse possibly dropping SpringMVC support?

One could say, 'Its a price for getting stuff free.'.
Or one could say, 'Get a life.. what else do you expect. It is the hazard of the landscape.'
Or as Arnold would say, 'Stop whining.'

I say, Is it even something one should feel averse to? Is it something that is not good for you? Isn't change supposed to be good?

Yes, sometimes you change your choice, sometimes the 'sphere of interest' changes its choices and sometimes someone else changes your choice. Its an opportunity.

Its an opportunity to learn something new. Its an opportunity to make yourself adapt.. and be agile.

Yes, a root canal at a dentist's office hurts.. but its good for you.

Hibernate and connection pools

I recently spent some time researching and fixing a bug in one of my applications. This is just a summary of its solution, for all those it may help.

Problem:
Mysql has a default time-out on idle connections. The default is 8 hours. If an application uses a connection that has been sitting idle in a connection pool beyond the database time-out period, the application throws an exception.

Solution:
Well, firstly, I thank all those who wrote on blogs/mailing-lists that I had to read to come to the solution, some of which I will quote from here.

To reporduce the problem, I first had to re-create it for testing. I updated mysql's my.ini (windows file, use the corresponding file in Unix/Linux environments) file. I added a wait_timeout value of 120 seconds.

[mysqld]
wait_timeout=120

This immediately produced the error after 2 minutes of reusing an idle connection.

org.hibernate.exception.GenericJDBCException: Cannot release connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
    ..
    ..
Caused by: java.sql.SQLException: Already closed.
    at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181)
    at org.hibernate.connection.DatasourceConnectionProvider.closeConnection(DatasourceConnectionProvider.java:74)
    at org.hibernate.jdbc.ConnectionManager.closeConnection(ConnectionManager.java:451)
    ... 44 more

I was using commons-dbcp connection pool as a spring managed bean. Since I used appfuse's spring mvc archtype as a quickstart, it came configured with the commons-dbcp. (http://appfuse.org/display/APF/AppFuse+QuickStart).

Gavin King from Hibernate suggests not to use the commons-dbcp as it is faulty. (http://opensource.atlassian.com/projects/hibernate/browse/HB-959) So, taking his advice, my first take was to change the datasource to use a different one.

So, the next question was, if not dbcp, then which one? After a while I stumbled across this connection pool that hibernate supports. I must say, I had not heard of it before, and with an acronym (c3p0) it is not forgetteable. (http://www.hibernate.org/214.html)

Accordingly, I first changed the bean definition from

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="maxActive" value="100"/>
        <property name="maxWait" value="1000"/>
        <property name="poolPreparedStatements" value="true"/>
        <property name="defaultAutoCommit" value="true"/>
</bean>

to the bean definition that uses c3p0:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
</bean>

Note the difference in some of the property names between them. driverClassName/driverClass, url/jdbcUrl, username/user etc. Since these are bean properties, and not resource definition (such as for a web container), the names can be whatever the bean-writers choose them to be.

Refer to this link for more discussion about setting up c3p0 as a bean.
http://forum.springframework.org/showthread.php?t=16309

As you can see, I did not set any other property in the bean definition. The reason requires a little bit of explanation.
In another link (http://forum.springframework.org/showthread.php?t=13078), you can see how the properties can be set.

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
        <property name="driverClass" value="${jdbc.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <!--<property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>-->
    <property name="properties">
            <props>
                <prop key="c3p0.acquire_increment">5</prop>
                <prop key="c3p0.idle_test_period">100</prop>
                <prop key="c3p0.max_size">100</prop>
                <prop key="c3p0.max_statements">0</prop>
                <prop key="c3p0.min_size">10</prop>
                <prop key="user">${db.user}</prop>
                <prop key="password">${db.pass}</prop>
            </props>
        </property>
    </bean>

But, if you set the properties in this manner, c3p0 does not pickup the user and password as a regular 'property', you have to specify them as prop's in the properties 'property'. So, I yanked all from here, but kept the user and password. I moved all other properties to a c3p0.properties file.

c3p0.acquireIncrement=1
c3p0.idleConnectionTestPeriod=100
c3p0.initialPoolSize=5
c3p0.maxIdleTime=80
c3p0.maxPoolSize=10
c3p0.maxStatements=0
c3p0.minPoolSize=5

One thing that is easily 'missable' is the names of properties. They are named differently in hibernate for the corresponding properties in c3p0. For example, the property of most interest c3p0.idleConnectionTestPeriod is named in hibernate as c3p0.idle_test_period. That makes me wonder, if the property was correctly set in the second springforum link I quoted. (http://forum.springframework.org/showthread.php?t=13078)

So, if you set your c3p0 properties in a c3p0.properties files, you should use c3p0 property names, and in hibernate config files, you should use hibernate co-equivalents.

Another note of caution, even though it is mentioned in passing in the hibernate document (http://www.hibernate.org/214.html), is when you set any of the hibernate cp30 properties, there are 7 properties that hibernate overrides. So you should set all those properties in hibernate, if you do not want hibernate defaults to override cp30 settings or defaults. You will find a reminder in cp30 documentation as well. (http://www.mchange.com/projects/c3p0/index.html#hibernate-specific)

Here is my hibernate.cfg.xml snippet.

<session-factory>
        <property name="connection.pool_size">10</property>
        
        <property name="c3p0.acquire_increment">1</property> 
    <property name="c3p0.idle_test_period">100</property> <!-- seconds --> 
    <property name="c3p0.max_size">10</property> 
    <property name="c3p0.max_statements">0</property> 
    <property name="c3p0.min_size">5</property> 
    <property name="c3p0.timeout">80</property> <!-- seconds --> 
    ..
    ..

As you can see they are same as that in c3p0.properties file. In any case it does not matter, hibernate values will supercede any corresponding value set in c3p0.properties file.

So, what do we have here. We have idle_test_period as 100 seconds, and we had kept mySql's wait-timeout as 120 seconds. This means that the connection pool will discard any connection that has been idle for 100 seconds, and thus will not get used.

The logs show evidence.

DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0] C3P0PooledConnectionPool.finerLoggingTestPooledConnection(315) | Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@17fd168] on IDLE CHECK.
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] C3P0PooledConnectionPool.finerLoggingTestPooledConnection(319) | Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@f5b2da] on IDLE CHECK has
SUCCEEDED.
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1] C3P0PooledConnectionPool.destroyResource(468) | Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@70ccb
DEBUG [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1] NewPooledConnection.close(566) | com.mchange.v2.c3p0.impl.NewPooledConnection@70ccb closed by a client.
java.lang.Exception: DEBUG -- CLOSE BY CLIENT STACK TRACE
        at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:566)
        at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:234)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
        at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

So, a long story in short.

Use c3p0 as your connection pool if you are using hibernate. c3p0 can be configured as a resource bean in Spring. Set hibernate properties for c3p0 to override the c3p0 defaults and hibernate's own c3p0 defaults.

Happy programming.

By the way, I like this convention over configuration thing, but care is needed. I may write about it in another post.