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 Regarding Bulk 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 "Regarding Bulk insert" Watch "Regarding Bulk insert" New topic
Author

Regarding Bulk insert

Deepthi Xavy
Ranch Hand

Joined: Aug 31, 2009
Posts: 32
Hai..

Can anyone just help me out for doing bulk insert in mysql ,I got 60 records for marking attendance for a particular class,all the records has to be inserted in database at a time without using looping concept.I do know how to proceed with query.

I just think of a concept that if the check box were marked,that particular student is absent else the student is present.How to do it with query.Can anyone make me clear with query
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1449
    
    6

batch updates might help you

http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html


Swastik
Deepthi Xavy
Ranch Hand

Joined: Aug 31, 2009
Posts: 32
ya..batch update i tried,but how to proceed with the concept i mean here..
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1449
    
    6

As per me it does not seem to be possible without a loop, because even with batch update you need to write 60 addBatch statements. Secondly as you have said marking present/absent status, so it seems that you have the student records in table and probably what you need is an update query rather than an insert query.
James Ward
Ranch Hand

Joined: Apr 27, 2003
Posts: 263
Well you could potentially do it with Two Queries instead of looping over a query 60 times:

//Update for Students who are present. The in part of the query you need to construct dynamically.
UPDATE student_table SET present='true' where student_id in ('10041', '10043', '10082', '10091');

//Update for Students who are present. The in part of the query you need to construct dynamically.
UPDATE student_table SET present='false' where student_id in ('10033', '10013', '10088', '10099', '10887');

Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1449
    
    6

Not very sure, but might lead to some optimization issues. Please correct me if I am going wrong.
James Ward
Ranch Hand

Joined: Apr 27, 2003
Posts: 263
Swastik Dey wrote:Not very sure, but might lead to some optimization issues. Please correct me if I am going wrong.


Looping is not required for this requirement, and will be much slower.

An index on student_id column is expected, and most likely it will exist if it is a primary key.
The index is expected anyway irrespective of any kind of update.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1449
    
    6

I am suggesting to use the loop only for the addBatch statement, so in this case executeBatch is triggered only once. May be the logic can go something like this.

A Hashtable/hashmap contains the roll and the status(true/false)

Start a loop to enumerate through the map


Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41062
    
  43
Why do you wish to avoid something as elementary as loops?

60 is a small number when it comes to databases. I wouldn't spend much time trying to optimize this for performance, since the difference will most likely be too small to be relevant during runtime.


Ping & DNS - my free Android networking tools app
Deepthi Xavy
Ranch Hand

Joined: Aug 31, 2009
Posts: 32
loop might take more time to get executed,because if we want to insert 60 records.the loop has to be executed 60 times..it might be time consuming..so am planing to insert all the records at a time..using bulk insert..at a moment all records has to be inserted.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41062
    
  43
The beauty of batch operations is that they don't get acted upon until all the parts of a batch have been submitted. Whether the client code did that in a loop or not is irrelevant for performance.

As I said, I wouldn't worry too much about performance until you have proven that you need to do do. Preconceived notions of what should or should not perform well often turn out to be incorrect.
Deepthi Xavy
Ranch Hand

Joined: Aug 31, 2009
Posts: 32
thanks for everyone who helped me out to come from the trouble,let me try out everything...& report
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Regarding Bulk insert
 
Similar Threads
Need a sample query
Bulk updates vs Sql query to update bulk records in Hibernate
how to do multiple insert
Best way to insert 10000 rows in JDBC
incrementing numbers in database when a new object is added