Java tutorial
/* * Copyright (c) 1995, 2011, Oracle and/or its affiliates. All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions * are met: * * - Redistributions of source code must retain the above copyright * notice, this list of conditions and the following disclaimer. * * - Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in the * documentation and/or other materials provided with the distribution. * * - Neither the name of Oracle or the names of its * contributors may be used to endorse or promote products derived * from this software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS * IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ package com.oracle.tutorial.jdbc; import java.math.BigDecimal; import java.math.BigInteger; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class StoredProcedureJavaDBSample { private String dbName; private Connection con; private String dbms; private String schema = "APP"; public StoredProcedureJavaDBSample(Connection connArg, String dbName, String dbmsArg) { super(); this.con = connArg; this.dbName = dbName; this.dbms = dbmsArg; } public static void raisePrice(String coffeeName, double maximumPercentage, BigDecimal[] newPrice) throws SQLException { Connection con = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement pstmt = null; ResultSet rs = null; BigDecimal oldPrice; String queryGetCurrentCoffeePrice = "select COFFEES.PRICE " + "from COFFEES " + "where COFFEES.COF_NAME = ?"; pstmt = con.prepareStatement(queryGetCurrentCoffeePrice); pstmt.setString(1, coffeeName); rs = pstmt.executeQuery(); if (rs.next()) { oldPrice = rs.getBigDecimal(1); } else { return; } BigDecimal maximumNewPrice = oldPrice.multiply(new BigDecimal(1 + maximumPercentage)); // Test if newPrice[0] > maximumNewPrice if (newPrice[0].compareTo(maximumNewPrice) == 1) { newPrice[0] = maximumNewPrice; } // Test if newPrice[0] <= oldPrice if (newPrice[0].compareTo(oldPrice) < 1) { newPrice[0] = oldPrice; return; } String queryUpdatePrice = "update COFFEES " + "set COFFEES.PRICE = ? " + "where COFFEES.COF_NAME = ?"; pstmt = con.prepareStatement(queryUpdatePrice); pstmt.setBigDecimal(1, newPrice[0]); pstmt.setString(2, coffeeName); pstmt.executeUpdate(); } public static void getSupplierOfCoffee(String coffeeName, String[] supplierName) throws SQLException { Connection con = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement pstmt = null; ResultSet rs = null; String query = "select SUPPLIERS.SUP_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "and ? = COFFEES.COF_NAME"; pstmt = con.prepareStatement(query); pstmt.setString(1, coffeeName); rs = pstmt.executeQuery(); if (rs.next()) { supplierName[0] = rs.getString(1); } else { supplierName[0] = null; } } public static void showSuppliers(ResultSet[] rs) throws SQLException { Connection con = DriverManager.getConnection("jdbc:default:connection"); Statement stmt = null; String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " + "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME"; stmt = con.createStatement(); rs[0] = stmt.executeQuery(query); } public void createProcedures(Connection con) throws SQLException { Statement stmtCreateShowSuppliers = null; Statement stmtCreateGetSupplierOfCoffee = null; Statement stmtCreateRaisePrice = null; Statement stmtDropShowSuppliers = null; Statement stmtDropGetSupplierOfCoffee = null; Statement stmtDropRaisePrice = null; String queryDropShowSuppliers = "DROP PROCEDURE SHOW_SUPPLIERS"; String queryDropGetSupplierOfCoffee = "DROP PROCEDURE GET_SUPPLIER_OF_COFFEE"; String queryDropRaisePrice = "DROP PROCEDURE RAISE_PRICE"; String queryShowSuppliers = "CREATE PROCEDURE SHOW_SUPPLIERS() " + "PARAMETER STYLE JAVA " + "LANGUAGE JAVA " + "DYNAMIC RESULT SETS 1 " + "EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers'"; String queryGetSupplierOfCoffee = "CREATE PROCEDURE GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40)) " + "PARAMETER STYLE JAVA " + "LANGUAGE JAVA " + "DYNAMIC RESULT SETS 0 " + "EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.getSupplierOfCoffee'"; String queryRaisePrice = "CREATE PROCEDURE RAISE_PRICE(IN coffeeName varchar(32), IN maximumPercentage float, INOUT newPrice numeric(10,2)) " + "PARAMETER STYLE JAVA " + "LANGUAGE JAVA " + "DYNAMIC RESULT SETS 0 " + "EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.raisePrice'"; try { System.out.println("Calling DROP PROCEDURE"); stmtDropShowSuppliers = con.createStatement(); stmtDropShowSuppliers.execute(queryDropShowSuppliers); stmtDropGetSupplierOfCoffee = con.createStatement(); stmtDropGetSupplierOfCoffee.execute(queryDropGetSupplierOfCoffee); stmtDropRaisePrice = con.createStatement(); stmtDropRaisePrice.execute(queryDropRaisePrice); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmtDropShowSuppliers != null) { stmtDropShowSuppliers.close(); } if (stmtDropGetSupplierOfCoffee != null) { stmtDropGetSupplierOfCoffee.close(); } if (stmtDropRaisePrice != null) { stmtDropRaisePrice.close(); } } try { System.out.println("Calling CREATE PROCEDURE"); stmtCreateShowSuppliers = con.createStatement(); stmtCreateShowSuppliers.execute(queryShowSuppliers); stmtCreateGetSupplierOfCoffee = con.createStatement(); stmtCreateGetSupplierOfCoffee.execute(queryGetSupplierOfCoffee); stmtCreateRaisePrice = con.createStatement(); stmtCreateRaisePrice.execute(queryRaisePrice); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (stmtCreateShowSuppliers != null) { stmtCreateShowSuppliers.close(); } if (stmtCreateGetSupplierOfCoffee != null) { stmtCreateGetSupplierOfCoffee.close(); } if (stmtCreateRaisePrice != null) { stmtCreateRaisePrice.close(); } } } public void registerJarFile(String jarPathName) throws SQLException { CallableStatement cs2 = null; CallableStatement cs2a = null; CallableStatement cs3 = null; String query2 = "CALL sqlj.install_jar('" + jarPathName + "','" + this.schema + ".JDBCTutorial',0)"; String query2a = "CALL sqlj.replace_jar('" + jarPathName + "','" + this.schema + ".JDBCTutorial')"; String query3 = "CALL syscs_util.syscs_set_database_property('derby.database.classpath','" + this.schema + ".JDBCTutorial')"; try { System.out.println("Calling " + query2); cs2 = con.prepareCall(query2); cs2.execute(); } catch (SQLException e2) { JDBCTutorialUtilities.printSQLException(e2); } finally { if (cs2 != null) { cs2.close(); } try { System.out.println("Calling " + query2a); cs2a = con.prepareCall(query2a); cs2a.execute(); } catch (SQLException e2a) { JDBCTutorialUtilities.printSQLException(e2a); } finally { if (cs2a != null) { cs2a.close(); } } } try { System.out.println("Calling " + query3); cs3 = con.prepareCall(query3); cs3.execute(); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { if (cs3 != null) { cs3.close(); } } } public void runStoredProcedures(String coffeeNameArg, double maximumPercentageArg, double newPriceArg) throws SQLException { CallableStatement cs = null; try { System.out.println("\nCalling the stored procedure GET_SUPPLIER_OF_COFFEE"); cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}"); cs.setString(1, coffeeNameArg); cs.registerOutParameter(2, Types.VARCHAR); cs.execute(); String supplierName = cs.getString(2); 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.setDouble(2, maximumPercentageArg); cs.registerOutParameter(3, Types.DOUBLE); cs.setDouble(3, newPriceArg); cs.execute(); 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(); } } } public static void main(String[] args) { JDBCTutorialUtilities myJDBCTutorialUtilities; Connection myConnection = null; if (args[0] == null) { System.err.println("Properties file not specified at command line"); return; } else { try { myJDBCTutorialUtilities = new JDBCTutorialUtilities(args[0]); } catch (Exception e) { System.err.println("Problem reading properties file " + args[0]); e.printStackTrace(); return; } } try { myConnection = myJDBCTutorialUtilities.getConnection(); StoredProcedureJavaDBSample mySP = new StoredProcedureJavaDBSample(myConnection, myJDBCTutorialUtilities.dbName, myJDBCTutorialUtilities.dbms); // JDBCTutorialUtilities.initializeTables(myConnection, // myJDBCTutorialUtilities.dbName, // myJDBCTutorialUtilities.dbms); System.out.println("\nCreating stored procedure:"); mySP.createProcedures(myConnection); // System.out.println("\nAdding jar file to Java DB class path:"); // mySP.registerJarFile(myJDBCTutorialUtilities.jarFile); System.out.println("\nRunning all stored procedures:"); mySP.runStoredProcedures("Colombian", 0.10f, 19.99f); } catch (SQLException e) { JDBCTutorialUtilities.printSQLException(e); } finally { JDBCTutorialUtilities.closeConnection(myConnection); } } }