hibernate - Vaadin SQLContainer + Grails 给出错误 "org.postgresql.util.PSQLException: This connection has been closed"

Question

编辑:这是一个演示问题的普通 Grails 项目:https://github.com/jbwiv/vaadin_connection_problem_demo . H2 和 PostgreSQL 驱动程序都会出现这种情况。

我正在尝试将 Vaadin 的 SQLContainer 与 Grails 结合使用。为了访问池，我将 dataSource 注入(inject)到 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)

但是，它会导致异常:

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

这是我的 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
            }
        }
    }
}

我认为这意味着有关 grails 数据源的某些内容处于无效状态。任何关于如何使这项工作的想法将不胜感激。

谢谢。

Answer 1

解决方案 1:

这是一个可行的解决方案，但会创建另一个连接池。

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

解决方案 2(首选):

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

将依赖添加到 BuildConfig.groovy

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

创建新的数据源:

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
    }
}

将新 bean 添加到 resources.groovy

beans = {
    dataSource(GrailsDataSource)
}

在您的 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)