How to do Polling and also check for new incoming data in a Database for a regular interval

Question

Here the method reads the database which has an unique ID with the sequence number which keeps on increasing, since am a beginner in java,can I know how to implement this repetitive polling and check for new incoming message each time.

/**
 * Method which defines polling of the database and also count the number of Queries
 * @return pojo collection
 * @throws Exception
 */
public List<KAMessage> fullPoll() throws Exception {
    Statement st = dbConnection.createStatement();  
    ResultSet rs = st.executeQuery("select * from msg_new_to_bde where ACTION = 804 order by SEQ DESC");
        List<KAMessage> pojoCol = new ArrayList<KAMessage>();
        while (rs.next()) {
            KAMessage filedClass = convertRecordsetToPojo(rs);
            pojoCol.add(filedClass);
        }

        return pojoCol;
        }


/**
 * Converts a provided record-set to a {@link KAMessage}.
 * 
 * The following attributes are copied from record-set to pojo:
 * 
 * <ul>
 * <li>SEQ</li>
 * <li>TABLENAME</li>
 * <li>ENTRYTIME</li>
 * <li>STATUS</li>
 * </ul>
 * 
 * @param rs
 * @return the converted pojo class object
 * @throws SQLException
 *     
 */
private KAMessage convertRecordsetToPojo(ResultSet rs) throws SQLException {

    KAMessage msg = new KAMessage();
    int sequence = rs.getInt("SEQ");
    msg.setSequence(sequence);
    int action = rs.getInt("ACTION");
    msg.setAction(action);
    String tablename = rs.getString("TABLENAME");
    msg.setTableName(tablename);
    Timestamp entrytime = rs.getTimestamp("ENTRYTIME");
    Date entryTime = new Date(entrytime.getTime());
    msg.setEntryTime(entryTime);
    Timestamp processingtime = rs.getTimestamp("PROCESSINGTIME");
    if (processingtime != null) {
        Date processingTime = new Date(processingtime.getTime());
        msg.setProcessingTime(processingTime);
    }
    String keyInfo1 = rs.getString("KEYINFO1");
    msg.setKeyInfo1(keyInfo1);
    String keyInfo2 = rs.getString("KEYINFO2");
    msg.setKeyInfo2(keyInfo2);
    return msg;
    }
      }

This is what I have tried :

       while(true){
        try {

           incomingMessages.addAll(fullPoll());
       System.out.println("waiting 6 seconds");
           //perform this operation in a loop
           Thread.sleep(6000);
           } 
           catch (InterruptedException e)
           {
        // TODO Auto-generated catch block
         e.printStackTrace();
        }
            catch (Exception e) 
            {
        // TODO Auto-generated catch block
        e.printStackTrace();                               
           }

How to pass the parameters using prepared statements into the query for this code am stuck up here..

public List<KAMessage> fullPoll() throws Exception {
            PreparedStatement oldSeq = null;
    PreparedStatement newSeq = null;
    Statement st = dbConnection.createStatement();
    System.out.println("Polling");
    String query = "select * from msg_new_to_bde where ACTION = 804 and SEQ between           oldSeq and newSeq order by SEQ DESC";// insert in table
    pstmt = conn.prepareStatement(query);
 }

Answer 1

There could be various way to implement repetitive polling, the simplet way i can think of is to put the polling method inside a while, with thread.sleep for example:

    while(true){
        fullPoll();
        Thread.sleep(10000);
     }

do not forget to use try catch since you have an exception thrown. Alternatively you could use something like timer tasks or frameworks as quartz.

In order to check if there is new data in the DB i can think of the following ways on the fly(there can be more optimized ones):

You could keep a count of the rows in order to understand if other rows have been added so you can rerun the query.

This does not cover the case when rows are delete and reinserted, to cover also this you could try to store somewhere the max id used the last time you queried the db and check each time if the new max id is different form the last one you memorized, if yes, database has changed.

For example, if you keep the old index in variable oldSeq, and the new in variable newSeq and you want to get only the newly added messages, you could use a query as :

select * from msg_new_to_bde where ACTION = 804 and SEQ between oldSeq and newSeq order by SEQ DESC

you should check if between for your DB includes also the test values (oldSeq and newSeq)

Answer 2

you can call your method fullPoll() inside a while loop and use thread.sleep(<number of millis>) to wait between invocations.

another solution would be to use a full blown scheduler framework such as quartz.

hope that answers your question.