Example JDBC Client

This example shows how a JDBC client can call a data-aware procedure and use a CallableStatement to work with the procedure's result sets.

package com.pivotal.gemfirexd.jdbc;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import com.pivotal.gemfirexd.procedure.ProcedureExecutionContext;

public class MyClient {
  
  public static class ExampleObj implements Serializable {
    private static final long serialVersionUID = 1L;
    private int val;
    
    public void setValue(int val) {
      this.
      val = val;
    }
    
    public int getValue() {
      return this.val;
    }
  }
  
  public static void main(String[] args) {
    try {
      Connection cxn = DriverManager.getConnection("jdbc:gemfirexd:");
      Statement stmt = cxn.createStatement();

      stmt.execute("create type ExampleObjType external name '"
          + ExampleObj.class.getName() + "' language java");

      stmt.execute("CREATE PROCEDURE myProc " + "(IN inParam1 VARCHAR(10), "
          + " OUT outParam2 INTEGER, "
          + " INOUT example ExampleObjType, OUT count INTEGER)"
          + "LANGUAGE JAVA PARAMETER STYLE JAVA " + "READS SQL DATA "
          + "DYNAMIC RESULT SETS 2 " + "EXTERNAL NAME '"
          + ProcedureTest.class.getName() + ".myProc'");

      stmt.execute("create table MyTable(x int not null, y int not null)");
      stmt.execute("insert into MyTable values (1, 10), (2, 20), (3, 30), (4, 40)");
      CallableStatement callableStmt = cxn
          .prepareCall("{CALL myProc('abc', ?, ?, ?) ON TABLE MyTable WHERE x BETWEEN 5 AND 10}");
      callableStmt.registerOutParameter(1, Types.INTEGER);
      callableStmt.registerOutParameter(2, Types.JAVA_OBJECT);
      callableStmt.registerOutParameter(3, Types.INTEGER);

      callableStmt.setObject(2, new ExampleObj());

      callableStmt.execute();

      int outParam2 = callableStmt.getInt(1);
      ExampleObj example = (ExampleObj)callableStmt.getObject(2);

      assert example.getValue() == 100;
      assert outParam2 == 200;

      ResultSet thisResultSet;
      boolean moreResults = true;
      int cnt = 0;
      int rowCount = 0;
      do {
        thisResultSet = callableStmt.getResultSet();
        int colCnt = thisResultSet.getMetaData().getColumnCount();
        if (cnt == 0) {
          System.out.println("Result Set 1 starts");
          while (thisResultSet.next()) {
            for (int i = 1; i < colCnt + 1; i++) {
              System.out.print(thisResultSet.getObject(i));
              if (i == 1) {
                System.out.print(',');
              }
            }
            System.out.println();
            rowCount++;
          }
          System.out.println("ResultSet 1 ends\n");
          cnt++;
        }
        else {
          thisResultSet.next();
          System.out.println("ResultSet 2 starts");
          for (int i = 1; i < colCnt + 1; i++) {
            cnt = thisResultSet.getInt(1);
            System.out.print(cnt);
            System.out.println();
          }
          System.out.println("ResultSet 2 ends");
        }
        moreResults = callableStmt.getMoreResults();
      } while (moreResults);
      assert rowCount == cnt;
      assert rowCount == 4;
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void myProc(String inParam1, int[] outParam2,
        ExampleObj[] example, int[] count, ResultSet[] resultSet1,
        ResultSet[] resultSet2,
        ProcedureExecutionContext ctx) throws SQLException {
      Connection conn = ctx.getConnection();
      ExampleObj obj = new ExampleObj();
      obj.setValue(100);
      example[0] = obj;
     
      outParam2[0] = 200;
     
      Statement stmt = conn.createStatement();
      stmt.execute("select * from mytable");
      resultSet1[0] = stmt.getResultSet();
     
      Statement stmt3 = conn.createStatement();
      stmt3 .execute("select count(*) from mytable");
      stmt3.getResultSet().next();
      Integer cnt = stmt3.getResultSet().getInt(1);
      count[0] = cnt;
     
      Statement stmt2 = conn.createStatement();
      stmt2.execute("select count(*) from mytable");
      resultSet2[0] = stmt2.getResultSet();
    }
  }