Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to get java to listen to SQL?

 
Ardel Grider
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone, I am working on writing a program that has to talk to a database. There will be about 20 to 30 people using this program and it needs to update every time there is a change to a specific column on a few tables. The best method I can come up with is to create a producer who will stay in touch with the database and notify each persons running application when there is an update so they will refresh. The problem I am running into with this method is how to get the Producer to know when the data has been updated. I have researched quite a bit into this and the best I have came up with is to put a trigger on the specific columns I need to know when they update, have that create a text file at a shared location with the Producer, and then the producer will read that and know to update then push updates to everyone else. I have seen a lot of back and forth on whether or not to use triggers in this way. What would be the problem with doing it this way? Is there a better method than for my program to check the data every X seconds?
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ardel Grider wrote:Is there a better method than for my program to check the data every X seconds?


This has the advantage of being simple. Solutions which involve triggers can have several problems -- most obviously using a trigger which writes to a file can slow down database updates, although maybe not significantly. But you also have to worry what happens with simultaneous updates, when two instances of the trigger try to write to the text file at the same time. Of course it depends on how many rows you're monitoring and how frequently you expect them to be updated. And on how many users are watching the rows for updates.

Personally I would just write code to refresh the data every few seconds until that was shown to be unsatisfactory for some reason, just because it's a lot simpler. Like the arrivals and departures screen at the airport.
 
Ardel Grider
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Ardel Grider wrote:Is there a better method than for my program to check the data every X seconds?


This has the advantage of being simple. Solutions which involve triggers can have several problems -- most obviously using a trigger which writes to a file can slow down database updates, although maybe not significantly. But you also have to worry what happens with simultaneous updates, when two instances of the trigger try to write to the text file at the same time. Of course it depends on how many rows you're monitoring and how frequently you expect them to be updated. And on how many users are watching the rows for updates.

Personally I would just write code to refresh the data every few seconds until that was shown to be unsatisfactory for some reason, just because it's a lot simpler. Like the arrivals and departures screen at the airport.


The problem I have with updating the data every few seconds is that the whole sql portion takes about 30 seconds to run and while it is running it brings the servers CPU usage from average of 2% to an average of 12%. What I have thought about since making this post though is on program start up it will pull the data it needs to pull. Then it will run another query that will fill with the status that I need to know if it changes or not. Example Hashmap with (Order_id, Status). Then every X seconds I will run this tiny query that may only have 300 orders and compare against the map I have saved. If they match then no changes and it runs again in X seconds. If there is a change it will stop the small query, run the large query and notify the Consumer programs that they need to pull data from this producer. What do you think about this set up?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic