This week's book giveaway is in the OCAJP 8 forum.
We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Batch Insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Batch Insert" Watch "Batch Insert" New topic

Batch Insert

Arulanand Dayalan
Ranch Hand

Joined: Aug 10, 2005
Posts: 124
Hi all,

I am using oracle database. I want to do a batch insert, i want to know is it possible. I have Distribution Class which contains Distribution Package Id, Product Type List and Channel List. I have around List of 50000 Distribution objects which needs to be inserted into DB.

I have two table Product , Product Channel.

Product - Product Id (Sequence,PK),Package Id, Product Type
Product Channel - Proudct Id , Channel Type

I want to do the insert as batch so that i can improve performance. My algorithum would look like thsi,

for each Distribution Object in the List
Get the Package Id
Get the Product List
Get the Channel List
for each Product Type in the Proudct List
insert in to DB - Product Type,Package Id. Get the Product Id
for each Channle in the Channel List
insert in to DB - Product Id,Channel Type

It would be great if some could tell how to do it or guide me to some resources where i could get more information.

Thanks in Advance.

Stevi Deter
Ranch Hand

Joined: Mar 22, 2008
Posts: 265

The basic format for creating and executing a batch in JDBC 2 is:

However, given the complexity of your specific code, you may want to look at creating a stored procedure and calling it, as that will make it easier to leverage getting the ID back from the first insert, etc.

You could also look into a batch insert using a select subquery ("insert into TABLE () values select ...")

There will always be people who are ahead of the curve, and people who are behind the curve. But knowledge moves the curve. --Bill James
Arulanand Dayalan
Ranch Hand

Joined: Aug 10, 2005
Posts: 124
Thanks Stevi Deter for quick reply. I am planning to do this using Stored Procedure. Think this way it would perform better.

It is sorta covered in the JavaRanch Style Guide.
subject: Batch Insert
jQuery in Action, 3rd edition