18.8. Advanced EDB Connector/Java Functionality

18.8.1. Getting results based on a cursor

By default the EDB Connector/Java driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.

This ensures that a small number of rows are cached on the client side of the connection and when exhausted the next block of rows is retrieved by repositioning the cursor.

18.8.2. Example

Changing code to cursor mode is as simple as setting the fetch size of the Statement to the appropriate size. Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour). The following code listing shows just how to do this:

	// make sure autocommit is off
	db.setAutoCommit(false);
	Statement st = db.createStatement();

	// Turn use of the cursor on.
	st.setFetchSize(50);
	ResultSet rs = st.executeQuery("SELECT * FROM emp");
	while (rs.next()) {
	   System.out.print("a row was returned.");
	}
	rs.close();

	// Turn the cursor off.
	st.setFetchSize(0);
	rs = st.executeQuery("SELECT * FROM emp");
	while (rs.next()) {
	   System.out.print("many rows were returned.");
	}
	rs.close();

	// Close the statement.
	st.close();
	      

18.8.3. Using PreparedStatements

Sometimes, it is more convenient and/or efficient to use a PreparedStatement object for sending SQL statements to the DBMS. The main advantage of using a PreparedStatement over a simple Statement is that if you need to use the same, or similar query with different parameters multiple times, the statement can be compiled and optimized by the DBMS just once. Whereas if we use the same SQL statement with a normal Statement it is compiled each time we use the same SQL statement.

PreparedStatement are also created with a Connection method. The following snippet shows how to create a parameterized SQL statement with two input parameters:

PreparedStatement prepareInsertEmp = con.prepareStatement("INSERT INTO emp(empno,ename) VALUES(?, ?)");

Before we can execute a PreparedStatement, we need to supply values for the parameters. You can set these values before each execution of the prepared statement.

Continuing the above example, we would write:

 prepareInsertEmp.setObject(1,new Integer(c.readLine()));         
 prepareInsertEmp.setObject(2,c.readLine().toString());    

The following code shows the complete source code for invoking a PreparedStatement that accepts an employee number and employee name and inserts that employee information as a new row in the emp table.

import java.sql.*;
public class PreparedStatementTest
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
    String message = "/********************************************************/ \r\n";
          message += "*                                                        * \r\n";
          message += "*  The sample program will take employee number and    *\r \n";
          message += "*  employee name and insert a new record in EMP table    * \r\n";
          message += "*                                                        * \r\n";
          message += "/********************************************************/ ";
    try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      PreparedStatement prepareInsertEmp = con.prepareStatement(
      "INSERT INTO emp(empno,ename) VALUES(?, ?)");
  
      System.out.println(message);
      System.out.print("\r\nEmployee Number : ");
      prepareInsertEmp.setObject(1,new Integer(c.readLine()));
           
      System.out.print("Employee Name : ");
      prepareInsertEmp.setObject(2,c.readLine().toString());
      prepareInsertEmp.execute();
      System.out.println("\r\nThe procedure successfully executed.");
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");                                                                                 
      System.out.println("See full details below.\r\n");
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
 }

	      

18.8.4. Executing Stored Procedures

A stored procedure is simply a module that is stored in the database and is written in EnterpriseDB's SPL (Superset Procedural language) programming language. A stored procedure can optionally return a value, and it may have input and/or output parameters.

A stored procedure is tightly coupled with EnterpriseDB and consists of database access commands (SQL), plus control statements and data structures to manipulate the data obtained from the database. This is especially useful when extensive manipulation of the data is required either before it is sent to the database or before it is sent to the client or when there is no client involved such as in a batch program.

18.8.5. Invoking Stored Procedures

A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMS's. We just created a sample stored procedure in EnterpriseDB in the above example. We actually call the stored procedure using a CallableStatement object. This call is written in an escape syntax that may take one of two forms: one form with a result parameter, and the other without one. A result parameter, a kind of OUT parameter, is the return value for the stored procedure. Both forms may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). A question mark serves as a placeholder for a parameter.

The syntax for invoking a stored procedure in JDBC is shown below. Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.

{call procedure_name([?, ?, ...])}
	

The syntax for a procedure that returns a result parameter is:

{? = call procedure_name([?, ?, ...])}
        

To elaborate the concepts of stored procedures and their invocation from Java code, let us proceed with looking at 2 examples. The first example concentrates on a simple stored procedure without any arguments and result parameters, whereas the second one takes in two arguments.

18.8.6. Example 1: Executing a Simple Stored Procedure

