Accessing database multiple times

I am working on solution of below mentioned but could not find any best practice/tool for this. For a batch of requests(say 5000 unique ids and records)开发者_如何学Python received in webservice, it has to fetch rows for those unique ids in database and keep them in buffer(or cache) and compare those with records received in webservice. If there is a change for a particular data(say column) that will be updated in table for that unique id. And in turn, the child tables of that table also get affected. For ex, if someone changes his laptop model number and country, model number will be updated in a table and country value in another table. Likewise it goes on accessing multiple tables in short time. The maximum records coming in a webservice call might reach 70K in one call in an hour.

I don't have any other option than implementing it in java. Is there any good practice of implementing this, or can it be achieved using any open source java tools. Please suggest. Thanks.

Hibernate is likely to be the first thing you should try. I tend to avoid because it is overkill for most of my applications but it is a standard tool for accessing database which anyone who knows Java should at least have an understanding of. There are dozens of other solutions you could use but Hibernate is the most often used.

JDBC is the API to use to access relational database. Useful performance and security tips:

• use prepared statements
• use where ... in () queries to load many rows at once, but beware on the limit in the number of values in the in clause (1000 max in Oracle)
• use batched statements to make your updates, rather than executing each update separately (see http://download.oracle.com/javase/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html)

See http://download.oracle.com/javase/tutorial/jdbc/ for a tutorial on JDBC.

This sounds not that complicated. Of course, you must know (or learn):

• SQL
• JDBC

Then you can go through the web service data record by record and for each record do the following:

fetch corresponding database record
for each field in record
    if updated
        execute corresponding update SQL statement
commit // every so many records

70K records per hour should be not the slightest problem for a decent RDBMS.