Originally pointed out here:

https://github.com/brettwooldridge/HikariCP/wiki/Pool-Analysis#sql-warnings-2

A connection pool should clear SQL warnings via Connection.clearWarnings() either when the Connection is returned to the pool or before it is taken from the pool. Vibur does not do this.

Further comments:

Determine whether calling Connection.clearWarnings() is necessary, as well as whether it has to happen always or to be configurable.

Hi guys,

 I would like a help to configure my java application so it can use Hibernate with Vibur connecting my database through a JNDI Datasource.

 My application is already working without vibur. But when I insert vibur properties to my hibernate.cfg.xml it doesn't work. 

My CFG is like below:

` org.postgresql.Driver

    <property name="hibernate.current_session_context_class">thread</property>
	<property name="hibernate.connection.datasource">java:comp/env/jdbc/myDatasource</property>

	<!-- JDBC connection pool (use the built-in) -->
	<property name="connection.pool_size">10</property>

	<!-- SQL dialect -->
	<property name="dialect">org.hibernate.dialect.PostgreSQL94Dialect</property>
    
	<!-- Enable Hibernate's automatic session context management -->
	<property name="current_session_context_class">thread</property>

	<!-- Vibur DBCP specific properties -->
	<property name="hibernate.connection.provider_class">
	    org.vibur.dbcp.integration.ViburDBCPConnectionProvider
	</property>
	
	<property name="hibernate.vibur.poolInitialSize">1</property>
	<property name="hibernate.vibur.poolMaxSize">300</property>
	
	<property name="hibernate.vibur.connectionIdleLimitInSeconds">2</property>
	<property name="hibernate.vibur.testConnectionQuery">isValid</property>
	
	<property name="hibernate.vibur.logQueryExecutionLongerThanMs">500</property>
	<property name="hibernate.vibur.logStackTraceForLongQueryExecution">true</property>
	
	<property name="hibernate.vibur.statementCacheMaxSize">200</property>`

After execute my Tomcat 8.5 server a exception is returned like below:

org.vibur.dbcp.ViburDBCPException: Unexpected type for configuration property externalDataSource/java:comp/env/jdbc/myDatasource at org.vibur.dbcp.ViburDBCPDataSource.configureFromProperties(ViburDBCPDataSource.java:198) at org.vibur.dbcp.ViburDBCPDataSource.<init>(ViburDBCPDataSource.java:125) at org.vibur.dbcp.integration.ViburDBCPConnectionProvider.configure(ViburDBCPConnectionProvider.java:63) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:241) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:209) at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.buildJdbcConnectionAccess(JdbcEnvironmentInitiator.java:145) at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:66) at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88) at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:258) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:232) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:209) at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51) at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94) at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:241) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:209) at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:356) at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:112) at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.build(MetadataBuildingProcess.java:84) at org.hibernate.boot.internal.MetadataBuilderImpl.build(MetadataBuilderImpl.java:470) at org.hibernate.boot.internal.MetadataBuilderImpl.build(MetadataBuilderImpl.java:91) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:691) at util.HibernateUtil.buildSessionFactory(HibernateUtil.java:22) at util.HibernateUtil.<clinit>(HibernateUtil.java:11) at empresa.ControladorEmpresa.<init>(ControladorEmpresa.java:44) at empresa.ControladorEmpresa.getInstance(ControladorEmpresa.java:38) at util.fachadas.Fachada.recuperarUrlCompletaEmpresa(Fachada.java:1085) at util.web.FiltroAcesso.doFilter(FiltroAcesso.java:38) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:610) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:660) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:798) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:808) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)

My Context.xml is configured like:

<Resource auth="Container" driverClassName="org.postgresql.Driver" global="jdbc/myDatasource" maxIdle="10" maxTotal="20" maxWaitMillis="-1" name="jdbc/myDatasource" username="postgres" password="123456" type="javax.sql.DataSource" url="jdbc:postgresql://localhost:5432/Database"/>

anyone could you help me with this problem? Or say how to define an externalDataSource for vibur?

We are currently looking for a new connection pool. We are unhappy with our existing connection pool due to the validation overhead. We configured our exiting connection pool to validate on borrow. We are using Spring JdbcTemplate and as a consequence that happens quite frequently.
Our application is a batch application. The validation behaviour we want is the ability to explicitly get a validated connection out of the pool at the start of a batch job. During the batch job the connection no longer needs to be validated, if it becomes invalid throwing an exception is fine as there is no recovery possible.

