Receiving Result Sets in Java


The following examples cover how Java can be used in conjunction with stored procedures.

Receiving Result Sets in a Java Application

Figure H.1 shows java application code, TotalRaise.java. It is a complete rewrite of the total_raise() SQL procedure of Figure 5.3. TotalRaise.java relies on calling the SQL procedure read_emp() (in Figure 5.22) to provide a cursor for data to be processed. The TotalRaise.java program and the read_emp() procedure together accomplish exactly the same task as the total_raise() SQL procedure, although the two approaches are fundamentally different in design. When implementing the logic inside the SQL procedure, less data is passed between the client and server, which results in less network traffic and better performance. Using SQL procedures to process the logic, therefore, is more suitable for transactions involving large result sets. There are, however, advantages to processing data in the application as well:

  • You can implement more complex business logic on the client side because it can be integrated better with the client's application logic.

  • SQL PL is not as powerful a programming language as C/C++ or Java. For example, with these languages on the client side, you can issue calls to the operating system, as well as have access to information about the client environment.

  • You can build applications that can be portable across different RDBMs by using standard APIs.

Figure H.1. TotalRaise.javaAn example of using Java import java.lang. to receive a result set provided by an SQL procedure.
 import java.lang.*; import java.util.*; import java.io.*; import java.sql.*; import COM.ibm.db2.jdbc.app.*; class TotalRaise {     static     {         try         {             Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();         }         catch (Exception e)         {             System.out.println ("\n Error loading DB2 Driver...\n");             System.out.println (e);             System.exit(1);         }     }     public static void main (String argv[])     {         double v_min, v_max;         Connection con = null;         if (argv.length != 2)         {             System.out.println("Usage: java TotalRaise MinRaise MaxRaise");             System.exit(1);         }         v_min = Double.parseDouble(argv[0]);         v_max = Double.parseDouble(argv[1]);         try         {             // Connect to Sample database             String url = "jdbc:db2:sample";             con = DriverManager.getConnection(url);             CallableStatement cs = con.prepareCall("CALL db2admin.read_emp()");             cs.execute();             ResultSet rs = cs.getResultSet();                         /*--(1)*/             double v_total=0;             double v_raise;             double v_salary, v_bonus, v_comm;             while ( rs.next() )                                       /*--(2)*/             {                 v_raise = v_min;                 v_salary = rs.getDouble(1);                           /*--(3)*/                 v_bonus  = rs.getDouble(2);                 v_comm   = rs.getDouble(3);                           /*--(4)*/                 if ( v_bonus >= 600 ) { v_raise += 0.04; }                 if ( v_comm < 2000 )                 {                     v_raise += 0.03;                 }                 else if ( v_comm < 3000 )                 {                     v_raise += 0.02;                 }                 else                 {                     v_raise += 0.01;                 }                 if ( v_raise > v_max ) { v_raise = v_max; }                 v_total += v_salary * v_raise;             } /* while */             System.out.println(v_total);             rs.close();             cs.close ();             con.close ();             System.out.println("Complete(0).");         }         catch (Exception e)         {             try             {                 if( con != null )                 {                     con.close();                 }             }             catch (Exception x)             {   //ignore this exception             }             System.out.println (e);         } /* try-catch block */     } /* main() method */ } /* class TotalRaise */ import java.util.*; import java.io.*; import java.sql.*; import COM.ibm.db2.jdbc.app.*; class TotalRaise {     static     {         try         {             Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();         }         catch (Exception e)         {             System.out.println ("\n Error loading DB2 Driver...\n");             System.out.println (e);             System.exit(1);         }     }     public static void main (String argv[])     {         double v_min, v_max;         Connection con = null;         if (argv.length != 2)         {             System.out.println("Usage: java TotalRaise MinRaise MaxRaise");             System.exit(1);         }         v_min = Double.parseDouble(argv[0]);         v_max = Double.parseDouble(argv[1]);         try         {             // Connect to Sample database             String url = "jdbc:db2:sample";             con = DriverManager.getConnection(url);             CallableStatement cs = con.prepareCall("CALL db2admin.read_emp()");             cs.execute();             ResultSet rs = cs.getResultSet();                         /*--(1)*/             double v_total=0;             double v_raise;             double v_salary, v_bonus, v_comm;             while ( rs.next() )                                       /*--(2)*/             {                 v_raise = v_min;                 v_salary = rs.getDouble(1);                           /*--(3)*/                 v_bonus  = rs.getDouble(2);                 v_comm   = rs.getDouble(3);                           /*--(4)*/                 if ( v_bonus >= 600 ) { v_raise += 0.04; }                 if ( v_comm < 2000 )                 {                     v_raise += 0.03;                 }                 else if ( v_comm < 3000 )                 {                     v_raise += 0.02;                 }                 else                 {                     v_raise += 0.01;                 }                 if ( v_raise > v_max ) { v_raise = v_max; }                 v_total += v_salary * v_raise;             } /* while */             System.out.println(v_total);             rs.close();             cs.close ();             con.close ();             System.out.println("Complete(0).");         }         catch (Exception e)         {             try             {                 if( con != null )                 {                     con.close();                 }             }             catch (Exception x)             {   //ignore this exception             }             System.out.println (e);         } /* try-catch block */     } /* main() method */ } /* class TotalRaise */ 

Figure H.1 shows the code <for TotalRaise.java.

In Java, you need a CallableStatement object to invoke SQL procedures. The result sets will be returned when you invoke the getresultSet() method of the Statement interface in Line (1). To receive the result set, you will need to also declare a ResultSet object to hold the retrieved data. The ResultSet class has many getXXX() methods for all regular SQL data types. In this example, the getdouble() method is used on Lines (3) and (4). By calling the geTDouble() method, data is fetched into a local variable. The ResultSet class has the next() method to advance the cursor and fetch the next row of data. The return value from the next() method on Line (2) indicates whether the end of result set is reached. If next() returns false, the cursor has reached the end of the result set.

For better performance, and as good programming practice, close all ResultSet, CallableStatement, and Connection objects when they are no longer needed.

There are other useful methods for the Statement and ResultSet interface. Some of them will be covered later in this appendix.

Receiving Result Sets in a Java Stored Procedure

Writing a Java stored procedure is very similar to writing a Java application program. Figure H.2 shows how the procedure total_salary, as shown in Figure 8.15, can be rewritten in Java.

Figure H.2. Rewrite of total_salary() in Java.
 /**  * JDBC Stored Procedure DB2ADMIN.TotalSalary  * @param p_dept  * @param p_total  */ package TotalSalary; import java.sql.*;                   // JDBC classes public class TotalSalary {     public static void totalSalary ( String p_dept,                                      java.math.BigDecimal[] p_total ) throws SQLException, Exception     {         // Get connection to the database         Connection con = DriverManager.getConnection("jdbc:default:connection");   /* --1 */         CallableStatement cStmt = null;         ResultSet rs = null;         String sql;         // parameters from procedures        String v_fname, v_lname;        java.math.BigDecimal v_salary;        // Set up the dynamic call to the procedure        // Setup input parameters        // Register output parameters        // Call the procedures        sql = "CALL to_caller1(?)";        cStmt = con.prepareCall(sql);        cStmt.setString(1, p_dept);        cStmt.execute();        // Process the result set        rs = cStmt.getResultSet();        p_total[0] = new java.math.BigDecimal("0.00");        while (rs.next()) {           v_fname = rs.getString(1);           v_lname = rs.getString(2);           v_salary = rs.getBigDecimal(3).setScale(2);           p_total[0] = p_total[0].add(v_salary);        }        // Close the result set and statement        rs.close();        cStmt.close();     } } 

In a Java stored procedure, because the calling application already has a connection to the database, you inherit the connection as shown on Line (1). The Java procedure calls the to_caller SQL procedure, and traverses through the employees in the department to return the sum of the salaries for the department. In addition to building the java package for the stored procedure in Figure H.2, you would also need to register the stored procedure using the CREATE PROCEDURE statement. Figure H.3 shows an example of this for the iSeries platform.

Figure H.3. Registering the totalSalary procedure on iSeries.
 CREATE PROCEDURE TotalSalary ( IN p_dept CHARACTER(3),                                OUT p_total DECIMAL(9,2) )     SPECIFIC TOTAL_SALARY     NOT DETERMINISTIC     LANGUAGE Java     EXTERNAL NAME 'DB2ADMIN.TOTAL_SALARY:TotalSalary.TotalSalary.totalSalary'     FENCED     PARAMETER STYLE JAVA 



    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net