Jump to: navigation, search

Difference between revisions of "Jetty/Howto/Configure JNDI Datasource"

< Jetty‎ | Howto
 
m (Included validationQuery in DBCP config properties.)
(9 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{Jetty Howto
 
{{Jetty Howto
 
| introduction =
 
| introduction =
Below are examples of setting up a JNDI datasource for various databases.
+
This document provides examples of configuring a JNDI datasource for various databases.
  
{{tip|Tip:|Please read through the generic [[Jetty/Feature/JNDI|JNDI]] instructions for the background to configuring datasources.}}
+
{{tip|Tip:|Read the generic [[Jetty/Feature/JNDI|JNDI]] instructions for background information about configuring datasources.}}
  
These examples all correspond to a <code><resource-ref></code> in <code>web.xml</code> like:
+
These examples all correspond to a <code><resource-ref></code> in <code>web.xml</code>:
 
   
 
   
 
<source lang="xml">
 
<source lang="xml">
Line 16: Line 16:
 
</source>
 
</source>
  
For convenience, we will assume that all of the datasources are declared at the JVM scope, but you can of course use other scopes, as discussed on the page on [[Jetty/Feature/JNDI|JNDI]].
 
  
Don't forget that all JNDI resources can be configured in a jetty.xml file or in a <code>WEB-INF/jetty-env.xml</code> file, or a context xml file. More information on that can be found on the page on [[Jetty/Feature/JNDI|JNDI]].
+
These examples assume that all of the datasources are declared at the JVM scope, but you can, of course, use other scopes. You can configure all JNDI resources in a <code>jetty.xml</code> file or in a <code>WEB-INF/jetty-env.xml</code> file, or a context XML file. For more information, see [[Jetty/Feature/JNDI|JNDI]].
 +
 
 +
{{tip|Reminder|Be aware that you have to provide jetty with the libraries necessary to instantiate the datasource you have configured by putting the corresponding jar in jetty_home/lib/ext.}}
  
| examples =
 
 
==Pooling DataSources==
 
==Pooling DataSources==
  
Enables connection pooling. Connection pooling is basically re-using existing connections instead of creating a new connection to the database.This would be highly efficient in terms of memory allocation and speed of the request to the database. In production, this is '''highly''' recommended.
+
Enables connection pooling. Connection pooling lets you reuse an existing connection instead of creating a new connection to the database.This is highly efficient in terms of memory allocation and speed of the request to the database. We highly recommend this option for production environments.
 +
 
 +
===BoneCP===
 +
 
 +
Connection pooling, available at http://jolbox.com/index.html?page=http://jolbox.com/download.html
 +
All configuration options for BoneCP are described here: http://jolbox.com/bonecp/downloads/site/apidocs/com/jolbox/bonecp/BoneCPDataSource.html
 +
 
 +
<source lang="xml">
 +
  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 +
    <Arg></Arg>
 +
    <Arg>jdbc/DSTest</Arg>
 +
    <Arg>
 +
      <New class="com.jolbox.bonecp.BoneCPDataSource">
 +
        <Set name="driverClass">com.mysql.jdbc.Driver</Set>
 +
        <Set name="jdbcUrl">jdbc.url</Set>
 +
        <Set name="username">jdbc.user</Set>
 +
        <Set name="password">jdbc.pass</Set>
 +
        <Set name="minConnectionsPerPartition">5</Set>
 +
        <Set name="maxConnectionsPerPartition">50</Set>
 +
        <Set name="acquireIncrement">5</Set>
 +
        <Set name="idleConnectionTestPeriod">30</Set>
 +
      </New>
 +
    </Arg>
 +
  </New>
 +
</source>
  
 
===c3p0===
 
===c3p0===
Line 30: Line 54:
  
 
<source lang="xml">
 
<source lang="xml">
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 49: Line 73:
  
 
<source lang="xml">
 
<source lang="xml">
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 58: Line 82:
 
             <Set name="username">jdbc.user</Set>
 
             <Set name="username">jdbc.user</Set>
 
             <Set name="password">jdbc.pass</Set>
 
             <Set name="password">jdbc.pass</Set>
 +
            <Set name="validationQuery">SELECT 1</Set>
 
         </New>
 
         </New>
 
     </Arg>
 
     </Arg>
 
     </New>
 
     </New>
 
</source>
 
</source>
 +
 +
Note: ''validationQuery'' is optional, and works together with the ''testOnBorrow'' parameter (which defaults to ''true'' but has no effect unless ''validationQuery'' is specified) to help ensure that old connections are valid when used.  See http://commons.apache.org/dbcp/configuration.html
  
 
===Atomikos 3.3.2+===
 
===Atomikos 3.3.2+===
Line 68: Line 95:
  
 
<source lang="xml">
 
<source lang="xml">
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
       <Arg></Arg>
 
       <Arg></Arg>
 
       <Arg>jdbc/DSTest</Arg>
 
       <Arg>jdbc/DSTest</Arg>
Line 95: Line 122:
 
     </New>
 
     </New>
 
</source>
 
</source>
 +
 +
  
 
==Non-pooling DataSources==
 
==Non-pooling DataSources==
  
If you're deploying in production environment, use the Pooling DataSources instead.
+
If you are deploying in a production environment, use the Pooling DataSources instead.
  
 
===MySQL===
 
===MySQL===
Line 105: Line 134:
  
 
<source lang="xml">  
 
<source lang="xml">  
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 117: Line 146:
 
     </New>
 
     </New>
 
</source>
 
</source>
 +
  
 
===SQL Server 2000===
 
===SQL Server 2000===
  
Implements javax.sql.DataSource, javax.sql.ConnectionPoolDataSource\
+
Implements javax.sql.DataSource, javax.sql.ConnectionPoolDataSource
  
 
<source lang="xml">
 
<source lang="xml">
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 136: Line 166:
 
     </Arg>
 
     </Arg>
 
     </New>
 
     </New>
</source
+
</source>
  
 
===Oracle 9i/10g===
 
===Oracle 9i/10g===
Line 143: Line 173:
  
 
<source lang="xml">  
 
<source lang="xml">  
<New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
    <Arg></Arg>
+
    <Arg></Arg>
    <Arg>jdbc/DSTest</Arg>
+
    <Arg>jdbc/DSTest</Arg>
    <Arg>
+
    <Arg>
       <New class="oracle.jdbc.pool.OracleConnectionPoolDataSource">
+
       <New class="oracle.jdbc.pool.OracleDataSource">
        <Set name="URL">jdbc:oracle:thin:@localhost:1521:orcl</Set>
+
        <Set name="DriverType">thin</Set>
        <Set name="User">user</Set>
+
        <Set name="URL">jdbc:oracle:thin:@fmsswdb1:10017:otcd</Set>
        <Set name="Password">pass</Set>
+
        <Set name="User">xxxx</Set>
 +
        <Set name="Password">xxxx</Set>
 +
        <Set name="connectionCachingEnabled">true</Set>
 +
        <Set name="connectionCacheProperties">
 +
          <New class="java.util.Properties">
 +
            <Call name="setProperty">
 +
              <Arg>MinLimit</Arg>
 +
              <Arg>5</Arg>
 +
            </Call>
 +
            <!-- put the other properties in here too -->
 +
          </New>
 +
        </Set>
 
       </New>
 
       </New>
    </Arg>
+
    </Arg>
</New>
+
  </New>
 
</source>
 
</source>
 +
 +
For more information please see http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/conncache.htm#CDEBCBJC.
  
 
===PostgreSQL===
 
===PostgreSQL===
Line 161: Line 204:
  
 
<source lang="xml">
 
<source lang="xml">
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 179: Line 222:
  
 
<source lang="xml">  
 
<source lang="xml">  
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 200: Line 243:
  
 
<source lang="xml">
 
<source lang="xml">
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 220: Line 263:
  
 
<source lang="xml">  
 
<source lang="xml">  
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 238: Line 281:
  
 
<source lang="xml">
 
<source lang="xml">
   <New id="DSTest" class="org.eclipse.jetty.plus.naming.Resource">
+
   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
 
     <Arg></Arg>
 
     <Arg></Arg>
 
     <Arg>jdbc/DSTest</Arg>
 
     <Arg>jdbc/DSTest</Arg>
Line 253: Line 296:
 
</source>
 
</source>
  
| category = [[Category:Jetty Howto]]
 
 
}}
 
}}

