Getting a connection from a datasource...

[bill.may]

I need to use some straight SQL in my application. I was wondering if there is a way to grab a connection from the connection pool in my datasouce, use that connection as one would w/ JDBC and then return it.

Thank you.

[hakucho]

You simply look up your DataSource via JNDI and call getConnection() on it, for example:

InitialContext ic = new InitialContext();
 DataSource dataSource =
 (DataSource) ic.lookup("java:comp/env/jdbc/myDataSource");
 Connection conn = dataSource.getConnection
 // use the connection for JDBC like normal, make sure to close it afterwards

Note that the EJB doing the JNDI lookup needs to have the DataSource declared in its name space as follows (in ejb-jar.xml):

<session>
 <ejb-name>MyEJB</ejb-name>
 ...
 <resource-ref>
 <!-- logical name chosen by bean provider -->
 <res-ref-name>jdbc/myDataSource</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
 </resource-ref>
 </session>

... and in jboss.xml:

<session>
 <ejb-name>MyEJB</ejb-name>
 ...
 <resource-ref>
 <!-- same logical name chosen by bean provider -->
 <res-ref-name>jdbc/myDataSource</res-ref-name>
 <!-- real JNDI name chosen by server admin -->
 <jndi-name>java:/theDataSource</jndi-name>
 </resource-ref>
 </session>

... and in the blah-ds.xml in the deploy folder:

<?xml version="1.0" ?>
<datasources>
 <local-tx-datasource>
 <!-- real JNDI name chosen by server admin -->
 <jndi-name>theDataSource</jndi-name>
 <connection-url>blah</connection-url>
 <driver-class>com.blah.Blah</driver-class>
 <user-name>blah</user-name>
 <password>halb</password>
 <min-pool-size>10</min-pool-size>
 <max-pool-size>100</max-pool-size>
 <blocking-timeout-millis>5000</blocking-timeout-millis>
 <idle-timeout-minutes>15</idle-timeout-minutes>
 </local-tx-datasource>
</datasources>

Hope this is what you were asking!

Andrew

[bill.may]

Thank you for your reply! I got it working, I was missing java: in front of my request for the context.

Ignorant me.

Now, another question. Will the datasource act as a connection pool?

[hakucho]

That depends which DataSource implementation you happen to be using. I know that the excellent Spring project for example includes several implementations, some of which use a pool and some of which do not.

To find out about yours, simply log the class of the DataSource instance after you look it up via JNDI:

LOGGER.debug("DataSource is a " + dataSource.getClass().getName());

then look up the JavaDoc or other doco for that class.