JDBC for Occasional Users

Executing a Prepared Statement

The following query uses a prepared statement to return a list of all employees with names beginning in “A” through “E” who work for a company with a name starting in “M” through “Z”:

   Select ID, Name, Company->Name from Demo.Employee
   Where Name < ? and Company->Name > ?
   Order By Company->Name

To implement the prepared statement:

• Create the string containing the query and use it to initialize the PreparedStatement object, then set the values of the query parameters and execute the query:

     String sql=
        "Select ID, Name, Company->Name from Demo.Employee " +
        "Where Name < ? and Company->Name > ? " +
        "Order By Company->Name";
     PreparedStatement pstmt = dbconnection.prepareStatement(sql);
  
     pstmt.setString(1,"F");
     pstmt.setString(2,"L");
     java.sql.ResultSet rs = pstmt.executeQuery();
  
  

  

• Retrieve and display the result set:

     java.sql.ResultSet rs = pstmt.executeQuery();
     ResultSetMetaData rsmd = rs.getMetaData();
     int colnum = rsmd.getColumnCount();
     while (rs.next()) {
        for (int i=1; i<=colnum; i++) {
           System.out.print(rs.getString(i) + "  ");
        }
        System.out.println();
     }
  
  

  

Executing Stored Procedures with CallableStatement

The following code executes ByName, an InterSystems IRIS stored procedure contained in Demo.Person:

• Create a java.sql.CallableStatement object and initialize it with the name of the stored procedure. The SqlName of the procedure is SP_Demo_By_Name, which is how it must be referred to in the Java client code:

     String sql="call Demo.SP_Demo_By_Name(?)"
     CallableStatement cs = dbconnection.prepareCall(sql);
  
  

  

• Set the value of the query parameter and execute the query, then iterate through the result set and display the data:

     cs.setString(1,"A");
     java.sql.ResultSet rs = cs.executeQuery();
  
     ResultSetMetaData rsmd = rs.getMetaData();
     int colnum = rsmd.getColumnCount();
     while (rs.next()) {
        for (int i=1; i<=colnum; i++)
           System.out.print(rs.getString(i) + "  ");
     }
     System.out.println();
  
  

  

Returning Multiple Result Sets

InterSystems IRIS allows you to define a stored procedure that returns multiple result sets. The InterSystems JDBC driver supports the execution of such stored procedures. Here is an example of an InterSystems IRIS stored procedure that returns two result sets (note that the two query results have different column structures):

   /// This class method produces two result sets.
   ClassMethod DRS(st) [ ReturnResultsets, SqlProc ]
   {
    $$$ResultSet("select Name from Demo.Person where Name %STARTSWITH :st")
    $$$ResultSet("select Name, DOB from Demo.Person where Name %STARTSWITH :st")
    Quit
   }

$$$ResultSet is a predefined InterSystems macro that prepares a SQL statement (specified as a string literal), executes it, and returns the resultset.

The following code executes the stored procedure and iterates through both of the returned result sets:

• Create the java.sql.CallableStatement object and initialize it using the name of the stored procedure. Set the query parameters and use execute to execute the query:

     CallableStatement cs = dbconnection.prepareCall("call Demo.Person_DRS(?)");
     cs.setString(1,"A");
     boolean success=cs.execute();
  
  

  

• Iterate through the pair of result sets displaying the data. After getResultSet retrieves the current result set, getMoreResults closes it and moves to the CallableStatement object's next result set.

     if(success) do{
        java.sql.ResultSet rs = cs.getResultSet();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int j=1; j<rsmd.getColumnCount() + 1; j++)
           System.out.print(rsmd.getColumnName(j)+ "\t\t");
        System.out.println();
        int colnum = rsmd.getColumnCount();
        while (rs.next()) {
           for (int i=1; i<=colnum; i++)
              System.out.print(rs.getString(i) + " \t ");
           System.out.println();
        }
        System.out.println();
      } while (cs.getMoreResults());
  
  

  

Inserting Data and Retrieving Generated Keys

The following code inserts a new row into Demo.Person and retrieves the generated ID key.

• Create the PreparedStatement object, initialize it with the SQL string, and specify that generated keys are to be returned:

     String sqlIn="INSERT INTO Demo.Person (Name,SSN,DOB) " + "VALUES(?,?,?)";
     int keys=Statement.RETURN_GENERATED_KEYS;
     PreparedStatement pstmt = dbconnection.prepareStatement(sqlIn, keys);
  
  

  

• Set the values for the query parameters and execute the update:

     String SSN = Demo.util.generateSSN(); // generate a random SSN
     java.sql.Date DOB = java.sql.Date.valueOf("1984-02-01");
  
     pstmt.setString(1,"Smith,John"); // Name
     pstmt.setString(2,SSN); // Social Security Number
     pstmt.setDate(3,DOB); // Date of Birth
     pstmt.executeUpdate();
  
  

  

