Skip to main content
Previous sectionNext section

JDBC for Occasional Users

JDBC needs no introduction for experienced Java database developers, but it can be very useful even if you only use Java for occasional small utility applications. This chapter is a quick overview of JDBC that provides examples of Java code for querying databases and working with the results.

A Simple JDBC Application

This section describes a very simple JDBC application that demonstrates the use of some of the most common JDBC classes:

  • A IRISDataSource object is used to create a Connection object that links the JDBC application to the InterSystems IRIS database.

  • The Connection object is used to create a PreparedStatement object that can execute a dynamic SQL query.

  • The PreparedStatement query returns a ResultSet object that contains the requested rows.

  • The ResultSet object has methods that can be used to move to a specific row and read or update specified columns in the row.

All of these classes are discussed in more detail later in the chapter.

The SimpleJDBC Application

To begin, import the JDBC packages and open a try block:


import java.sql.*;
import javax.sql.*;
import com.intersystems.jdbc.*;

public class SimpleJDBC{
  public static void main() {
    try {

// Use IRISDataSource to open a connection
      Class.forName ("com.intersystems.jdbc.IRISDriver").newInstance();
      IRISDataSource ds = new IRISDataSource();
      ds.setURL("jdbc:IRIS://127.0.0.1:1972/User");
      Connection dbconn = ds.getConnection("_SYSTEM","SYS");

// Execute a query and get a scrollable, updatable result set.
      String sql="Select Name from Demo.Person Order By Name";
      int scroll=ResultSet.TYPE_SCROLL_SENSITIVE;
      int update=ResultSet.CONCUR_UPDATABLE;
      PreparedStatement pstmt = dbconn.prepareStatement(sql,scroll,update);
      java.sql.ResultSet rs = pstmt.executeQuery();

// Move to the first row of the result set and change the name.
      rs.first();
      System.out.println("\n Old name = " + rs.getString("Name"));
      rs.updateString("Name", "Bill. Buffalo");
      rs.updateRow();
      System.out.println("\n New name = " + rs.getString("Name") + "\n");

// Close objects and catch any exceptions.
      pstmt.close();
      rs.close();
      dbconn.close();
    } catch (Exception ex) {
      System.out.println("SimpleJDBC caught exception: "
             + ex.getClass().getName() + ": " + ex.getMessage());
    }
  } // end main()
} // end class SimpleJDBC
Copy code to clipboard
Note:

In the rest of this chapter, examples will be presented as fragments of code, rather than whole applications. These examples demonstrate some basic features as briefly and clearly as possible, and are not intended to teach good coding practices. It will be assumed that a connection has already been opened, and that all code fragments are within an appropriate try/catch statement.

Setting the SQL Dialect

By default, JDBC uses the InterSystems IRIS SQL dialect. You can change the dialect to support Transact SQL (TSQL) dialects:

   connection.setSQLDialect(int);
Copy code to clipboard

or

   statement.setSQLDialect(int);
Copy code to clipboard

The available int options are 0 = InterSystems IRIS SQL (the default); 1 = MSSQL; 2 = Sybase.

You can also define the dialect in the driver properties.

When dialect > 0, the SQL statements prepared and or executed via JDBC are handled slightly differently on the server. The statements are processed using the dialect specified, and then converted to InterSystems IRIS SQL and/or ObjectScript statements.

Using Queries

The sql.java package provides the PreparedStatement and CallableStatement classes to query databases and return a ResultSet. Both classes are instantiated by calls to Connection methods. The following sections discuss how to use these classes:

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
Copy code to clipboard
Note:

This statement uses Implicit Join syntax (the –> operator), which provides a simple way to access the Company class referenced by Demo.Employee.

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();
    
    Copy code to clipboard
  • 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();
       }
    
    Copy code to clipboard

Using Callable Statements to Execute Stored Procedures

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);
    
    Copy code to clipboard
  • 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();
    
    Copy code to clipboard

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
   }
Copy code to clipboard

$$$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();
    
    Copy code to clipboard
  • 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());
    
    Copy code to clipboard

Inserting and Updating Data

There are several ways to insert and update InterSystems IRIS data using JDBC:

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);
    
    Copy code to clipboard
  • 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("1973-02-01");
    
       pstmt.setString(1,"Smith,John"); // Name
       pstmt.setString(2,SSN); // Social Security Number
       pstmt.setDate(3,DOB); // Date of Birth
       pstmt.executeUpdate();
    
    Copy code to clipboard
  • 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);
    
    Copy code to clipboard

    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();
    
    Copy code to clipboard

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();
    
    Copy code to clipboard

    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();
       }
    
    Copy code to clipboard
  • 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();
    
    Copy code to clipboard
  • 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();
    
    Copy code to clipboard
  • 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();
    
    Copy code to clipboard
  • 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();
    
    Copy code to clipboard

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);
    
    Copy code to clipboard
  • 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();
    
    Copy code to clipboard
  • 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)
             }
          }
       }
    
    Copy code to clipboard

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.

Note:
The Native API for Java transaction model

The Native API for Java offers an alternative to the java.sql transaction model demonstrated here. The Native API transaction model is based on ObjectScript transaction methods, and is not interchangeable with the JDBC model. The Native API model must be used if your transactions include Native API method calls. See Using the Native API for Java for details.