Before we jump into the Java code for invoking our stored procedure (called "list_dept10") for this example, let us first create the stored procedure in our EDB database. The following code listing shows the SPL for the "list_dept10" stored procedure:

CREATE OR REPLACE PROCEDURE list_dept10
IS
v_deptname VARCHAR2(30);
BEGIN
	DBMS_OUTPUT.PUT_LINE('Dept No: 10');
	SELECT dname INTO v_deptname FROM dept WHERE deptno = 10;
	DBMS_OUTPUT.PUT_LINE('Dept Name: ' || v_deptname);
END;

You can just copy paste the following code into the "Query Tool" window of "EDB Studio" as shown in the following screenshot:

Or optionally you can also create the stored procedure via EDB-PSQL+ as shown below:

edb=# CREATE OR REPLACE PROCEDURE LIST_DEPT10
edb-# IS
edb$# v_deptname VARCHAR2(30);
edb$# BEGIN
edb$# DBMS_OUTPUT.PUT_LINE('Dept No: 10');
edb$# SELECT dname INTO v_deptname FROM dept WHERE deptno = 10;
edb$# DBMS_OUTPUT.PUT_LINE('Dept Name: ' || v_deptname);
edb$# END;
CREATE PROCEDURE

To use the "LIST_DEPT10" procedure from our Java code we must prepare the callable statement via the prepareCall() method of the Connection object. The example below creates an instance of CallableStatement that contains a call to the "LIST_DEPT10" stored procedure.

import java.sql.*;
public class SelectDept10
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
     try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call LIST_DEPT10()}");      
      System.out.print("Executing LIST_DEPT10 Stored Procedure");
      st.execute();
	  System.out.print("Stored Procedure executed successfully");
	  
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

When you execute this stored procedure successfully via JDBC you should see the message "Stored Procedure executed successfully" on the client end. The message printed via DBMS_OUTPUT.PUT_LINE will only be visible on the server side since this message is shown on the Console window of the server.

18.8.7. Example 2:Executing Stored Procedures with IN parameters

For our next example we will first create and then invoke another EDB stored procedure called "JDBC_DML" which accepts 2 IN parameters (employee number, and employee name) and then inserts that respective information into the "emp" table.

The following code listing shows the SPL code for the "EMP_INSERT" stored procedure:

CREATE OR REPLACE PROCEDURE EMP_INSERT(pENAME IN VARCHAR,pJOB IN VARCHAR,pSAL IN FLOAT4, pCOMM IN FLOAT4, pDEPTNO IN INTEGER,pMgr IN INTEGER) AS
DECLARE
  CURSOR TESTCUR IS SELECT MAX(EMPNO) FROM EMP;
  MAX_EMPNO INTEGER := 10;
BEGIN
  
  OPEN TESTCUR;
  FETCH TESTCUR INTO MAX_EMPNO;   
  INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,COMM,DEPTNO,MGR) VALUES(MAX_EMPNO+1,pENAME,pJOB,pSAL,pCOMM,pDEPTNO,pMgr);
  close testcur;
END;

Create the above stored procedure either via Query Tool from "EDB Studio" or through "EDB PSQL+" as shown for the first example. Once you have created the stored procedure, use the following code for invoking that stored procedure from your Java code and passing it 6 IN arguments via command line.

import java.sql.*;
public class InsertEmp
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
     try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call EMP_INSERT(?,?,?,?,?,?)}");      

      System.out.print("Employee Name : ");
      st.setObject(1,new String(c.readLine()));      
	  System.out.print("Employee Job : ");
	  st.setObject(2,new String(c.readLine()));	  
	  System.out.print("Salary : ");
	  st.setObject(3,new Float(c.readLine()));	  
	  System.out.print("Commission : ");
	  st.setObject(4,new Float(c.readLine()));
	  System.out.print("Department No : ");
	  st.setObject(5,new Integer(c.readLine()));
	  System.out.print("Manager : ");
	  st.setObject(6,new Integer(c.readLine()));

      st.execute();

    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

Whether the ? placeholders are IN, OUT, or INOUT parameters depends on the stored procedure "EMP_INSERT".

Passing in any IN parameter values to a CallableStatement object is done using the setObject method whereas if the stored procedure returns OUT parameters, the SQL type of each OUT parameter must be registered before the CallableStatement object can be executed. (This is necessary because some DBMSs require the SQL type.) Registering the SQL type is done with the method registerOutParameter. Then after the statement has been executed, CallableStatement's getObject method retrieves the parameter value.

