*
The moose likes JDBC and the fly likes Inserting multiple record into a database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Inserting multiple record into a database" Watch "Inserting multiple record into a database" New topic
Author

Inserting multiple record into a database

Claude Nichols
Greenhorn

Joined: Oct 25, 2001
Posts: 7
hello ranchers,

I got a problem.
I have a servlet that uses the JDBC facilities to connect to a database. The servlet recieves several records at any one time and must access the database and insert the new records into the database. Each record contains info for 6 fields.
I am able to insert a single record at a time but i need to insert multiple records to releave servlet overhead.
Is there a way to achieve multiple record insertion without coding a insert statements for each record or creating a large insert statement encompassing all records.

Thanks in advance
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
I have seen a number of postings about "batching inserts", so I would do some searches on this forum with "batch" or "batching"
Dan
Thomas Suer
Ranch Hand

Joined: Sep 03, 2001
Posts: 50
I think the only way is to create a 'large' insert statement of the following form:

Tom
Claude Nichols
Greenhorn

Joined: Oct 25, 2001
Posts: 7

Thanks for you responces
I have heard of batch inserts but never come across it before. If you find anything on how to send me the link and how it works.
I'll however try to use the format for a large insert statement given and see how that goes.

later then

thanks
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html
Jamie
...by the way, I've never heard of large insert statements. I don't think they are standard SQL because it doesn't work with Oracle. I would stick to Batches because they are very efficient and are a portable(standard) solution.
Claude Nichols
Greenhorn

Joined: Oct 25, 2001
Posts: 7

Unfortunetly I have to use an existing access database and it does not support batch processing.
Throws a UnsupportedOperationException
The suggestion of a large string is still on the table the only problem is that insert values are determined dynamicaly and amount are not previously known.
I'll keep trying
thanks
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Just a note:
It's not the database that doesn't support batch statements, it's the JDBC driver that doesn't implement it.
another note:
I can use batch statements with the jdbc dbc bridge to an access database, which is as bare bones as it can get. Unless you are using some obscure database, there is a driver that will support batch updates.
Which database/driver/jdk combination are you using? may be you are using an old jdbc driver or an older jdk
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Inserting multiple record into a database
 
Similar Threads
TimerTask in Struts for handling Stored Procedures.
Is it possible or not?
Error inserting data into database after few successfull insert.
Java Array Converter
TimerTask in Struts for handling Stored Procedures.