I am using Informix DB. This question may not be tied to one specific database. But I want to know how I can in JAVA, continuously probe into a Database and check if a certain row has been added to a table in the DB. Basically, the flow is:
(1) My JAVA application should use JDBC to check if a certain table is populated.
(2) If no, it should wait until a row has been inserted.
My question how can I have JAVA be aware of a row insertion. I am not expecting to add any triggers or anything, but in pure java be able to check that the row is added.
Some thoughts that come to my mind are continuously call DB for the row, or periodically (every half-hour or so) call DB and check if the row is available. But what I am looking for is something like a Listener which can do this.
and remember a listener is just a lump of code that waits for a trigger/event from the other end.
you press a button the button pressed event get fired, the button pressed listeners gets told and runs.
So if you don't want to be polling the database you will need a database trigger.
With "pure Java" the best you can probably get is to check continuously. Make sure you have an index in place that can be used to access the row you're looking for, so that the query is not expensive. But checking once every 30 minutes probably won't cause noticeable increase of database load.
In Oracle, two other possibilities exist. First, there is the Database Change Notification mechanism, by which the DB will call you back when rows matching your criteria gets inserted, updated or deleted (you need 11g to be able to specify the criteria, and 11g JDBC driver). We had some problems setting this up, but for small scale changes and monitoring this would be OK. Maybe other DBs would provide similar functionality (?)
Other possibility would be to set up a trigger and use some messaging mechanism to let your app know. This is certainly doable in Oracle, other databases might also provide some messaging, but there is the trigger thing.