18.8.8. Example 3: Stored Procedure with OUT parameters

For our next example we will first create and then invoke another SPL stored procedure called "DEPT_SELECT" which accepts 1 IN parameter (department number) and returns 2 OUT parameters (the department name and location) corresponding against that department number.

The following code listing shows the SPL code for the "DEPT_SELECT" stored procedure:

CREATE OR REPLACE PROCEDURE DEPT_SELECT(pDEPTNO IN INTEGER,pDNAME OUT VARCHAR,pLOC OUT VARCHAR) AS
DECLARE
  CURSOR TESTCUR IS SELECT DNAME,LOC FROM DEPT;
   REC RECORD;
BEGIN
  
  OPEN TESTCUR;
  FETCH TESTCUR INTO REC  ;
  pDNAME:=REC.DNAME;  
  pLOC:=REC.LOC;  
  close testcur;
END;

You can create the above stored procedure either via Query Tool or "EDB PSQL+" as shown in the other examples.

Now that you are done creating the stored procedure let's invoke the stored procedure from Java. The following code sample illustrates the passing of one IN and the retrieval of two OUT parameters.

import java.sql.*;
public class SelectDept 
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
     try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call DEPT_SELECT(?,?,?)}");      

      System.out.print("DEPT  No : ");
      st.setObject(1,new Integer(c.readLine()));      

      st.registerOutParameter(2,Types.VARCHAR);
      st.registerOutParameter(3,Types.VARCHAR);

      st.execute();

      System.out.println("Dept Name: "+st.getString(2));
      System.out.println("Location : "+st.getString(3));
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}
    

Whether the ? placeholders are IN, OUT, or INOUT parameters depends on the stored procedure "DEPT_SELECT".

As this stored procedure returns OUT parameters, the JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed. This is necessary because some DBMSs require the SQL type. Registering the JDBC type is done with the method registerOutParameter. Then after the statement has been executed, the CallableStatement's getXXX methods can be used to retrieve OUT parameter values. The correct CallableStatement.getXXX method to use is the type in the Java programming language that corresponds to the JDBC type registered for that parameter.

With reference to our current example the following code registers 2 OUT parameters and after executing the stored procedure retrieves the values returned in the OUT parameters. Since both OUT parameters represent VARCHAR datatypes we retrieve them using the getString() method.

18.8.9. Example 4:Executing Stored Procedures with IN OUT parameters

For our final example we will first create and then invoke an SPL stored procedure called "DEPT_UPDATE" which accepts 1 IN parameter (department number) and then returns the respective information for an employee (as 4 OUT parameters) against that deptno.

The following code listing shows the SPL code for the "JDBC_DML" stored procedure:

CREATE OR REPLACE PROCEDURE DEPT_UPDATE(pDEPTNO IN INTEGER,pLOCATION IN OUT VARCHAR) AS
DECLARE
	CURSOR TESTCUR IS SELECT LOC FROM DEPT WHERE DEPTNO=pDEPTNO;
BEGIN
	UPDATE DEPT SET LOC='EDISON' WHERE DEPTNO=pDEPTNO;
	OPEN TESTCUR;
	FETCH TESTCUR INTO pLOCATION;
END;

Create the EMP_SELECT stored procedure via Query Tool or "EDB PSQL+" before proceeding with the invocation of the stored procedure from Java.

The following code illustrates the usage of one IN OUT parameter and the retrieval of for OUT parameters.

import java.sql.*;
public class SelectEmp 
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
     try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call emp_select(?,?,?,?,?)}");      

      System.out.print("Employee No : ");
      st.setObject(1,new Integer(c.readLine()));      

      st.registerOutParameter(2,Types.VARCHAR);
      st.registerOutParameter(3,Types.VARCHAR);
      st.registerOutParameter(4,Types.FLOAT);
      st.registerOutParameter(5,Types.FLOAT);

      st.execute();

      System.out.println("Employee Name: "+st.getString(2));
      System.out.println("Job : "+st.getString(3));
      System.out.println("Salary : "+st.getFloat(4));
      System.out.println("Commission : "+st.getFloat(5));
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}
    

The JDBC type of each OUT parameter is registered before the CallableStatement object is executed via the CallableStatement's getXXX methods. The same holds true for all IN OUT parameters as they must also be returned as well as passed via the setObject method.

So the EMP_SELECT stored procedure first sets one IN OUT parameter and after the execution of the stored procedure retrieves five values returned through four OUT parameters and one IN OUT parameter