mysql - Hibernate-mysql-c3p0 的连接丢失问题

Question

这是我在整个网络上看到的一个问题。我会再次提出它，因为到目前为止我还没有解决同样的问题。

  I am using hibernate 3. mysql 5 and latest c3p0 jar. I am getting a broken pipe exception. Following is my hibernate.cfg file.

com.mysql.jdbc.驱动程序 org.hibernate.dialect.MySQLDialect

    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.use_sql_comments">true</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="connection.autoReconnect">true</property>
    <property name="connection.autoReconnectForPools">true</property>
    <property name="connection.is-connection-validation-required">true</property>

    <!--<property name="c3p0.min_size">5</property>
    <property name="c3p0.max_size">20</property>
    <property name="c3p0.timeout">1800</property>
    <property name="c3p0.max_statements">50</property>


   --><property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider
</property>
    <property name="hibernate.c3p0.acquireRetryAttempts">30</property>
    <property name="hibernate.c3p0.acquireIncrement">5</property>
    <property name="hibernate.c3p0.automaticTestTable">C3P0TestTable</property>

    <property name="hibernate.c3p0.idleConnectionTestPeriod">36000</property>

    <property name="hibernate.c3p0.initialPoolSize">20</property>
    <property name="hibernate.c3p0.maxPoolSize">100</property>
    <property name="hibernate.c3p0.maxIdleTime">1200</property>
    <property name="hibernate.c3p0.maxStatements">50</property>
    <property name="hibernate.c3p0.minPoolSize">10</property>-->  

我的连接池运行良好。白天它很好，但是一旦我让它在晚上闲置，第二天我发现它给我断开连接错误。

公共(public)类 HibernateUtil {

private static Logger log = Logger.getLogger(HibernateUtil.class);
//private static Log log = LogFactory.getLog(HibernateUtil.class);

private static Configuration configuration;
private static SessionFactory sessionFactory;

static {
    // Create the initial SessionFactory from the default configuration files
    try {

        log.debug("Initializing Hibernate");

        // Read hibernate.properties, if present
        configuration = new Configuration();
        // Use annotations: configuration = new AnnotationConfiguration();

        // Read hibernate.cfg.xml (has to be present)
        configuration.configure();

        // Build and store (either in JNDI or static variable)
        rebuildSessionFactory(configuration);

        log.debug("Hibernate initialized, call HibernateUtil.getSessionFactory()");
    } catch (Throwable ex) {
        // We have to catch Throwable, otherwise we will miss
        // NoClassDefFoundError and other subclasses of Error
        log.error("Building SessionFactory failed.", ex);
        throw new ExceptionInInitializerError(ex);
    }
}

/**
 * Returns the Hibernate configuration that was used to build the SessionFactory.
 *
 * @return Configuration
 */
public static Configuration getConfiguration() {
    return configuration;
}

/**
 * Returns the global SessionFactory either from a static variable or a JNDI lookup.
 *
 * @return SessionFactory
 */
public static SessionFactory getSessionFactory() {
    String sfName = configuration.getProperty(Environment.SESSION_FACTORY_NAME);
    System.out.println("Current s name is "+sfName);
    if ( sfName != null) {
        System.out.println("Looking up SessionFactory in JNDI");
        log.debug("Looking up SessionFactory in JNDI");
        try {
            System.out.println("Returning new sssion factory");
            return (SessionFactory) new InitialContext().lookup(sfName);
        } catch (NamingException ex) {
            throw new RuntimeException(ex);
        }
    } else if (sessionFactory == null) {
        System.out.println("calling rebuild session factory now");
        rebuildSessionFactory();
    }
    return sessionFactory;
}

/**
 * Closes the current SessionFactory and releases all resources.
 * <p>
 * The only other method that can be called on HibernateUtil
 * after this one is rebuildSessionFactory(Configuration).
 */
public static void shutdown() {
    log.debug("Shutting down Hibernate");
    // Close caches and connection pools
    getSessionFactory().close();

    // Clear static variables
    sessionFactory = null;
}


/**
 * Rebuild the SessionFactory with the static Configuration.
 * <p>
 * Note that this method should only be used with static SessionFactory
 * management, not with JNDI or any other external registry. This method also closes
 * the old static variable SessionFactory before, if it is still open.
 */
 public static void rebuildSessionFactory() {
    log.debug("Using current Configuration to rebuild SessionFactory");
    rebuildSessionFactory(configuration);
 }

/**
 * Rebuild the SessionFactory with the given Hibernate Configuration.
 * <p>
 * HibernateUtil does not configure() the given Configuration object,
 * it directly calls buildSessionFactory(). This method also closes
 * the old static variable SessionFactory before, if it is still open.
 *
 * @param cfg
 */
 public static void rebuildSessionFactory(Configuration cfg) {
    log.debug("Rebuilding the SessionFactory from given Configuration");
    if (sessionFactory != null && !sessionFactory.isClosed())
        sessionFactory.close();
    if (cfg.getProperty(Environment.SESSION_FACTORY_NAME) != null) {
        log.debug("Managing SessionFactory in JNDI");
        cfg.buildSessionFactory();
    } else {
        log.debug("Holding SessionFactory in static variable");
        sessionFactory = cfg.buildSessionFactory();
    }
    configuration = cfg;
 }

以上是我的 session 工厂代码。我只有选择操作。

下面是最常用于执行我的选择查询的方法。我不理解的一件棘手的事情是在我的 findById 方法中，我正在使用这行代码 getSession().beginTransaction();没有它，它会给我一个错误，说没有交易就不会发生这种情况。但我无处关闭此交易。除了不适用于 select 语句的提交或回滚(据我所知)之外，没有其他方法可以关闭事务。

public T findById(ID id, boolean lock) throws HibernateException, DAOException { log.debug("使用 ID ="+id+"和 lock ="+lock 调用 findNyId); T实体； getSession().beginTransaction();

    if (lock)
        entity = (T) getSession().load(getPersistentClass(), id, LockMode.UPGRADE);
    else
        entity = (T) getSession().load(getPersistentClass(), id);

    return entity;
}

谁能建议我该怎么做？我已经通过谷歌搜索、stackoverlow 或 hibernate 论坛尝试了几乎所有可用的解决方案，但都无济于事。 (在我的例子中，增加 mysql 的 wait_timeout 不是一个有效的选项)。

Answer 1

我知道 MySQL 可以在“n”小时不使用后使连接失效(参见 here )以供引用。

那么您能否配置 C3P0 以在将连接提供给您(客户端)之前验证连接？或者将 C3P0 配置为在特定时间后超时连接？参见 this link了解更多信息。