When transitioning to requiring Java 8, please upgrade to Caffeine. The performance should be relatively the same and the per-instance memory usage should be smaller.

ConcurrentLinkedHashMap changes will continue to be minimal, even more so now, and driven by requests from Java 6 users unable to upgrade. Caffeine is ideally the upgrade path for Guava cache users too, which due to Android cannot be significantly modified.

When declared as JNDI resource ViburDBCPDataSource.configureFromProperties() throws a ViburDBCPException, e.g. "Unexpected configuration property: auth"

<Resource auth="Container" name="jdbc/xxxx" type="javax.sql.DataSource" 
    driverClassName="org.postgresql.Driver" 
    factory="org.vibur.dbcp.ViburDBCPObjectFactory" ...  />

This can either be fixed by excluding all parameters by name in ViburDBCPObjectFactory e.g.

if("auth".equals(pname)
        || "singleton".equals(pname)
        || "description".equals(pname)
        || "type".equals(pname)
        || "scope".equals(pname)
        || "factory".equals(pname)
        || "closeMethod".equals(pname)) {
    /*
      need to skip these parameters as otherwise org.vibur.dbcp.ViburDBCPDataSource.configureFromProperties()
      throws a ViburDBCPException("Unexpected configuration property: " + key);
     */
    continue;
}

Or, as this is very cumbersome to maintain across Application Servers, to rather log a warning than throw an Exception

} catch (NoSuchFieldException e) {
    //throw new ViburDBCPException("Unexpected configuration property: " + key);
    logger.warn("Unexpected configuration property: " + key);
}

On large deployments, due to very high number of executions, AbstractInvocationHandler::invoke shows up a hotspot. Snapshot of the profiler output is given below. Issue is due to the number of times, the if condition is evaluated, causing misses.

Can You define vibur as JNDI datasource with tomcat 7 like C3P0:

<Resource name="jdbc/ContentDBDatasource" auth="Container"
        type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/content?useUnicode=true&amp;characterEncoding=UTF-8"
        username="xxx" password="xxx" maxActive="15" maxIdle="7"
        defaultTransactionIsolation="READ_COMMITTED" validationQuery="Select 1"/>

From vibur-dbcp/src/main/java/org/vibur/dbcp/util/ViburUtils.java

public static String formatSql(String sqlQuery, List<Object[]> sqlQueryParams) {
    StringBuilder result = new StringBuilder(1024).append("-- ").append(sqlQuery);

    if (sqlQueryParams != null && !sqlQueryParams.isEmpty()) {
        Object[] params = sqlQueryParams.toArray();
        Arrays.sort(params, new Comparator<Object>() {
            @Override
            public int compare(Object o1, Object o2) {
                return Integer.compare((int) ((Object[]) o1)[1], (int) ((Object[]) o2)[1]);
            }
        });

        result.append("\n-- Parameters:\n-- ").append(Arrays.deepToString(params));
    }
    return result.toString();
}

Causes crashes when the input values are not integers.

Different usernames and passwords are not supported yet and the call to DataSource.getConnection(username, password) will simply log a warning message and then will create and return a connection using the configured default username and password.

Using different usernames and passwords for creating connections to a database (from one and the same application) is a rarely used feature, and implementing support for it will be considered based on users requests and demand.

Until then an obvious workaround will be to use different connection pools for the different usernames and passwords required, i.e. if the application needs a pair of different usernames and passwords for accessing the database, 2 different connection pools can be created and used by the application.

java.lang.reflect.UndeclaredThrowableException
    ... unrelated frames ...
Caused by: java.lang.reflect.InvocationTargetException
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
    ... unrelated frames ...
	at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:788)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.vibur.dbcp.proxy.AbstractInvocationHandler.targetInvoke(AbstractInvocationHandler.java:144)

AbstractInvocationHandle.targetInvoke should be catching InvocationTargetException and throwing its unwrapped getCause() instead of allowing it to escape the proxy, because it is a checked exception that the proxied methods do not declare, causing it to be re-wrapped as an UndeclaredThrowableException. This means that code that tries to catch SQLException fails to, meaning the pool is inadvertently altering the exception behaviour of the methods. It should not do that.

