Using Java Database Connectivity (JDBC) with Oracle

Like this article? We recommend

Core Java, Vol. II-Advanced Features, 12th Edition

Learn More Buy

Like this article? We recommend

Like this article? We recommend 

Core Java, Vol. II-Advanced Features, 12th Edition

Learn More Buy

Handling Resultsets in JDBC

We have seen how a query executed against a database returned a ResultSet that could be processed for obtaining the individual rows returned by the query. To do this, we used the ResultSet object obtained as return value of the exceuteQuery() method of Statement or PreparedStatement object. Also, we saw how a PL/SQL function returned a REF CURSOR using prepareCall() method of CallableStatement object and how this was captured in a JDBC resultset using getCursor() method cast to an OracleCallableStatement. The resultsets obtained in this way conform to JDBC 1.0 standards and are limited in scope. In this section we will elaborate on enhancements of resultsets in JDBC 2.0. We start with a discussion of PL/SQL procedures and/or functions returning resultsets.

Returning Resultsets

In the sub-section "Oracle Type Extensions," within the section "Oracle JDBC Extensions", a method of handling resultsets in the form of a REF CURSOR returned by a PL/SQL procedure or function was described. This is one way of handling returned resultsets in JDBC. In this sub-section, we describe a second way of handling returned resultsets. We use the same example as demonstrated earlier. The steps involved in this are as follows:

1. Call the packaged function using a JDBC CallableStatement object.

2. The return value of the function which is a REF CURSOR is registered as an OUT parameter using the typecode OracleTypes.CURSOR.

3. Execute the CallableStatement to obtain the REF CURSOR returned by the PL/SQL packaged function.

4. The CallableStatement object is cast to a ResultSet object (instead of an OracleCallableStatement object) to use the getObject() (instead of a getCursor() method), which is an Oracle extension to the standard JDBC API, and returns the REF CURSOR into a ResultSet object.

5. Process the resultset as desired, for example, write to an O/S file.

The complete JDBC program is given below:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

