如何使用JPA和Hibernate复制INSERT / UPDATE / DELETE语句

Question

我想以一种可为我的系统恢复的方式重命名PostgreSQL（9.6）表（使用JPA / Hibernate的Java应用程序）

在我的Java代码中，JPA实体将具有以下注释@Entity
@Table(name="old_name")，数据库将具有名为的等效表old_name。

我想将表重命名为new_name，以便可以逐步更新数据库和Java应用程序，从而允许失败和回滚。

典型的步骤是

1. 创建old_namein的副本new_name
2. 确保两种读/写都可用（即，两种方式都复制了数据）
3. 更新Java应用程序以使用新表 new_name
4. 信心十足的系统更新完成后，删除 old_name

实际上，我希望在具有相同数据的相同模式下的重复表能够接收来自JPA实体的读取和写入。

我知道触发器的使用，并希望避免这种情况。我希望有一种我不知道并且还没有发现的技术，比使用触发器可以减轻痛苦。

我试图重命名该表并在其上创建一个“简单视图”，但是JPA实体抱怨，因为它找不到带有视图名称的表。（因为它是一个视图，而不是一个表:)，并且似乎没有@
View / @ Table JPA批注可以处理此问题）

我还没有尝试过这里列出的功能：http
:
//wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling，因为大多数似乎与池化，分片有关，我需要一个简单的短期表副本，但是我还将对此进行调查。

谢谢-当然，我想要最简单的选择，宁愿内置于postgres / JPA中，但也要认真考虑第三者的选择。

Answer 1

由于这是一个非常常见的问题，因此我写了 这篇文章，此答案基于该文章。

数据库表

假设您有以下两个表：

CREATE TABLE old_post (
    id int8 NOT NULL,
    title varchar(255),
    version int4 NOT NULL,
    PRIMARY KEY (id)
)

CREATE TABLE post (
    id int8 NOT NULL,
    created_on date, 
    title varchar(255),
    version int4 NOT NULL,
    PRIMARY KEY (id)
)

JPA实体

该old_post表必须与更新的表一起复制post。请注意，该post表现在具有比旧表更多的列。

我们只需要映射Post实体：

@Entity(name = "Post")
@Table(name = "post")
public static class Post {

    @Id
    private Long id;

    private String title;

    @Column(name = "created_on")
    private LocalDate createdOn = LocalDate.now();

    @Version
    private int version;

    //Getters and setters omitted for brevity
}

hibernate事件监听器

现在，我们必须注册3个事件侦听器，以拦截Post实体的INSERT，UPDATE和DELETE操作。

我们可以通过以下事件监听器来做到这一点：

public class ReplicationInsertEventListener 
        implements PostInsertEventListener {

    public static final ReplicationInsertEventListener INSTANCE = 
        new ReplicationInsertEventListener();

    @Override
    public void onPostInsert(
            PostInsertEvent event) 
            throws HibernateException {
        final Object entity = event.getEntity();

        if(entity instanceof Post) {
            Post post = (Post) entity;

            event.getSession().createNativeQuery(
                "INSERT INTO old_post (id, title, version) " +
                "VALUES (:id, :title, :version)")
            .setParameter("id", post.getId())
            .setParameter("title", post.getTitle())
            .setParameter("version", post.getVersion())
            .setFlushMode(FlushMode.MANUAL)
            .executeUpdate();
        }
    }

    @Override
    public boolean requiresPostCommitHanding(
            EntityPersister persister) {
        return false;
    }
}

public class ReplicationUpdateEventListener 
    implements PostUpdateEventListener {

    public static final ReplicationUpdateEventListener INSTANCE = 
        new ReplicationUpdateEventListener();

    @Override
    public void onPostUpdate(
            PostUpdateEvent event) {
        final Object entity = event.getEntity();

        if(entity instanceof Post) {
            Post post = (Post) entity;

            event.getSession().createNativeQuery(
                "UPDATE old_post " +
                "SET title = :title, version = :version " +
                "WHERE id = :id")
            .setParameter("id", post.getId())
            .setParameter("title", post.getTitle())
            .setParameter("version", post.getVersion())
            .setFlushMode(FlushMode.MANUAL)
            .executeUpdate();
        }
    }

    @Override
    public boolean requiresPostCommitHanding(
            EntityPersister persister) {
        return false;
    }
}