Revision as of 11:27, 15 July 2012



Introduction

This document provides examples of configuring a JNDI datasource for various databases.

Idea.png
Tip:
Read the generic JNDI instructions for background information about configuring datasources.


These examples all correspond to a <resource-ref> in web.xml:

  <resource-ref>
     <description>My DataSource Reference</description>
     <res-ref-name>jdbc/DSTest</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
  </resource-ref>


These examples assume that all of the datasources are declared at the JVM scope, but you can, of course, use other scopes. You can configure all JNDI resources in a jetty.xml file or in a WEB-INF/jetty-env.xml file, or a context XML file. For more information, see JNDI.

Idea.png
Reminder
Be aware that you have to provide jetty with the libraries necessary to instantiate the datasource you have configured by putting the corresponding jar in jetty_home/lib/ext.


Pooling DataSources

Enables connection pooling. Connection pooling lets you reuse an existing connection instead of creating a new connection to the database.This is highly efficient in terms of memory allocation and speed of the request to the database. We highly recommend this option for production environments.

BoneCP

Connection pooling, available at http://jolbox.com/index.html?page=http://jolbox.com/download.html All configuration options for BoneCP are described here: http://jolbox.com/bonecp/downloads/site/apidocs/com/jolbox/bonecp/BoneCPDataSource.html

  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
       <New class="com.jolbox.bonecp.BoneCPDataSource">
         <Set name="driverClass">com.mysql.jdbc.Driver</Set>
         <Set name="jdbcUrl">jdbc.url</Set>
         <Set name="username">jdbc.user</Set>
         <Set name="password">jdbc.pass</Set>
         <Set name="minConnectionsPerPartition">5</Set>
         <Set name="maxConnectionsPerPartition">50</Set>
         <Set name="acquireIncrement">5</Set>
         <Set name="idleConnectionTestPeriod">30</Set>
      </New>
    </Arg>
  </New>

c3p0

Connection pooling, available at http://repo1.maven.org/maven2/c3p0/c3p0/0.9.1.2/c3p0-0.9.1.2.jar

  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
      <New class="com.mchange.v2.c3p0.ComboPooledDataSource">
         <Set name="driverClass">org.some.Driver</Set>
         <Set name="jdbcUrl">jdbc.url</Set>
         <Set name="user">jdbc.user</Set>
         <Set name="password">jdbc.pass</Set>
      </New>
     </Arg>
    </New>

dbcp

Connection pooling, available at http://repo1.maven.org/maven2/commons-dbcp/commons-dbcp/1.2/commons-dbcp-1.2.jar

  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
         <New class="org.apache.commons.dbcp.BasicDataSource">
            <Set name="driverClassName">org.some.Driver</Set>
            <Set name="url">jdbc.url</Set>
            <Set name="username">jdbc.user</Set>
            <Set name="password">jdbc.pass</Set>
            <Set name="validationQuery">SELECT 1</Set>
         </New>
     </Arg>
    </New>

Note: validationQuery is optional, and works together with the testOnBorrow parameter (which defaults to true but has no effect unless validationQuery is specified) to help ensure that old connections are valid when used. See http://commons.apache.org/dbcp/configuration.html

Atomikos 3.3.2+

Connection pooling + XA transactions.

   <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
      <Arg></Arg>
      <Arg>jdbc/DSTest</Arg>
      <Arg>
         <New class="com.atomikos.jdbc.AtomikosDataSourceBean">
            <Set name="minPoolSize">2</Set>
            <Set name="maxPoolSize">50</Set>
            <Set name="xaDataSourceClassName">com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</Set>
            <Set name="UniqueResourceName">DSTest</Set>
            <Get name="xaProperties">
               <Call name="setProperty">
                  <Arg>url</Arg>
                  <Arg>jdbc:mysql://localhost:3306/databasename</Arg>
               </Call>
               <Call name="setProperty">
                  <Arg>user</Arg>
                  <Arg>some_username</Arg>
               </Call>
               <Call name="setProperty">
                  <Arg>password</Arg>
                  <Arg>some_password</Arg>
               </Call>
            </Get>
         </New>
      </Arg>
    </New>


