Application Techniques

Using JDBC with Session Beans

How to write a session bean that retrieves data using a JDBC connection to a SilverStream database:

About this technique	Details
Category	Enterprise JavaBean Techniques
Description	You'll learn about: About this example Accessing JDBC from a session bean
	You can run this technique code from: Database: Examples37_EJB Page: pgStateTaxCalculationJDBC.html NOTE   First make sure that database is running on your localhost SilverStream server
Related reading	See the Developing EJBs section in the Programmer's Guide

Session beans can access data from entity beans, from AgaDatas, or directly from a database using JDBC. Accessing data using JDBC allows the session bean to be portable across EJB servers but makes the session bean database dependent. The code shown in this example is portable because it is J2EE standard.

About this example

You run this example from the pgStateTaxCalculationJDBC.html page. The page displays an HTML view that contains a list of the states and their tax rate. (The view is bound to the page's agData and is not described in this technique.)

The page allows users to enter a price and a state code. When users press the Calculate Tax button, the page accesses the SBStateSalesTaxJDBC session bean (following the typical access to the bean's home and remote interfaces.) The SBStateSalesTaxJDBC session bean makes JDBC calls to retrieve the state tax rate for the user-entered state code, then uses that tax rate and the user-entered price to calculate the total cost of an item.

Here is a brief summary of the application components:

Component	Description
SBStateSalesTaxJDBCBean	The State Sales Tax Session Bean This bean includes all of the JDBC code
SBStateSalesTaxHome	The SBStateSalesTaxJDBCBean's home interface
SBStateSalesTaxRemote	The SBStateSalesTaxJDBCBean's remote interface
SBStateSalesTaxJDBC.jar	The EJB JAR
SBStateSalesTaxJDBCDeplPlan	The EJB Deployment Plan
SBStateSalesTaxJDBCRemote.jar	The SBStateSalesTaxJDBCBean's remote jar
SBStateSalesTaxJDBCDeployed	The deployed object
pgStateTaxCalculationJDBC.html	The user interface for accessing the EJB components

Accessing JDBC from a session bean

Accessing JDBC from a session bean is straightforward. You need to:

• Import these packages (in addition to those normally required for EJB access):

  • java.sql.* -- For the connection-related tasks

  • javax.sql.DataSource -- For DataSource related tasks

• Define a resource reference lookup in your session bean.

  A resource reference is a JNDI name that resolves to a DataSource (javax.sql.DataSource) object. This DataSource object gives you access to the JDBC connection pool. Resource references are stored in the java:comp/env context.

• Construct the JDBC calls that access the data. The JDBC calls should:

  • Get a database connection from SilverStream's connection pool.

  • Construct, compile, and run the SQL statement that returns the required data.

  • Close the database connection when done. (SilverStream automatically returns the connection to the pool on close()).

• Create an environment entry in the deployment descriptor for the session bean when you add it to the EJB JAR.

  The environment entry for the session bean must be of type resource reference. By convention, DataSource references are named jdbc/xxx. This name must exactly match the name on which the session bean does the environment context lookup.

• Map the resource reference to a SilverStream database when you create a deployment plan for the session bean.

Defining a resource reference lookup in the session bean

Your session bean can obtain a javax.sql.DataSource object by doing a JNDI lookup in the java:comp/env context. In this example, the resource reference in the deployment descriptor is:

  /jdbc/StateSalesTaxDataSource

So a lookup for this entry in the java:comp/env context looks like this:

  String dataSourceName =
     "java:comp/env/jdbc/StateSalesTaxDataSource";
  m_jdbcSource = (DataSource)ctx.lookup(dataSourceName);	 

where m_jdbcSource is defined as:

  private DataSource m_jdbcSource = null;

Note the following about this code:

• A resource reference lookup allows you to simply cast the object to the correct type instead of using the PortableRemoteObject.narrow() method (unlike lookups for EJB home and remote references, which do require the narrow()). Because the SilverStream container does not store EJB environment entries and resource references in the external naming system, a PortableRemoteObject.narrow() is not needed.

• A DataSource object is a factory for Connection objects.

• If the ctx.lookup() fails, the application cannot proceed. It is an unrecoverable error, so it throws an EJBException rather than an application exception.

Constructing the JDBC calls

The JDBC calls in this example:

• Get a java.sql.Connection from the connection pool

• Construct, compile, and execute a SQL Select statement

• Manipulate the query's ResultSet

For an example of more complex JDBC calls, see the JDBC version of the BankDemo application in the EJB Examples database. It includes examples of updates, commits, and rollbacks.

Getting a Connection

You use the javax.sql.DataSource object m_jdbcSource to get a connection as follows:

  connection = m_jdbcSource.getConnection();

where connection is a java.sql.Connection object initialized earlier to null:

  Connection connection = null ;

Constructing, compiling, and executing a SQL Select statement

This example needs to obtain the tax rate (tax_rate) for the state code (state_id) that was passed to the session bean from the calling page (the psStateID parameter). The Select statement looks like this:

  String sStatement = "SELECT state_id, tax_rate, state_name " +
     "FROM States " + "WHERE state_id = '" + psStateID + "'";

To precompile the statement, you call the connection.prepareStatement() method passing in a String representing the SQL you constructed:

  preparedStatement = connection.prepareStatement(sStatement);

Once you have a compiled SQL query (a javax.sql.PreparedStatement), you can execute the SQL using the executeQuery() method:

  resultSet = preparedStatement.executeQuery();

The resultSet is defined earlier as a java.sql.ResultSet object like this:

  ResultSet resultSet = null;

Manipulating the query's ResultSet and returning it to the caller

The important part of the ResultSet is the tax rate which is returned as the second column. To get the second column, use the getObject(index) method like this:

  Object objData1 = resultSet.getObject(1);
  Object objData2 = resultSet.getObject(2);
  Object objData3 = resultSet.getObject(3);

Convert the price (psPrice passed in from the page) from a String to a BigDecimal so that you can perform calculations with it:

     BigDecimal bdPrice = new BigDecimal (psPrice);

Cast the tax rate to a BigDecimal variable (the resultSet.getObject() returns the BigDecimal value as an Object):

     BigDecimal bdTaxRate = (BigDecimal) objData2;

Because the tax rate is only the amount of the tax, add 1 to it so that when it is multiplied, the total price (including the tax) is calculated, not just the sales tax amount:

  bdTaxRate = bdTaxRate.add(new BigDecimal(1) );
  BigDecimal bdTotal = bdPrice.multiply(bdTaxRate) ;

Convert the total to a String and return it to the caller:

  String sTotal = bdTotal.toString();
  return sTotal; 

Finally, close the preparedStatement, the resultSet, and the Connection objects:

  try {
       if (preparedStatement != null)
         preparedStatement.close();
       if (resultSet != null)
         resultSet.close();
       if (connection != null)
         connection.close();
  }
    catch (Exception e) {
  }

Creating an environment entry

So that the session bean's environment context lookup actually finds a javax.sql.DataSource object, the deployment descriptor for the session bean must include a resource reference entry. A resource reference is considered an environment entry like a role reference or a bean reference.

The resource reference entry in the deployment descriptor must exactly match the session bean's lookup. For this example, the name must match /jdbc/StateSalesTaxDataSource. Since it is defined as a resource reference, the SilverStream container will store it in the java:comp/env context.

Here is what the entry for this example looks like:

Mapping the resource reference to a SilverStream database

For the container to be able to resolve a resource reference to a database at runtime, the resource reference must be mapped in the deployment plan.

The database must be a SilverStream database and must already be added to the server before you can map it.

Here is what the entry for this example looks like:

    For more information on mapping resource references to SilverStream databases, see the Deployment Plan Designer chapter in the online Tools Guide.

Application Techniques

Copyright © 2001, SilverStream Software, Inc. All rights reserved.