JPAContainer keeps database's connections open

Question

i'm having this problem: with vaadin 7, i've configured my persistence.xml , here's the code

<?xml version="1.0" encoding="UTF-8"?>

http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0">

<persistence-unit name="xxxx">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
    <property name="javax.persistence.jdbc.url" value="jdbc:sqlserver://xxxxx;databaseName=xxxx;"/>
    <property name="javax.persistence.jdbc.user" value="xxxx"/>
    <property name="javax.persistence.jdbc.password" value="xxxx"/>
    <property name="javax.persistence.jdbc.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="hibernate.connection.release_mode" value="after_transaction" />
    </properties>
</persistence-unit>

When i create a new JPAContainer and i bind it to a Table, i see on MSSQL Activity Monitor my new connection, but if my session is timed out or logged out, i still see the connection on SQL.

If i relog in my app, and i refresh the table, a new connection is opened, and so on.

My problem then is that i'm having a tons of opened connections on my sql server that are never closed.

I've tried to add the property

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

but nothing changed. Can you help me please? What i'm missing? Thanks so much

mstahv · Accepted Answer · 2015-02-20T13:16:53.013

Unless you have really strong reasons not to, I'd really suggest to take a modern Java EE server and use its provided JPA, transaction management and connection pooling. Define the connection into the server and use JTA transactions (jta-data-source tag in your persistence.xml). Another option is to base your application on Spring. Many things can go wrong if you go "low level" like you seem to be doing. If you are already familiar with Hibernate, I'd choose Wildfly as it has Hibernate as its JPA provider.

Instead of JPAContainer it is better to connect your UI via a facade (EJB) and if you need a lazy loaded Vaadin Container, use e.g. MTable from Viritin or Lazy Query Container. This webinar and e.g. this example project might help your to get started.

If you really have no solution but to go with low level "application managed JPA session management", you should ensure your correctly close your EntityManager instance. I'd create a helper method to your UI class that returns the same EntityManager for each jpacontainer in you UI. Then add DetachListener to your UI and close the entity manager there.

Thanks for your time, i've never worked on JAVA EE Server so i'm trying to avoiding it, because the project i'm working on is just started and i need to work on what i know. Maybe i've solved the problem: when i lunch my UI, i put the connections used by my JPAContainers in session, and when session time out, i disconnect all sessions. What do you think about this solution? I can't upvote your answer, because i don't have enough reputation! :P — Leviand, Feb 20 '15 at 07:46
Many people are avoiding Java EE servers for wrong reasons nowadays. They are really lightweight and makes your tasks simpler, not more complicated. Spring stack is another option, then you can go with plain tomcat or jetty. I'll modify my answer to cover the "low level usage" as well, although I still don't suggest that kind of architecture for anybody. — mstahv, Feb 20 '15 at 13:11

JPAContainer keeps database's connections open

1 Answers1