插入时如何忽略重复行 [英] How ignore duplicate rows when insert

问题描述

我使用 hibernate-jpa-2.1-api 。我需要一些功能。

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

我每分钟解析一个文件并将数据插入MSSQL DB。我需要跳过重复的行。例如，在 12:00 我的文件中有300行。我解析每一个并插入300行。一分钟后（ 12:01 ），我的文件包含500行。我解析它，我想只插入200个新行而不是旧的300行。

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.

在程序的旧实现中我使用SQL插入并且没有使用ORM。

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

这是我的旧SQL查询：

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, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

这是我的新插入ORM：

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();
}

如何使用模拟函数 ignore_row_on_dupkey_index 使用ORM？

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

ps在旧的实现中，我使用了Oracle DB。

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

推荐答案

数据库样式选项

Hibernate不提供将插入的选项添加到语句中。我不知道MS SQL是否有相同的选项。

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.

但是如果你找到这样的选项，你可以拦截insert语句并自己添加：

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;
  }

} 

您需要在 persistence.xml中声明此拦截器：

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

JPA样式选项

您可以记住上次解析的最后一行（或从数据库中检索它）并跳过档案联合国直到那条线。在这种情况下，您甚至可以节省一次又一次地解析每个现有项目的时间。

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.

从我的角度来看，这是JPA方式，因为您通常只将数据库用作存储，并将业务逻辑保留在（Java）应用程序中。

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.

这篇关于插入时如何忽略重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！