Vaadin SQLContainer + Grails提供了错误“org.postgresql.util”。PSQLException:此连接已关闭”

Edit: Here's a plain Grails project which demonstrates the problem: https://github.com/jbwiv/vaadin_connection_problem_demo. It occurs with both H2 and PostgreSQL drivers.

I'm trying to use Vaadin's SQLContainer with Grails. To get access to the pool, I inject dataSource into a DataAccessService grails service class, and then call

我正在尝试用Grails使用Vaadin的SQLContainer。为了访问池，我将dataSource注入到DataAccessService grails服务类中，然后调用。

Table table = new Table("My Table")
JDBCConnectionPool pool = new J2EEConnectionPool(Grails.get(DataAccessService).dataSource)
TableQuery query = new TableQuery("service_order", pool)
SQLContainer container = new SQLContainer(query)
table.setContainerDataSource(container)

However, it results in an exception:

然而，它导致了一个例外:

com.vaadin.server.ServerRpcManager$RpcInvocationException: Unable to invoke method layoutClick in com.vaadin.shared.ui.orderedlayout.AbstractOrderedLayoutServerRpc
    at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:170)
    at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:118)
    at com.vaadin.server.communication.ServerRpcHandler.handleBurst(ServerRpcHandler.java:207)
    at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:111)
    at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:91)
    at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:37)
    at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1390)
    at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:238)
    at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
    at grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter.doFilter(RequestHolderAuthenticationFilter.java:49)
    at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:82)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.reflect.InvocationTargetException
    at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:168)
    ... 13 more
Caused by: com.vaadin.event.ListenerMethod$MethodException: Invocation of method layoutClick in com.myproject.ui.components.LinkButton$1 failed.
    at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:528)
    at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:198)
    at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:161)
    at com.vaadin.server.AbstractClientConnector.fireEvent(AbstractClientConnector.java:969)
    at com.vaadin.ui.AbstractOrderedLayout.access$000(AbstractOrderedLayout.java:35)
    at com.vaadin.ui.AbstractOrderedLayout$1.layoutClick(AbstractOrderedLayout.java:44)
    ... 14 more
Caused by: java.lang.RuntimeException: Failed to update item set size.
    at com.vaadin.data.util.sqlcontainer.SQLContainer.updateCount(SQLContainer.java:1174)
    at com.vaadin.data.util.sqlcontainer.SQLContainer.size(SQLContainer.java:403)
    at com.vaadin.ui.AbstractSelect.size(AbstractSelect.java:762)
    at com.vaadin.ui.Table.refreshRenderedCells(Table.java:1654)
    at com.vaadin.ui.Table.attach(Table.java:4171)
    at com.vaadin.server.AbstractClientConnector.attach(AbstractClientConnector.java:583)
    at com.vaadin.ui.AbstractComponent.attach(AbstractComponent.java:572)
    at com.vaadin.server.AbstractClientConnector.attach(AbstractClientConnector.java:583)
    at com.vaadin.ui.AbstractComponent.attach(AbstractComponent.java:572)
    at com.myproject.ui.components.EventedCustomComponent$$E0.attach(EventedCustomComponent.groovy:17)
    at com.vaadin.ui.AbstractComponent.setParent(AbstractComponent.java:479)
    at com.vaadin.ui.AbstractComponentContainer.addComponent(AbstractComponentContainer.java:215)
    at com.vaadin.ui.AbstractOrderedLayout.addComponent(AbstractOrderedLayout.java:85)
    at com.vaadin.navigator.Navigator$ComponentContainerViewDisplay.showView(Navigator.java:191)
    at com.vaadin.navigator.Navigator.navigateTo(Navigator.java:568)
    at com.vaadin.navigator.Navigator.navigateTo(Navigator.java:526)
    at com.myproject.ui.MyUI.navigateTo(MyUI.groovy:28)
    at com.myproject.ui.components.LinkButton$1.layoutClick(LinkButton.groovy:24)
    at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:508)
    ... 19 more
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:837)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:798)
    at com.vaadin.data.util.sqlcontainer.query.AbstractTransactionalQuery.commit(AbstractTransactionalQuery.java:74)
    at com.vaadin.data.util.sqlcontainer.query.TableQuery.commit(TableQuery.java:406)
    at com.vaadin.data.util.sqlcontainer.query.TableQuery.getCount(TableQuery.java:221)
    at com.vaadin.data.util.sqlcontainer.SQLContainer.updateCount(SQLContainer.java:1163)
    ... 37 more
server.DefaultErrorHandler 
java.lang.RuntimeException: Failed to update item set size.
    at com.vaadin.data.util.sqlcontainer.SQLContainer.updateCount(SQLContainer.java:1174)
    at com.vaadin.data.util.sqlcontainer.SQLContainer.size(SQLContainer.java:403)
    at com.vaadin.ui.AbstractSelect.size(AbstractSelect.java:762)
    at com.vaadin.ui.Table.refreshRenderedCells(Table.java:1654)
    at com.vaadin.ui.Table.getVisibleCells(Table.java:3960)
    at com.vaadin.ui.Table.beforeClientResponse(Table.java:3181)
    at com.vaadin.server.communication.UidlWriter.write(UidlWriter.java:96)
    at com.vaadin.server.communication.UidlRequestHandler.writeUidl(UidlRequestHandler.java:149)
    at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:97)
    at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:37)
    at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1390)
    at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:238)
    at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
    at grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter.doFilter(RequestHolderAuthenticationFilter.java:49)
    at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:82)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:837)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:275)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.prepareStatement(AbstractJdbc2Connection.java:311)
    at com.vaadin.data.util.sqlcontainer.query.TableQuery.executeQuery(TableQuery.java:526)
    at com.vaadin.data.util.sqlcontainer.query.TableQuery.getCount(TableQuery.java:210)
    at com.vaadin.data.util.sqlcontainer.SQLContainer.updateCount(SQLContainer.java:1163)
    ... 17 more

