hibernate_如何使用Hibernate批处理DELETE语句

最新推荐文章于 2021-04-15 01:57:40 发布

cunhu2961

最新推荐文章于 2021-04-15 01:57:40 发布

阅读量477

点赞数

文章标签：数据库 hibernate mysql java 人工智能

原文链接：https://www.javacodegeeks.com/2015/03/how-to-batch-delete-statements-with-hibernate.html

版权

本文探讨了在Hibernate中如何实现批处理DELETE语句，包括JPA级联删除的问题和解决方案，如手动刷新Session、使用SQL级联删除。文章通过实例展示了不同方法下批处理的实现方式和效果，强调了DELETE语句处理的复杂性。

摘要由CSDN通过智能技术生成

hibernate

介绍

在我以前的文章中，我解释了批处理INSERT和UPDATE语句所需的Hibernate配置。这篇文章将继续该主题的DELETE语句批处理。

领域模型实体

我们将从以下实体模型开始：

Post实体与Comment具有一对多关联，并且与PostDetails实体具有一对一关系：

@OneToMany(cascade = CascadeType.ALL, mappedBy = "post",
        orphanRemoval = true)
private List<Comment> comments = new ArrayList<>();

@OneToOne(cascade = CascadeType.ALL, mappedBy = "post",
        orphanRemoval = true, fetch = FetchType.LAZY)
private PostDetails details;

即将进行的测试将针对以下数据进行：

doInTransaction(session -> {
    int batchSize = batchSize();
    for(int i = 0; i < itemsCount(); i++) {
        int j = 0;

        Post post = new Post(String.format(
            "Post no. %d", i));        
        post.addComment(new Comment( String.format(
            "Post comment %d:%d", i, j++)));
        post.addComment(new Comment(String.format(
            "Post comment %d:%d", i, j++)));
        post.addDetails(new PostDetails());

        session.persist(post);
        if(i % batchSize == 0 && i > 0) {
            session.flush();
            session.clear();
        }
    }
});

Hibernate配置

正如已经说明，以下属性所需的配料INSERT和UPDATE语句：

properties.put("hibernate.jdbc.batch_size", 
    String.valueOf(batchSize()));
properties.put("hibernate.order_inserts", 
    "true");
properties.put("hibernate.order_updates", 
    "true");
properties.put("hibernate.jdbc.batch_versioned_data", 
    "true");

接下来，我们将检查DELETE语句是否也被批处理。

JPA级联删除

因为级联实体状态转换很方便，所以我将证明CascadeType.DELETE和JDBC批处理不能很好地混合。

以下测试将要进行：

选择一些帖子以及评论和帖子详细信息
删除帖子，同时将delete事件传播到Comments和PostDetails

@Test
public void testCascadeDelete() {
    LOGGER.info("Test batch delete with cascade");
    final AtomicReference<Long> startNanos = 
        new AtomicReference<>();
    addDeleteBatchingRows();
    doInTransaction(session -> {
        List<Post> posts = session.createQuery(
            "select distinct p " +
            "from Post p " +
            "join fetch p.details d " +
            "join fetch p.comments c")
        .list();
        startNanos.set(System.nanoTime());
        for (Post post : posts) {
            session.delete(post);
        }
    });
    LOGGER.info("{}.testCascadeDelete took {} millis",
        getClass().getSimpleName(),
        TimeUnit.NANOSECONDS.toMillis(
            System.nanoTime() - startNanos.get()
    ));
}

运行此测试将给出以下输出：

Query:{[delete from Comment where id=? and version=?][55,0]} {[delete from Comment where id=? and version=?][56,0]} 
Query:{[delete from PostDetails where id=?][3]} 
Query:{[delete from Post where id=? and version=?][3,0]} 
Query:{[delete from Comment where id=? and version=?][54,0]} {[delete from Comment where id=? and version=?][53,0]} 
Query:{[delete from PostDetails where id=?][2]} 
Query:{[delete from Post where id=? and version=?][2,0]} 
Query:{[delete from Comment where id=? and version=?][52,0]} {[delete from Comment where id=? and version=?][51,0]} 
Query:{[delete from PostDetails where id=?][1]} 
Query:{[delete from Post where id=? and version=?][1,0]}

仅批注Comment DELETE语句，其他实体在单独的数据库往返中删除。

此行为的原因由ActionQueue排序实现给出：

if ( session.getFactory().getSettings().isOrderUpdatesEnabled() ) {
    // sort the updates by pk
    updates.sort();
}
if ( session.getFactory().getSettings().isOrderInsertsEnabled() ) {
    insertions.sort();
}

