wood burning stoves 2.0*
The moose likes JDBC 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 Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
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.

I agree. Here's the link: http://aspose.com/file-tools
subject: Batch Insert
Similar Threads
Advice on Object data types to use for the rich:tree I am trying to create
J2EE transactions - stateless beans (CMT)
Batch consisting of multiple PreparedStatements
problem with bulk update and auto_increment
reading the configurtation file divided in the sections