Hibernate and Tomcat Database Connection Pooling

1. Tomcat Database Connection Pooling (should have been an older post)
usage in a web application

1a. define db connection in context.xml in META-INF

e.g.
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/gripswsmx-hibernated">
<Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver" maxActive="20" maxIdle="10" maxWait="-1" name="jdbc/GRIPSHI" password="gripshi" type="javax.sql.DataSource" url="jdbc:oracle:thin:@192.168.2.94:1521:ORCL" username="gripshi" validationQuery="select 1 from dual"/>
</Context>

note that Context path must correspond to the web application name
validationQuery is vital to keep the connection alive

once deployed this file is moved to catalina conf/Catalina/localhost under the name of the web application / context path - so, if there are changes in db connection this file has to be edited (to avoid a new deployment)

1b. in web.xml define the connection name e.g. jdbc/GRIPSHI

<resource-ref>
<description>GRIPS MX web service DataSource Reference</description>
<res-ref-name>jdbc/GRIPSHI</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

2. Hibernate
in hibernate.properties or in hibernate.cfg.xml
2a) specify properties
hibernate.dialect
hibernate.connection.datasource pointing to the name specified in web.xml

e.g. in hibernate.cfg.xml
<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
<property name="hibernate.connection.datasource">java:comp/env/jdbc/GRIPSHI</property>

2b) make sure the rest of hibernate properties for db connection
i.e.
hibernate.connection.driver_class
hibernate.default_schema
hibernate.connection.url
hibernate.connection.username
hibernate.connection.password
are omitted


see also Using Hibernate with Tomcat - C3P0 connection pool