如何使用JPA和Hibernate复制INSERT / UPDATE / DELETE语句 [英] How to replicate INSERT/UPDATE/DELETE statements using JPA and Hibernate
问题描述
我想以一种可为我的系统恢复的方式重命名PostgreSQL（9.6）表（使用JPA / Hibernate的Java应用程序）
在我的Java中代码，JPA实体将具有以下注释 @Entity @Table（name = old_name）和数据库将有一个等效的表，称为 old_name 。
我想将表重命名为 new_name 可以逐步更新数据库和Java应用，从而允许失败和回滚。
典型步骤是
- 在
new_name old_name的副本> - 确保两种读/写都可用（即，数据以两种方式复制）
- 更新Java应用程序以使用新表
new_name - 完成自信的系统更新后，删除
old_name
有效地，我想在同一模式wi中创建一个重复的表
我知道触发器的使用，并且希望避免这种情况。我希望有一种我不知道并且没有发现的技术可以使它比使用触发器减轻痛苦。
我试图重命名表并在其上创建一个简单视图，但是JPA实体抱怨，因为找不到具有该视图名称的表。 （因为它是一个视图，而不是一个表：），而且似乎没有@ View / @ Table JPA批注可以处理此问题）
我还没有尝试过此处列出的功能： http://wiki.postgresql.org/wiki/Replication,_Clustering， _and_Connection_Pooling ，因为大多数似乎与池化，分片有关，我需要一个简单的短期表副本，但我还将对此进行调查。
谢谢-我当然想要最简单的选择，宁愿使用postgres / JPA内置的东西，但也会认真考虑第三者的选择。
< blockquote>
数据库表
假设您有以下两个表：
创建表old_post（
id int8 NOT NULL，
标题varchar（255），
版本int4 NOT NULL，
主键（id）
）
CREATE TABLE post（
id int8非空，
创建日期，
标题varchar（255），
版本int4非空，
主键（id）
）
JPA实体
old_post 表必须与较新的 post 复制。注意， post 表现在比旧表具有更多的列。
我们只需要映射 Post 实体：
@Entity（name = Post）
@Table（name = post）
公共静态类Post {
@Id
private Long id;
私有字符串标题；
@Column（name = created_on）
私有LocalDate createdOn = LocalDate.now（）;
@ Version
private int版本；
//为简便起见，省略了字母和设置器
}
休眠事件侦听器
现在，我们必须注册3个事件侦听器以拦截 Post 实体。
我们可以通过以下事件监听器执行此操作：
公共类ReplicationInsertEventListener
实现PostInsertEventListener {
public static final ReplicationInsertEventListener INSTANCE =
new ReplicationInsertEventListener（）;
@Override
public void onPostInsert（
PostInsertEvent事件）
抛出HibernateException {
最终对象实体= event.getEntity（）;
if（Post的实体实例）{
Post post =（Post）实体；
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 requirePostCommitHanding（
EntityPersister持久性）{
返回false;
}
}
公共类ReplicationUpdateEventListener
实现PostUpdateEventListener {
public static final ReplicationUpdateEventListener INSTANCE =
新的ReplicationUpdateEventListener（） ;
@Override
public void onPostUpdate（
PostUpdateEvent event）{
最终对象实体= event.getEntity（）;
if（Post的实体实例）{
Post post =（Post）实体；
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 requirePostCommitHanding（
EntityPersister持久性）{
返回false;
}
}
公共类ReplicationDeleteEventListener
实现PreDeleteEventListener {
public static final ReplicationDeleteEventListener INSTANCE =
新的ReplicationDeleteEventListener（） ;
@Override
public boolean onPreDelete（
PreDeleteEvent event）{
最终对象实体= event.getEntity（）;
if（Post的实体实例）{
Post post =（Post）实体；
event.getSession（）。createNativeQuery（
Delete from old_post +
WHERE id =：id）
.setParameter（ id，post .getId（））
.setFlushMode（FlushMode.MANUAL）
.executeUpdate（）;
}
返回false；
}
}
可以使用Hibernate注册3个事件侦听器 Integrator ：
公共类ReplicationEventListenerIntegrator
实现了Integrator {
公共静态最终ReplicationEventListenerIntegrator INSTANCE =
new ReplicationEventListenerIntegrator（）;
@Override
公共无效积分（
元数据元数据，
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瓦解（
SessionFactoryImplementor sessionFactory，
SessionFactoryServiceRegistry serviceRegistry）{
}
}
然后，指示Hibernate使用此自定义的 Integrator ，则需要设置 hibernate.integrator_provider 配置属性：
<属性名称= hibernate.integrator_provider
value = com.vladmihalcea.book.hpjp.hibernate.listener.ReplicationEventListenerIntegrator />
测试时间
现在，当坚持一个 Post 实体：
Post post1 = new Post（）;
post1.setId（1L）;
post1.setTitle（
将发布高性能Java持久性书！
）;
entityManager.persist（post1）;
Hibernate将执行以下SQL INSERT语句：
查询：[ INSERT INTO old_post（id，title，version）VALUES（？，？，？）]，参数：[（1，高性能Java持久性书，0）]
查询：[插入帖子（created_on，标题，版本，id）值（？，？，？，？）]，参数：[（ 2018年12月12日，《高性能Java持久性》这本书即将发行！，0，1）]
进行另一笔交易以更新现有的 Post 实体并创建新的 Post 实体时：
Post post1 = entityManager.find（Post.class，1L）;
post1.setTitle（post1.getTitle（）。replace（成为，））;
Post post2 = new Post（）;
post2.setId（2L）;
post2.setTitle（
高性能Java持久性书很棒！
）;
entityManager.persist（post2）;
Hibernate将所有操作复制到 old_post 表中以及：
查询：[选择tablerepli0_.id作为id1_1_0_，tablerepli0_.created_on作为created_2_1_0_，tablerepli0_.title作为title3_1_0_， tablerepli0_.version作为posttablerepli0__中的version4_1_0_，其中tablerepli0_.id =？]，参数：[（1）]
查询：[在old_post中插入ID（标题，标题，版本）VALUES（？， ？，？）]，Params：[（2，高性能Java持久性书很棒！，0）]
查询：[插入帖子（created_on，title，version，id ）values（？，？，？，？）]，Params：[（（2018-12-12，高性能Java持久性书很棒！，0，2）]
查询： [更新发布集created_on = ?、 title = ?、 version = ?，其中id =？和version =？]，参数：[（2018-12-12，高性能Java持久性书发行！，1， 1，0）]
查询：[ UPDATE old_post SET title =？，version =？WHERE id =？]，Params：[（高性能Java Persiste ，因为书已发布！，1，1）]
删除帖子时实体：
Post post1 = entityManager.getReference（Post.class，1L）;
entityManager.remove（post1）;
old_post 记录也被删除：
查询：[从old_post WHERE id =？中删除]，参数：[（1）]
查询：[删除帖子，其中id =？和version =？]，参数：[（1，1）]
有关更多详细信息，请查看这篇文章。
代码可在 GitHub 。
I would like to rename a PostgreSQL (9.6) table in a way that is recoverable for my system (A java app using JPA/Hibernate)
In my java code, a JPA entity would have the following annotations @Entity @Table(name="old_name") and the database would have an equivalent table called old_name.
I would like to rename the table to new_name in a way that I can incrementally update the database and java app, allowing for failure and rollback.
Typical steps would be
- create copy of
old_nameinnew_name - ensure read/writes available in both (i.e. data is replicated both ways)
- update java app to use new table
new_name - when confident system update complete, remove
old_name
Effectively I would like a duplicate table in the same schema with the same data, both able to accept reads and writes, that can be read from JPA entities.
I am aware of the use of triggers, and would like to avoid that. I am hoping there is a technique I'm not aware of and haven't found that would make this less painful than using triggers.
I have tried to rename the table and create a "simple view" over it, however the JPA entity complains as it can't find a table with the name of the view. (Because it is a view, not a table :) and there seems no @View/@Table JPA annotation that will handle this)
I haven't yet tried the facilities listed here: http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling as the majority seem to be about pooling, sharding, and I need a simple short term table replica, but I will be investigating these also.
Thanks - I would like the simplest option of course, preferring something built in to postgres/JPA but will seriously consider 3rd party options also.
Since this is a very common question, I wrote this article, on which this answer is based on.
Database tables
Assuming you have the following two tables:
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 entity
The old_post table must be replicated with the newer post. Notice that the post table has more columns now than the old table.
We only need to map the Post entity:
@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 event listeners
Now, we have to register 3 event listeners to intercept the INSERT, UPDATE, and DELETE operations for the Post entity.
We can do this via the following event listeners:
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;
}
}
The 3 event listeners can be registered using a Hibernate 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) {
}
}
And, to instruct Hibernate to use this custom Integrator, you need to set up the hibernate.integrator_provider configuration property:
<property name="hibernate.integrator_provider"
value="com.vladmihalcea.book.hpjp.hibernate.listener.ReplicationEventListenerIntegrator "/>
Testing time
Now, when persisting a Post entity:
Post post1 = new Post();
post1.setId(1L);
post1.setTitle(
"The High-Performance Java Persistence book is to be released!"
);
entityManager.persist(post1);
Hibernate will execute the following SQL INSERT statements:
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)]
When doing another transaction that updates an existing Post entity and creates a new Post entity:
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 replicates all actions to the old_post table as well:
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)]
When deleting a Post entity:
Post post1 = entityManager.getReference(Post.class, 1L);
entityManager.remove(post1);
The old_post record is deletected as well:
Query:["DELETE FROM old_post WHERE id = ?"], Params:[(1)]
Query:["delete from post where id=? and version=?"], Params:[(1, 1)]
For more details, check out this article.
Code available on GitHub.
这篇关于如何使用JPA和Hibernate复制INSERT / UPDATE / DELETE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