public class RefCursorExample2 {
 public static void main(String[] args)
 throws SQLException, IOException {
 int ret_code;
 Connection conn = null;
 try {
  //Load and register Oracle driver
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 //Establish a connection

 conn = DriverManager.getConnection("jdbc:oracle:thin:
 @training:1521:Oracle", "oratest", "oratest");

 String i_etype = "SENIOR";
 CallableStatement cstmt = conn.prepareCall("{? = 
 call pkg_refcur.f_refcur(?)}");

 // Register the OUT parameter of the PL/SQL function as 
 // a OracleTypes.CURSOR datatype
 cstmt.registerOutParameter(1, OracleTypes.CURSOR);
 cstmt.setString(2, i_etype);
 cstmt.executeUpdate();

 ResultSet rset;

 // Obtain the cursor returned by the PL/SQL function using getObject()
 // and cast it to the ResultSet object.
 rset = (ResultSet) cstmt.getObject(1);
 String str = "";
 while (rset.next())
  str += rset.getString(1)+" "+ rset.getInt(2)+" "
  +rset.getString(3)+" "+rset.getFloat(4)+" 
  "+rset.getFloat(5)+" "+rset.getInt(6)+"\n";
 byte buf[] = str.getBytes();
 OutputStream fp = new FileOutputStream("senior.lst");
 fp.write(buf);
 fp.close();

 rset.close();
 cstmt.close();
 conn.close();
  } catch (SQLException e) {ret_code = e.getErrorCode(); 
  System.err.println(ret_code + e.getMessage()); conn.close();}
   catch (IOException e) { System.err.println
   ("Java File I/O Error:"+ e.getMessage()); }
 }
}

Scrollable Resultsets

In JDBC 1.0, the resultsets so defined were limited in scope in that the rows in the resultset could be accessed only in the forward-direction. This means there was no way of moving back and forth in a resultset or jumping to a particular row identified by a row number. Also, the resultsets were read-only in that there was no way for inserting new rows into the resultset, updating a particular row, or deleting a particular row. JDBC 2.0 made enhancements to resultsets by introducing scrollability, positioning, and concurrency capabilities. This sub-section discusses the first two enhancements of scrollability and positioning. The next sub-section discusses the enhanced feature of concurrency or updateable resultsets.

Scrollability refers to moving forwards or backwards through rows in a resultset. Positioning refers to moving the current row position to a different position by jumping to a specific row. These two features are provided by means of three additional method calls, one each for createStatement(), prepareStatement(), and prepareCall() methods. These new methods take two new parameters namely, the resultSetType and resultSetConcurrency. The definition of these new methods is as follows:

connection.createStatement(int resultSetType, int resultSetConcurrency);
connection.prepareStatement(String sql, int resultSetType, 
    int resultSetConcurrency);
connection.prepareCall(String sql, int resultSetType, 
    int resultSetConcurrency);

The parameter resultSetType determines whether a resultset is scrollable or not. It can take one of the following three values only:

ResultSet.TYPE_FORWARD_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE

TYPE_FORWARD_ONLY specifies that a resultset is not scrollable, that is, rows within it can be advanced only in the forward direction.

TYPE_SCROLL_INSENSITIVE specifies that a resultset is scrollable in either direction but is insensitive to changes committed by other transactions or other statements in the same transaction.

TYPE_SCROLL_SENSITIVE specifies that a resultset is scrollable in either direction and is affected by changes committed by other transactions or statements within the same transaction.

The second parameter resultSetConcurrency determines whether a resultset is updateable or not and can take one of the two values only:

ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE

With these resultset types and two concurrency types, there are six different kinds of resultsets that can be defined. These are

Type_Forward_Only/Concur_Read_Only
Type_Forward_only/Concur_Updateable
Type_Scroll_Insensitive/Concur_Read_Only
Type_Scroll_Insensitive/Concur_Updateable
Type_Scroll_Sensitive/Concur_Read_Only
Type_Scroll_Sensitive/Concur_Updateable

Specify Scroll Sensitivity

In a JDK1.1.x environment, the following have to be specified:

OracleResultSet.TYPE_SCROLL_SENSITIVE and OracleResultSet.TYPE_SCROLL_INSENSITIVE. This is because the static constants are part of the OracleResultSet class included as an Oracle extension. To make this class available, import oracle.jdbc.driver.* package.

A scrollable resultset allows for random access to its rows. Random access in turn allows for accessing a particular row directly without having to advance row by row. This direct positioning of control at a specific row is termed positioning.

To enable scrollability and positioning, in JDBC 2.0, the ResultSet class provides a set of _new methods in addition to the next() method available in JDBC 1.0. These methods are as follows:

• boolean first()—Positions the control at the first row in the resultset and returns true. If there are no rows in the resultset, it returns false.

• boolean last()—Positions the control at the last row in the resultset and returns true. If there are no rows in the resultset, it returns false.

• boolean previous()—Positions the control at the previous row in the resultset relative to the current row. This returns true if the previous row exists. It returns false, if it causes the resultset to be positioned before the first row.

• boolean absolute(int row)—Directly jumps to the row specified by the parameter. This is absolute positioning, meaning it jumps to the row specified starting from the beginning or end depending on whether the parameter is positive or negative. If control moves beyond the first or last row, the cursor is left before the first row or after the last row. This returns true if the row jumped to is valid. If a zero is passed, it throws an exception. Remember that absolute(1) is same as first() and absolute(-1) is same as last().

  boolean relative(int offset)—directly jumps to the row starting from the current row by an offset specified by the parameter. This is relative positioning meaning, it jumps to the row specified starting from the current row. This returns true if the row jumped to is valid. If control moves beyond the first or last row, the cursor is left before the first row or after the last row.

• void beforeFirst()—Positions the resultset before the very first row.

• void afterLast()—Positions the resultset after the last row.

• boolean isFirst()—Returns true if the resultset is positioned at the first row.

• boolean isLast()—Returns true if the resultset is positioned at the last row.

• boolean isBeforeFirst()—Returns true if the resultset is positioned before the first row.

• boolean isAfterLast()—Returns true if the resultset is positioned after the last row.

• int getRow()—Returns the row number of the current row.

Updateable Resultsets

Updateable resultsets means the ability to update the contents of specific row(s) in the resultset and propagating these changes to the underlying database. This capability is supported in JDBC 2.0 only. Also the operations of INSERT and DELETE are possible. New rows can be inserted into the underlying table and existing rows can be deleted from both the resultset as well as the underlying table. The CLASSPATH has to include classes12.zip for using updateable resultsets.

We will first discuss the operation of UPDATE, followed by INSERT and DELETE.

UPDATE Operation Through a Resultset

The following are the steps involved in creating and using an updateable resultset:

1. To create an updateable resultset, the resultSetConcurrency parameter has to be specified as ResulSet.CONCUR_UPDATABLE while defining the createStatement(), preparedStatement(), or prepareCall() method on the Connection object. This is shown below:

