如何解决谷歌CloudSQL 8小时超时？

Question

我正在使用开发一个应用程序。我正在使用Hibernate 4.2.0。最后，我注意到，在8个小时的不活动之后，我的连接关闭了。我一直在网上搜索，我找不到任何有效解决这个问题的方法。我发现的所有信息都在以下项目中进行了总结：

• 使用连接池，在其中我应该指定更高的超时。我尝试过使用c3p0和DBCP2.1.1库，但它们都没有解决这个问题。
• 在查询之前打开一个连接，然后再关闭它。问题是，为了使用实体查询，我想使用EntityManager。

下面是我对DBC2.1.1 (http://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2/2.1.1)所做的工作。问题是，当我尝试在我的ExceptionInInitializerError中使用findAll时，Goggle日志显示了一个PersistenceService。日志还表明，无法将我的DataSource (见下文)转换为String。我不明白为什么。

这是我现在使用的PersistenceService

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();
    }
}

这是我的DataSource

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();
    }




}

Answer 1

为mysql连接添加autoReconnect=true选项。最简单的方法是将其添加到连接url本身：

persistence.db.url=jdbc:mysql://x.x.x.x:3306/y?autoReconnect=true

Answer 2

8小时的空闲连接超时来自mysql服务器，而不是客户机，因此更改客户机上的超时不会有多大效果。可以通过更改wait_timeout标志在服务器上调整此设置。8小时是默认值。

您应该配置连接池，以便在一段时间后自动删除空闲连接。在DBCP中，您可以通过timeBetweenEvictionRunsMillis选项启用空闲连接驱逐器。

Answer 3

在Vadim的回答中添加一点，您可以在CloudSQL上通过云控制台编辑实例设置标志。在谷歌上，你不能通过运行像SET GLOBAL wait_timeout=60;这样的东西来做到这一点，因为它不支持超级特权。

我花了一段时间才找到这个，所以我想它可能会帮助别人把它加在这里。