Java Hibernate 不释放连接池中的连接

Question

提问by Reda

I'm creating an application with Hibernate JPA and I use c3p0 for connection pooling with MySQL. I have an issue with the number of connections to the MySQL database as it hits the 152 opened connections, this is not wanted since I define in my c3p0 config file the max pool size to 20, and of course I close every entity manager I get from the EntityManagerFactoryafter committing every transaction.

我正在使用 Hibernate JPA 创建一个应用程序，并使用 c3p0 与 MySQL 建立连接池。我对 MySQL 数据库的连接数有问题，因为它达到了 152 个打开的连接，这是不想要的，因为我在我的 c3p0 配置文件中将最大池大小定义为 20，当然我关闭了我得到的每个实体管理器从EntityManagerFactory提交每个事务之后。

For every time a controller is executed, I notice more than 7 connections are opened, and if I refresh, then 7 connections are opened again without the past idle connections being closed. And in every DAO function I call, the em.close() is executed. I admit here that the issue is in my code, but I don't know what I am doing wrong here.

每次执行控制器时，我都会注意到打开了 7 个以上的连接，如果我刷新，则会再次打开 7 个连接，而不会关闭过去的空闲连接。在我调用的每个 DAO 函数中，都会执行 em.close()。我在这里承认问题出在我的代码中，但我不知道我在这里做错了什么。

This is the Sondage.java entity:

这是 Sondage.java 实体：

@Entity
@NamedQuery(name="Sondage.findAll", query="SELECT s FROM Sondage s")
public class Sondage implements Serializable {

    private static final long serialVersionUID = 1L;

    public Sondage() {}

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String name;

    private byte needLocation;

    //bi-directional many-to-one association to ResultatSondage
    @OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL)
    @OrderBy("sondage ASC")
    private List<ResultatSondage> resultatSondages;

    //bi-directional many-to-one association to SondageSection
    @OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL)
    private List<SondageSection> sondageSections;
}

And here's my DAO class:

这是我的 DAO 类：

@SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
    EntityManager em = PersistenceManager.getEntityManager();
    List<Sondage> allSondages = new ArrayList<>();
    try {
        em.getTransaction().begin();
        Query query = em.createQuery("SELECT s FROM Sondage s");
        allSondages = query.getResultList();
        em.getTransaction().commit();
    } catch (Exception ex) {
        if (em.getTransaction().isActive()) {
            em.getTransaction().rollback();
        }
        allSondages = null;
    } finally {
        em.close();
    }
    return allSondages;
}

As you see, emis closed. In my JSP, I do this: I know this is not the good way of doing thing in the view side.

如您所见，em已关闭。在我的 JSP 中，我这样做：我知道这不是在视图方面做事的好方法。

<body>
    <div class="header">
        <%@include file="../../../Includes/header.jsp" %>
    </div>
    <h2 style="color: green; text-align: center;">??????????</h2>
    <div id="allsurveys" class="pure-menu custom-restricted-width">
        <%
            List<Sondage> allSondages = (List<Sondage>) request.getAttribute("sondages");

            for (int i = 0; i < allSondages.size(); i++) {
        %>
        <a  href="${pageContext.request.contextPath }/auth/dosurvey?id=<%= allSondages.get(i).getId()%>"><%= allSondages.get(i).getName()%></a> &nbsp;
        <%
            if (request.getSession().getAttribute("user") != null) {
                Utilisateur user = (Utilisateur) request.getSession().getAttribute("user");
                if (user.getType().equals("admin")) {
        %>
        <a href="${pageContext.request.contextPath }/aauth/editsurvey?id=<%= allSondages.get(i).getId()%>">?????</a>
        <%
                }
            }
        %>
        <br />
        <%
            }
        %>
    </div>
</body>

I'm guessing that every time I call user.getType(), a request is established ? If so, how can I prevent this?

我猜每次我打电话时user.getType()，都会建立一个请求？如果是这样，我该如何防止这种情况？

For c4p0 config file, I included it in persistence.xml, I saw several posts saying that I need to put the c3p0 config file in c3p0-config.xml, but with my setup the c3p0 is initialized with the values I pass in the persistence.xml file, also the mysql connections are reaching 152 connections but the maxpoolsizeis at 20, here's the persistence.xml file

