DB Connection Pooling

If you are using DriverManagerDataSource

then note: That this class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call.

So change the class to: com.mchange.v2.c3p0.ComboPooledDataSource

c3p0 is an easy-to-use library for making traditional JDBC drivers "enterprise-ready" by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2.

http://www.mchange.com/projects/c3p0/#quickstart

You need to include the dependency in your pom.xml as follows,

                               <dependency>

                                                <groupId>com.mchange</groupId>

                                                <artifactId>c3p0</artifactId>

                                                <version>0.9.2.1</version>

                                </dependency>

Hibernate configurations would be as follows,
<bean id="ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="${JDBC_CONNECTION_STRING}" />
<property name="user" value="${DATASOURCE_USER_NAME}" />
<property name="password" value="${DATASOURCE_USER_PASSWORD}" />
<!-- To get rid of com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from   -->
<property name="idleConnectionTestPeriod" value="${C3P0_POOL_IDLE_CONNECTION_TEST_PERIOD}"/> 
<property name="preferredTestQuery" value="select 1"/>
<!-- performance improvements and configuration so that you don't get above error from: http://javatech.org/2007/11/c3p0-connectionpool-configuration-rules-of-thumb/ -->
<property name="acquireIncrement" value="${C3P0_POOL_ACQUIRE_INCREMENT}"/>
<property name="maxIdleTime" value="${C3P0_POOL_MAX_IDLE_TIME}"/>
<property name="maxIdleTimeExcessConnections" value="${C3P0_POOL_MAX_IDLE_TIME_EXCESS_CONNECTIONS}"/>
<property name="maxPoolSize" value="${C3P0_POOL_MAX_POOL_SIZE}"/>
<property name="minPoolSize" value="${C3P0_POOL_MIN_POOL_SIZE}"/>
<property name="numHelperThreads" value="${C3P0_POOL_NUM_HELPER_THREADS}"/>
<property name="unreturnedConnectionTimeout" value="${C3P0_POOL_UNRETURNED_CONNECTION_TIMEOUT}"/>  
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="ds" />
<property name="packagesToScan" value="com.model" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</prop>
<prop key="hibernate.show_sql">${HIBERNATE_SHOW_SQL}</prop>
<prop key="hibernate.hbm2ddl.auto">false</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.auto_close_session">true</prop>
<!-- below line may be important for some apps for the releasing to work properly -->
               <prop key="hibernate.connection.release_mode">after_transaction</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>named-queries.xml</value>
<!-- <value>native-named-queries.xml</value> -->
</list>
</property>
</bean>

In TEST environment you can have the following values for the variables,

JDBC_CONNECTION_STRING	jdbc:mysql://xxx.xxxx.xxx/xxxx
DATASOURCE_USER_NAME	xxxx
DATASOURCE_USER_PASSWORD	xxx
HIBERNATE_SHOW_SQL	FALSE
C3P0_POOL_IDLE_CONNECTION_TEST_PERIOD	300
C3P0_POOL_ACQUIRE_INCREMENT	1
C3P0_POOL_MAX_IDLE_TIME	3600
C3P0_POOL_MAX_IDLE_TIME_EXCESS_CONNECTIONS	2
C3P0_POOL_MAX_POOL_SIZE	3
C3P0_POOL_MIN_POOL_SIZE	2
C3P0_POOL_NUM_HELPER_THREADS	2
C3P0_POOL_UNRETURNED_CONNECTION_TIMEOUT	3600

In PROD environment you can have the following values for the variables,

JDBC_CONNECTION_STRING	jdbc:mysql://xxx.xxxx.xxx/xxxx
DATASOURCE_USER_NAME	xxxx
DATASOURCE_USER_PASSWORD	xxx
HIBERNATE_SHOW_SQL	FALSE
C3P0_POOL_IDLE_CONNECTION_TEST_PERIOD	300
C3P0_POOL_ACQUIRE_INCREMENT	5
C3P0_POOL_MAX_IDLE_TIME	3600
C3P0_POOL_MAX_IDLE_TIME_EXCESS_CONNECTIONS	300
C3P0_POOL_MAX_POOL_SIZE	100 (if instance is large else 50)
C3P0_POOL_MIN_POOL_SIZE	20
C3P0_POOL_NUM_HELPER_THREADS	6
C3P0_POOL_UNRETURNED_CONNECTION_TIMEOUT	3600

For Joins query include the following - LEFT JOIN FETCH

SELECT d FROM X d LEFT JOIN FETCH d.abc AS abc WHERE d.cde = :Id and d.enabled = 'enabled'