HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

"Oracle Extends Support for Standards Based SQLJ"
Volume: 4 Issue: 2, p. 44

	

Listing 1.
 
CREATE TABLE PARTS_MASTER 
 (PART_ID NUMBER(8) PRIMARY KEY, 
  PART_NAME VARCHAR(40), 
  SUPPLIER VARCHAR(200)); 

CREATE TABLE MRP 
 (PART_ID REFERENCES PARTS_MASTER, 
  QUANTITY_ON_HAND NUMBER(6), 
  REORDER_THRESHOLD NUMBER(6)); 
  

// Part of a SQLJ program, showing definition of one method: 
 public class inventory { 
 ... 
 public void pullStock (int part, int quantity) throws OutOfStock { 

  int on_hand, threshold; 

  #sql { SELECT QUANTITY_ON_HAND, REORDER_THRESHOLD 
    INTO :on_hand, :threshold FROM MRP 
    WHERE PART_ID = :part FOR UPDATE }; 
  on_hand -= quantity; 

  if (on_hand < threshold) { 
  String supplier; 
  
#sql { SELECT SUPPLIER INTO :supplier FROM PARTS_MASTER 
    WHERE PART_ID = :part }; 
  inventory.orderMore(part, quantity, supplier); 
  } 

  if (on_hand < 0) { 
  #sql { ROLLBACK }; 
  throw new OutOfStock(); 
  } else { 
  #sql { UPDATE MRP SET QUANTITY_ON_HAND = :on_hand 
    WHERE PART_ID = :part }; 
  #sql { COMMIT }; 
  } 
 } 
 ... 
 } 
  

Listing 2.
 
public void printStock () { 
 AllStock iter; 
 // Instantiate the iterator with a SQL query 
 #sql iter = { SELECT PART_NAME AS "part", 
       QUANTITY_ON_HAND AS "quantity" 
     FROM PARTS_MASTER, MRP 
    WHERE PARTS_MASTER.PART_ID = MRP.PART_ID }; 
 // Now loop through the result rows 
 while (iter.next()) { 
  System.out.println("Part: " + iter.part() + ", Quantity: " 
       + iter.quantity()); 
 } 
 iter.close(); 
 } 

Listing 3.
 
// SQLJ 
 float w; java.sql.Date x; int y; String z; 
 ... 
 #sql { SELECT C1, C2 INTO :w, :x FROM TAB WHERE C3 = :y AND C4 = :z }; 
  

// JDBC 
 float w; java.sql.Date x; int y; String z; 
 ... 
 PreparedStatement s = connection.prepareStatement( 
 "SELECT C1, C2 FROM TAB WHERE C3 = ? AND C4 = ?"); 
 s.setInt(1, y); 
 s.setString(2, z); 
 ResultSet r = s.executeQuery(); 
 r.next(); 
 w = r.getFloat(1); 
 x = r.getDate(2); 
 r.close(); 
 s.close(); 

      
 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.