Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Batch Insert with duplicate key

Reply
Thread Tools

Batch Insert with duplicate key

 
 
Felix Roberto
Guest
Posts: n/a
 
      12-07-2006
any one knows how to do a
prepared statement batch insert, and when you get a pk exception
instead of breaking
the batch, keep going with the rest?

this is a simplyfied example of what i do
problem is i can get as much as 1500 record that are generated.
so how can i insert them into the db with out much performance
sacrifice?
and garantee that if a record allready exists it just keeps going with
the rest of the
group.

PreparedStatement pstm = con.crearPreparedStatement(
"INSERT INTO "+TABLE+" ("+PK_1+","+PK_2+") VALUES (?,?)");


for(int x=0;x<record1.length;x++){
for(int y=0;y<record2.length;y++){
pstm.setString(1, record1[x]);
pstm.setString(2, record2[y]);
pstm.addBatch();
}
}
pstm.executeBatch();

 
Reply With Quote
 
 
 
 
Furious George
Guest
Posts: n/a
 
      12-07-2006

Felix Roberto wrote:
> any one knows how to do a
> prepared statement batch insert,


as you have it below.

> and when you get a pk exception
> instead of breaking
> the batch, keep going with the rest?


One way would be like this.

CREATE TEMPORARY TABLE t1 ( ... same as permanent table but without
primary key ... ) ;
batch insert into temporary table ;
batch execute ;
INSERT INTO permanentTable SELECT * FROM t1 WHERE primary key logic ;

>
> this is a simplyfied example of what i do
> problem is i can get as much as 1500 record that are generated.
> so how can i insert them into the db with out much performance
> sacrifice?


Maybe your db has a better way. For example MySQL has a LOAD DATA
statement that is useful for loading large amounts of data.

> and garantee that if a record allready exists it just keeps going with
> the rest of the
> group.


You would really need to read the documentation for your particular
database to figure the optimal solution.

>
> PreparedStatement pstm = con.crearPreparedStatement(
> "INSERT INTO "+TABLE+" ("+PK_1+","+PK_2+") VALUES (?,?)");
>
>
> for(int x=0;x<record1.length;x++){
> for(int y=0;y<record2.length;y++){
> pstm.setString(1, record1[x]);
> pstm.setString(2, record2[y]);
> pstm.addBatch();
> }
> }
> pstm.executeBatch();


 
Reply With Quote
 
 
 
 
Felix Roberto
Guest
Posts: n/a
 
      12-12-2006
Rigth now we are using oracle 10g
but problem is that the data is not to be loaded it is generated.
it calculated data that is generated in whole block, up to 10k records
and there is a big chance for the primary key to be duplicated.
so what i was triying to do, was to execute the batch,
and on error to keep executing and give me at the end
all the error or something like that.

or any other solution that i can implement using jdbc

Furious George wrote:
> Felix Roberto wrote:
> > any one knows how to do a
> > prepared statement batch insert,

>
> as you have it below.
>
> > and when you get a pk exception
> > instead of breaking
> > the batch, keep going with the rest?

>
> One way would be like this.
>
> CREATE TEMPORARY TABLE t1 ( ... same as permanent table but without
> primary key ... ) ;
> batch insert into temporary table ;
> batch execute ;
> INSERT INTO permanentTable SELECT * FROM t1 WHERE primary key logic ;
>
> >
> > this is a simplyfied example of what i do
> > problem is i can get as much as 1500 record that are generated.
> > so how can i insert them into the db with out much performance
> > sacrifice?

>
> Maybe your db has a better way. For example MySQL has a LOAD DATA
> statement that is useful for loading large amounts of data.
>
> > and garantee that if a record allready exists it just keeps going with
> > the rest of the
> > group.

>
> You would really need to read the documentation for your particular
> database to figure the optimal solution.
>
> >
> > PreparedStatement pstm = con.crearPreparedStatement(
> > "INSERT INTO "+TABLE+" ("+PK_1+","+PK_2+") VALUES (?,?)");
> >
> >
> > for(int x=0;x<record1.length;x++){
> > for(int y=0;y<record2.length;y++){
> > pstm.setString(1, record1[x]);
> > pstm.setString(2, record2[y]);
> > pstm.addBatch();
> > }
> > }
> > pstm.executeBatch();


 
Reply With Quote
 
andrewmcdonagh
Guest
Posts: n/a
 
      12-12-2006


On Dec 12, 3:07 pm, "Felix Roberto" <(E-Mail Removed)>
wrote:
> Rigth now we are using oracle 10g
> but problem is that the data is not to be loaded it is generated.
> it calculated data that is generated in whole block, up to 10k records
> and there is a big chance for the primary key to be duplicated.
> so what i was triying to do, was to execute the batch,
> and on error to keep executing and give me at the end
> all the error or something like that.
>
> or any other solution that i can implement using jdbc
>


So your Java app is generating 10k records, some of which their primary
keys might already have been used?

If so....

does java app need to create the keys, can't you use the auto-generated
ones from Oracle?

If your app does need to, can it also validate that its used them
itself before sending to db?

if it can't, then I'd suggest calling a Stored Procedure with the 10k
entries and have it process them. This would be much faster than the
java app, trying to do multiple small batches or single inserts. Take a
look at 'setArray()' from the JDBC classes, or if oracle is the only
db, look at their 'setARRAY()' version.


Andrew
NOTE: the stored Procedure can also be Java - but it wont run as fast
as a normal SQL stored Proc. It should however, run as fast as a
PL/SQL stored Proc.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Handling a duplicate index value on insert Dave ASP .Net 5 12-17-2009 07:14 PM
stl multimap, insert with duplicate keys, is ordering stable? reppisch C++ 6 06-19-2007 09:38 AM
map.insert(key,val) vs. map[key]=val ? Patrick Guio C++ 6 10-20-2004 01:54 PM
Replace Tab Key to Return Key (Enter Key) from Web Forms? M P ASP General 1 08-06-2004 08:32 AM



Advertisments