hibernate spring doesn t close connections

Question

I try to use spring and hibernate to populate a database (MySQL). But after ~ 25-30k inserts, i got the following error :

févr. 15, 2017 12:14:27 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 08001
févr. 15, 2017 12:14:27 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:447)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy28.create(Unknown Source)
    at main.Main.recupReq(Main.java:353)
    at main.Main.manageCategories(Main.java:140)
    at main.Main.transformOldModelToNewModel(Main.java:119)
    at main.Main.transferXMLtoDB(Main.java:82)
    at main.Main.main(Main.java:59)
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:102)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:129)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:247)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:254)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:203)
    at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:56)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:189)
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
    ... 12 more
Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:489)
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:1677)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1601)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:196)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:159)
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
    at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:99)
    ... 19 more
Caused by: com.mysql.cj.core.exceptions.CJCommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.

This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable. 

For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.

For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
    at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
    at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
    at com.mysql.cj.core.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:149)
    at com.mysql.cj.mysqla.io.MysqlaSocketConnection.connect(MysqlaSocketConnection.java:83)
    at com.mysql.cj.mysqla.MysqlaSession.connect(MysqlaSession.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:1619)
    ... 32 more
Caused by: java.net.BindException: Address already in use: connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at com.mysql.cj.core.io.StandardSocketFactory.connect(StandardSocketFactory.java:202)
    at com.mysql.cj.mysqla.io.MysqlaSocketConnection.connect(MysqlaSocketConnection.java:57)
    ... 34 more
févr. 15, 2017 12:14:27 PM org.springframework.context.support.ClassPathXmlApplicationContext doClose
INFOS: Closing org.springframework.context.support.ClassPathXmlApplicationContext@41906a77: startup date [Wed Feb 15 12:12:59 CET 2017]; root of context hierarchy
févr. 15, 2017 12:14:27 PM org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean destroy
INFOS: Closing JPA EntityManagerFactory for persistence unit 'default'

If i understand correctly, connections to database are not closed correctly and the number of sockets used reach a limit. Problem is connections are normally managed by spring and should not stay open.

I dont want to increase this limit, just close and open a connection each time I insert or update something.

I think the key is to change the application context file, but couldn t find how.

Here are the applicationContext :

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:util="http://www.springframework.org/schema/util"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

    <!-- Activation annotations et scan des classes -->
    <context:annotation-config />
    <context:component-scan base-package="jpa" />



    <!-- Specify the Hibernate properties setting file -->
    <context:property-placeholder
        location="classpath:hibernate.properties" />

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <!-- Creation EntityManagerFactory à partir de la dataSource -->
    <bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="packagesToScan" value="jpa" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect" />
                <property name="showSql" value="${hibernate.show_sql}" />
                <property name="generateDdl" value="true" />
            </bean>
        </property>
<!--        <property name="jpaProperties"> -->
<!--    <props> -->
<!--        <prop key="hibernate.hbm2ddl.auto">${hibernate.action}"</prop> -->
<!--    </props> -->
<!-- </property> -->
    </bean>


    <!-- getionnaire transaction entityManagerFactory -->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

    <!-- activation de l'annotation @Transcationnal -->
    <tx:annotation-driven transaction-manager="transactionManager" />

    <!-- activation translation d'execption -->
    <bean
        class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"></bean>

</beans

and the property file :

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/calib?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&autoReconnect=true&useSSL=false
jdbc.username=root
jdbc.password=root
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.show_sql=false
hibernate.action=create-drop

Edit : I tried to open and close the ClassPathXmlApplicationContext to renew the connection but the same error happens : java.net.BindException: Address already in use: connect

Edit 2 : Dao :

package jpa.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import jpa.model.NewModelAttr;

@Repository
@Transactional
public class DaoAttrJpa implements DaoAttr{

    @PersistenceContext
    private EntityManager em;

    @Override
    public void create(NewModelAttr obj) {
        em.persist(obj);

    }

    @Override
    public void delete(NewModelAttr obj) {
        em.remove(em.merge(obj));

    }