More detail is available at the internal Atlassian issue https://jdog.jira-dev.com/browse/JEX-30043

We are using mysql connector for jdbc.
It's implementation of java.sql.connection caches the timezone.
In our application we offer the possibility to change the default TimeZone AFTER a connection has been obtained.
So we now have a pool of connections with the wrong TimeZone. We need to close those connections and remove them from the pool.

I now have a GetConnection() hook that throws a ViburDBCPException. I get a new Connection (this is good!) but the Connection that throws the ViburDBCPException is not removed from the pool and is not closed. How can I achieve that?

StatementInvocationHandler intercepts the close() method and unless the statement cache is enabled it appears to never close the wrapped statement object:

https://github.com/vibur/vibur-dbcp/blob/a893a63/src/main/java/org/vibur/dbcp/proxy/StatementInvocationHandler.java#L93

Could this be a regression introduced by the refactoring in commit e5e4116? Prior to that commit there was an else branch in processClose():

if (statement.state() != null) { // if this statement is in the cache
    ... mark available in cache and close if already evicted ...
} else
    closeStatement(rawStatement);

I ran into this when debugging an OutOfMemoryError with the Oracle JDBC driver (which internally holds onto a list of all open statements). Enabling statement cache with setStatementCacheMaxSize seems to be a viable workaround as the statement cache will close the wrapped statement on eviction.

The pom.xml states that ConcurrentLinkedHashMap dependency is optional:

    <!-- ConcurrentLinkedHashMap dependency is used for JDBC statement caching only,
         that is disabled by default. If the client's application doesn't
         enable the statement caching, it may safely exclude this dependency. -->
    <dependency>
        <groupId>com.googlecode.concurrentlinkedhashmap</groupId>
        <artifactId>concurrentlinkedhashmap-lru</artifactId>
        <version>${concurrentlinkedhashmap-lru.version}</version>
    </dependency>

However when using vibur as a global JNDI resource in tomcat, it is required:

03-Dec-2015 11:34:15.544 SEVERE [main] org.apache.catalina.startup.Catalina.start The required Server component failed to start so Tomcat is unable to start.
 org.apache.catalina.LifecycleException: Failed to start component [StandardServer[8005]]
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:154)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:625)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:351)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:485)
Caused by: java.lang.NoClassDefFoundError: com/googlecode/concurrentlinkedhashmap/EvictionListener
    at java.lang.Class.getDeclaredConstructors0(Native Method)
    at java.lang.Class.privateGetDeclaredConstructors(Class.java:2671)
    at java.lang.Class.getConstructor0(Class.java:3075)
    at java.lang.Class.getConstructor(Class.java:1825)
    at org.apache.tomcat.util.modeler.modules.MbeansDescriptorsIntrospectionSource.isBeanCompatible(MbeansDescriptorsIntrospectionSource.java:166)
    at org.apache.tomcat.util.modeler.modules.MbeansDescriptorsIntrospectionSource.supportedType(MbeansDescriptorsIntrospectionSource.java:139)
    at org.apache.tomcat.util.modeler.modules.MbeansDescriptorsIntrospectionSource.initMethods(MbeansDescriptorsIntrospectionSource.java:241)
    at org.apache.tomcat.util.modeler.modules.MbeansDescriptorsIntrospectionSource.createManagedBean(MbeansDescriptorsIntrospectionSource.java:299)
    at org.apache.tomcat.util.modeler.modules.MbeansDescriptorsIntrospectionSource.execute(MbeansDescriptorsIntrospectionSource.java:77)
    at org.apache.tomcat.util.modeler.modules.MbeansDescriptorsIntrospectionSource.loadDescriptors(MbeansDescriptorsIntrospectionSource.java:70)
    at org.apache.tomcat.util.modeler.Registry.load(Registry.java:582)
    at org.apache.tomcat.util.modeler.Registry.findManagedBean(Registry.java:485)
    at org.apache.tomcat.util.modeler.Registry.registerComponent(Registry.java:614)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1085)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:663)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:256)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:95)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:761)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    ... 7 more
Caused by: java.lang.ClassNotFoundException: com.googlecode.concurrentlinkedhashmap.EvictionListener
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 27 more