JDBC Fundamentals
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 section provides examples of some frequently used JDBC classes and methods for querying databases and working with the results.
-
A Simple JDBC Application is a complete but very simple application that demonstrates the basic features of JDBC.
-
The following sections demonstrate how to use PreparedStatement and CallableStatement to query databases and return a ResultSet:
-
Executing a Prepared Statement — an example using implicit join syntax.
-
Executing Stored Procedures with CallableStatement — an example that executes a stored procedure.
-
Returning Multiple Result Sets — accessing multiple result sets returned by InterSystems IRIS stored procedures.
-
-
The following sections demonstrate using JDBC result sets to insert and update data in a database:
-
Inserting Data and Retrieving Generated Keys — using PreparedStatement and the SQL INSERT command.
-
Scrolling a Result Set — randomly accessing any row of a result set.
-
Using Transactions — using the JDBC transaction model to commit or roll back changes.
-
In most cases, the examples in this section 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. The examples assume that a connection object named dbconnection has already been opened, and that all code fragments are within an appropriate try/catch statement.
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 in the following sections.
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
Query Using 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
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();
-
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(); }
Query Using 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();
Query 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.
The Native SDK for Java offers an alternative to the java.sql transaction model demonstrated here. The Native SDK transaction model is based on ObjectScript transaction methods, and is not interchangeable with the JDBC model. The Native SDK model must be used if your transactions include Native SDK method calls. See Using the Native SDK for Java for details.