File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Error inserting data into database after few successfull 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 "Error inserting data into database after few successfull insert." Watch "Error inserting data into database after few successfull insert." New topic
Author

Error inserting data into database after few successfull insert.

Jigar Naik
Ranch Hand

Joined: Dec 12, 2006
Posts: 753
Hello,

I'm trying to insert 3000 reocrds into MySQL Database from CSV file using JDBC but after seccessfull insertion of aprox 1300 records, Its giving me the connection refuse to connect.

Stack Trace :



Jigar Naik


Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

Usually this happens because of a timeout value somewhere in the connection between the java process and the server. First, I'd check the MySQL configuration and increase timeouts there. Then I'd examine the web server for similar timeouts.

Also, how are grouping your inserts? One record per insert statement, multiple records per insert statement, or all records within a single insert statement?

You can either find the timeout value and increase, or insert 1,000 records at a time. It's been my experience that the optimal solution to bulk insertion for network bandwidth and database performance, is to insert about 500 records per insert statement (although keep the 500 value configurable). Often in larger scale environments this prevents timeouts from ever being hit. In one case of inserting data on a shared server I saw performance like this for bulk inserts:

1 record per insert statement: 1 hour to complete
500 records per insert statement: 35 seconds to complete
All records (50k) per insert statement: never completed do to web server timeout

In short, bulk inserts require more thought and planning if they are a common occurance than traditional inserts.


My Blog: Down Home Country Coding with Scott Selikoff
 
 
subject: Error inserting data into database after few successfull insert.
 
Similar Threads
Connecting with MySql
MySQL 4.1 [java.net.ConnectException: Connection refused]
java.net.ConnectException: Connection refused: connect
another JDBC driver question
new to db connection help needed