Batch Insert with duplicate key

Discussion in 'Java' started by Felix Roberto, Dec 7, 2006.

  1. 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();
    Felix Roberto, Dec 7, 2006
    #1
    1. Advertising

  2. 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();
    Furious George, Dec 7, 2006
    #2
    1. Advertising

  3. 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();
    Felix Roberto, Dec 12, 2006
    #3
  4. On Dec 12, 3:07 pm, "Felix Roberto" <>
    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.
    andrewmcdonagh, Dec 12, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    3,144
    Marina
    Nov 29, 2005
  2. Patrick Guio
    Replies:
    6
    Views:
    3,197
    chris
    Oct 20, 2004
  3. Steve
    Replies:
    0
    Views:
    458
    Steve
    Dec 3, 2006
  4. reppisch
    Replies:
    6
    Views:
    982
    reppisch
    Jun 19, 2007
  5. M P
    Replies:
    1
    Views:
    466
Loading...

Share This Page