/* ALL RIGHTS RESERVED,COPYRIGHT (C)2000,HITACHI,LTD. */
/* LICENSED MATERIAL OF HITACHI,LTD. */
/************************************************************************/
/* name = HiRDB 06-00 Java stored sample 2 */
/************************************************************************/
import java.lang.*;
import java.math.*;
import java.sql.*;
/************************************************************************/
/* name = sample_2 class */
/************************************************************************/
public class sample2 {
/*==================================================================*/
/* name = main method for debugging */
/*==================================================================*/
public static void main(String args[]) throws SQLException {
java.sql.Date fromdate = Date.valueOf("1996-06-01");
java.sql.Date todate = Date.valueOf("1996-06-30");
try {
// Registering the Driver class
Class.forName("JP.co.Hitachi.soft.HiRDB.JDBC.PrdbDriver");
} catch (ClassNotFoundException ex) {
System.out.println("\n*** ClassNotFoundException caught ***\n");
ex.printStackTrace();
System.out.println ("");
System.out.println("\n*************************************\n");
return;
}
jproc1(fromdate, todate);
}
/*==================================================================*/
/* name = sample_2 method */
/*==================================================================*/
public static void jproc1(java.sql.Date fromdate, java.sql.Date todate)
throws SQLException {
java.lang.Integer x_goods_no;
java.math.BigDecimal x_quantity_1, x_total_quantity;
try {
// Creating a connection object (CONNECT not issued within the Java
procedure)
java.sql.Connection con =
DriverManager.getConnection("jdbc:hitachi:PrdbDrive",
"\"USER1\"", "\"PASS1\"");
con.setAutoCommit(false); // Suppressing automatic commit
// SELECT (stmt1) preprocessing
java.sql.PreparedStatement stmt1 =
con.prepareStatement("SELECT goods_no, quantity_1
, entrydate FROM tran_t1
WHERE entrydate BETWEEN ? AND ? ORDER BY entrydate");
// SELECT (stmt2) preprocessing (outside the loop)
java.sql.PreparedStatement stmt2 =
con.prepareStatement("SELECT total_quantity FROM master_t1
WHERE goods_no = ?");
// INSERT (stmt3) preprocessing (outside the loop)
java.sql.PreparedStatement stmt3 =
con.prepareStatement("INSERT INTO master_t1 VALUES(?, ?)");
// UPDATE (stmt4) preprocessing (outside the loop)
java.sql.PreparedStatement stmt4 =
con.prepareStatement("UPDATE master_t1 SET total_quantity = ?
WHERE goods_no = ?");
// Specifying SELECT (stmt1) input parameters
stmt1.setDate(1, fromdate);
stmt1.setDate(2, todate);
// Executing SELECT (stmt1)
java.sql.ResultSet rs1 = stmt1.executeQuery();
while (rs1.next()) {
// Obtaining the retrieval result of SELECT (stmt1)
x_goods_no = (Integer)rs1.getObject("goods_no");
x_quantity_1 = rs1.getBigDecimal("quantity_1");
// Specifying SELECT (stmt2) input parameter
stmt2.setObject(1, x_goods_no);
// Executing SELECT (stmt2)
java.sql.ResultSet rs2 = stmt2.executeQuery();
// Checking whether or not goods_no has been registered to
determine action
if (!rs2.next()) { // Not registered ==> Add a new entry
// Closing the SELECT (stmt2) cursor before updating
rs2.close();
// Specifying INSERT (stmt3) input parameters
stmt3.setObject(1, x_goods_no);
stmt3.setBigDecimal(2, x_quantity_1);
// Executing INSERT (stmt3)
stmt3.executeUpdate();
} else { // Registered ==> Update the
existing entry
// Obtaining the current value
x_total_quantity = rs2.getBigDecimal("total_quantity");
// Incrementing
x_total_quantity = x_total_quantity.add(x_quantity_1);
// Closing SELECT (stmt2) cursor before updating
rs2.close();
// Specifying UPDATE (stmt4) input parameters
stmt4.setBigDecimal(1, x_total_quantity);
stmt4.setObject(2, x_goods_no);
stmt4.executeUpdate() ;
}
}
// Closing SELECT (stmt1) cursor
rs1.close();
// Releasing each statement object
stmt1.close();
stmt2.close();
stmt3.close();
stmt3.close();
// Disconnecting
con.close();
} catch (SQLException ex) { // SQL error handling procedure
SQLException fast_ex = ex;
System.out.println("\n***** SQLException caught *****\n");
while (ex != null) {
System.out.println ("SQLState: " + ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("Vendor: " + ex.getErrorCode ());
ex.printStackTrace();
ex = ex.getNextException ();
System.out.println ("");
}
System.out.println("*******************************\n");
throw fast_ex;
}
return;
}
} |