Calling-Out to EJB Components in J2EE Servers from Java Stored Procedures

Table Of Contents

• Introduction
• Application Architecture Diagram
• Pre-Requisites
• Installation and Configuration
• Description of Sample files

This sample illustrates invoking EJB components running under an application server from within a Stored Procedure. Currently, it is not possible to invoke an EJB directly from the database. This sample application discusses a workaround for this -

• Callout a web application in the middle tier like a Java Server Page or Java Servlet from a stored procedure
  
  
• The Java Server Page or Java Servlet in turn invokes an Enterprise JavaBean which may or may not be co-located
  

The architecture of this sample application is depicted in Figure 1.1. For better understanding we shall consider a simple Entity bean Employee which maps to the employee table in the database. The employee information is managed through this Employee entity bean deployed in an application server like OC4J. A stateless session bean EmployeeInfo is used to retrieve the employee information from the entity bean.

We now have to access this Employee entity bean from the database. For this purpose, a Java Stored Procedure EJBCallout is loaded to the database. This procedure connects to a Servlet Employee. The Employee ID whose information has to be retrieved is passed to the Servlet from the Stored Procedure. The servlet invokes the EmployeeInfo session bean which in turn invokes the Employee entity bean. The employee information is passed back from the Employee entity bean to the Servlet as EmpDetails Value Object. This information is added to the output stream by the Servlet. The Java Stored Procedure reads the response from the Servlet and prints the data.

    Figure 1.1- Application Architecture Diagram

 

Pre-requisites

Back To Top

• Oracle9i Database version 9.2 or later. You can download the Oracle9i database from the Oracle Technology Network.
• Oracle9iAS Containers for J2EE (OC4J), 9.0.2 or later, available here.
  or
  Oracle9i JDeveloper available here
• If you wish to deploy the application to standalone OC4J then you must download and install the Jakarta Ant utility version 1.4 or later

The section has step-by-step instructions to run this application successfully.
This will be discussed in the following subsections

Notation

Extracting source code of the Application

Configuring the Application

Deploying the Application

Running the Application

Notation

• %SAMPLE_HOME% - The directory where the EJBCallout.jar file was unzipped to. This directory contains the JSP files, the SRC directories and ANT build script.



• %OC4J_HOME% - The directory where OC4J is installed. For example, if oc4j_extended.zip was unzipped to the C drive, then %OC4J_HOME% would be c:\.



• %ANT_HOME% - The directory where the Ant distribution has been unpacked.



• %JDEV_HOME% - The directory where Oracle9i JDeveloper is installed.

Extracting source code of the Application

The application files are stored in an archive named EJBCallout.jar. Download the archive and extract it to a convenient directory (%SAMPLE_HOME%). Execute following command to extract the files:

jar xvf EJBCallout.jar

The archive EJBCallout.jar extracts the files that implement the EJB Callout demo. All the files are extracted into the EJBCallout directory. This directory will have all the sources as described later.

Configuring the Application

1. This demo requires the Java Stored Procedure to be loaded to the database. This is done from a command prompt as shown below (Ensure the %ORACLE_HOME%\bin is added to your system path before you run this step)

loadjava -thin -verbose -user scott/tiger@<hostname>:<port>:<SID> %SAMPLE_HOME%/EJBCallout/src/EJBCallout.java %SAMPLE_HOME%/EJBCallout/src/oracle/otnsamples/dto/EmpDetails.java

where,

For example,

loadjava -thin -verbose -user scott/tiger@insn104a.idc.oracle.com:1521:otn9i c:/EJBCallout/src/EJBCallout.java c:/EJBCallout/src/oracle/otnsamples/dto/EmpDetails.java

2. Connect to your database as "scott" user and run the script publish.sql at the SQL prompt :
SQL>@%SAMPLE_HOME%\EJBCallout\config\publish.sql

This sql script publishes the stored procedure created in the previous step to the database

3. This demo requires some permissions to be granted to the database user to connect to the Java Servlet. For this you will need to connect to your database as sys or system user and run the following command

SQL> execute dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','<server-host-name>:<port>','connect,resolve');

You will need to modify server-host_name and port to point to your application server where the Servlet will be running. For example,

SQL> execute dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','incq184b.idc.oracle.com:8888','connect,resolve');

Deploying the Application

The application can be deployed to an OC4J instance in one of the following ways

Deploying the Application to Oracle9i JDeveloper's embedded OC4J

EAR( Enterprise Application aRchive ) Deployment

