Scanning a table sql with java every 5 second

Question

Can anyone help me, how to create a function in java to do scanning every 5 seconds to know the existence of new data entered in a mysql table.

Answer 1

Making searches in big tables - especially in big tables - is a heavy operation. So, probably, you can reduce amount of table reads by detecting new data in other way.

For example, you can check table's size before actual data fetch. For doing that you can just perform "select count(*) from table" operation or even calculate table's size on disk like here: How to get the sizes of the tables of a mysql database?

Variant with database trigger also can help. For example, what if your trigger will update some marker of the last table's update on which your java app will look. That variant also will help to avoid performing idle reads of your table.

Answer 2

In case you need java solution only, you can do it with timer & timer task provided by java.

Here is the code.

    java.util.TimerTask task = new java.util.TimerTask() {
        int prevCount = 0; // you can declare it static
        @Override
        public void run() {
            Connection conn = getConnection();
            try {
                ResultSet rs = conn.prepareStatement("Select Count(*) from table").executeQuery();
                int count = rs.getInt(1);
                System.out.println("Count diff:"+ (prevCount-count));
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    };
    java.util.Timer timer = new java.util.Timer(true);// true to run timer as daemon thread
    timer.schedule(task, 0, 5000);// Run task every 5 second
    try {
        Thread.sleep(60000); // Cancel task after 1 minute.
    } catch (InterruptedException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    timer.cancel();

Answer 3

You don't need a java program to scan changes in DB , apart from a redundant it will be costly to network and DB, it's not a good practice to implement a feature that is already been given as a standard solution.

CREATE TRIGGER `some_update_happened` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `mydb`.`mytable`
FOR EACH ROW BEGIN
    // your code here for db trigger calling java function
END;

What you can do is use DB Update Triggers refer this link & this link . After implementing trigger I suppose you need to catch trigger using a service implemented in php, java etc. You need to implement event listener for receiving trigger automation. just like done here for PHP and there is also an example for it in oracle docs but that is for Oracle. here is an example in java+mysql .

I understand you are a beginner , just go step by step , error by error and you will get there. good luck.