• Each time you insert a new row, the system automatically generates an object ID for the row. The generated ID key is retrieved into a result set and displayed along with the SSN:

     java.sql.ResultSet rsKeys = pstmt.getGeneratedKeys();
     rsKeys.next();
     String newID=rsKeys.getString(1);
     System.out.println("new ID for SSN " + SSN + " is " + newID);
  
  

  

  Although this code assumes that the ID will be the first and only generated key in rsKeys, this is not always a safe assumption in real life.

• Retrieve the new row by ID and display it (Age is a calculated value based on DOB).

     String sqlOut="SELECT IName,Age,SSN FROM Demo.Person WHERE ID="+newID;
     pstmt = dbconnection.prepareStatement(sqlOut);
     java.sql.ResultSet rsPerson = pstmt.executeQuery();
  
     int colnum = rsPerson.getMetaData().getColumnCount();
     rsPerson.next();
     for (int i=1; i<=colnum; i++)
        System.out.print(rsPerson.getString(i) + "  ");
     System.out.println();
  
  

  

Scrolling a Result Set

The InterSystems JDBC driver supports scrollable result sets, which allow your Java applications to move both forward and backward through the resultset data. The prepareStatement() method uses following parameters to determine how the result set will function:

• The resultSetType parameter determines how changes are displayed:

  • ResultSet.TYPE_SCROLL_SENSITIVE creates a scrollable result set that displays changes made to the underlying data by other processes.

  • ResultSet.TYPE_SCROLL_INSENSITIVE creates a scrollable result set that only displays changes made by the current process.

• The resultSetConcurrency parameter must be set to ResultSet.CONCUR_UPDATABLE if you intend to update the result set.

The following code creates and uses a scrollable result set:

• Create a PreparedStatement object, set the query parameters, and execute the query:

     String sql="Select Name, SSN from Demo.Person "+
        " Where Name > ? Order By Name";
     int scroll=ResultSet.TYPE_SCROLL_SENSITIVE;
     int update=ResultSet.CONCUR_UPDATABLE;
  
     PreparedStatement pstmt = dbconnection.prepareStatement(sql,scroll,update);
     pstmt.setString(1,"S");
     java.sql.ResultSet rs = pstmt.executeQuery();
  
  

  

  A result set that is going to have new rows inserted should not include the InterSystems IRIS ID column. ID values are defined automatically by InterSystems IRIS.

• The application can scroll backwards as well as forwards through this result set. Use afterLast to move the result set's cursor to after the last row. Use previous to scroll backwards.

     rs.afterLast();
     int colnum = rs.getMetaData().getColumnCount();
     while (rs.previous()) {
        for (int i=1; i<=colnum; i++)
           System.out.print(rs.getString(i) + "  ");
        System.out.println();
     }
  
  

  

• Move to a specific row using absolute. This code displays the contents of the third row:

     rs.absolute(3);
     for (int i=1; i<=colnum; i++)
        System.out.print(rs.getString(i) + "  ");
     System.out.println();
  
  

  

• Move to a specific row relative to the current row using relative. The following code moves to the first row, then scrolls down two rows to display the third row again:

     rs.first();
     rs.relative(2);
     for (int i=1; i<=colnum; i++)
        System.out.print(rs.getString(i) + "  ");
     System.out.println();
  
  

  

• To update a row, move the cursor to that row and update the desired columns, then invoke updateRow:

     rs.last();
     rs.updateString("Name", "Avery. Tara R");
     rs.updateRow();
  
  

  

• To insert a row, move the cursor to the “insert row” and then update that row's columns. Be sure that all non-nullable columns are updated. Finally, invoke insertRow:

     rs.moveToInsertRow();
     rs.updateString(1, "Abelson,Alan");
     rs.updateString(2, Demo.util.generateSSN()));
     rs.insertRow();
  
  

  

Using Transactions

The InterSystems JDBC driver supports the standard JDBC transaction model.

• In order to group SQL statements into a transaction, you must first disable autocommit mode using setAutoCommit():

     dbconnection.setAutoCommit(false);
  
  

  

• Use commit() to commit to the database all SQL statements executed since the last execution of commit() or rollback:

     pstmt1.execute();
     pstmt2.execute();
     pstmt3.execute();
     dbconnection.commit();
  
  

  

• Use rollback() to roll back all of the transactions in a transactions. Here the rollback() is invoked if SQLException is thrown by any SQL statement in the transaction:

     catch(SQLException ex) {
        if (dbconnection != null) {
           try {
              dbconnection.rollback();
           } catch (SQLException excep){
              // (handle exception)
           }
        }
     }
  
  

  

Here is a brief summary of the java.sql.Connection methods used in this example:

• setAutoCommit()

  By default Connection objects are in autocommit mode. In this mode an SQL statement is committed as soon as it is executed. To group multiple SQL statements into a transaction, first use setAutoCommit(false) to take the Connection object out of autocommit mode. Use setAutoCommit(true) to reset the Connection object to autocommit mode.

• commit()

  Executing commit() commits all SQL statements executed since the last execution of either commit() or rollback(). Note that no exception will be thrown if you call commit() without first setting autocommit to false.

• rollback()

  Executing rollback aborts a transaction and restores any values changed by the transaction back to their original state.