Deploying the Application to Oracle9i JDeveloper's embedded OC4J

1. Open Oracle9i JDeveloper.

2. Ensure that you create a database connection with the name Oracle. For this navigate to Connections-> Database-> New Connection. Specify the Connection name as Oracle, username and password as scott/tiger respectively. Also ensure that the hostname, SID and port that you specify point to your database.

3. Navigate to %SAMPLE_HOME%\EJBCallout

4. Open the file EJBCallout.jws and expand EJBCallout.jws node by clicking it.

5. Expand EJBCallout.jpr node by clicking it. The entire source code has been organized by directories.

6. Edit the servlet Employee.java. You will need to modify the following line :

env.put(Context.PROVIDER_URL, "ormi://localhost:<rmi-port>/current-workspace-app");

Change rmi-port to point to the right port. For example,

env.put(Context.PROVIDER_URL, "ormi://localhost:23781/current-workspace-app");

7. You can deploy the EJBs and Servlet to the embedded OC4Jsimply by clicking on Run EJBCallout.jpr on the Run Menu.

8. To now invoke these EJBs from the Stored Procedure refer to Running the application section.

EAR(Enterprise Application archive) Deployment

1. Edit the servlet Employee.java. You will need to modify the following line :

env.put(Context.PROVIDER_URL, "ormi://localhost:<rmi-port>/current-workspace-app");

Change rmi-port to point to the right port and the application name to ejbcallout. For example,

env.put(Context.PROVIDER_URL, "ormi://localhost:23791/ejbcallout");

2. Edit the file %SAMPLE_HOME%/EJBCallout/config/data-sources.xml to point to your database.

3.Ensure that the environment variables ANT_HOME,JAVA_HOME,OC4J_HOME and OC4J_HOST are set
For Windows Platform you will set the variables as shown below
C:\EJBCallout\set ANT_HOME=d:\ant141
C:\EJBCallout\set JAVA_HOME=d:\jdk1.3.1_01
C:\EJBCallout\set OC4J_HOME=d:\oc4j903
C:\EJBCallout\set OC4J_HOST=localhost:23791

For Linux Platform you will set the variables as shown below

home\EJBCallout>ANT_HOME=home\ant141
home\EJBCallout>export ANT_HOME
home\EJBCallout>JAVA_HOME=home\jdk1.3
home\EJBCallout>export JAVA_HOME
home\EJBCallout>OC4J_HOME=home\oc4j
home\EJBCallout>export OC4J_HOME
home\EJBCallout>OC4J_HOST=localhost:23791
home\EJBCallout>export OC4J_HOST

Note : You will need to have ANT_HOME/bin in your PATH.

4. Start OC4J .[ java -jar oc4j.jar from %OC4J_HOME%\j2ee\home]

5. Run ant from %SAMPLE_HOME%

6. This completes the deployment to the standalone OC4J server. You can access the application following the steps given under Running the application.

Running the Application

1. Connect to the database as scott/tiger

2. Edit the file SAMPLE_HOME\EJBCallout\config\invokeEJB.sql. Change the hostname and port to point to your application server.

3. From the SQL prompt, run the script invokeEJB.sql.

4. This will invoke the Java Stored Procedure and show the results.

5. You can edit invokeEJB.sql and change the empID to view the details of any employee.

 

Directory	Filename	Description
EJBCallout	build.xml	Project build file for generating deployment file using ANT
	EJBCallout.jws	Oracle9i JDeveloper workspace file
	EJBCallout.jpr	Oracle9i JDeveloper project file
EJBCallout\config	application.xml	Configuration file for the Application Server
	data-sources.xml	This file has the details of the datasource to be used by the application
	invokeEJB.sql	The sql script used to run the Java Stored Procedure and view the results from SQL prompt
	publish.sql	The sql script is used to publish the java stored procedure to the database
EJBCallout\doc	Readme.html	This file
EJBCallout\src	EJBCallout.java	Java Stored Procedure that calls out the Servlet
	oracle.otnsamples.entity	Classes that implement the Employee entity bean
	oracle.otnsamples.session	Classes that implement the EmployeeInfo session bean
	oracle.otnsamples.dto	Classes that EmpDetails Value object used to transfer Employee Data from the Entity to the Web tier
EJBCallout\web-inf	web.xml	Configuration file for the Application Server

Revision history: Jun 23, 2003

Please enter your comments about this sample in the OTN Sample Code Discussion Forum.