RelationalDBDesign RelationalDBDesign 


Advanced Queuing   «Prev 

When to use Advanced Queuing

So, when would you use Advanced Queuing, with its asynchronous communication? First of all, Advanced Queuing would not be appropriate if you are depending on an interaction between the client and the server. If a response is required before the client can move on, there is generally no advantage to asynchronous communication.
If you do not need a response from the server before the client can move on, you might want to consider Advanced Queuing. It can be especially useful if an application is requesting a service from another application, and the application receiving the requests may have limited resources available to handle requests. For instance, you might use Advanced Queuing to make a credit-check call as part of an order entry process. The credit-check application may be swamped with requests at any given time, so simply placing the requests into a queue will avoid you having to wait for a response. Of course, this means that the application will either have to check back for the results of the credit check later, or make it clear to the user that any order placed is subject to a credit check before the order can actually be fulfilled.

Coding the Stored Procedure

The code for the Java Stored Procedure that will post to this queue appears below

Java Implementation of a Queue using Oracle

package com.wrox.examples;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.AQ.*;
import java.math.*;
import com.wrox.examples.CustCreditBreach;
public class CreditManager {
 public static void checkLimit(Integer customerId, Double creditNeeded){
  String qry = "SELECT credit_limit FROM demo.customer " +
  "WHERE customer_id = " +
  customerId.toString();
  Connection cn = null;
PreparedStatement ps = null;
ResultSet rs = null;
oracle.AQ.AQSession aqs = null;
oracle.AQ.AQQueue ccbq = null;
oracle.AQ.AQMessage ccbm = null;
CustCreditBreach ccb = null;
double creditLimit = 0;
double difference = 0;
try {
cn = new OracleDriver().defaultConnection();
ps = cn.prepareStatement(qry);
rs = ps.executeQuery();
if(!rs.next())
throw(new Exception("Unable to retrieve credit " +
"limit for customer "));
creditLimit = rs.getDouble(1);
difference = creditNeeded.doubleValue() - creditLimit;
if(difference > 0)
{
// Initialize the message
ccb = new CustCreditBreach();
ccb.setCustomerId(
BigDecimal.valueOf((long)customerId.intValue()));
ccb.setCreditLimit(new BigDecimal(creditLimit));
ccb.setCreditGap(new BigDecimal(difference));
// Get a reference to the Queue
oracle.AQ.AQDriverManager.registerDriver(
new oracle.AQ.AQOracleDriver());
aqs = AQDriverManager.createAQSession(cn);
ccbq = aqs.getQueue("scott", "CustCreditBreachQ");
// Put the message on the queue
ccbm = ccbq.createMessage();
ccbm.getMessageProperty().setExpiration(1);
ccbm.getObjectPayload().setPayloadData(ccb);
ccbq.enqueue(new AQEnqueueOption(), ccbm);
cn.commit();
System.out.println(
"Successfully created AQ session");
}
System.out.println("Done");
}
finally {
if(rs != null) rs.close();
if(ps != null) ps.close();
}
}
}

First of all, we have two new imports: oracle.AQ.* and com.wrox.examples.CustCreditBreach.
  1. The first provides access to the Oracle Advanced Queueing Java package, from which we use the AQSession, AQQueue, and AQMessage classes.
  2. The second imports one of the classes we created earlier with jpub.
This is the class that will encapsulate the queue's payload. There is one method in the CreditManager class: checkLimit. This method takes two parameters: customerId, which identifiers the customer and creditNeeded which specifies how much credit the customer requires (that is the purchase amount, as we're assuming all purchases are made on credit in this business-to-business scenario).Inside checkLimit(), we first create a dynamic query that will get us the customer's credit limit from the DEMO.customer table. Then, in the try clause, standard JDBC functionality is used to execute the query and retrieve the credit limit as a Double. At that point, the difference between the credit limit and the amount of credit requested is calculated and stored in the variable difference. If the credit limit would be exceeded (the difference is greater than 0), we need to write a CustomerCreditBreach message to the queue.
First of all, a CustCreditBreach object is created to encapsulate the message, then we use the accessor methods that jpub generated to set the customer ID, credit limit, and credit gap fields:

ccb = new CustCreditBreach();
ccb.setCustomerId(
BigDecimal.valueOf((long)customerId.intValue()));
ccb.setCreditLimit(new BigDecimal(creditLimit));
ccb.setCreditGap(new BigDecimal(difference));

Next, we establish a connection to the queue. There are three steps to this. First, we register the Advanced Queueing driver:
oracle.AQ.AQDriverManager.registerDriver(new oracle.AQ.AQOracleDriver());

Then we create an AQ session using the existing JDBC connection:

AQDriverManager.createAQSession(cn);

Finally, we connect to the CustCreditBreachQ queue:
ccbq = aqs.getQueue("scott", "CustCreditBreachQ");
Now that we have a connection to the queue, we can post a message. The next four statements enable this. The first creates an AQMessage object with the createMessage( ) method of our AQQueue object. The second specifies when the message should expire. The third sets the payload by passing in the CustCreditBreach object. The fourth places the message on the queue.

ccbm = ccbq.createMessage();
ccbm.getMessageProperty().setExpiration(1);
ccbm.getObjectPayload().setPayloadData(ccb);
ccbq.enqueue(new AQEnqueueOption(), ccbm);
Once the commit() method of our JDBC connection is called, the message is queued.