对于c4p0 配置文件，我将其包含在persistence.xml 中，我看到几篇帖子说我需要将c3p0 配置文件放在c3p0-config.xml 中，但是在我的设置中，c3p0 使用我在持久性中传递的值进行了初始化.xml 文件，mysql 连接也达到 152 个连接，但maxpoolsize在 20，这是persistence.xml 文件

<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="CAOE" transaction-type="RESOURCE_LOCAL">
        <class>com.caoe.Models.ChoixQuestion</class>
        <class>com.caoe.Models.Question</class>
        <class>com.caoe.Models.Reponse</class>
        <class>com.caoe.Models.ResultatSondage</class>
        <class>com.caoe.Models.Section</class>
        <class>com.caoe.Models.Sondage</class>
        <class>com.caoe.Models.SondageSection</class>
        <class>com.caoe.Models.SousQuestion</class>
        <class>com.caoe.Models.Utilisateur</class>
        <properties>
            <property name="hibernate.connection.provider_class"
                      value=" org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" />

            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.connection.password" value=""/>

            <property name="hibernate.connection.url"
                      value="jdbc:mysql://localhost:3306/caoe?useUnicode=yes&amp;characterEncoding=UTF-8"/>

            <property name="hibernate.connection.username" value="root"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.show_sql" value="true" />

            <property name="hibernate.c3p0.max_size" value="50" />
            <property name="hibernate.c3p0.min_size" value="3" />
            <property name="hibernate.c3p0.max_statements" value="20" />
            <property name="hibernate.c3p0.acquire_increment" value="1" />
            <property name="hibernate.c3p0.idle_test_period" value="30" />
            <property name="hibernate.c3p0.timeout" value="35" />
            <property name="hibernate.c3p0.checkoutTimeout" value="60000" />
            <property name="hibernate.connection.release_mode" value="after_statement" />

            <property name="debugUnreturnedConnectionStackTraces"
                      value="true" />
        </properties>
    </persistence-unit>
</persistence>

EDIT: I'm deploying the Application to a red hat server with Tomcat and MySQL Installed. I'm just wondering why Hibernate is opening too much connections to MySQL, with all entity managers closed no connection will remain open, but this is not the case. I'm guessing and correct me if I'm true that the connections are opened when I do something like this:

编辑：我正在将应用程序部署到安装了 Tomcat 和 MySQL 的红帽服务器。我只是想知道为什么 Hibernate 向 MySQL 开放了太多连接，所有实体管理器都关闭了，没有任何连接将保持打开状态，但事实并非如此。如果我确实在执行以下操作时打开了连接，我在猜测并纠正我：

List<Sondage> allSondages = SondageDao.getAllSondages();

for (Sondage sondage : allSondages) {
    List<Question> questions = sondage.getQuestions();
    //code to display questions for example
}

Here when I use sondage.getQuestions(), does Hibernate open a connection to the database and doesn't close it after, am I missing something in the configuration file that close or return connection to pool when it's done with it. Thanks in advance for any help.

在这里，当我使用时sondage.getQuestions()，Hibernate 是否会打开与数据库的连接并且之后没有关闭它，我是否在配置文件中遗漏了一些在完成后关闭或返回到池的连接的内容。在此先感谢您的帮助。

EDIT2 : Since people are asking for versions, here they are : JAVA jre 1.8.0_25 Apache Tomcat v7.0 hibernate-core-4.3.10 hibernate c3p0 4.3.10.final hibernate-jpa 2.1 Thanks in advance

EDIT2 ：由于人们要求版本，因此它们是： JAVA jre 1.8.0_25 Apache Tomcat v7.0 hibernate-core-4.3.10 hibernate c3p0 4.3.10.final hibernate-jpa 2.1 提前致谢

The mysql version is Mysql 5.6.17 if that can help...

mysql 版本是 Mysql 5.6.17 如果可以帮助...

EDIT 4: as people are getting confused about witch version of the code I posted is buggy, let me edit this so you'll know what happens exactly:

编辑 4：因为人们对我发布的代码的女巫版本感到困惑，所以让我编辑它，这样你就会知道到底发生了什么：

First I'll start by showing what's the buggy code, as you guys don't care about what's working:

首先，我将首先展示错误代码是什么，因为你们不关心什么是有效的：

@SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
    EntityManager em = PersistenceManager.getEntityManager();
    List<Sondage> allSondages = new ArrayList<>();
    try {
       em.getTransaction().begin();
       Query query = em.createQuery("SELECT s FROM Sondage s");
       allSondages = query.getResultList();
       em.getTransaction().commit();
    } catch (Exception ex) {
    if (em.getTransaction().isActive()) {
        em.getTransaction().rollback();
    }
    allSondages = null;
    } finally {
        em.close();
    }
    return allSondages;
  }

So this is basically what I did for all my dao functions, I know transaction is not needed here, since I saw questions pointing that transactions are important for connection to close. beside this , I getEntityManager from PersistenceManager class that has an EntityManagerFactory singleton Object, so getEntityManager creates an entityManager from the EntityManagerFactory singleton Object:=> code is better than 1000 word : PesistenceManager.java:

所以这基本上就是我为所有 dao 函数所做的，我知道这里不需要事务，因为我看到一些问题指出事务对于关闭连接很重要。除此之外，我从具有 EntityManagerFactory 单例对象的 PersistenceManager 类中获取了实体管理器，因此 getEntityManager 从 EntityManagerFactory 单例对象创建了一个 entityManager：=> 代码比 1000 字要好：PesistenceManager.java：

import javax.persistence.EntityManager;
    import javax.persistence.EntityManagerFactory;
    import javax.persistence.Persistence;

    public class PersistenceManager 
    {
    private static EntityManagerFactory emf = null;

    public static EntityManager getEntityManager()
    {
        return getEntityManagerFactory().createEntityManager();     
    }

    public static EntityManagerFactory getEntityManagerFactory()
    {
            if(emf == null) {
                    emf = Persistence.createEntityManagerFactory("CAOE");
                    return emf;
        }
            else
                    return emf;
        }
}

Yes this is cool and all good, but where's the problem?

是的，这很酷而且一切都很好，但问题出在哪里？

The problem here is that this version opens the connections and never close them, the em.close() have no effect, it keeps the connection open to the database.

这里的问题是这个版本打开连接并且从不关闭它们， em.close() 没有效果，它保持与数据库的连接打开。

The noob fix:

菜鸟修复：

What I did to fix this issue is create an EntityManagerFactory for every request, it mean that the dao looks something like this:

我为解决这个问题所做的是为每个请求创建一个 EntityManagerFactory，这意味着 dao 看起来像这样：

    @SuppressWarnings("unchecked")
public static List<Sondage> GetAllSondage() {
    //this is the method that return the EntityManagerFactory Singleton Object
    EntityManagerFactory emf = PersistenceManager.getEntitManagerFactory();
    EntityManager em = emf.createEntityManager();
        List<Sondage> allSondages = new ArrayList<>();
        try {
            em.getTransaction().begin();
            Query query = em.createQuery("SELECT s FROM Sondage s");
            allSondages = query.getResultList();
            em.getTransaction().commit();
    } catch (Exception ex) {
        if (em.getTransaction().isActive()) {
            em.getTransaction().rollback();
        }
        allSondages = null;
        } finally {
        em.close();
        emf.close();
    }
    return allSondages;
}

Now this is bad and I'll just keep it while I don't have answer for this question (it seems like forver :D ). So with this code basically All connections gets closed after hibernate doesn't need them. Thanks in advance for any efforts you put in this question :)

现在这很糟糕，我会保留它，而我对这个问题没有答案（似乎永远 :D ）。因此，使用此代码基本上所有连接都在休眠后不需要它们而关闭。预先感谢您在这个问题上所做的任何努力:)

Answer 1

回答by emamedov

Looks like issue is related to Hibernate bug. Please try to specify fetch strategy EAGER in your OneToMany annotations.

看起来问题与Hibernate bug 有关。请尝试在 OneToMany 注释中指定获取策略 EAGER。

@OneToMany(mappedBy = "sondage", cascade = CascadeType.ALL, fetch = FetchType.EAGER)

Answer 2

回答by Norbert van Nobelen

Can you try the following:

您可以尝试以下操作：

<property name="hibernate.connection.release_mode" value="after_transaction" />
<property name="hibernate.current_session_context_class" value="jta" />

instead of your current release mode?

而不是您当前的发布模式？

Answer 3

回答by sibnick

You call Persistence.createEntityManagerFactory("CAOE")every time. It is wrong. Each call createEntityManagerFactorycreates new (indepented) connection pool. You should cache EntityManagerFactory object somewhere.

你Persistence.createEntityManagerFactory("CAOE")每次都打电话。这是错误的。每次调用createEntityManagerFactory都会创建新的（独立的）连接池。您应该在某处缓存 EntityManagerFactory 对象。

EDIT:

编辑：

Also you should manually shutdown EntityManagerFactory. You can do it in @WebListener:

您还应该手动关闭 EntityManagerFactory。你可以在@WebListener 中做到：

@WebListener
public class AppInit implements ServletContextListener {

    public void contextInitialized(ServletContextEvent sce) {}

    public void contextDestroyed(ServletContextEvent sce) {
         PersistenceManager.closeEntityMangerFactory();
    }
}

Otherwise each case of redeploy is source of leaked connections.

否则，每个重新部署的情况都是泄漏连接的来源。

Answer 4

回答by Alex Barnes

I think that Hibernate and C3P0 are behaving correctly here. In fact you should see that there are always at least three connections to the database open as per your C3P0 configuration.

我认为 Hibernate 和 C3P0 在这里表现正确。事实上，您应该看到，根据您的 C3P0 配置，始终至少有三个到数据库的连接打开。

When you execute a query Hibernate will use a connection from the pool and then return it when it is done. It will not close the connection. C3P0 might shrink the pool if the min size is exceeded and some of the connections time out.

当您执行查询时，Hibernate 将使用池中的连接，然后在完成后返回它。它不会关闭连接。如果超出最小大小并且某些连接超时，C3P0 可能会缩小池。

In your final example you see the connections closed because you've shut down your entity manager factory and therefore your connection pool as well.

在最后一个示例中，您会看到连接关闭，因为您关闭了实体管理器工厂，因此也关闭了连接池。

Answer 5

回答by piet.t

Since sibnick has already answered the technical questions I'll try to address some points you seem to be confused about. So let me give you some ideas on how a hibernate application and connection-pool is intended to work:

由于 sibnick 已经回答了技术问题，我将尝试解决您似乎感到困惑的一些问题。所以让我给你一些关于休眠应用程序和连接池如何工作的想法：

Opening a database connection is an "expensive" operation. In order to avoid having to pay that cost for each and every request, you use a connection-pool. The pool opens a certain number of connections to the database in advance and when you need one you can borrow one of those existing connections. At the end of the transaction, these connections will not be closed but returned to the pool so they can be borrowed by the next request. Under heavy load, there might be too few connections to serve all requests so the pool might open additional connections that might be closed later on but not at once.
Creating an EntityManagerFactoryis even more expensive (it will create caches, open a new connection-pool, etc.), so, by all means, avoid doing it for every request. Your response-times will become incredibly slow. Also creating too many EntityManagerFactories might exhaust your PermGen-space. So only create one EntityManagerFactoryper application/persistence-context, create it at application startup (otherwise the first request will take too long) and close it upon application shutdown.

打开数据库连接是一项“昂贵”的操作。为了避免为每个请求支付该费用，您使用连接池。该池提前打开一定数量的数据库连接，当您需要一个连接时，您可以借用其中一个现有连接。在事务结束时，这些连接不会关闭而是返回到池中，以便下一个请求可以借用它们。在高负载下，可能有太少的连接来处理所有请求，因此池可能会打开可能稍后关闭但不会立即关闭的其他连接。
创建一个EntityManagerFactory更昂贵（它会创建缓存，打开一个新的连接池等），所以，无论如何，避免为每个请求都这样做。您的响应时间将变得异常缓慢。此外，创建过多的 EntityManagerFactories 可能会耗尽您的 PermGen 空间。所以只EntityManagerFactory为每个应用程序/持久性上下文创建一个，在应用程序启动时创建它（否则第一个请求将花费太长时间）并在应用程序关闭时关闭它。

Bottom line: When using a connection-pool you should expect a certain number of DB-connections to remain open for the lifetime of your application. What must not happen is that the number increases with every request. If you insist on having the connections closed at the end of the session don't use a pool and be prepared to pay the price.

底线：使用连接池时，您应该期望一定数量的数据库连接在应用程序的生命周期内保持打开状态。绝对不能发生的是，每个请求都会增加这个数字。如果您坚持在会话结束时关闭连接，请不要使用池并准备付出代价。

Answer 6

回答by Willem van Rooyen