Here is my DataSource.groovy. Note, I'm only testing (and experiencing the problem) in the "development" environment:

这是我的DataSource.groovy。注意，我只是在“开发”环境中测试(并体验这个问题):

dataSource {
    pooled = true
    jmxExport = true
    driverClassName = "org.postgresql.Driver"
    dialect = "org.hibernate.dialect.PostgreSQLDialect"
    username = "user"
    password = "password"
}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
//    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
    cache.region.factory_class = 'org.hibernate.cache.ehcache.EhCacheRegionFactory' // Hibernate 4
//    singleSession = true // configure OSIV singleSession mode
}

// environment specific settings
environments {
    development {
        dataSource {
            dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:postgresql://localhost:5432/mydb"
        }
    }
    test {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
        }
    }
    production {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:prodDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
            properties {
               // See http://grails.org/doc/latest/guide/conf.html#dataSource for documentation
               jmxEnabled = true
               initialSize = 5
               maxActive = 50
               minIdle = 5
               maxIdle = 25
               maxWait = 10000
               maxAge = 10 * 60000
               timeBetweenEvictionRunsMillis = 5000
               minEvictableIdleTimeMillis = 60000
               validationQuery = "SELECT 1"
               validationQueryTimeout = 3
               validationInterval = 15000
               testOnBorrow = true
               testWhileIdle = true
               testOnReturn = false
               jdbcInterceptors = "ConnectionState"
               defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
            }
        }
    }
}

I assume this means that something about the grails datasource is in an invalid state. Any ideas on how to make this work would be greatly appreciated.

我认为这意味着grails数据源的某些内容处于无效状态。任何关于如何做这项工作的想法都将得到极大的赞赏。

Thanks.

谢谢。

2 个解决方案

#1

SOLUTION 1:

解决方案1:

Here is a solution that works but will create another connection pool.

这里有一个解决方案，但将创建另一个连接池。

class MyUI extends UI {

    @Override
    protected void init(VaadinRequest vaadinRequest) {

        VerticalLayout layout = new VerticalLayout()

        JDBCConnectionPool pool = new GrailsConnectionPool()
        TableQuery query = new TableQuery("user", pool)
        SQLContainer container = new SQLContainer(query)
        Table table = new Table("Testing")
        table.setContainerDataSource(container)
        layout.addComponent(table)
        setContent(layout)
    }
}


class GrailsConnectionPool extends SimpleJDBCConnectionPool {

    GrailsConnectionPool() throws SQLException {
        super(
                Grails.get(GrailsApplication).config.dataSource.driverClassName as String,
                Grails.get(GrailsApplication).config.dataSource.url as String,
                Grails.get(GrailsApplication).config.dataSource.username as String,
                Grails.get(GrailsApplication).config.dataSource.password as String
        )
    }
}

SOLUTION 2 (preferred):

解决方案2(首选):

Other solution is to create another data source that we will use instead of the default one, so there is no other connection pool:

其他的解决方案是创建另一个数据源，我们将使用而不是默认的数据源，所以没有其他连接池:

Add dependency to BuildConfig.groovy

添加依赖BuildConfig.groovy

compile 'commons-dbcp:commons-dbcp:1.4'

Create new data source:

创建新数据源:

import com.vaadin.grails.Grails
import org.apache.commons.dbcp.BasicDataSource
import org.codehaus.groovy.grails.commons.GrailsApplication
import org.springframework.jdbc.datasource.DelegatingDataSource
import java.sql.Connection
import java.sql.SQLException

class GrailsDataSource extends DelegatingDataSource {

    private boolean _initialized

    @Override
    Connection getConnection() throws SQLException {
        initialize()
        return super.getConnection()
    }

    @Override
    void afterPropertiesSet() {
        // override to not check for targetDataSource since it's lazily created
    }

    private synchronized void initialize() {
        if (_initialized) {
            return
        }

        def config = Grails.get(GrailsApplication).config.dataSource
        setTargetDataSource(new BasicDataSource(
                driverClassName: config.driverClassName, password: config.password,
                username: config.username, url: config.url))

        _initialized = true
    }
}

Add new bean into resources.groovy

将新bean添加到resources.groovy中。

beans = {
    dataSource(GrailsDataSource)
}

Use it in your UI class

在UI类中使用它。

DataSource dataSource = Grails.applicationContext.getBean('dataSource')
JDBCConnectionPool pool = new J2EEConnectionPool(dataSource)

TableQuery query = new TableQuery("user", pool)
SQLContainer container = new SQLContainer(query)
Table table = new Table("Testing")
table.setContainerDataSource(container)

#2

This is apparently to be a bug in Vaadin's TableQuery implementation. See: http://dev.vaadin.com/ticket/12370

这显然是Vaadin的TableQuery实现中的一个缺陷。参见:http://dev.vaadin.com/ticket/12370

The ticket proposes a code change, but in my case I simply replaced the offending TableQuery with a FreeformQuery. Other workarounds include using a connection pool like SimpleJDBCConnectionPool, which apparently tolerates releasing the same connection twice (though that pool is not recommended for production use.)

这张票据提出了一个代码变更，但在我的例子中，我只是用一个FreeformQuery替换了这个讨厌的表查询。其他的工作区包括使用一个类似SimpleJDBCConnectionPool的连接池，它显然可以容忍两次释放相同的连接(尽管不建议使用该池进行生产)。