• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to get java to listen to SQL?

 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Looky! I'm being abducted by space aliens! Me and this tiny ad!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic