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.

Designing Correct Solutions

Recently I was asked to describe my experience with solutioning. Fristly, I'm not sure if solutioning is a word, but, assuming that it means 'my experience with creating solutions', I said:

"Each opportunity presents a unique set of challenges and requires a unique approach for arriving at a solution.
I have come to realize that the best solution comes by consulting the team, explaining the solution to stakeholders, prototyping and building on the results, learning from past experiences and never shunning an advice.
Technology plays a role in making a right solution, but is not the driver for a solution. A customer's need should drive a solution.
A good solution is a correct solution. A solution can be enhanced by making it efficient, robust, cost effective and scalable. "

Well, I wrote this, that is why I have the original quote. And I thought, elaborate it in my post. So, instead of repeating what I said, I'll just highlight a thought and expound on it.

Each unique opportunity requires a unique approach
Meaning, one cannot take a cookie cutter approach to any challenge presented, how so much ever it is similar to what he/she has done before. Of course, one can take from one's experience, but each new task should be taken on its own merits, analysed, designed, solved and tested. Identify the challenges, find parallels in your experience and extract what is new and what can be reused, solve the problem as a whole independent problem with a whole independent solution.

Consult with the team
Ever so often, in this every changing and tele-commuting world, with vertical job descriptions, we forget that we are a part of a team. It is a job of a team to provide a solution. An architect, an analyst, a tech lead, a developer, a tester, a project manager, a designer, an information architect etc. etc. comprises a team. These people cannot, should not work in a vacuum. If an architect comes with a design, consult with your tech lead, your PM, your stakeholder before you set it in stone. If a developer comes up with re-usable components, consult with team lead, testers, folks who will maintain the codebase before you refactor. If an IA comes up with a new work flow, consult with who-so-ever it will affect before you put it in your wireframes. Increased communication brings increased clarity, increased productivity, decreases re-works and every team member appreciates the other.

Explain your solution to stakeholders
One of the reasons so many solutions fail to live up to their promise is that the end result is not what you were hired to build. There exists a flurry of agile methodologies that address this problem, be it SCRUM, XP, RUP or their variations. Among many things these methodologies try to solve is to make sure the solution is presented to stakeholders so that they can see the results sooner, and they can control the direction. Which is good. For me, it is as simple as honoring a contract. If you were contracted to build a wall, you build a wall. You do not build a picket fence and deliver at the end with no budget to correct it.

Prototype and build on results
As mentioned earlier, every problem presents a unique set of challenges and require a unique set of solutions. From them are those solutions that one has no experience with, or just a better way of achieving the results. I say, go for it. But prototype first, on a small scale. If it works, then refactor and build. If not, you haven't lost much, rather learned which path not to take in future.

Learning from past experiences
Experience is a valuable asset and one's seniority is defined by his/her experience. In my humble opinion, a lot of repetition does not qualify for equivalent time of experience, rather the exposure to different tasks or variations of a task amount to experience. For example, there is difference in the quality of experience of a person who has installed 100 bath tubs in peoples homes versus a person who has installed 10 bath tubs, but each in a different setting, such as a residential complex, a business, a public facility, a government institution etc. etc.