   Connection conn = null;
   //Load and register Oracle driver
     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    //Establish a connection
    conn =
   DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
    Oracle", "oratest", "oratest");
   Statement stmt = conn.createStatement(Resultset.TYPE_SCROLL_SENSITIVE,
   ResultSet.CONCUR_UPDATEABLE);
   String sql = ...
   ResultSet rset = stmt.executeQuery(sql);
   <... ... ... ...

2. Use the updateXXX() (for example, updateInt() and updateString()) methods on the ResultSet object to set the values of the resultset columns. JDBC 2.0 has provided these methods for each of the Java primitive types as well for some SQL types that correspond to Java objects. The use of this method is as follows:

   rset.updateFloat(2, new_val);

   Here 2 refers to the second column in the resultset and new_val is a variable that holds a new value to which this column data is to be set to.

3. Use the updateRow() method on the ResultSet object to propagate the changes made to the resultset to the underlying database table and commit them. This has to be done once for each row in the resultset that is changed. This is shown below:

   rset.updateRow();

A complete example is shown below:

import java.sql.*;

public class UpdateableResultSetExample {
 public static void main(String[] args)
 throws SQLException {
int ret_code;
Connection conn = null;
 try {
  //Load and register Oracle driver
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 //Establish a connection

 conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:Oracle", 
 "oratest", "oratest");
 int i_deptno = 10;
 String sql = "SELECT empno, sal, comm FROM emp_with_type WHERE deptno = ?" ;

 // Specify the resultset as Scroll Sensitive and Updateable
 PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_
 SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  pstmt.setInt(1, i_deptno);
  ResultSet rset = pstmt.executeQuery();
  while (rset.next()) {
   float i_sal = rset.getFloat(2);
   float i_comm = rset.getFloat(3);
   // Populate the resultset column using 
   // the updateFloat() method on the ResultSet object
   rset.updateFloat(2, (float)(i_sal+(1.25*i_comm)));
   // Update the corresponding resultset row using the above value.
   rset.updateRow();
   }
 rset.close();
 pstmt.close();
 pstmt.close();
 conn.close();
  } catch (SQLException e) {ret_code = e.getErrorCode(); 
   System.err.println(ret_code + e.getMessage()); conn.close();}
 }
}

INSERT Operation Through a Resultset

A new row can be inserted into the underlying database table through a resultset. The steps involved are:

1. Navigate to the insert row. This is done by using the method moveToInsertRow() on the ResultSet object. The insert row is a special row in the resultset and is different from the existing rows in the resultset that have been returned by the query. After the insert, the control can be made to navigate to the current row (i.e., the row before moving to the new insert row) using the method moveToCurrentRow() on the ResultSet object.

2. Update the contents of this insert row by using the updateXXX() methods on the ResultSet object.

3. Apply the new row to the database by using insertRow() method on the ResultSet object.

DELETE Operation Through a Resultset

A row in a resultset can be deleted as follows:

1. Navigate to the specific row.

2. Delete the row using the deleteRow() method on the ResultSet object.

Metadata

Metadata is data about data. Given a database table, the rows in the table constitute data whereas the type and name of the individual columns constitutes metadata. Oracle JDBC supports two types of metadata, namely,

1. Resultset metadata

2. Database metadata

Resultset Metadata

The resultset metadata can be obtained by calling the methods in the ResultSetMetaData class. To get the basic information about resultset metadata, the following methods can be used:

• getMetaData()—This method operates on a ResultSet object and returns a ResultSetMetaData object. This is shown below:

ResultSet rset;
ResultSetMetaData rsmd;
rset = pstmt.executeQuery();
rsmd = rset.getMetaData();

• getColumnName(int)—This method operates on a ResultSetMetaData object and returns the name of the column in the resultset whose position is specified by the int parameter. This is shown below:

String col_name = rsmd.getColumnName(i);

• getColumnType(int)—This method operates on a ResultSetMetaData object and returns the data type of the column in the resultset whose position is specified by the int parameter. It always returns an integer value. This return value corresponds to one of the variables in the java.sql.Types class. This is shown below:

  int col_data_type = rsmd.getColumnType(i);
  if (col_data_type = java.sql.Types.VARCHAR)
  {
  ......
  }

Database Metadata

The database metadata can be obtained by calling the methods in the DatabaseSetMetaData class. Examples of database metadata include the type of JDBC drivers supported, the table names, constraints defined on tables in the database, and so on. To get the basic information about database metadata, the following method must be used:

• getMetaData()—This method operates on a Connection object and returns a DatabaseMetaData object. This is shown below:

  Connection conn;
  DatabaseMetaData dbmd;
  dbmd = conn.getMetaData();