Accessing a Database Connection Pool from a Session EJB in JBoss AS

There may be situations in which it makes sense to access data from an EJB session bean using JDBC.  Perhaps you are only reading values from a table and since you are not making any changes to the table, using an entity bean may seem like overkill.  In this post I cover setting up a database connection pool in JBoss and accessing the database using JDBC from an EJB session bean.

Configuring the Datasource

Creating the connection pool requires copying a -ds.xml file to the deploy directory in your JBoss server.  This datasource XML file is no different from any other datasource file.  If you’ve been working with EJB, you’ve already done this, but here’s an example:

<datasources>
	<no-tx-datasource>
		<jndi-name>jdbc/oxb-util</jndi-name>
		<connection-url>jdbc:mysql://localhost:3306/utilDB</connection-url>
		<driver-class>com.mysql.jdbc.Driver</driver-class>
		<user-name>user123</user-name>
		<password>abc123</password>
		<use-java-context>false</use-java-context>
		<!--security-domain>domainX</security-domain-->
		<idle-timeout-minutes>5</idle-timeout-minutes>

		<!--Defaults -->
		<!--min-pool-size>0</min-pool-size-->
		<!--max-pool-size>20</max-pool-size-->
	</no-tx-datasource>
</datasources>

Accessing the Connection Pool through a SessionContext from a Session EJB
One way to access the connection pool is by injecting a SessionContext into your Session EJB and then gaining access to the datasource connection.

Injection of the session context is acomplished through the @javax.annotation.Resource annotation:

@Stateless(name = "SetupServiceEJB")
public class SetupServiceBean implements SetupServiceRemoteSession {
	@Resource
	SessionContext ejbContext;
	private Connection connection;

You can then access a connection as follows:

	private Connection getConnection() throws SQLException {
		if (this.connection == null) {
			DataSource source = (DataSource) ejbContext.lookup("jdbc/oxb-util");
			connection = source.getConnection();
		}
		return this.connection;
	}

Here’s an example of a method that performs a query using this getConnection() method:

	public void createLocations() {
		String query = "select * from country";
		try {
			Connection con = this.getConnection();
			Statement statement = con.createStatement();
			ResultSet set = statement.executeQuery(query);

			// do something with the result set...

Accessing the Connection Pool through a DataSource from a Session EJB
A second alternative is to inject into your session EJB a DataSource as follows:

@Stateless(name = "SetupServiceEJB")
public class SetupServiceBean implements SetupServiceRemoteSession {
	@Resource(name = "jdbc/oxb-util", type = DataSource.class, mappedName = "jdbc/oxb-util")
	DataSource datasource;
	private Connection connection;

Accessing the connection would then be done as follows:

		String query = "select * from country limit 20";

		try {
			Connection con = this.datasource.getConnection();
			Statement statement = con.createStatement();
			ResultSet set = statement.executeQuery(query);

			use result set here...