CODEWITHSUNDEEP
javamysqlsql-serverhibernatejpa
user5620472
user5620472

Reputation: 2892

How ignore duplicate rows when insert

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.

Upvotes: 0

Views: 2063

Answers (1)

Tobias Liefke
Tobias Liefke

Reputation: 9042

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.

Upvotes: 1

Related Questions