Non-pooling DataSources

If you are deploying in a production environment, use the Pooling DataSources instead.

MySQL

Implements javax.sql.DataSource, javax.sql.ConnectionPoolDataSource

 
  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
        <New class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
           <Set name="Url">jdbc:mysql://localhost:3306/databasename</Set>
           <Set name="User">user</Set>
           <Set name="Password">pass</Set>
        </New>
     </Arg>
    </New>


SQL Server 2000

Implements javax.sql.DataSource, javax.sql.ConnectionPoolDataSource

  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
        <New class="net.sourceforge.jtds.jdbcx.JtdsDataSource">
           <Set name="User">user</Set>
           <Set name="Password">pass</Set>
           <Set name="DatabaseName">dbname</Set>
           <Set name="ServerName">localhost</Set>
           <Set name="PortNumber">1433</Set>
        </New>
     </Arg>
    </New>

Oracle 9i/10g

Implements javax.sql.DataSource, javax.sql.ConnectionPoolDataSource

 
  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
    <Arg></Arg>
    <Arg>jdbc/DSTest</Arg>
    <Arg>
      <New class="oracle.jdbc.pool.OracleDataSource">
        <Set name="DriverType">thin</Set>
        <Set name="URL">jdbc:oracle:thin:@fmsswdb1:10017:otcd</Set>
        <Set name="User">xxxx</Set>
        <Set name="Password">xxxx</Set>
        <Set name="connectionCachingEnabled">true</Set>
        <Set name="connectionCacheProperties">
          <New class="java.util.Properties">
            <Call name="setProperty">
              <Arg>MinLimit</Arg>
              <Arg>5</Arg>
            </Call>
            <!-- put the other properties in here too -->
          </New>
        </Set>
      </New>
    </Arg>
  </New>

For more information please see http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/conncache.htm#CDEBCBJC.

PostgreSQL

Implements javax.sql.DataSource

  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
        <New class="org.postgresql.ds.PGSimpleDataSource">
           <Set name="User">user</Set>
           <Set name="Password">pass</Set>
           <Set name="DatabaseName">dbname</Set>
           <Set name="ServerName">localhost</Set>
           <Set name="PortNumber">5432</Set>
        </New>
     </Arg>
  </New>

Implements javax.sql.ConnectionPoolDataSource

 
  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
        <New class="org.postgresql.ds.PGConnectionPoolDataSource">
           <Set name="User">user</Set>
           <Set name="Password">pass</Set>
           <Set name="DatabaseName">dbname</Set>
           <Set name="ServerName">localhost</Set>
           <Set name="PortNumber">5432</Set>
 
        </New>
     </Arg>
  </New>

Sybase

Implements javax.sql.DataSource

  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
        <New class="com.sybase.jdbc2.jdbc.SybDataSource">
           <Set name="DatabaseName">dbname</Set>
           <Set name="User">user</Set>
           <Set name="Password">pass</Set>
           <Set name="ServerName">servername</Set>
           <Set name="PortNumber">5000</Set>
        </New>
     </Arg>
  </New>

DB2

Implements javax.sql.DataSource

 
  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
        <New class="com.ibm.db2.jcc.DB2SimpleDataSource">
           <Set name="DatabaseName">dbname</Set>
           <Set name="User">user</Set>
           <Set name="Password">pass</Set>
           <Set name="ServerName">servername</Set>
           <Set name="PortNumber">50000</Set>
        </New>
     </Arg>
  </New>

Implements javax.sql.ConnectionPoolDataSource

  <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
     <Arg></Arg>
     <Arg>jdbc/DSTest</Arg>
     <Arg>
        <New class="com.ibm.db2.jcc.DB2ConnectionPoolDataSource">
           <Set name="DatabaseName">dbname</Set>
           <Set name="User">user</Set>
           <Set name="Password">pass</Set>
           <Set name="ServerName">servername</Set>
           <Set name="PortNumber">50000</Set>
        </New>
     </Arg>
  </New>