I ran into the same problem and was able to fix it by creating a singleton wrapper class for the EntityManagerFactory and creating the EntityManager where it's needed. You're having the connection overload problem because you're wrapping the EntityManager creation in the singleton class, which is wrong. The EntityManager provides the transaction scope (should not be re-used), the EntityManagerFactory provides the connections (should be re-used).

我遇到了同样的问题，并且能够通过为 EntityManagerFactory 创建一个单例包装类并在需要的地方创建 EntityManager 来解决它。您遇到了连接过载问题，因为您将 EntityManager 创建包装在单例类中，这是错误的。EntityManager 提供事务范围（不应该重用），EntityManagerFactory 提供连接（应该重用）。

from: https://cloud.google.com/appengine/docs/java/datastore/jpa/overview

来自：https: //cloud.google.com/appengine/docs/java/datastore/jpa/overview

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public final class EMF {
    private static final EntityManagerFactory emfInstance =
        Persistence.createEntityManagerFactory("CAOE");

private EMF() {}

public static EntityManagerFactory get() {
    return emfInstance;
    }
}

and then use the factory instance to create an EntityManager for each request.

然后使用工厂实例为每个请求创建一个 EntityManager。

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import EMF;

// ...
EntityManager em = EMF.get().createEntityManager();

Answer 7

回答by Md. Sajedul Karim

In my application property i have some datasource related parameter. Those are given bellow:

在我的应用程序属性中，我有一些与数据源相关的参数。这些是在下面给出的：

# DataSource Parameter
minPoolSize:5
maxPoolSize:100
maxIdleTime:5
maxStatements:1000
maxStatementsPerConnection:100
maxIdleTimeExcessConnections:10000

Here, **maxIdleTime**value is the main culprit. It takes value in second. Here maxIdleTime=5 means after 5 seconds if connection is not using then it will release the connection and it will take the minPoolSize:5 connection. Here maxPoolSize:100 means it will take maximum 100 connection at a time.

在这里，**maxIdleTime**价值是罪魁祸首。我吨发生在第二值。这里 maxIdleTime=5 意味着在 5 秒后如果连接没有被使用，那么它将释放连接并使用 minPoolSize:5 连接。这里 maxPoolSize:100 表示一次最多需要 100 个连接。

In my DataSourceConfiguration class i have a bean. Here is the example code:

在我的DataSourceConfiguration 类中，我有一个 bean。这是示例代码：

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.core.env.Environment;
import org.springframework.beans.factory.annotation.Autowired;

@Autowired
    private Environment env;

 @Bean
    public ComboPooledDataSource dataSource(){
        ComboPooledDataSource dataSource = new ComboPooledDataSource();

        try {
            dataSource.setDriverClass(env.getProperty("db.driver"));
            dataSource.setJdbcUrl(env.getProperty("db.url"));
            dataSource.setUser(env.getProperty("db.username"));
            dataSource.setPassword(env.getProperty("db.password"));
            dataSource.setMinPoolSize(Integer.parseInt(env.getProperty("minPoolSize")));
            dataSource.setMaxPoolSize(Integer.parseInt(env.getProperty("maxPoolSize")));
            dataSource.setMaxIdleTime(Integer.parseInt(env.getProperty("maxIdleTime")));
            dataSource.setMaxStatements(Integer.parseInt(env.getProperty("maxStatements")));
            dataSource.setMaxStatementsPerConnection(Integer.parseInt(env.getProperty("maxStatementsPerConnection")));
            dataSource.setMaxIdleTimeExcessConnections(10000);

        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

Hope this will solve your problem :)

希望这能解决您的问题:)

Java Hibernate 不释放连接池中的连接

提问by Reda

回答by emamedov

回答by Norbert van Nobelen

回答by sibnick

回答by Alex Barnes

回答by piet.t

回答by Willem van Rooyen

回答by Md. Sajedul Karim

相关推荐

最近更新

标签

Java Hibernate 不释放连接池中的连接

提问by Reda

回答by emamedov

回答by Norbert van Nobelen

回答by sibnick

回答by Alex Barnes

回答by piet.t

回答by Willem van Rooyen

回答by Md. Sajedul Karim

相关推荐

如何在 Java 中从另一个构造函数调用一个构造函数？

Java 如何将多个变量值存储到单个数组或列表中？

如何在 Java 中将 UTF-8 转换为 US-Ascii

Java 如何在 RetroFit 中使用 Gson 转换器？

相关推荐

最近更新

标签