Configure hibernate to connect to database via JNDI Datasource

Question

Hi all I'm using Hibernate+Struts2+Tomcat6+Mysql as my J2EE framework. I've been using hibernate's built-in connection pooling mechanism but it turned out to be problematic where mysql closes it's connections after 8 hours. Anyway I googled around a little bit and found that I should get my connections through JNDI datasource but I couldn't reach a good and complete tutorial on this. What steps should I take to do so? please provide me enough of details, I'm kinda new on this. here is my hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration>   <session-factory>      <property name="connection.driver_class">com.mysql.jdbc.Driver</property>     <property name="hibernate.connection.url">             jdbc:mysql://localhost/hposg?characterEncoding=UTF-8     </property>     <property name="connection.username">root</property>     <property name="connection.password"></property>     <property name="show_sql">true</property>     <property name="dialect">org.hibernate.dialect.MySQLDialect</property>     <property name="current_session_context_class">thread</property>     <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>     <property name="hbm2ddl.auto">update</property>      <property name="hibernate.max_fetch_depth">3</property>      <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>     <property name="hibernate.c3p0.acquire_increment">2</property>     <property name="hibernate.c3p0.idle_test_period">300</property>     <property name="hibernate.c3p0.timeout">1800</property>     <property name="hibernate.c3p0.max_size">25</property>     <property name="hibernate.c3p0.min_size" >3</property>     <property name="hibernate.c3p0.max_statement">0</property>     <property name="hibernate.c3p0.preferredTestQuery">select 1;</property>     <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>     <property name="hibernate.c3p0.validate">true</property>        <!-- Mapping files -->        <mapping resource="com/hposg/hibernate/resources/Player.hbm.xml"/>       <mapping resource="com/hposg/hibernate/resources/Game.hbm.xml"/>       ...    </session-factory> </hibernate-configuration> 

I also tried this but it didn't work out: Creating a context.xml in WebContent/META-INF containing:

<Context path="" docBase="../RSGames" debug="0" reloadable="true">     <Resource name="jdbc/RSGames" auth="Container" type="javax.sql.DataSource"          username="root"          password=""          driverClassName="com.mysql.jdbc.Driver"          url="jdbc:mysql://localhost:3306/hposg?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8"          maxActive="15"          maxIdle="7"          validationQuery="Select 1" /> </Context> 

putting this in web.xml:

<resource-ref>     <description>Connection Pool</description>     <res-ref-name>jdbc/RSGames</res-ref-name>     <res-type>javax.sql.Datasource</res-type>     <res-auth>Container</res-auth> </resource-ref> 

and modifying hibernate config like this:

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration>   <session-factory>   ...     <property name="connection.datasource">java:comp/env/jdbc/RSGames</property>   ...   </session-factory> </hibernate-configuration> 

as I mentioned before, it doesn't get connected to database this way. any ideas?

[EDIT] When I execute the app I get this exception:

Mar 16, 2011 12:29:13 AM com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run WARNING: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1f18cbe -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:  java.lang.NullPointerException     at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(Unknown Source)     at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(Unknown Source)     at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(Unknown Source)     at java.sql.DriverManager.getDriver(Unknown Source)     at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:224)     at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:120)     at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:143)     at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:132)     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)     at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)     at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)     at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)     at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) Mar 16, 2011 12:29:13 AM com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run WARNING: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@14f1726 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:  java.lang.NullPointerException     at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(Unknown Source)     at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(Unknown Source)     at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(Unknown Source)     at java.sql.DriverManager.getDriver(Unknown Source)     at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:224)     at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:120)     at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:143)     at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:132)     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)     at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)     at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)     at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)     at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) Mar 16, 2011 12:29:13 AM com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run WARNING: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@cad437 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:  java.lang.NullPointerException     at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(Unknown Source)     at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(Unknown Source)     at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(Unknown Source)     at java.sql.DriverManager.getDriver(Unknown Source)     at com.mchange.v2.c3p0.DriverManagerDataSource.driver(DriverManagerDataSource.java:224)     at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:120)     at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:143)     at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:132)     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)     at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)     at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)     at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)     at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) 29994 [http-8081-3] WARN org.hibernate.cfg.SettingsFactory - Could not obtain connection to query metadata java.sql.SQLException: Connections could not be acquired from the underlying database!     at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)     at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)     at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:78)     at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:114)     at org.hibernate.cfg.Configuration.buildSettingsInternal(Configuration.java:2163)     at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2159)     at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1383)     at com.hposg.hibernate.util.HibernateUtil.buildSessionFactory(HibernateUtil.java:12)     at com.hposg.hibernate.util.HibernateUtil.<clinit>(HibernateUtil.java:7)     at com.hposg.login.LoginFactory.doLogin(LoginFactory.java:25)     at com.hposg.controller.struts.LoginAction.execute(LoginAction.java:59)     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)     at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)     at java.lang.reflect.Method.invoke(Unknown Source)     at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:441)     at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:280)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:243)     at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:165)     at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:252)     at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)     at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)     at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)     at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:179)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:94)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:130)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)     at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:165)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:179)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)     at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)     at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)     at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:488)     at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:395)     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)     at java.lang.Thread.run(Unknown Source) Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.     at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)     at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)     at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)     at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)     ... 74 more 

Answer 1

Apparently, you did it right. But here is a list of things you'll need with examples from a working application:

1) A context.xml file in META-INF, specifying your data source:

<Context>     <Resource          name="jdbc/DsWebAppDB"          auth="Container"          type="javax.sql.DataSource"          username="sa"          password=""          driverClassName="org.h2.Driver"          url="jdbc:h2:mem:target/test/db/h2/hibernate"          maxActive="8"          maxIdle="4"/> </Context> 

2) web.xml which tells the container that you are using this resource:

<resource-env-ref>     <resource-env-ref-name>jdbc/DsWebAppDB</resource-env-ref-name>     <resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type> </resource-env-ref> 

3) Hibernate configuration which consumes the data source. In this case, it's a persistence.xml, but it's similar in hibernate.cfg.xml

<persistence-unit name="dswebapp">     <provider>org.hibernate.ejb.HibernatePersistence</provider>     <properties>         <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />         <property name="hibernate.connection.datasource" value="java:comp/env/jdbc/DsWebAppDB"/>     </properties> </persistence-unit>