print Printer View e-mail E-mail this page Bookmark Bookmark 
/*
 * @author  Savitha
 * @version 1.0
 *
 * Development Environment        :  Oracle JDeveloper 10g
 * Name of the Application        :  UsingNamedParameters.java
 * Creation/Modification History  :
 *
 *    Savitha     01-Sept-2003      Created.
 */

package oracle.otnsamples.jdbc;

// Java SQL classes
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

// Java IOException class
import java.io.IOException;

//Java Util classes
import java.util.Enumeration;
import java.util.Properties;
import java.util.ResourceBundle;

// Oracle Specific JDBC datasouce class
import oracle.jdbc.pool.OracleDataSource;

 /**
  * This class will demonstrate the JDBC 3.0 feature: Named Parameters.
  * The method createProductInfo() will call a database procedure that will
  * demonstrate the how named parameters can be passed to CallableStatement.
  * It also demonstrates retrieving and registering output parameters by name.
  */
public class UsingNamedParameters {

  /** Database Connection object */
  private static Connection conn = null;

  /** CallableStatement object for calling database procedure. */
  private static CallableStatement cstmt = null;

 /**
  * Default Constructor to instantiate and get a handle to class methods
  * and variables.
  */
  public UsingNamedParameters() {
  }

  /**
   * Main runnable class.
   */
  public static void main(String[] args) throws SQLException,
                                                ClassNotFoundException {
    try {

      // Get the int value of the input parameters for minprice and listprice.
      int minprice  = Integer.parseInt(args[1]);
      int listprice = Integer.parseInt(args[3]);

      // Call the method to create the product in the database.
      UsingNamedParameters.namedParametersInCS(args[0], minprice,
                                               args[2], listprice);

    } catch (Exception ex) {
        // Print exception message.
        System.out.println("unknown exception: "+ex.toString());
        ex.printStackTrace();
    }
  }

 /**
  *  This method will call a database procedure 'createproduct()' by passing
  *  values for the IN parameters and registering the OUT parameter to retrieve
  *  value from the database procedure. Using the JDBC 3.0 feature:
  *  Named Parameters, IN and OUT variables are bound using names instead of
  *  Integers.
  *
  * The database procedure will first create a record in the table:
  * product_information and then another corresponding record for the same
  * product_id in the table: product_descriptions. The procedure returns the
  * created product_id as the OUT parameter when the record is succesfully
  * created.
  * @param<b>name</b> The name of the product to be created.
  * @param<b>minprice</b> The minprice of the product to be created.
  * @param<b>desc</b> The description of the product to be created.
  * @param<b>listprice</b> The listprice of the product to be created.
  * @throws<b>SQLException</b> if any error in executing the SQL statement.
  * @throws<b>ClassNotFoundException</b>
  */
  private static void namedParametersInCS(String name, int minprice,
                                       String desc, int listprice)
    throws SQLException, ClassNotFoundException {
    try {

      // Connect to the database.
      UsingNamedParameters.dbConnection();

      // Create SQL query statement to call the database procedure:
      // createproduct which creates product records in product_information and
      // product_descriptions tables in the database.
      String sqlCall = "BEGIN createProduct(?,?,?,?,?); END;";
      cstmt = (CallableStatement)conn.prepareCall(sqlCall);

      // CallableStatement interface is enhanced by adding new setting
      // methods by name like below:
      cstmt.setString("productname",name);   // Set Product Name.
      cstmt.setString("productdesc",desc);   // Set Product Description.
      cstmt.setInt("listprice",listprice);   // Set Product ListPrice.
      cstmt.setInt("minprice",minprice);     // Set Product MinPrice.

      // Register out parameter which should return the product is created.
      // CallableStatement interface is enhanced to support retrieving and
      // registering output parameters by name.
      cstmt.registerOutParameter("prod_id",Types.INTEGER);

      // Execute the call.
      cstmt.execute();

      // Get the value of the id from the OUT parameter: prod_id
      int id = cstmt.getInt("prod_id");

      // Print the created new product id to the standard output.
      System.out.println("The new Product id is: "+id);

      // Check if the record is succesfully created.
      // Return values : 1 and -1 indicate errors. Check the createSP.sql for
      // more details on this.
      if ((id==1)||(id==-1)) {
        System.out.println("Product record was not created due to some "+
                           " errors. "+"\n"+ "Check the Troubleshooting"+
                           " section in the document: Install.html");
      }

    } catch (SQLException sqlex) {
        // Catch Exceptions and display messages accordingly.
        System.out.println("Problem while connecting and querying the " +
                           "database table: " + sqlex.toString());
                           sqlex.printStackTrace();
    } catch (Exception ex) {
        System.out.println("Problem with jdbc driver in the method: " +
                           ex.toString());
                           ex.printStackTrace();
    } finally {
        // Close the Statement and the connection objects.
        if (cstmt!=null) cstmt.close();
        if (conn!=null)  conn.close();
    }
  }

  /**
   * This method reads the properties file:Connection.properties which is
   * passed as the parameter to it and load it into a java Properties
   * object and returns it.
   * @param<b>file</b> The name of the file having connection details.
   */
  private static Properties loadParams( String file ) throws IOException {
    // Loads a ResourceBundle and creates Properties from it.
    Properties prop = new Properties();
    ResourceBundle bundle = ResourceBundle.getBundle( file );
    Enumeration enum = bundle.getKeys();
    String key = null;
    while( enum.hasMoreElements() ) {
      key = (String)enum.nextElement();
      prop.put( key, bundle.getObject( key ) );
    }
    return prop;
  }

  /**
   * Creates a database connection object using DataSource object. Please
   * substitute the database connection parameters with appropriate values in
   * Connection.properties file
   */
  private static void dbConnection() {
    try {
      // Load the properties file to get the connection information.
      Properties prop = UsingNamedParameters.loadParams("Connection");

      // Create a OracleDataSource instance.
      OracleDataSource ods = new OracleDataSource();

      // Set the driver type.
      ods.setDriverType("thin");

      // Set the database server name.
      ods.setServerName((String)prop.get("HostName"));

      // Set the database name.
      ods.setDatabaseName((String)prop.get("SID"));

      // Set the port number.
      ods.setPortNumber(new Integer((String)prop.get("Port")).intValue());

      // Set the user name.
      ods.setUser((String)prop.get("UserName"));

      // Set the password.
      ods.setPassword((String)prop.get("Password"));

      // Create a connection  object.
      conn = ods.getConnection();

    } catch(SQLException ex) { // Trap SQL errors.
        System.out.println("Error in Connecting to the Database "+'\n'+
                           ex.toString());
    } catch(IOException ex) { // Trap IO errors.
        System.out.println("Error in reading the properties file "+'\n'+
                           ex.toString());
    }
  }
}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy