Oracle8i Enterprise JavaBeans Developer's Guide and Reference
Release 3 (8.1.7)

Part Number A83725-01


Solution Area



Go to previous page Go to beginning of chapter Go to next page

SQLJ Example



This example demonstrates doing a database query using SQLJ. pay
attention to the makefile (UNIX) or the makeit.bat batch file (Windows
NT), and note that the files that SQLJ generates (SER files converted
to class files) must be loaded into the database with deployejb also.

Compare this example with the jdbcimpl basic EJB example, which uses
JDBC instead of SQLJ to perform exactly the same query.

Source files

Invoke the client program from the command line, passing it four
   - the name of the service URL, e.g. sess_iiop://localhost:2222
   - the path and name of the published bean, e.g. /test/employeeBean
   - the username for db authentication
   - the password (you wouldn't do this in a production program, of course)

For example

% java Client -classpath LIBs sess_iiop://localhost:2222 /test/employeeBean
   scott tiger

The client looks up and activates the bean, then invokes the query() method on
the bean. query() returns an EmpRecord structure with the salary and the name
of the employee whose ID number was passed to query().

There is no error checking in this code. See the User's Guide for more
information about the appropriate kinds of error checking in this kind of
client code.

The client prints:

Emp name is ALLEN
Emp sal  is 3100.0


This class is the bean implementation. A SQLJ named iterator is declared to
hold the results of the query. The; statement is used as is to
keep the code simple: after all the parameter passed in is a known valid
primary key for the EMP table. (See what happens if you try an empno that is
not in the table.)

The EmpIter getter methods are used to retrieve the query results into the
EmpRecord object, which is then returned *by value*, as a serialized object,
to the client.


A class that is in essence a struct to contain the employee name and salary,
as well as the ID number.

Note that the class *must* be defined as implementing the java.rmi.Serializable
interface, to make it a valid serializable RMI object that can be passed from
server to the client.


The bean remote interface. 


The bean home interface.

Compiling and Running the Example


Enter the command 'make all' or simply 'make' in the shell to compile,
load, and deploy the objects, and run the client program.  Other
targets are 'run' and 'clean'.

Make sure that a shell environment variable ORACLE_HOME is set to
point to the home location of the Oracle installation. This is
operating system dependent, so see the Installation documentation that
came with your system for the location. Also, review the README file
for the Oracle database, and the README file for the CORBA/EJB server
(the Oracle8i ORB), for additional up-to-date information.

Windows NT

On Windows NT, run the batch file makeit.bat from a DOS command prompt
to compile, load, and deploy the objects. Run the batch file runit.bat
to run the client program, and see the results.

Make sure that the environment variables %ORACLE_HOME%, %CLASSPATH%,
and %SERVICE% are set appropriately for the DOS command window. You
can set these as either user or system environment variables from the
Control Panel. Double click on System in the Control Panel then on
the Environment tab to set these variables. Start a new DOS window
after setting environment variable values.

See the Installation documentation that came with your Oracle8i system
for the values of these variables. Also, review the README file for
the Oracle database, and the README file for the CORBA/EJB server (the
Oracle8i ORB), for additional up-to-date information.

You can also set an environment variable %JAVA_HOME% to point to the
root of your Java JDK. For example, SET JAVA_HOME=C:\JDK1.1.6.


import employee.Employee;
import employee.EmployeeHome;
import employee.EmpRecord;

import oracle.aurora.jndi.sess_iiop.ServiceCtx;

import javax.naming.Context;
import javax.naming.InitialContext;
import java.util.Hashtable;

public class Client {
  public static void main (String [] args) throws Exception {
    if (args.length != 4) {
      System.out.println("usage: Client serviceURL objectName user password");
    String serviceURL = args [0];
    String objectName = args [1];
    String user = args [2];
    String password = args [3];

    Hashtable env = new Hashtable();
    env.put(Context.URL_PKG_PREFIXES, "oracle.aurora.jndi");
    env.put(Context.SECURITY_PRINCIPAL, user);
    env.put(Context.SECURITY_CREDENTIALS, password);
    Context ic = new InitialContext (env);

    EmployeeHome home = (EmployeeHome)ic.lookup (serviceURL + objectName);
    Employee testBean = home.create();
    EmpRecord empRec = empRec = testBean.query (7499);
    System.out.println ("Emp name is " + empRec.ename);
    System.out.println ("Emp sal  is " + empRec.sal);

Home Interface

package employee;

import javax.ejb.*;
import java.rmi.RemoteException;

public interface EmployeeHome extends EJBHome {
  public Employee create()
       throws CreateException, RemoteException;

Remote Interface

package employee;

import javax.ejb.EJBObject;
import java.rmi.RemoteException;

public interface Employee extends EJBObject  {
  public EmpRecord query (int empNumber)
       throws java.sql.SQLException, RemoteException;

Bean Implementation


package employeeServer;

import employee.EmpRecord;

import java.sql.*;
import java.rmi.RemoteException;
import javax.ejb.*;

public class EmployeeBean implements SessionBean {
  //SessionContext ctx;

  public void ejbCreate() throws CreateException, RemoteException {}
  public void ejbActivate() {}
  public void ejbPassivate() {}
  public void ejbRemove() {}
  public void setSessionContext(SessionContext ctx) {
    //this.ctx = ctx;

  public EmpRecord query (int empNumber) throws SQLException, RemoteException
    String ename;
    double sal;

    #sql { select ename, sal into :ename, :sal from emp 
                  where empno = :empNumber }; 
System.out.println ("ename = " + ename);
System.out.println ("sal   = " + sal);

    return new EmpRecord (ename, empNumber, sal);

package employee;

public class EmpRecord implements {
  public String ename;
  public int empno;
  public double sal;

  public EmpRecord (String ename, int empno, double sal) {
    this.ename = ename;
    this.empno = empno;
    this.sal = sal;

Go to previous page
Go to beginning of chapter
Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.


Solution Area