Does anyone have a code sample of a multithreading case that has about 25 threads that monitor a specific table in the database for a change then execute a process based on that change?
3 Answers
If you just want to be notified in the client application that something has changed in the database and you need to react on that in the application itself (so that triggers are not an option) you can use Oracle's change notification.
In order to do that, you register a listener with the JDBC driver specifying the "result set" that should be monitored. That listener will be called whenever something changes in the database.
For details on how this works, see the manual:
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/dbmgmnt.htm#CHDEJECF
2 Comments
Suraj Chandran
how to do with non-oracle dbs?
The only other DBMS I know that has something similar is PostgreSQL with the LISTEN/NOTIFY concept: postgresql.org/docs/current/static/sql-listen.html (which is available through JDBC as well)
If you want to monitor the table (in the database) and make changes also in the database, then you should really consider Triggers.
Simply, Triggers are kind of procedures that run automatically BEFORE or AFTER changes on a table. You can monitor UPDATE, INSERT or DELETE transactions and then make your action.
1 Comment
ed0
The thing is I want the threads to recieve their jobs from the db for example if a certain field in the table reads available a certain a certain process on the Java side is to run and when it finishes it updates the db as completed
Try directly using a Database Trigger instead.
Check this question about getting events in java from Database
3 Comments
Suraj Chandran
@ed0 U can create a change trigger on a table. When the change occurs your trigger will be notified and u can execute any storedproc or sql on it
ed0
The thing is I want the threads to recieve their jobs from the db for example if a certain field in the table reads available a certain a certain process on the Java side is to run and when it finishes it updates the db as completed. The process I want to run cannot be executed by a stored procedure. It can only happen on the Java side
Suraj Chandran
@ed0 i have update my answer to add another link that explains how to get db events in java