Continuously query a database in Java


I'm trying to see if its possible to maintain a connection to a database and get the new records as they are inserted into the table.

I'm connecting to a db2 database, and I have no idea when the table gets updated (not regular intervals).

Appreciate any help!
Errang GenevreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
As long as there's a "insertedTimestamp" (or something like that) It should be relatively easy to query the database to get records that have been created since the last time your Java program queried. (Of course, your Java program would have to keep track of the last time it queried the table.)

Your query would look something like this:

select someColumn1,
       <etc, etc>
  from YourTable
 where insertedTimestamp > :lastTimeYouQueried

Open in new window

Like Dave said, you need to poll to get this information - i.e. run a query every so many minutes or seconds to see if there's new data.

Personally, I'd suggest just keeping track of the primary key of the table and asking for any records that have a value larger than the primary key that you last loaded.  That way you can be sure the query is efficient (as searches by primary key are always indexed).

Errang GenevreAuthor Commented:
There is no timestamp column, and the "primary key" is a composite key; so I have no easy way to tell what's added... And to make matters worse, I have no guarantees that newer entries keys will be greater than the previous entries.

Also, I can't change the table in anyway, or have a view/triggers, nothing... All I have is a select statement.

Any hopes of achieving it with just that and some form of clever looping strategy in Java?
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

You can do many things in Java but they key is identifying new records as the other experts have mentioned.

What is the schema design for your table? Please state the columns. Are any of the columns autogenerated via sequences?
Any hopes of achieving it with just that and some form of clever looping strategy in Java?

You can certainly determine if new rows were added if you're willing to pull down the entire table from the database each time.  Then you keep a local copy and compare the ones you found before to the new ones.  (If you're worried about the size of the data transfer you might be able to compute a checksum for each row in the table and send that - but it's likely the load on the db is the more concerning factor than the data transfer).

However if you're not willing to dump the entire table each time when looking for changes, then you will need to figure out a way to detect the changes using SQL.  Nothing to do with Java really - you need to be able to write a query which returns the new rows given some information you can glean from earlier rows you have collected.  We suggested timestamps or keys.  Does anything have AUTO_INCREMENT on it?

If you can't write such a query, then there's no magic Java can add on top to somehow solve this for you - it's access to the db is always purely limited to what's possible in SQL.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You might of course be able to query system tables or views depending on the RDBMS. A dba could even possibly provide a custom stored routine to do that. That would probably be the most efficient, if possible.
Errang GenevreAuthor Commented:
Sadly no... Working on proprietary systems so I can't share any schema details; but there is no auto increment column, and I don't have access to any other tables.

I knew it was a long shot; but I was hoping Java had some features I didn't know about, maybe keeping the connection allowed Java to maybe keep tabs on the table or something.
This is of course why it's often a good idea to have a timestamp 'updated' column in tables
You aren't authorized to disclose table definitions, modify table definitions, create SPs/triggers nor perhaps do much that's truly useful (outside of a Java app). However, should we also assume that a customer DBA can't do those things for you?

There may numerous things that could be done.

What DB2 are you connecting to? (LUW? For i? For z?) Version?
Errang GenevreAuthor Commented:
Nope, can't do modifications of any sort... Not entirely sure of the database version myself.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.