[google-cloud-sql-discuss] How to solve CloudSQL 8 hours timeout problem?

Hi

I'm working in an app with Google Cloud SQL. I'm using Hibernate 4.2.0.Final and I've noticed that after 8 hours of inactivity my connection is closed. I've been searching on the Internet and I couldn't find anything effective to solve this issue. All the information I've found is summarized in these items:

• Use a connection pool where I should specify a higher timeout. I've tried with c3p0 and DBCP 2.1.1 libraries but none of them solved the issue.
  
• Open a connection before some query and then close it after that. The problem is that I would like to use the EntityManager in order to use queries with entities.

Here it's what I've done with DBCP 2.1.1 (http://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2/2.1.1). The problem is that Goggle App Engine log shows an ExceptionInInitializerError when I try to use findAll in PersistenceService. It also shows that my own DataSource (see below) cannot be cast to String. I don't understand why.

This is the PersistenceService I'm using right now

package co.peewah.ems.utils;

import com.google.appengine.api.utils.SystemProperty;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.AbstractMap.SimpleEntry;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.hibernate.cfg.AvailableSettings;


/**
 *
 * @author Muacito
 */
public class PersistenceService
{
    private static final EntityManagerFactory EMF = createEntityManagerFactory();

    private static EntityManager entityManager = EMF.createEntityManager();

    private static EntityManagerFactory createEntityManagerFactory()
    {
        //String mode = "";
        /*
        if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Development)
        {
            mode = "dev.";
        }
        */

        Map<String, Object> properties = new HashMap<>();
       
        //Properties properties = new Properties();

        //properties.put("javax.persistence.jdbc.driver", System.getProperty("persistence." + mode + "db.driver"));
        //properties.put("javax.persistence.jdbc.url", System.getProperty("persistence." + mode + "db.url"));
        //properties.put("javax.persistence.jdbc.user", System.getProperty("persistence." + mode + "db.user"));
        //properties.put("javax.persistence.jdbc.password", System.getProperty("persistence." + mode + "db.password"));
        try
        {
            properties.put(AvailableSettings.DATASOURCE, DataSource.getInstance());
        } catch (IOException | SQLException | PropertyVetoException e)
        {
             e.printStackTrace();
        }
       
       
        System.out.println("----------------------------");
        System.out.println("----------------------------");
        System.out.println(properties);
        System.out.println("----------------------------");
        System.out.println("----------------------------");

        return Persistence.createEntityManagerFactory("Demo", properties);
    }

    private static EntityManager getEntityManager()
    {
        if (!PersistenceService.entityManager.isOpen())
        {
            PersistenceService.entityManager = PersistenceService.EMF.createEntityManager();
        }

        return PersistenceService.entityManager;
    }

    public static <T> void create(T entity)
    {
        try
        {
            if (entity.getClass().getMethod("getId").invoke(entity) == null)
            {
                entity.getClass().getMethod("setId", String.class).invoke(entity,
                        UUID.randomUUID().toString().replace("-", ""));
            }

            if (entity.getClass().getMethod("getCreated").invoke(entity) == null)
            {
                entity.getClass().getMethod("setCreated", Date.class).invoke(entity,
                        GregorianCalendar.getInstance().getTime());
            }

            getEntityManager().getTransaction().begin();

            getEntityManager().persist(entity);

            getEntityManager().flush();

            getEntityManager().getTransaction().commit();

        } catch (Exception ex)
        {
            Logger.getLogger(PersistenceService.class.getName()).log(Level.SEVERE, null, ex);
            getEntityManager().getTransaction().rollback();
        }
    }

    public static <T> void edit(T entity)
    {
        try
        {
            if (entity.getClass().getMethod("getUpdated").invoke(entity) == null)
            {
                entity.getClass().getMethod("setUpdated", Date.class).invoke(entity,
                        GregorianCalendar.getInstance().getTime());
            }

            getEntityManager().getTransaction().begin();

            getEntityManager().merge(entity);

            getEntityManager().flush();

            getEntityManager().getTransaction().commit();
        } catch (Exception ex)
        {
            Logger.getLogger(PersistenceService.class.getName()).log(Level.SEVERE, null, ex);
            getEntityManager().getTransaction().rollback();
        }
    }

    public static <T> void remove(T entity)
    {
        try
        {
            getEntityManager().getTransaction().begin();

            getEntityManager().remove(entity);

            getEntityManager().flush();

            getEntityManager().getTransaction().commit();

        } catch (Exception ex)
        {
            Logger.getLogger(PersistenceService.class.getName()).log(Level.SEVERE, null, ex);
            getEntityManager().getTransaction().rollback();
        }
    }

    public static <T> List<T> filter(Class<T> entityClass, String query, SimpleEntry<String, Object>... parameters)
    {
        TypedQuery<T> typedQuery = getEntityManager().createQuery(query, entityClass);

        for (SimpleEntry<String, Object> param : parameters)
        {
            typedQuery.setParameter(param.getKey(), param.getValue());
        }

        return typedQuery.getResultList();
    }

    public static <T> T find(Class<T> entityClass, Object id)
    {
        T entity = getEntityManager().find(entityClass, id);

        return entity;
    }

    public static <T> List<T> findBy(Class<T> entityClass, String criteria, Object value)
    {
        String c = criteria.replaceFirst(criteria.charAt(0) + "", (criteria.charAt(0) + "").toLowerCase());

        TypedQuery<T> query = getEntityManager().createNamedQuery(entityClass.getSimpleName() + ".findBy" + criteria,
                entityClass);
        query.setParameter(c, value);

        return query.getResultList();
    }

    public static <T> List<T> findAll(Class<T> entityClass)
    {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        cq.select(cq.from(entityClass));

        return getEntityManager().createQuery(cq).getResultList();
    }

    public static <T> List<T> findRange(Class<T> entityClass, int[] range)
    {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        cq.select(cq.from(entityClass));
        Query q = getEntityManager().createQuery(cq);
        q.setMaxResults(range[1] - range[0] + 1);
        q.setFirstResult(range[0]);

        return q.getResultList();
    }

    public static <T> int count(Class<T> entityClass)
    {
        CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        Root<T> rt = cq.from(entityClass);
        cq.select(getEntityManager().getCriteriaBuilder().count(rt));
        Query q = getEntityManager().createQuery(cq);

        return ((Long) q.getSingleResult()).intValue();
    }
}

This is the DataSource I'm using

package co.peewah.ems.utils;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

import com.google.appengine.api.utils.SystemProperty;

/**
 *
 * @author csacanam
 *
 */
public class DataSource
{
   
    private static DataSource datasource;
    private BasicDataSource ds;
   
    private DataSource() throws IOException, SQLException, PropertyVetoException
    {
        ds = new BasicDataSource();
       
       
        String mode = "";

        if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Development)
        {
            mode = "dev.";
        }
       
        String user = System.getProperty("persistence." + mode + "db.user");
        String password = System.getProperty("persistence." + mode + "db.password");
        String address = System.getProperty("persistence." + mode + "db.url");
        String driver = System.getProperty("persistence." + mode + "db.driver");
       
        // Create and configure DBCP DataSource
        ds.setDriverClassName(driver);
        ds.setUrl(address);
        ds.setUsername(user);
        ds.setPassword(password);
       
        ds.setMinIdle(5);
        ds.setMaxIdle(20);
 
        ds.setMaxOpenPreparedStatements(180);
    }
   
    public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException
    {
        if(datasource == null)
        {
            datasource = new DataSource();    
        }
       
        return datasource;
    }
   
    public Connection getConnection() throws SQLException
    {
        return this.ds.getConnection();
    }
   
   
   

}

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/319ec344-3380-4db9-96aa-55ecdcc68b27%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.