docs.intersystems.com
Home  /  Application Development: Language Bindings and Gateways  /  Using Java JDBC with InterSystems Products  /  A JDBC Primer


Using Java JDBC with InterSystems Products
A JDBC Primer
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


This chapter is a quick overview of JDBC API usage for readers new to this aspect of Java development. It provides examples of Java code using the InterSystems JDBC™ driver to query databases and work 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:
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:51773/User");
      Connection dbconn = ds.getConnection("_SYSTEM","SYS");

// Execute a query and get a scrollable, updatable result set.
      String sql="Select Name from Sample.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
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.
Using Queries
The sql.java package provides three classes used to query databases and return a ResultSet:Statement, PreparedStatement, and CallableStatement. All three classes are instantiated by calls to Connection methods. The following sections discuss how to use these classes:
Using Statement to Execute a SELECT
The following code executes an SQL SELECT on InterSystems IRIS using the Statement class:
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 Sample.Employee
   Where Name < ? and Company->Name > ?
   Order By Company->Name
Note:
This statement uses Implicit Join syntax (the –> operator), which provides a simple way to access the Company class referenced by Sample.Employee.
The prepared statement is implemented just like a regular statement:
Using Callable Statements to Execute Stored Procedures
The following code executes ByName, an InterSystems IRIS stored procedure contained in Sample.Person:
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 Sample.Person where Name %STARTSWITH :st")
    $$$ResultSet("select Name, DOB from Sample.Person where Name %STARTSWITH :st")
    Quit
   }
Note:
This stored procedure is not defined in Sample.Person. In order to try this example, you must first open Sample.Person in Studio and add the class method shown above. The $$$ResultSet routine prepares and executes a SQL statement (which must be a string literal, available at compile time) and returns the resultset. To use it, you must add the declaration include %occResultSet at the start of the Sample.Person file (before the class definition, as shown here):
include %occResultSet
Class Sample.Person Extends (%Persistent, %Populate, %XML.Adaptor)
{ ...
Remember to recompile Sample.Person after making these changes.
The following code executes the stored procedure and iterates through both of the returned result sets:
Note:
By default getMoreResults closes the current result set before moving to the next. The InterSystems JDBC Driver does not support keeping the current result set open after moving to the next.
Inserting and Updating Data
There are several ways to insert and update InterSystems IRIS data using JDBC:
The makeTestSSN() Method
In this section, several examples insert new rows into Sample.Person, which requires SSN (Social Security Number) as a unique key. The following method is used in these examples to generate a random SSN of the form nnn-nn-nnnn:
  public static String makeTestSSN() {
    java.util.Random random = new java.util.Random();
    StringBuffer sb = new StringBuffer();
    for (int i=1; i<=9; i++) sb.append(random.nextInt(10));
    sb.insert(5,'-');
    sb.insert(3,'-');
    return sb.toString();
  }
Inserting Data and Retrieving Generated Keys
The following code inserts a new row into Sample.Person and retrieves the generated ID key.
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 following code creates and uses a scrollable result set:
Updating a Scrollable Result Set
The following code updates an open result set and saves the changes to the database:
Using Transactions
The InterSystems JDBC driver supports the JDBC transaction API.
Transaction Handling Methods
Here is a brief summary of the java.sql.Connection methods used for transaction handling:
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.
setTransactionIsolation()
Sets the isolation level for a transaction. InterSystems IRIS supports the following JDBC transaction isolation levels:
getIsolationLevel()
Returns the current transaction isolation level for the Connection object.