2

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 

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 {
    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.

Thanks.

jbwiv
  • 1,015
  • 9
  • 21
  • is your datasource config working for dev at all? e.g. can you create a domain object in bootstrap? just to rule that out... – cfrick Jul 22 '14 at 08:42
  • Yes, everything works fine with it on the Grails side of the fence. Also, stepping through code, it appears that it's working at some times in the Vaadin SQLContainer code. Just something causes it to become closed. This happens with both the H2 and the Postgresql drivers, so I'm thinking there's it's expected unexpected behavior, if that makes sense. Going to put together a github project to demonstrate... – jbwiv Jul 22 '14 at 13:02
  • I've added the link to the github project at the top of the original question. You can download and run this and see the problem for yourself. – jbwiv Jul 22 '14 at 13:38

2 Answers2

1

SOLUTION 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):

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

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

beans = {
    dataSource(GrailsDataSource)
}

Use it in your UI class

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)
Ondrej Kvasnovsky
  • 4,592
  • 3
  • 30
  • 40
  • I find this approach works ok, although I'm not certain of the impacts of using two connection pools in the log run, nor do I know much about Vaadin's SimpleJDBCConnectionPool and how well it can be expected to performn. That said, it does seem to work. One thing I've done to make it a bit easier is add the pool to resources.groovy: beans = { grailsConnectionPool(GrailsConnectionPool) {} } Then, I can simply access it in my code as Grails.get(GrailsConnectionPool). Thanks for your help!\ – jbwiv Jul 23 '14 at 12:41
1

This is apparently to be a bug in Vaadin's TableQuery implementation. See: 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.)

Bampfer
  • 2,120
  • 16
  • 25