虽然介绍了INSERTS和UPDATES ，但根本不对DELETE语句进行排序。仅当所有语句都属于同一数据库表时，才能重新使用JDBC批处理。当传入语句针对另一个数据库表时，必须释放当前批处理，以便新批处理与当前语句数据库表匹配：

public Batch getBatch(BatchKey key) {
    if ( currentBatch != null ) {
        if ( currentBatch.getKey().equals( key ) ) {
            return currentBatch;
        }
        else {
            currentBatch.execute();
            currentBatch.release();
        }
    }
    currentBatch = batchBuilder().buildBatch(key, this);
    return currentBatch;
}

移除孤儿和手动冲洗

一种变通方法是在前进到新的Child关联之前，先手动刷新Hibernate Session，再解除所有Child实体的关联：

@Test
public void testOrphanRemoval() {
    LOGGER.info("Test batch delete with orphan removal");
    final AtomicReference<Long> startNanos = 
        new AtomicReference<>();

    addDeleteBatchingRows();

    doInTransaction(session -> {
        List<Post> posts = session.createQuery(
            "select distinct p " +
            "from Post p " +
            "join fetch p.details d " +
            "join fetch p.comments c")
        .list();

        startNanos.set(System.nanoTime());

        posts.forEach(Post::removeDetails);
        session.flush();

        posts.forEach(post -> {
            for (Iterator<Comment> commentIterator = 
                     post.getComments().iterator(); 
                     commentIterator.hasNext(); ) {
                Comment comment =  commentIterator.next();
                comment.post = null;
                commentIterator.remove();
            }
        });
        session.flush();

        posts.forEach(session::delete);
    });
    LOGGER.info("{}.testOrphanRemoval took {} millis",
        getClass().getSimpleName(),
        TimeUnit.NANOSECONDS.toMillis(
            System.nanoTime() - startNanos.get()
    ));
}

这次所有DELETE语句都已正确批处理：

Query:{[delete from PostDetails where id=?][2]} {[delete from PostDetails where id=?][3]} {[delete from PostDetails where id=?][1]} 
Query:{[delete from Comment where id=? and version=?][53,0]} {[delete from Comment where id=? and version=?][54,0]} {[delete from Comment where id=? and version=?][56,0]} {[delete from Comment where id=? and version=?][55,0]} {[delete from Comment where id=? and version=?][52,0]} {[delete from Comment where id=? and version=?][51,
Query:{[delete from Post where id=? and version=?][2,0]} {[delete from Post where id=? and version=?][3,0]} {[delete from Post where id=? and version=?][1,0]}

SQL级联删除

更好的解决方案是使用SQL级联删除，而不是JPA实体状态传播机制。这样，我们还可以减少DML语句的数量。由于Hibernate Session充当事务后写式缓存，因此在将实体状态转换与数据库端自动操作混合使用时，我们必须格外谨慎，因为持久性上下文可能无法反映最新的数据库更改。

Post实体一对多注释关联使用Hibernate特定的@OnDelete批注进行标记，以便自动生成的数据库架构包括ON DELETE CASCADE指令：

@OneToMany(cascade = {
       CascadeType.PERSIST, 
       CascadeType.MERGE}, 
    mappedBy = "post")
@OnDelete(action = OnDeleteAction.CASCADE)
private List<Comment> comments = new ArrayList<>();

生成以下DDL ：

alter table Comment add constraint 
FK_apirq8ka64iidc18f3k6x5tc5 foreign key (post_id) 
references Post on delete cascade

使用PostDetails实体一对一的Post关联也可以做到这一点：

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "id")
@MapsId
@OnDelete(action = OnDeleteAction.CASCADE)
private Post post;

以及相关的DDL ：

alter table PostDetails add constraint 
FK_h14un5v94coafqonc6medfpv8 foreign key (id) 
references Post on delete cascade

CascadeType.ALL和orphanRemoval被CascadeType.PERSIST和CascadeType.MERGE取代，因为我们不再希望Hibernate传播实体移除事件。

测试仅删除Post实体。

doInTransaction(session -> {
    List<Post> posts = session.createQuery(
        "select p from Post p")
    .list();
    startNanos.set(System.nanoTime());
    for (Post post : posts) {
        session.delete(post);
    }
});

由于只有一个目标表，因此DELETE语句已正确批处理。

Query:{[delete from Post where id=? and version=?][1,0]} {[delete from Post where id=? and version=?][2,0]} {[delete from Post where id=? and version=?][3,0]}