How ignore duplicate rows when insert

Question

I use hibernate-jpa-2.1-api. And I need some functionality.

I parse a file every minute and insert data into MSSQL DB. I need to skip duplicate rows. For example at 12:00 I've got in my file 300 rows. I parse every of them and insert 300 rows. After one minute (12:01) my file contains 500 rows. I parse it and I want to insert only the 200 new rows and not the old 300 rows.

In the old realization of the program I used SQL insert and did not use ORM.

Here is my old SQL query:

insert /*+ ignore_row_on_dupkey_index(avaya_cm_cdr, i_avaya_cm_cdr_nodub) */  into avaya_cm_cdr(acmcdr_id, cdrdate, cdrtime, secdur, condcode, attdconsole, codeused, outcrtid, codedial, dialednum, intrkcode, incrtid, callingnum, vdn, bcc, ppm, acctcode, authcode) values(seq_acmcdr_id.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

And here is my new insert with ORM:

em = Persistence.createEntityManagerFactory("COLIBRI").createEntityManager();
public void insertAVAYAcmCDRs(List<AvayaCmCdr> cdrList) {
    em.getTransaction().begin();
    for (AvayaCmCdr aCdrList : cdrList) {
        em.persist(aCdrList);
    }
    em.getTransaction().commit();
}

How can I use the analog to the function ignore_row_on_dupkey_index with ORM?

p.s. In the old realization I've used an Oracle DB.

What's wrong with the (russian) answer you've just got here? — Tobias Liefke
– Tobias Liefke, Commented Nov 30, 2015 at 11:13

Tobias Liefke · Accepted Answer · 2015-11-30 11:26:58Z

Database style option

Hibernate does not offer to add an option to its insert into statements. And I don't know if the same option is available for MS SQL.

But if you find such an option, you can intercept the insert statement and add that yourself:

public class IgnoreRowOnDupInterceptor extends EmptyInterceptor {

  public String onPrepareStatement(String sql) {
    if (sql.startsWith("insert into avaya_cm_cdr") {
      return sql.replace("insert into", 
        "insert /*+ ignore_row_on_dupkey_index(avaya_cm_cdr, i_avaya_cm_cdr_nodub) */ into");
    }
    return sql;
  }

}

You need to declare this interceptor in your persistence.xml:

<property name="hibernate.ejb.interceptor" value="...IgnoreRowOnDupInterceptor" />

JPA style option

You could remember the last line from the last parsing (or retrieve it from the database) and skip the file until that line. In that case you even would save the time to parse every existing item again and again.

From my point of view this is the JPA way, because you usually use the database only as storage and keep the business logic in the (Java) application.

Collectives™ on Stack Overflow

How ignore duplicate rows when insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related