JDBC 3.0 feature: Named Parameters - Create Product Application Readme

• CallableStatement interface is enhanced by adding new setter methods by name.
  For example,
  

  public void setInt(String parameterName, int x) throws SQLException; public int getInt(String parameterName) throws SQLException;

  
  
• When named parameters are used, you do not need to bind all the IN or INOUT parameters, if those unbound parameters have a default value.
  

public void registerOutParameter(String parameterName, int sqlType) throws SQLException;

Using the JDBC 3.0 feature: Named Parameters, the sample application will call a database procedure by passing values for the IN and OUT parameters. The sample code makes use of a table: product_information in OE schema. The called database procedure will insert a record each in product_information and product_descriptions tables in the database with the same product_id, and returns the product_id of the newly created record.

     2.1 Code Support

     Following is the code snippet from UsingNamedParameters.java file which demonstrates the usage of named parameters while calling a
    database procedure from a JDBC application.

 /**
  *  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.
  */
  private static void namedParametersInCS(String name, int minprice,
                                       String desc, int listprice)
    throws SQLException, ClassNotFoundException {
    ...........

      // 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 setter
      // 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);
      ..................
	  ..................
  }
			  

3.1 Readme file and Stylesheets

3.2 Sample Application files

Please refer the Install.html for running the sample application.

Sun JDK 1.4.x API documentation

Sun JDBC API Documentation