I have an Spring+Hibernate+Tomcat+MySql application in production, I'm running into a problem. I think the application is not closing it's jdbc connections, and when it reaches its limits (currently 200), the application stop responding, and I have to restart tomcat. Do I need to close this connections somewhere ? Here is my Datasource:
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/" override="true" reloadable="false" swallowOutput="false">
<!-- jdbcInterceptors, removeAbandoned, removeAbandonedTimeout van juntos, ver http://www.tomcatexpert.com/blog/2012/01/24/using-tomcat-7-jdbc-\
connection-pool-production, y https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html -->
<!-- removeAbandonedTimeout es en segundos -->
<Resource name="jdbc/catWDB" auth="Container" type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
url="@URL@"
username="@USERNAME@"
password="@PASSWORD@"
maxActive="200"
maxIdle="50"
minIdle="10"
suspectTimeout="60"
timeBetweenEvictionRunsMillis="30000"
minEvictableIdleTimeMillis="60000"
validationQuery="select 1"
validationInterval="30000"
testOnBorrow="true"
removeAbandoned="true"
removeAbandonedTimeout="60"
abandonWhenPercentageFull="10"
maxWait="10000"
jdbcInterceptors="ResetAbandonedTimer;StatementFinalizer"
/>
</Context>
And here is an image of the appdynamics monitoring the connections, from 3 days until now
Here is a excerpt of the error I get on the catalina.out log file:
type Exception report
message Request processing failed; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
description The server encountered an internal error that prevented it from fulfilling this request.
exception
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:932) org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:816) javax.servlet.http. . . .
root cause
org.hibernate.exception.JDBCConnectionException: Cannot open connection org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99) org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) org.hibernate.jdbc.ConnectionManager. . . .
root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections sun.reflect.GeneratedConstructorAccessor67.newInstance(Unknown Source) sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) java.lang.reflect.Constructor.newInstance(Constructor.java:513) com.mysql.jdbc.Util.handleNewInstance(Util.java:406) com.mysql.jdbc.Util.getInstance(Util.java:381) com.mysql.jdbc.SQLError.
UPDATE
public Category findByIdByApp(Integer id, int appListId) throws DataAccessException {
Criteria criteria = getSession().createCriteria(Category.class,"category")
.createAlias("appList", "app")
.add(Restrictions.and(Restrictions.eq("category.categoryId", id), Restrictions.eq("app.appListId", appListId)));
List result=criteria.list();
if(result.size()==1)
return (Category) result.get(0);
else
return null;
}
The Category domain object is mapped like this:
@Entity
@Table(name = "category")
public class Category implements Comparable, Serializable {
So my guess is, taking mithridas comments into account, that I´m using the hibernate session´s manually, And I would need to close them with something like this:
this.getSession().clear();
Or i could implement @PersistenceContext. Would anyone direct me to this implementations so I can evaluate which is best for us to use ?
Thank you.
UPDATE2: Added more info in answer to James Massey comment:
These are my: datasource, sessionFactory, transactionManager, and categoryDAO sessionFactory assignment:
<!-- ========================= DATA ACCESS OBJECT DEFINITIONS ======================== -->
<bean id="dataSource"
class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:comp/env/jdbc/catWDB</value>
</property>
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref local="dataSource"/>
</property>
<property name="configLocation" value="classpath:hibernate.cfg.xml" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.connection.pool_size">3</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.max_fetch_depth">1</prop>
</props>
</property>
</bean>
<!---->
<bean id="transactionManager" lazy-init="true"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref local="sessionFactory"/>
</property>
</bean>
<bean id="categoryDAOTarget" class="com.bamboo.catW3.DAO.impl.CategoryDAOImpl">
<property name="sessionFactory">
<ref local="sessionFactory"/>
</property>
</bean>