    @Override
    public NewModelAttr update(NewModelAttr obj) {
        return em.merge(obj);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<NewModelAttr> findAll() {
        List<NewModelAttr> liste = null;   
        Query query = em.createNamedQuery("NewModelAttr.findAll", NewModelAttr.class);
        liste = query.getResultList();
        return liste;
    }

    @Override
    public NewModelAttr findByPrimaryKey(Integer key) {
        return em.find(NewModelAttr.class, key);
    }

}

Singleton spring :

package jpa.util;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Repository;

import jpa.dao.*;

@Repository
public class AccessDao {
    private static ClassPathXmlApplicationContext context;
    private static AccessDao instance = null;
    @Autowired
    private DaoProject daoProject;
    @Autowired
    private DaoLink daoLink;
    @Autowired
    private DaoPath daoPath;
    @Autowired
    private DaoRequirement daoRequirement;
    @Autowired
    private DaoScenario daoScenario;
    @Autowired
    private DaoStep daoStep;
    @Autowired
    private DaoAttr daoAttr;
    @Autowired
    private DaoCategory daoCategory;

//  private AccessDao() {
//      context = new ClassPathXmlApplicationContext("applicationContext.xml");
//      daoProject = (DaoProject) context.getBean("daoProjectJpa");
//      daoLink = (DaoLink) context.getBean("daoLinkJpa");
//      daoPath = (DaoPath) context.getBean("daoPathJpa");
//      daoRequirement = (DaoRequirement) context.getBean("daoRequirementJpa");
//      daoScenario = (DaoScenario) context.getBean("daoScenarioJpa");
//      daoStep = (DaoStep) context.getBean("daoStepJpa");
//      daoAttr = (DaoAttr) context.getBean("daoAttrJpa");
//      daoCategory = (DaoCategory) context.getBean("daoCategoryJpa");
//  }

    public static AccessDao getInstance() {
        if (AccessDao.instance == null) {
            context = new ClassPathXmlApplicationContext("applicationContext.xml");
            AccessDao.instance = (AccessDao) context.getBean("accessDao");

        }

        return AccessDao.instance;
    }

    public DaoProject getDaoProject() {
        return daoProject;
    }

    public void setDaoProject(DaoProject daoProject) {
        this.daoProject = daoProject;
    }

    public DaoLink getDaoLink() {
        return daoLink;
    }

    public void setDaoLink(DaoLink daoLink) {
        this.daoLink = daoLink;
    }

    public DaoPath getDaoPath() {
        return daoPath;
    }

    public void setDaoPath(DaoPath daoPath) {
        this.daoPath = daoPath;
    }

    public DaoRequirement getDaoRequirement() {
        return daoRequirement;
    }

    public void setDaoRequirement(DaoRequirement daoRequirement) {
        this.daoRequirement = daoRequirement;
    }

    public DaoScenario getDaoScenario() {
        return daoScenario;
    }

    public void setDaoScenario(DaoScenario daoScenario) {
        this.daoScenario = daoScenario;
    }

    public DaoStep getDaoStep() {
        return daoStep;
    }

    public void setDaoStep(DaoStep daoStep) {
        this.daoStep = daoStep;
    }

    public DaoAttr getDaoAttr() {
        return daoAttr;
    }

    public void setDaoAttr(DaoAttr daoAttr) {
        this.daoAttr = daoAttr;
    }

    public DaoCategory getDaoCategory() {
        return daoCategory;
    }

    public void setDaoCategory(DaoCategory daoCategory) {
        this.daoCategory = daoCategory;
    }

    public static void close() {
        if (instance != null) {
            instance.destroy();
        }
    }

    private void destroy() {
        context.close();
        instance=null;
    }
}

Also open and close many times was just a test. I usually open at the start and close at the end.

Edit 3:

Here is a part of the code where i insert :

private List<NewModelProject> transformOldModelToNewModel(List<ProjectType> projectsFromXML) {
        List<NewModelProject> projectsNewModel = new ArrayList<>();

        for (ProjectType projectType : projectsFromXML) {
            System.out.println("proj:" + projectType.getName());
            NewModelProject project = new NewModelProject();
            project.setBaseline(projectType.getBaseline());
            project.setDescription(projectType.getDescription());
            project.setId(Integer.parseInt(projectType.getId()));
            project.setName(projectType.getName());
            projectsNewModel.add(project);
            dao.getDaoProject().create(project);
            objectToUpdate.add(project);

            manageCategories(projectType, project);

            manageScenarios(projectType, project);
        }

        gestionTraceability();

        return projectsNewModel;
    }

Answer 1

Found the solution, thanks to M. Deinum !

I needed to replace DriverManagerDataSource with a BasicDataSource in applicationContext :

DriverManagerDataSource create a new connection for each request wich is problematic when we want to send a lot of request.