I need to watch a table i.e., whenever there is insert in a table my java code will do some actions. For this I am planning to use while(true) loop. In this infinite loop I will select data from table. If there is some new insert in table, I will perform my functions and will continue to do this whenever there is a new insert in table.
My code will run forever and there will be scheduled shutdown in a week and will be restarted in 5 - 10 minutes.
Is it a right approach to use while(true) ?
Any other way to listen database table ?
1. If your database table is very simple, then you can run a separate thread which constantly tracks changes in database. Upon change, you can notify and do processing. However, you would have to keep connection open always. Wastage of system resources.
2. I guess Oracle provides just what you want: DCN. Database Change Notification (DCN) is a system where the client registers its interest to the result of certain queries with the database. When the objects associated with these queries change, the database server notifies the client. Using JDBC driver’s DCN feature, multi-tier systems can maintain a data cache as updated as possible by receiving invalidation events from the JDBC drivers.
Acutally, I'm not sure why Ulf thinks that Thread.sleep is worse than the other alternatives. In cases I'm familiar with, they all end up using the same OS-level timer services regardless.
However, Thread.sleep is an imprecise mechanism, and simply puts the thread to sleep for the indicated period of time. Since it also takes time to do the work and setup a new sleep, that means that it shouldn't be used where you want an event to fire at an exact time. But it's OK for informal polling intervals.
On the other end of the spectrum, there's the Quartz Scheduler. It can be used to create very elaborate schedules, including taking time off for weekends and holidays, adding extra events for peak times, and so forth. It also can manage multiple types of scheduled events, and uses thread pooling so instead of requiring as many threads as there are event types, you only allocate as many threads as are expected to be executing concurrently.
On a completely different track...
I've gone on record that I'm not a big fan of putting logic inside the database server. I've seen that turn out badly too often. However, when you start talking about watching table changes, the first word that pops into my head is "trigger". Since you want Java code to respond to these changes, that may not be possible, but some databases also allow embedded Java procedures if that's a workable solution and your datacenter staff/DBA can deal with stuff like that.
You might also be able to code up a trigger that sends a message - perhaps an HTTP REST or Web Services request, and have the Java code get fired in response to that. If your changes are infrequent and irregular, that would be less overhead than polling.
I'm going to be a "small government" candidate. I'll be the government. Just me. No one else.