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

问题描述

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

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

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

以下是我的旧SQL查询：

  insert / * + ignore_row_on_dupkey_index（avaya_cm_cdr，i_avaya_cm_cdr_nodub）* /进入avaya_cm_cdr（acqcd_id，cdrdate，cdrtime，secdur，condcode，attdconsole，codeused，outcrtid，codedial，dialednum，intrkcode，incrtid，callingnum，vdn，bcc，ppm，acctcode，authcode）值（seq_acmcdr_id .nextval，？，？，？，？，？，？，？，？，？，？，？，？，？，？，？，？）
  pre> 
 
 
以下是带ORM的新插入： 
 
 
  em = Persistence.createEntityManagerFactory（COLIBRI）。createEntityManager（）; 
 public void insertAVAYAcmCDRs（List< AvayaCmCdr> cdrList）{
 em.getTransaction（）。begin（）; （AvayaCmCdr aCdrList：cdrList）{
 em.persist（aCdrList）; 
 
} 
 em.getTransaction（）。commit（）; 
} 
  
如何使用模拟函数 ignore_row_on_dupkey_index 与ORM？
 
 
 
 ps在旧的实现中，我使用了一个Oracle数据库。      
 
 > 
 
 
 Hibernate不提供在插入到语句中添加选项。我不知道MS SQL是否有相同的选项。
 
 
 
但是，如果您找到这样的选项，您可以截取insert语句并自行添加：  
 
 
  public class IgnoreRowOnDupInterceptor extends EmptyInterceptor {
 
 public String onPrepareStatement（String sql）{
返回sql.replace（insert into，
insert / * + ignore_row_on_dupkey_index（avaya_cm_cdr，i_avaya_cm_cdr_nodub）* / into）; $ b（sql.startsWith（insert into avaya_cm_cdr）{
 
 $ b return sql; 
} 
 
} 
  
您需要在 persistence.xml 中声明此拦截器：
 < property name =hibernate.ejb.interceptorvalue =... IgnoreRowOnDupInterceptor/> 
  
  JPA样式选项 
 
 
 
您可以记住最后解析的最后一行（或从数据库中检索它）并跳过文件un直到那条线。在这种情况下，您甚至可以节省时间来一次又一次解析每个现有项目。 
 
 
 
从我的角度来看，这是JPA方式，因为您通常只将数据库用作存储并将业务逻辑保留在（Java）应用程序中。
 
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.
解决方案
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.

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