1

I have two database on two different machines. Let say DB1 and DB2

DB1 - JDBC connection - Select Query

DB2 - Through Hibernate - Insert Query

In DB1, there is one table user in which 35K records are present. I need to fetch this data from Java JDBC and store the data in the hibernate entity object.

Now My question is : Should I fetch the 35K records in 1 go like ( Select * from User ) and create 35 K entity obj and store one by one in DB2. In this case...my concern point is memory and how to handle if exception occur like db connection failed with DB2.

What I feel is if there is any approach in which I select 200 records and store that 200 records and so on.

Plz suggest.

DavidPostill
  • 7,734
  • 9
  • 41
  • 60
VJS
  • 2,891
  • 7
  • 38
  • 70

2 Answers2

0

You might want to use this one:

Statement.setFetchSize(int rows)

The functionality is explained in this questions which deals with a similar problem: What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?

Community
  • 1
  • 1
Dennis Pietersma
  • 11
  • 3
0

If you have a bit of time and are interested in learning you could have a look at: http://projects.spring.io/spring-batch/

Spring Batch has the concept of chunks and mechanisms to store the state of your job it's own database. If your transfer job crashes then it can resume from the last chunk. You can also partition your job and distribute it. It's really easy to use, if you are familiar with Spring.

Generally saying, you should not load all rows into memory and transfer them in one go, this has the potential to break as you already pointed out (OOM, transaction log). Chunking into a dedicated staging table and then switching the data live when everything is all-right is a common solution.

If it's just one run and you are expecting only 35K of records then it's OK to copy them in one big chunk and most likely not worth the extra effort.

Andreas
  • 5,251
  • 30
  • 43