A Generated Code Examples

This appendix contains generated code examples that are explained in the following sections:

Generated Code: SQL Statement
Generated Code: Server-Side Java Call-in

Gener ated Code: SQL Statement

This section contains a generated code example for a specified SQL statement, related to the discussion in "Declaration of SQL Statements to Translate".

The example is for the following sample settings of the -sqlstatement option:

-sqlstatement.class=MySqlStatements
-sqlstatement.getEmp="select ename from emp
                      where ename=:{myname VARCHAR}"
-sqlstatement.return=both

Note that for this example:

Code comments show #sql statements that correspond to the translated code shown.
The getEmpBeans() method, generated because of the -sqlstatement.return=both setting, returns an array of JavaBeans. Each element represents a row of the result set. The GetEmpRow class is defined for this purpose.
JPublisher generates a SQLJ class. The result set is mapped to a SQLJ iterator.

(For UPDATE, INSERT, or DELETE statements, code is generated both with and without batching for array binds.)

The translated SQLJ code that JPublisher would produce is:

public class MySqlStatements_getEmpRow 
{ 
 
  /* connection management */ 
 
  /* constructors */ 
  public MySqlStatements_getEmpRow() 
  { } 
 
  public String getEname() throws java.sql.SQLException 
  { return ename; } 
 
  public void setEname(String ename) throws java.sql.SQLException 
  { this.ename = ename; } 
 
  private String ename; 
} 
 
/*@lineinfo:filename=MySqlStatements*/
/*@lineinfo:user-code*/
/*@lineinfo:1^1*/
import java.sql.SQLException; 
import sqlj.runtime.ref.DefaultContext; 
import sqlj.runtime.ConnectionContext; 
import java.sql.Connection; 
import oracle.sql.*; 

public class MySqlStatements 
{ 

  /* connection management */ 
  protected DefaultContext __tx = null; 
  protected Connection __onn = null; 
  public void setConnectionContext(DefaultContext ctx) throws SQLException 
  { release(); __tx = ctx; } 
  public DefaultContext getConnectionContext() throws SQLException 
  { if (__tx==null) 
    { __tx = (__onn==null) ? DefaultContext.getDefaultContext() : 
                             new DefaultContext(__onn); } 
    return __tx; 
  }; 
  public Connection getConnection() throws SQLException 
  { return (__onn==null) ? ((__tx==null) ? null : __tx.getConnection()) : __onn; } 
  public void release() throws SQLException 
  { if (__tx!=null && __onn!=null) __tx.close(ConnectionContext.KEEP_CONNECTION); 
    __onn = null; __tx = null; 
  } 
 
  /* constructors */ 
  public MySqlStatements() throws SQLException 
  { __tx = DefaultContext.getDefaultContext(); } 
  public MySqlStatements(DefaultContext c) throws SQLException 
  { __tx = c; } 
  public MySqlStatements(Connection c) throws SQLException 
  {__onn = c; __tx = new DefaultContext(c); } 
/*@lineinfo:generated-code*/
/*@lineinfo:36^1*/ 

//  ************************************************************ 
//  SQLJ iterator declaration: 
//  ************************************************************ 

public static class getEmpIterator 
    extends sqlj.runtime.ref.ResultSetIterImpl 
    implements sqlj.runtime.NamedIterator 
{ 
  public getEmpIterator(sqlj.runtime.profile.RTResultSet resultSet) 
    throws java.sql.SQLException 
  { 
    super(resultSet); 
    enameNdx = findColumn("ename"); 
    m_rs = (oracle.jdbc.OracleResultSet) resultSet.getJDBCResultSet(); 
  } 
  private oracle.jdbc.OracleResultSet m_rs; 
  public String ename() 
    throws java.sql.SQLException 
  { 
    return m_rs.getString(enameNdx); 
  } 
  private int enameNdx; 
} 
 
//  ************************************************************ 
 
/*@lineinfo:user-code*/
/*@lineinfo:36^56*/ 
 
  public MySqlStatements_getEmpRow[] getEmpBeans (String myname) 
         throws SQLException 
  { 
    getEmpIterator iter; 
    /*@lineinfo:generated-code*/
    /*@lineinfo:43^5*/ 
//  ************************************************************ 
//  #sql [getConnectionContext()] 
//                     iter = { select ename from emp where ename=:myname }; 
//  ************************************************************ 
{ 
  // declare temps 
  oracle.jdbc.OraclePreparedStatement __sJT_st = null; 
  sqlj.runtime.ref.DefaultContext __sJT_cc = getConnectionContext(); 
  if (__sJT_c c==null) sqlj.runtime.error.RuntimeRefErrors.raise_NULL_CONN_CTX(); 
  sqlj.runtime.ExecutionContext.OracleContext __sJT_ec = 
        ((__sJT_cc.getExecutionContext()==null) ? 
        sqlj.runtime.ExecutionContext.raiseNullExecCtx() : 
        __sJT_cc.getExecutionContext().getOracleContext()); 
  try { 
   String theSqlTS = "select ename from emp where ename= :1"; 
   __sJT_st = __sJT_ec.prepareOracleStatement
                    (__sJT_cc,"0MySqlStatements",theSqlTS); 
   // set IN parameters 
   __sJT_st.setString(1,myname); 
   // execute query 
   iter = new MySqlStatements.getEmpIterator
             (new sqlj.runtime.ref.OraRTResultSet 
             (__sJT_ec.oracleExecuteQuery(),__sJT_st,"0MySqlStatements",null)); 
  } finally { __sJT_ec.oracleCloseQuery(); } 
} 

//  ************************************************************ 
 
/*@lineinfo:user-code*/
/*@lineinfo:43^84*/ 
    java.util.Vector v = new java.util.Vector(); 
    while (iter.next()) 
    { 
       MySqlStatements_getEmpRow r = new MySqlStatements_getEmpRow(); 
       r.setEname(iter.ename()); 
       v.addElement(r); 
    } 
    MySqlStatements_getEmpRow[] __jPt_result = 
          new MySqlStatements_getEmpRow[v.size()]; 
    for (int i = 0; i < v.size(); i++) 
       __jPt_result[i] = (MySqlStatements_getEmpRow) v.elementAt(i); 
    return __jPt_result; 
  } 
 
  public java.sql.ResultSet getEmp (String myname) 
          throws SQLException 
  { 
    sqlj.runtime.ResultSetIterator iter; 
    /*@lineinfo:generated-code*/
    /*@lineinfo:62^5*/ 
 
//  ************************************************************ 
//  #sql [getConnectionContext()] iter = 
//                     { select ename from emp where ename=:myname }; 
//  ************************************************************ 
 
{ 
  // declare temps 
  oracle.jdbc.OraclePreparedStatement __sJT_st = null; 
  sqlj.runtime.ref.DefaultContext __sJT_cc = getConnectionContext(); 
  if (__sJT_c c==null) sqlj.runtime.error.RuntimeRefErrors.raise_NULL_CONN_CTX(); 
  sqlj.runtime.ExecutionContext.OracleContext __sJT_ec = 
              ((__sJT_cc.getExecutionContext()==null) ?
              sqlj.runtime.ExecutionContext.raiseNullExecCtx() :
              __sJT_cc.getExecutionContext().getOracleContext()); 
  try { 
   String theSqlTS = "select ename from emp where ename= :1"; 
   __sJT_st = __sJT_ec.prepareOracleStatement
                 (__sJT_cc,"1MySqlStatements",theSqlTS); 
   // set IN parameters 
   __sJT_st.setString(1,myname); 
   // execute query 
   iter = new sqlj.runtime.ref.ResultSetIterImpl
         (new sqlj.runtime.ref.OraRTResultSet
         (__sJT_ec.oracleExecuteQuery(),__sJT_st,"1MySqlStatements",null)); 
  } finally { __sJT_ec.oracleCloseQuery(); } 
} 
 
//  ************************************************************ 
 
/*@lineinfo:user-code*/
/*@lineinfo:62^84*/ 
    java.sql.ResultSet __jPt_result = iter.getResultSet(); 
    return __jPt_result; 
  } 
}
/*@lineinfo:generated-code*/

Generated Code: Server-Side Java Call-in

JPublisher supports the calling of Java methods in the database from a Java client outside the database. In Oracle Database 11g release 2 (11.2), the JPublisher -dbjava option is used for server-side Java invocation. Unlike the -java option, the -dbjava option supports non-serializable parameter or return types.

The Source Files

In this example, there are three source files to be created. These are:

A server-side Java class
A JavaBean used in the server-side Java class
An entry point Java class that invokes the methods in the published classes

The source code of these files is as follows:

Server-Side Java Class

The source code of the server-side Java class, Callin2.java, is as follows:

public class Callin2
{
  public static int testInt(int i)
  { return i; }
  public static int[] testInt(int[] i)
  { return i; }
  public static int[][] testInt(int[][] i)
  { return i; }
  public static Integer testInteger(Integer i)
  { return i; }
  public static Integer[] testInteger(Integer[] i)
  { return i; }
  public static Integer[][] testInteger(Integer[][] i)
  { return i; }

  // Test ORAData
  public static oracle.sql.NUMBER testNum(oracle.sql.NUMBER num)
  { return num; }
  public oracle.sql.NUMBER testInstNum(oracle.sql.NUMBER num)
  { return num; }
  public oracle.sql.NUMBER[] testInstNum(oracle.sql.NUMBER[] num)
  { return num; }
  public oracle.sql.NUMBER[][] testInstNum(oracle.sql.NUMBER[][] num)
  { return num; }

  // Test Beans
  public static Callin2Bean testBean()
  { return new Callin2Bean("mybean", new int[]{1,2}); }
  public static Callin2Bean testBean (Callin2Bean b)
  { return b; }
  public static Callin2Bean[] testBean (Callin2Bean[] b)
  { return b; }
  public static Callin2Bean[][] testBean (Callin2Bean[][] b)
  { return b; }
  public Callin2Bean testInstBean (Callin2Bean b)
  { return b; }
  public Callin2Bean[] testInstBean (Callin2Bean[] b)
  { return b; }
  public Callin2Bean[][] testInstBean (Callin2Bean[][] b)
  { return b; }

  // Test Serializable
  public static java.io.Serializable testSer()
  { return new String("test Serializable"); }
  public static java.io.Serializable testSer (java.io.Serializable b)
  { return b; }
  public static java.io.Serializable[] testSer (java.io.Serializable[] b)
  { return b; }
  public static java.io.Serializable[][] testSer (java.io.Serializable[][] b)
  { return b; }
  public java.io.Serializable testInstSer (java.io.Serializable b)
  { return b; }
  public java.io.Serializable[] testInstSer (java.io.Serializable[] b)
  { return b; }
  public java.io.Serializable[][] testInstSer (java.io.Serializable[][] b)
  { return b; }
}

JavaBean Used in the Server-Side Java Class

The source code of the JavaBean, Callin2Bean.java, used in the server-side Java class, Callin2.java, is as follows:

public class Callin2Bean 
{
  private String stringValue = "";
  private int[] numberValue;
  
  public Callin2Bean ()
  {
  }
 
  public Callin2Bean(String string_val, int[] number_val)
  {
    stringValue = string_val;
    numberValue = number_val;
  }
 
  public void setStringValue(String string_val)
  {
    stringValue = string_val;
  }
 
  public String getStringValue ()
  {
    return stringValue;
  }
 
  public void setNumberValue (int[] number_val)
  {
    numberValue = number_val;
  }
 
  public int[] getNumberValue ()
  {
    return numberValue;
  }
 
  public boolean equals(Object other)
  {
    if(other instanceof Callin2Bean)
    {
      Callin2Bean my_bean = (Callin2Bean)other;
      if ( stringValue.equals(my_bean.getStringValue()) && compareIntArray(numberValue, my_bean.getNumberValue()) )
      {
        return true;
      }
    }
    return false;
  }
 
  private boolean compareIntArray(int[] b1, int[] b2)
  {
    try
    { 
      if ((b1 == null) && (b2 == null))
        return true;
 
      if ((b1.length == 0) && (b2.length == 0))
        return true;
 
      if (b1.length != b2.length)
        return false;
      int x;
      for (x = 0; x < b1.length; x++)
      {
        if (b1[x] != b2[x])
          return false;
      }
      return true;
    }
    catch (Exception e)
    {
      return false;
    }
  }
}

Entry Point Java Class

The TestCallin2.java is the entry point Java class for this example. This class invokes the methods in the published class. The source code of the TestCallin2.java file is as follows:

public class TestCallin2
{
  public static void main(String[] args) throws Exception
  {
    java.sql.DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    oracle.jdbc.OracleConnection conn = (oracle.jdbc.OracleConnection) 
    //java.sql.DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:lsqlj1", "scott", "tiger");
    java.sql.DriverManager.getConnection("jdbc:oracle:oci8:@", "scott", "tiger");
 
   Callin2Client tkpu = new Callin2Client (conn);
 
   System.out.println("testInstNum() returned " + tkpu.testinstnum(new java.math.BigDecimal(1999)));
 
   TblNumber na = new TblNumber(new java.math.BigDecimal[]{new java.math.BigDecimal(2999)});
   System.out.println("testInstNum([]) returned " +
   tkpu.testinstnum(na).getArray()[0]);
 
   ObjCallin2bean mb = new ObjCallin2bean("mybean", na);
   System.out.println("testCallin2Bean() returned " + tkpu.testbean(mb).getStringValue());
   System.out.println("testCallin2Bean([]) returned " + 
tkpu.testbean(new TblObjCallin2bean(new ObjCallin2bean[]{mb})).getArray()[0].getStringValue());
 
   java.io.Serializable s = new java.util.Hashtable();
   ((java.util.Hashtable) s).put("bush", "cheny");
   ((java.util.Hashtable) s).put("kerry", "dean");
   java.io.Serializable[] s1 = new java.io.Serializable[]{s};
   java.io.Serializable[][] s2 = new java.io.Serializable[][]{s1};
   System.out.println("testSer() returned " + ((java.util.Hashtable) tkpu.testser(s)).get("kerry")); 
   System.out.println("testSer([]) returned " + ((java.util.Hashtable) tkpu.testser0(s1)[0]).get("kerry")); 
   System.out.println("testSer([][]) returned " + ((java.util.Hashtable) tkpu.testser1(s2)[0][0]).get("kerry")); 
  }
}

Note:

If you are connecting to the database using the JDBC Thin driver, then you must uncomment the first call to the getConnection() method in the preceding code and comment the second call to the getConnection() method, which includes a connect statement for an Oracle Call Interface (OCI) driver.

Publishing Server-Side Java Class

After you have created the required source files, you must publish these files. To publish the server-side Java classes, you must first load these files on to the database. Ensure that you load both Callin2.java and Callin2Bean.java. The command for loading the files is:

% loadjava -u scott -r -v -f Callin2.java Callin2Bean.java
Password: password

To publish these files, issue the following command:

% jpub -u scott -sysuser=sys/sys_password -dbjava=Callin2:Callin2Client -dir=tmp
Enter scott password: password

The JPublisher output is:

tmp/Callin2JPub.java
tmp/plsql_wrapper.sql
tmp/plsql_dropper.sql
SCOTT.TBL_NUMBER
SCOTT.TBL_TBL_NUMBER
SCOTT.OBJ_CALLIN2BEAN
SCOTT.TBL_OBJ_CALLIN2BEAN
SCOTT.TBL_TBL_OBJ_CALLI
SCOTT.JPUB_PLSQL_WRAPPER
Executing tmp/plsql_dropper.sql
Executing tmp/plsql_wrapper.sql
Loading Callin2JPub.java

The Generated Files

When you publish the server-side Java classes JPublisher generates a few Java classes and PL/SQL scripts. Some of these files are:

Callin2JPub.java

This is the server-side Java wrapper class for Callin2.java.
Callin2Client.java

This is the client-side Java class.
plsql_wrapper.sql

This is the PL/SQL wrapper for the server-side Java class.
plsql_dropper.sql

This is the PL/SQL script dropping the PL/SQL wrapper.

The other files generated by JPublisher are as follows:

TblNumber.java

A client-side wrapper generated for int[].
TblTblNumber.java

A client-side wrapper generated for int[][].
ObjCallin2bean.java

A client-side wrapper generated for the server-side class Callin2Bean.
ObjCallin2beanRef.java

A client-side wrapper generated for the server-side class Callin2Bean, used as a REF column in a table. This file is generated, but not used in the call-in scenario.
TblObjCallin2bean.java

A client-side wrapper generated for Callin2Bean[].
TblTblObjCalli.java

A client-side wrapper generated for Callin2Bean[][].

Testing the Published Files

After the files have been published, you can test the published classes by issuing the following commands:

% javac -classpath tmp:${CLASSPATH} -d tmp TestCallin2.java
% java -classpath tmp:${CLASSPATH} TestCallin2