public class ReplicationDeleteEventListener 
        implements PreDeleteEventListener {

    public static final ReplicationDeleteEventListener INSTANCE = 
        new ReplicationDeleteEventListener();

    @Override
    public boolean onPreDelete(
            PreDeleteEvent event) {
        final Object entity = event.getEntity();

        if(entity instanceof Post) {
            Post post = (Post) entity;

            event.getSession().createNativeQuery(
                "DELETE FROM old_post " +
                "WHERE id = :id")
            .setParameter("id", post.getId())
            .setFlushMode(FlushMode.MANUAL)
            .executeUpdate();
        }

        return false;
    }
}

可以使用Hibernate注册3个事件侦听器Integrator：

public class ReplicationEventListenerIntegrator 
        implements Integrator {

    public static final ReplicationEventListenerIntegrator INSTANCE = 
        new ReplicationEventListenerIntegrator();

    @Override
    public void integrate(
            Metadata metadata,
            SessionFactoryImplementor sessionFactory,
            SessionFactoryServiceRegistry serviceRegistry) {

        final EventListenerRegistry eventListenerRegistry =
                serviceRegistry.getService(EventListenerRegistry.class);

        eventListenerRegistry.appendListeners(
            EventType.POST_INSERT, 
            ReplicationInsertEventListener.INSTANCE
        );

        eventListenerRegistry.appendListeners(
            EventType.POST_UPDATE, 
            ReplicationUpdateEventListener.INSTANCE
        );

        eventListenerRegistry.appendListeners(
            EventType.PRE_DELETE, 
            ReplicationDeleteEventListener.INSTANCE
        );
    }

    @Override
    public void disintegrate(
            SessionFactoryImplementor sessionFactory,
            SessionFactoryServiceRegistry serviceRegistry) {

    }
}

并且，要指示Hibernate使用此自定义Integrator，您需要设置hibernate.integrator_provider配置属性：

<property name="hibernate.integrator_provider"
          value="com.vladmihalcea.book.hpjp.hibernate.listener.ReplicationEventListenerIntegrator "/>

测试时间

现在，当持久化Post实体时：

Post post1 = new Post();
post1.setId(1L);
post1.setTitle(
    "The High-Performance Java Persistence book is to be released!"
);

entityManager.persist(post1);

Hibernate将执行以下SQL INSERT语句：

Query:["INSERT INTO old_post (id, title, version) VALUES (?, ?, ?)"], Params:[(1, The High-Performance Java Persistence book is to be released!, 0)]

Query:["insert into post (created_on, title, version, id) values (?, ?, ?, ?)"], Params:[(2018-12-12, The High-Performance Java Persistence book is to be released!, 0, 1)]

在执行另一项更新现有Post实体并创建新Post实体的事务时：

Post post1 = entityManager.find(Post.class, 1L);
post1.setTitle(post1.getTitle().replace("to be ", ""));

Post post2 = new Post();
post2.setId(2L);
post2.setTitle(
    "The High-Performance Java Persistence book is awesome!"
);

entityManager.persist(post2);

Hibernate还将所有操作复制到old_post表中：

 Query:["select tablerepli0_.id as id1_1_0_, tablerepli0_.created_on as created_2_1_0_, tablerepli0_.title as title3_1_0_, tablerepli0_.version as version4_1_0_ from post tablerepli0_ where tablerepli0_.id=?"], Params:[(1)]

 Query:["INSERT INTO old_post (id, title, version) VALUES (?, ?, ?)"], Params:[(2, The High-Performance Java Persistence book is awesome!, 0)]

 Query:["insert into post (created_on, title, version, id) values (?, ?, ?, ?)"], Params:[(2018-12-12, The High-Performance Java Persistence book is awesome!, 0, 2)]

 Query:["update post set created_on=?, title=?, version=? where id=? and version=?"], Params:[(2018-12-12, The High-Performance Java Persistence book is released!, 1, 1, 0)]

 Query:["UPDATE old_post SET title = ?, version = ? WHERE id = ?"], Params:[(The High-Performance Java Persistence book is released!, 1, 1)]

删除Post实体时：

Post post1 = entityManager.getReference(Post.class, 1L);
entityManager.remove(post1);

该old_post记录也被删除：

Query:["DELETE FROM old_post WHERE id = ?"], Params:[(1)]
Query:["delete from post where id=? and version=?"], Params:[(1, 1)]

有关更多详细信息，请查看本文。

代码可在GitHub上获得。