How can I do Asynchronous messaging while reading and processing of the database records by using Java

Question

Here I have the thread pool and an Another Polling class for implementing polling and Reading the messages from the database. Now the problem is I have to avoid reading redundant messages for updating and process the other messages waiting at the same time, since there are vast messages waiting.

// the code for poll method
public void poll() throws Exception {
    // Method which defines polling of the data entry for counting its size.
    st = conn.createStatement();
    int count = 1;
    long waitInMillisec = 1 * 60 * 125; // Wait for 7.5 seconds.
    for (int i = 0; i < count; i++) {
        System.out.println("Wait for " + waitInMillisec + " millisec");
        Thread.sleep(waitInMillisec);

        java.util.Date date = new java.util.Date();
        Timestamp start = new Timestamp(date.getTime());
        rs = st.executeQuery("select * from msg_new_to_bde where ACTION=804");
        java.util.Date date1 = new java.util.Date();
        Timestamp end = new Timestamp(date1.getTime());
        System.out.print("Query count: ");
        System.out.println(end.getTime() - start.getTime());

        Collection<KpiMessage> pojoCol = new ArrayList<KpiMessage>();
        while (rs.next()) {
            KpiMessage filedClass = convertRecordsetToPojo(rs);
            pojoCol.add(filedClass);

        }

Answer 1

I don't know if you have a choice on how your messages are stored, but they appear to be inserted into a table that you're polling. You might add a database trigger to this table that in turn pushes a message into an Oracle AQ with the same data plus a correlation id.

If you can do without the table, I would suggest just defining the Oracle AQ in the same schema to store the messages, and dequeue by partial correlation id using pattern matching like corrid="804%". The full correlation id for the AQ message might be "804" + the unique pk of the message. You could then reuse this same queue for multiple actions for example, and define a Java queue 804 action worker class to wait on messages of that particular action (804 correlation id prefix on the AQ messages).

The documentation is pretty good at Oracle for AQ, and the package you would use to create the queue is dbms_aqadm. The package you would use to enqueue/dequeue is dbms_aq. There's a few priv's/grants you'll need to get too before the aq can be created and the dbms_aq packages can be used. The dbms_aq should easily be callable from Java.

Go to docs.oracle.com to lookup the details on dbms_aqadm and dbms_aq packages. Once you create the AQ (which will create an AQ table that backs the queue), I would suggest you add an index to the AQ table on corrid for performance.

If you can't avoid the current table architecture you have in place or don't want to get into AQ technology, the other option you could use is to create a lock in Oracle (dbms_lock package) and call that in your polling class to obtain the lock or block/wait. That way you synchronize all your polling classes to avoid multiple threads from picking up the same message. So the first thing the polling class would do is to try to obtain the lock, if successful it pulls a message out of the table, processes it, updates it as processed, releases the lock. The dbms_lock package can block/wait for the lock or return immediately, but based on the operations success/failure you can take further action. But it will help you control the threads from picking up the same message. Oracle's docs are pretty good on this package too.