Correctly setting a new database connection within a Java Application?

Question

I have a java application where I am trying to flag a person entity (corresponding to PERSON row in my db) if the record is over one year old. I.e. setting the OBSOLETE row in the DB to be "Y".

I was getting the error:

SQL Error: 2396, SQLState: 61000
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-02396: exceeded maximum idle time, please connect again
WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 17008, SQLState: 99999
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Closed Connection

This was because my DB idle timeout is set at 30 minutes and the application was running for longer than this.

To try and fix this I am starting and closing a new connection for each Person that will be flagged, however the error is still occurring. How can I fix this?

Method:

 public void flagPersonIfDeletable() {

        List<String> allPersonIds = getAllPersonIds();


        for (String personId : allPersonIds) {
            try {
                startNewConnection();
                flagPersonById(personId)

            } finally {

                closeConnection();
            }
        }

Start new Connection method:

  public void startNewConnection() {

            this.Persistence.done();
            this.Persistence.unbind();
            this.Persistence.entityManager();
        }

Close Connection method:

public void closeConnection() {
        if (entityManager() != null &&
                entityManager().isOpen()) {
            if (entityManager().getTransaction().isActive()) {
                entityManager().getTransaction().commit();
            }
            entityManager().close();
        }
    }

My Persistence.xml:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">

    <persistence-unit name="myPersistence">
        <class>com.my.package.PersonEntity</class>
        <properties>
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.provider_class" value="org.hibernate.c3p0.internal.C3P0ConnectionProvider"/>
            <property name="hibernate.c3p0.max_size" value="1"/>
            <property name="hibernate.c3p0.min_size" value="1"/>
            <property name="hibernate.c3p0.acquire_increment" value="1"/>
            <property name="hibernate.c3p0.idle_test_period" value="300"/>
            <property name="hibernate.c3p0.max_statements" value="0"/>
            <property name="hibernate.c3p0.timeout" value="0"/>
            <property name="hibernate.c3p0.unreturnedConnectionTimeout" value="30000"/>
            <property name="hibernate.c3p0.dataSourceName" value="JPA"/>
        </properties>
    </persistence-unit>
</persistence>

Answer 1

Please refer the below link. https://developer.jboss.org/thread/27079

I hope this helps.

But I strongly suggest you to close the connection and reopen as required and not to keep it open "always".

I hope this help.