Having said that, experience is a valuable asset and lessons should be extracted from it, and applied in your work. If you find an urge to try someting fanciful and it cannot be justified within the context of a project, try it on a personal pet project. (of course, I would not say do not try it.. just don't try it on a project at the expense of your business sponsor)

Never shun an advice
Even if it is from a person not in your descipline. Be grateful that someone cares about you and wants to help you achieve your goals. If you disagree, disagree on its merits or demerits, not because 'who is he/she to say this.' All members of a team come from varied backgrounds and have had unique experiences in their past work or jobs, and if someone offers an advice, hear it. This does not mean, you do not excercise your judgment. No. Rather, what I am saying is, use that judgment to analyze the advice. If there is something valueable in it, acknowledge it and use it. If not, then you have heard.

Technology is not a driver of a solution
Rather, the business owner, stakeholders are the driver. One should not forget that someone owns the project and has funded it. They need something out of it which brings some business value to them. That should be the goal in designing a solution. Then, use the technology to achieve it. Technology is a means to the solution, not the goal.

Yes, sometimes you may be bound by technology choices, which are made by someone sitting in a board room somewhere and has paid enormous license fees. And sometimes, you are the boss, you can choose your latest and greatest tool available out there. So, in the first case, make the best of what is available to you. If you want change, get every concerned party on board before changing. Be prepared to explain that the technology change is supprotable, maintainable, affordable and necessary.In the second case, do not get carried away. Use the technology that is just right, affordable and maintainable for your client, even if it means not using your favorite tool that was beta-released a day before.

A good solution is a correct solution
How often do we see emphasis put on the scalablity of the solution, or configurability or how cost efficient the solution is. But we loose sight of the bigger picture, is the solution put in place the 'correct' solution? Yes we can build a scissor that can cut thru 100 pages in 2 seconds and build it very cheap, but can a surgeon finely cut muscle tissue with it in an operation, the reason for builing this scissor?

So, the first thing one should focus on is that the solution is the correct solution for the problem. Once you have a correct solution, you can work day and night to make it most efficient, cost effective, robust, scalable or any other enhancement if you like.

Finally,

Of course much more could be said, and I'm sure has been said. I welcome critique, corrections or additions.

Why Nassi-Schniederman a flowchart?

If you can use a flowchart synonymously with a Nassi Schniederman diagram (NSD), why would you want to use it? Flow-charts are widely supported in many tools and any algorithm can be possibly drawn with it, why would you need a NSD? I felt the need for it. Here is why.

While working on an SMS solution where the system responds to individual SMS text messages, each controlled by a single keyword. Each SMS has a response based on conditions such as, which phone was used to text, if the keyword was sent before from that phone, if a user had access to request a resource, etc. etc. I had to make sure that each and every possible scenario is handled by the system resulting in a successful or an error response. A logical approach would be to draw a flowchart outlining each possible path that ends in a response. While technically correct, I felt it did not deliver what I was looking for, which is a complete snapshot of all possible scenarios laid out, guaranteeing that no scenario was missing.

I recalled a good example in the book 'Groovy in Action', they used a NSD to identify how a method is invoked in a Groovy Class, depending on if the class instance is a Groovy Object, a Closure or an instance of Groovy interceptable. It identifies at the bottom of the chart which method gets invoked based on the path taken from top to bottom. I felt, if I laid out all possible scenarios in a diagram like this, it would 'shout' at me if I was missing a scenario/path a SMS message could take.

Next step was to draw one. Of course I could draw by hand, but I needed to document it as well. That resulted in a google search, and I did find couple of commercial products, and with little further search I found Structorizer (GPL), an open source product. Though rough on edges, it does the job. Very satisfied with the tool, outputs the diagram in a range of formats, including .png and .pdf.

As an comparison, I thought post an example NSD. Below you will find a NSD equivalent of a flowchart example found on wikipedia's flowchart page.

Flow Chart example (from wikipedia)


Equivalent NSD drawn using Structorizer.

So, experiment with NSD in addition to a flowchart. You never know why one diagram may add value over the other, even if technically they have the same information.

jfcTags is out

jfcTags 0.4 is released (alpha release) under LGPL license and you can download it here .

So what is jfcTags?
It is an easy way to build charts in web applications by defining the charts using jfcTags tag library. The goal was to make it easy for web developers to use jfreeChart charting library. Using it the traditional way was, lets say, a decent amount of work per chart, and if you are making an application with many charts, writing that many servlets can be daunting, not to mention all the chart display properties would be compiled in. So I decided to make it easy on myself and wrote this library and I thought to share with others in the open source community. So, how do you build charts?

Lets say, you have a JFreeChart PieDataset 'chartData' in request scope, and you want to build a pie chart. Here is how you would do it.

1. set it up
a. Include jfcTags and JFreeChart jar files and all the JFreeChart dependencies.
b. define jfcTags chart servlet in web.xml

<servlet>
    <servlet-name>chart</servlet-name>
    <servlet-class>
        com.logific.jfctags.servlet.ChartServlet
    </servlet-class>
</servlet>

<servlet-mapping>
    <servlet-name>chart</servlet-name>
    <url-pattern>
     /chart/*
    </url-pattern>
</servlet-mapping>

2. use it
a. Include jfcTags taglib declaration

<%@ taglib prefix="jfct" uri="http://www.logific.com/jfctags" %>

b. Define chart

<jfct:chart chartservletmapping="/chart"
    id="pieChartStrData"
    charttype="p"
    dataset="${chartData}"
    width="360"
    height="300"
    title="Coffee Pie Chart"
    sectionpaintlist="#FFB728|#FF5432|#2889FF"
    plotbackgroundpaint="#fdfdfd"
    backgroundpaint="#ffffff"
    generateurls="true"
    imagecssclass="noborder"
    urlprefix="/pieDetail.jsp"
    urlcategoryparamname="beverageName"
    urlindexparamname="pieIndex"
    maximumlabelwidth="20"
    valueformat="percent"
    valueformatstring="#0.00%"
    labelfont="helvetica"
    labelpaint="#679ed2"
    labelbackgroundpaint="#feeffe">
</jfct:chart>

This generates the following chart.

To make this post short, I'll just mention some other features:

• Supply data to chart using JFreeChart Datasets or String based formatted data
• URL support with Drill down capabilities
• ToolTips support
• Currently supports Pie, 3D Pie, Line, Bar and Stacked Bar charts

This release package contains:

1. jfcTags tag library jar file
2. jfcTags-sample-webapp war file
3. jfcTags user guide and tag library reference pdf.

Download it. Try it. Provide your valuable feedback here.

ORM and searching / filtering

OK, so you have decided to use an ORM. And there is a need to get all users or a user from database.

This is not a discussion about how to implement the DAO method. Is it by a query, calling a stored procedure or using the ORM's object based querying.

Having said that, to get users from database, you go ahead and write your DAO class with methods: getAll() and get(id)

How about, get all users who belong to a specific company. Easy. You go and write :getUsersByCompany(Company company).

So far so good. There is a new use case that requires all users who belong to a specific company and are employed full time and were hired in last 4 months and speak English.

Is the method: getUsersByComapnyAndByHireTypeAndByDateOfHireAndBy.... in works for your DAO class?

Or you just use the DAO method : getUsersByCompany(..)and then further filter in a service method by parsing and inspecting user attributes and excluding the ones that do not match the criteria?

This makes me think, what is a criteria for a method to qualify for a home in a DAO class? get(), getAll(), delete(id), update(object), getByThis(..), getByThisAndThat(..)These methods seem logical and most of them are necessary. But why would the above mentioned method not qualify? (or should it?)

There exists a solution in a more dynamic language based framework, like Grails based on Groovy, which kind of does away with the DAO objects and the domain class itself is responsible for its data access and storage. An example is : customer = Customer.findByName("... This is achieved by dynamic methods. But we're talking about java, which is not dynamic.

Having raised the question,I have couple of methods that I follow, but I'm not sure if they are most optimal.

I use a mix of DAO methods and additional service methods to apply further filtering. Lets take the example above: find users "who belong to a specific company and are employed full time and were hired in last 4 months and speak English".

Based on the overall scope of the project, I would decide to what detail I should create a DAO method. Accordingly I'll create: 
getUsersByCompanyAndEmploymentType(company, employmentType) Among the criteria to create this method, is, Is the resultant list of users manageable for any further processing without putting undue stress on the server? Is this method useful to fulfill some other use cases?

This gets the users for the specific company and who work full time, then I pass this to a service method that parses the users and filters users who were hired within last 4 months and who speak English.

Another alternative for such cases could be a single catch-all method called: searchUsers(criteria)where criteria could be sent either as a Hashmap, or a class with specific fields that the users can be search by. The service method fills in the criteria based on a use case and pass it on to DAO.

These may not be the most optimal solutions, but I would like to hear of alternatives.

On a side note, there exists a mini-framework that helps with Hibernate and this 'search' issue, which formalizes the Search method. Its called 'hibernate-generic-dao'. I have not used it, but concept looks nice, but again, its tied to Hibernate only.

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.