Reading data from an MySQL table whenever the table changes

Question

I have a MySQL database which contains a table in which new data are getting dumped.Now i have made a java program to read the table data.The nature of the data inserted into the table is such that sometimes it is continuously getting added and sometimes getting added after intervals.While my requirement is to read the data from the java application as soon as it gets added into the table ..

As soon as new data gets added into table, I need to read that through my java program and process it. Also I need to read only new rows..

Here is my java class code..

private void Readdata(ResultSet resultSet) throws SQLException {

  while (resultSet.next()) {
  Date date = resultSet.getDate("datum");
  String comment = resultSet.getString("comments");   

}
}

In this code if the while condition fails it comes out and stops reading.

The resultSet does not get automatically updated if the new data come in (that would be uneffective and dangerous for many reasons). In your case, I'd remember the last ID that was retrieved and then periodically issue new read starting from that ID (for a batch of say 500-1000 entries, because reading a lot of entries at once isn't recommended either). — Ashalynd, May 15 '14 at 12:12

score 1 · Answer 1 · answered May 15 '14 at 12:27

You can create a cron job and set it for checking database in interval as per your desire. You can make use of quartz-2.2.0.jar or similar library which works on cron expressions. Create a job and insert your data reading code inside that. You can also make use of Trigger in mysql.

score 1 · Answer 2 · edited May 19 '14 at 11:27

How about using a timer task and ScheduledExecutorService to check for updates at regular intervals You need to extend java.util.TimerTask. You will also need to have an anonymous inner 'run' class. Then you need set the java.util.concurrent.ScheduledExecutorService. (for more info http://docs.oracle.com/javase/7/docs/api/java/util/concurrent/ScheduledExecutorService.html)

scheduleAtFixedRate(timerTask, 0, 4, TimeUnit.HOURS); = scheduleAtFixedRate(Runnable command, long delay,long time, TimeUnit unit)


public class MyClass extends TimerTask{

public void StartScheduledCheck(){
 TimerTask timerTask ;
        ScheduledExecutorService scheduledExecutorService =
        Executors.newScheduledThreadPool(5);
         Timer timer = new Timer(true);
         timerTask = new MyClass();
          ScheduledFuture scheduledFuture = 

scheduledExecutorService.scheduleAtFixedRate(timerTask, 0, 4, TimeUnit.HOURS);
    }


     public void run(){
            //Call sql class here  
        }

    }

This will do this task every four hours. You can change it to seconds or minutes.

score 0 · Answer 3 · answered May 15 '14 at 12:14

Try this:

You receive your data once from your mysql-table, and store it in a list. now you check if there is an item with id of your list's length in the table. If yes, you have a new item. If no, there is no new item in the table. The coding behind this is pretty simple. And if you want to loop forever, use:

while(running){  //where running is a boolean value
//check for new entries
}

and put this in a thread, otherwise it will block your main program.

Reading data from an MySQL table whenever the table changes

3 Answers3