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   lt  xml version  1 0  encoding  UTF-8   gt   lt  DOCTYPE hibernate-configuration PUBLIC  -  Hibernate Hibernate Configuration DTD 3 0  EN   http   hibernate sourceforge net hibernate-configuration-3 0 dtd  gt   lt hibernate-configuration gt     lt session-factory gt        lt property name  connection driver class  gt com mysql jdbc Driver lt  property gt       lt property name  hibernate connection url  gt              jdbc mysql   localhost hposg characterEncoding UTF-8      lt  property gt       lt property name  connection username  gt root lt  property gt       lt property name  connection password  gt  lt  property gt       lt property name  show sql  gt true lt  property gt       lt property name  dialect  gt org hibernate dialect MySQLDialect lt  property gt       lt property name  current session context class  gt thread lt  property gt       lt property name  cache provider class  gt org hibernate cache NoCacheProvider lt  property gt       lt property name  hbm2ddl auto  gt update lt  property gt        lt property name  hibernate max fetch depth  gt 3 lt  property gt        lt property name  hibernate connection provider class  gt org hibernate connection C3P0ConnectionProvider lt  property gt       lt property name  hibernate c3p0 acquire increment  gt 2 lt  property gt       lt property name  hibernate c3p0 idle test period  gt 300 lt  property gt       lt property name  hibernate c3p0 timeout  gt 1800 lt  property gt       lt property name  hibernate c3p0 max size  gt 25 lt  property gt       lt property name  hibernate c3p0 min size   gt 3 lt  property gt       lt property name  hibernate c3p0 max statement  gt 0 lt  property gt       lt property name  hibernate c3p0 preferredTestQuery  gt select 1  lt  property gt       lt property name  hibernate c3p0 testConnectionOnCheckout  gt true lt  property gt       lt property name  hibernate c3p0 validate  gt true lt  property gt          lt  -- Mapping files -- gt          lt mapping resource  com hposg hibernate resources Player hbm xml   gt         lt mapping resource  com hposg hibernate resources Game hbm xml   gt                lt  session-factory gt   lt  hibernate-configuration gt    I also tried this but it didn t work out  Creating a context xml in WebContent META-INF containing    lt Context path    docBase     RSGames  debug  0  reloadable  true  gt       lt 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 amp useUnicode true amp amp characterEncoding utf8           maxActive  15           maxIdle  7           validationQuery  Select 1    gt   lt  Context gt    putting this in web xml    lt resource-ref gt       lt description gt Connection Pool lt  description gt       lt res-ref-name gt jdbc RSGames lt  res-ref-name gt       lt res-type gt javax sql Datasource lt  res-type gt       lt res-auth gt Container lt  res-auth gt   lt  resource-ref gt    and modifying hibernate config like this    lt  xml version  1 0  encoding  UTF-8   gt   lt  DOCTYPE hibernate-configuration PUBLIC  -  Hibernate Hibernate Configuration DTD 3 0  EN   http   hibernate sourceforge net hibernate-configuration-3 0 dtd  gt   lt hibernate-configuration gt     lt session-factory gt             lt property name  connection datasource  gt java comp env jdbc RSGames lt  property gt           lt  session-factory gt   lt  hibernate-configuration gt    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  lt clinit gt  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

User · Answer

Tomcat-7 JNDI configuration   Steps    Open the server xml in the tomcat-dir conf Add below  lt Resource gt  tag with your DB details inside  lt GlobalNamingResources gt     lt Resource name  jdbc mydb            global  jdbc mydb            auth  Container            type  javax sql DataSource            driverClassName  com mysql jdbc Driver            url  jdbc mysql   localhost 3306 test            username  root            password              maxActive  10            maxIdle  10            minIdle  5            maxWait  10000   gt     Save the server xml file Open the context xml in the tomcat-dir conf Add the below  lt ResourceLink gt  inside the  lt Context gt  tag     lt ResourceLink name  jdbc mydb                 global  jdbc mydb                auth  Container                type  javax sql DataSource    gt     Save the context xml Open the hibernate-cfg xml file and add and remove below properties    Adding  -------  lt property name  connection datasource  gt java comp env jdbc mydb lt  property gt   Removing  --------  lt  -- lt property name  connection url  gt jdbc mysql   localhost 3306 mydb lt  property gt  -- gt   lt  -- lt property name  connection username  gt root lt  property gt  -- gt   lt  -- lt property name  connection password  gt  lt  property gt  -- gt     Save the file and put latest  WAR file in tomcat  Restart the tomcat  the DB connection will work

User · Answer

I was getting the same error in my IBM Websphere with c3p0 jar files  I have Oracle 10g database  I simply added the oraclejdbc jar files in the Application server JVM in IBM Classpath using Websphere Console and the error was resolved   The oraclejdbc jar should be set with your C3P0 jar files in your Server Class path whatever it be tomcat  glassfish of IBM

User · Answer

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    lt Context gt       lt 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   gt   lt  Context gt    2  web xml which tells the container that you are using this resource    lt resource-env-ref gt       lt resource-env-ref-name gt jdbc DsWebAppDB lt  resource-env-ref-name gt       lt resource-env-ref-type gt javax sql DataSource lt  resource-env-ref-type gt   lt  resource-env-ref gt    3  Hibernate configuration which consumes the data source  In this case  it s a persistence xml  but it s similar in hibernate cfg xml   lt persistence-unit name  dswebapp  gt       lt provider gt org hibernate ejb HibernatePersistence lt  provider gt       lt properties gt           lt property name  hibernate dialect  value  org hibernate dialect H2Dialect    gt           lt property name  hibernate connection datasource  value  java comp env jdbc DsWebAppDB   gt       lt  properties gt   lt  persistence-unit gt

User · Answer

Inside applicationContext xml file of a maven Hibernet web app project below settings worked for me    lt  xml version  1 0  encoding  UTF-8   gt   lt beans beans xmlns  http   www springframework org schema mvc      xmlns xsi  http   www w3 org 2001 XMLSchema-instance  xmlns beans  http   www springframework org schema beans      xmlns jee  http   www springframework org schema jee      xmlns context  http   www springframework org schema context  xmlns tx  http   www springframework org schema tx      xsi schemaLocation  http   www springframework org schema mvc http   www springframework org schema mvc spring-mvc xsd         http   www springframework org schema beans http   www springframework org schema beans spring-beans xsd         http   www springframework org schema context http   www springframework org schema context spring-context xsd         http   www springframework org schema tx http   www springframework org schema tx spring-tx-4 0 xsd         http   www springframework org schema jee          http   www springframework org schema jee spring-jee-3 0 xsd  gt         lt jee jndi-lookup id  dataSource                   jndi-name  Give DataSource Path From Your Server                   expected-type  javax sql DataSource    gt     Hope It will help someone Thanks

[java] Configure hibernate to connect to database via JNDI Datasource

Tomcat-7 JNDI configuration:

Steps:

Examples related to java

Examples related to hibernate

Examples related to jndi

Examples related to connection-pooling