The Spring Database connection does not close

advertisements

I am using SPRING BatchPreparedStatementSetter object to to do batch insertion of records in 1 table.

Once this is completed it is performing batch update in another table.

This process is repeated for multiple times depending on the number of records.

Now, Issue i am encountering is that after fix no iterations, Database connection is not getting obtained and system is just hanging as Spring is not returning any output.

After analysis its found the system is reaching the maximum permissible limit of connection which is perfectly valid but I have an understanding that Spring framework should take care of connection release process after each CRUD operation.

Is there anyway to handle this connection pooling issues. I am using Apache BasicDataSource as DataSource object.

Thanks in advance.


    Code detail is something like this -

    Service class -

public class ServiceImpl {
----
    if (list.size() == 1000) {
           daoInstance.storeRecordsinDB(list);
           daoInstance.updateRecordsInDB(list2);
    }
-----
   }
    Another class DaoInstance -

    public class DaoInstance extends JdbcTemplate {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate =jdbcTemplate;
    }

    public void storeRecords(List<dtoObj> valueList) {

           getJdbcTemplate().batchUpdate(insertSql, new BatchPreparedStatementSetter() {

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {

                     dtoObj dto = valueList.get(i);
                     ps.setLong(1, dto.getId());
                     ps.setString(2, dto.getName();

                }

                @Override
                public int getBatchSize() {
                    return valueList.size();
                }
            }) ;
    }

    public void updateRecordsInDB(final List<Long> idList)  {

            try {

                getJdbcTemplate().batchUpdate(updateSQL, new BatchPreparedStatementSetter() {

                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                                              long id = idList.get(i)
                        ps.setLong(1, id);
                    }

                    @Override
                    public int getBatchSize() {
                        if (idList != null ) {
                            return idList.size();
                        }else {
                            return 0;
                        }
                    }
                });
            }

Implemented transaction management on service class in Spring xml file as -

<tx:advice id = "txAdvice" transaction-manager="txManager">
        <tx:attributes>
            <tx:method name="*" propagation="REQUIRED" read-only="false"/>
            <!-- <tx:method name="*" read-only="true"/> -->
        </tx:attributes>
    </tx:advice>

    <aop:config>
    <aop:pointcut expression="execution(* com.package.serviceImpl.process*(..))" id="accuracyOperation"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="accuracyOperation"/>
    </aop:config>