Trail: JDBC(TM) Database Access(2)

package com.oracle.tutorial.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class StoredProcedureMySQLSample {

 private String dbName;
 private Connection con;
 private String dbms;

 public StoredProcedureMySQLSample(Connection connArg, String dbName,
 String dbmsArg) {
 super();
 this.con = connArg;
 this.dbName = dbName;
 this.dbms = dbmsArg;
 }
 
 public void createProcedureRaisePrice() throws SQLException {//新建存储过程
 
 String createProcedure = null;

 String queryDrop = "DROP PROCEDURE IF EXISTS RAISE_PRICE";

 createProcedure =
 "create procedure RAISE_PRICE(IN coffeeName varchar(32), IN maximumPercentage float, INOUT newPrice numeric(10,2)) " +
 "begin " +
 "main: BEGIN " +
 "declare maximumNewPrice numeric(10,2); " +
 "declare oldPrice numeric(10,2); " +
 "select COFFEES.PRICE into oldPrice " +//读取原价格
 "from COFFEES " +
 "where COFFEES.COF_NAME = coffeeName; " +
 "set maximumNewPrice = oldPrice * (1 + maximumPercentage); " +
 "if (newPrice > maximumNewPrice) " +
 "then set newPrice = maximumNewPrice; " +
 "end if; " +
 "if (newPrice <= oldPrice) " +
 "then set newPrice = oldPrice;" +
 "leave main; " +
 "end if; " +
 "update COFFEES " +
 "set COFFEES.PRICE = newPrice " +//写入新价格
 "where COFFEES.COF_NAME = coffeeName; " +
 "select newPrice; " +
 "END main; " +
 "end";
 
 Statement stmt = null;
 Statement stmtDrop = null;

 try {
 System.out.println("Calling DROP PROCEDURE");
 stmtDrop = con.createStatement();
 stmtDrop.execute(queryDrop);
 } catch (SQLException e) {
 JDBCTutorialUtilities.printSQLException(e);
 } finally {
 if (stmtDrop != null) { stmtDrop.close(); }
 }


 try {
 stmt = con.createStatement();
 stmt.executeUpdate(createProcedure);
 } catch (SQLException e) {
 JDBCTutorialUtilities.printSQLException(e);
 } finally {
 if (stmt != null) { stmt.close(); }
 }

 
 }
 
 
 public void createProcedureGetSupplierOfCoffee() throws SQLException {

 String createProcedure = null;

 String queryDrop = "DROP PROCEDURE IF EXISTS GET_SUPPLIER_OF_COFFEE";

 createProcedure =
 "create procedure GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40)) " +
 "begin " +
 "select SUPPLIERS.SUP_NAME into supplierName " +
 "from SUPPLIERS, COFFEES " +
 "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
 "and coffeeName = COFFEES.COF_NAME; " +
 "select supplierName; " +
 "end";
 Statement stmt = null;
 Statement stmtDrop = null;

 try {
 System.out.println("Calling DROP PROCEDURE");
 stmtDrop = con.createStatement();
 stmtDrop.execute(queryDrop);
 } catch (SQLException e) {
 JDBCTutorialUtilities.printSQLException(e);
 } finally {
 if (stmtDrop != null) { stmtDrop.close(); }
 }


 try {
 stmt = con.createStatement();
 stmt.executeUpdate(createProcedure);
 } catch (SQLException e) {
 JDBCTutorialUtilities.printSQLException(e);
 } finally {
 if (stmt != null) { stmt.close(); }
 }
 }
 

 public void createProcedureShowSuppliers() throws SQLException {
 String createProcedure = null;

 String queryDrop = "DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS";

 createProcedure =
 "create procedure SHOW_SUPPLIERS() " +
 "begin " +
 "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
 "from SUPPLIERS, COFFEES " +
 "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
 "order by SUP_NAME; " +
 "end";
 Statement stmt = null;
 Statement stmtDrop = null;

 try {
 System.out.println("Calling DROP PROCEDURE");
 stmtDrop = con.createStatement();
 stmtDrop.execute(queryDrop);
 } catch (SQLException e) {
 JDBCTutorialUtilities.printSQLException(e);
 } finally {
 if (stmtDrop != null) { stmtDrop.close(); }
 }


 try {
 stmt = con.createStatement();
 stmt.executeUpdate(createProcedure);
 } catch (SQLException e) {
 JDBCTutorialUtilities.printSQLException(e);
 } finally {
 if (stmt != null) { stmt.close(); }
 }
 }

public void runStoredProcedures(String coffeeNameArg, float maximumPercentageArg, float newPriceArg) throws SQLException {
    CallableStatement cs = null;

    try {
      
      System.out.println("\nCalling the procedure GET_SUPPLIER_OF_COFFEE");
      cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");//存储过程语句
      cs.setString(1, coffeeNameArg);//in的跟以前一样赋值
      cs.registerOutParameter(2, Types.VARCHAR);//执行前先注册out参数
      cs.executeQuery();//执行
            
      String supplierName = cs.getString(2);//获取结果,out参数为第二个
      
      if (supplierName != null) {
        System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName);          
      } else {
        System.out.println("\nUnable to find the coffee " + coffeeNameArg);        
      }
      
      System.out.println("\nCalling the procedure SHOW_SUPPLIERS");
      cs = this.con.prepareCall("{call SHOW_SUPPLIERS}");
      ResultSet rs = cs.executeQuery();

      while (rs.next()) {
        String supplier = rs.getString("SUP_NAME");
        String coffee = rs.getString("COF_NAME");
        System.out.println(supplier + ": " + coffee);
      }
      
      System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:");
      CoffeesTable.viewTable(this.con);
      
      System.out.println("\nCalling the procedure RAISE_PRICE");
      cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
      cs.setString(1, coffeeNameArg);
      cs.setFloat(2, maximumPercentageArg);
      cs.registerOutParameter(3, Types.NUMERIC);//inout类型的也要注册
      cs.setFloat(3, newPriceArg);
      
      cs.execute();//如果不确定会返回几个ResultSet就用这个
      
      System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3));
      
      System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:");
      CoffeesTable.viewTable(this.con);
      


    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      if (cs != null) { cs.close(); }
    }
  }

 

关于RowSet和几个不常见类型(略) (mysql没有,oracle有)

 

用Swing界面展现结果(略)