Connections remain in idle status and increase untill reached max connections limit

Question

I have a web-app using apache-camel to submit routes which execute some postgresql select and insert.

I'm not using any DAO, so I haven't a code where begin and close connections, I believed that connections life-cycle was managed by Spring but it seems not working.

The problem is that everytime my route executes, I see one more connection which remains IDLE, so previous IDLE connections are not being reused, this takes to the "too many client connections problem"

In my route I have:

<bean id="configLocation" class="org.springframework.core.io.FileSystemResource">
        <constructor-arg type="java.lang.String" value="..../src/main/resources/config/test.xml" />
    </bean> 

<bean id="dataSourcePostgres" class="org.apache.ibatis.datasource.pooled.PooledDataSource">
        <property name="driver" value="org.postgresql.Driver" />
        <property name="url" value="jdbc:postgresql://localhost:5432/postgres" />
        <property name="username" value="postgres" />
        <property name="password" value="postgres" />
    </bean>

<bean id="postgresTrivenetaSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSourcePostgres" />
        <property name="configLocation" ref="configLocation" />
    </bean>

Here they are some sample queries:

<select id="selectTest" resultType="java.util.LinkedHashMap">
        select * from test;
    </select>

    <insert id="insertTest" parameterType="java.util.LinkedHashMap" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        INSERT INTO test(note,regop_id)
    VALUES (#{note},#{idKey});
    </insert>

I tried even adding this:

<bean id="transactionManager"  
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <property name="dataSource" ref="dataSourcePostgresTriveneta" />  
    </bean> 

Answer 1

At last I found the problem, it was that the DataSource is never closed automatically at the end of a Camel route.

So, each time that Camel route executed, it left an open Datasource, then all the created IDLE connections (their number obviously depends from the DataSource configuration and its usage) remained and accumulate over and over.

The final solution was to add a bean created ad hoc at the end of the Camel route, taking the DataSource as argument and closing it, that's all.