docs.intersystems.com
Home  /  Application Development: Language Bindings and Gateways  /  Using Java with the InterSystems JDBC Driver  /  JDBC for Occasional Users


Using Java with the InterSystems JDBC Driver
JDBC for Occasional Users
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


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:
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 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
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 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
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:
Using Callable Statements to Execute Stored Procedures
The following code executes ByName, an InterSystems IRIS stored procedure contained in Demo.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 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:
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.
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:
Using Transactions
The InterSystems JDBC driver supports the JDBC transaction API.
Here is a brief summary of the java.sql.Connection methods used in this example: