我想以一种可为我的系统恢复的方式重命名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
实际上，我希望在同一架构中有一个具有相同数据的重复表，既能接受读写，也能从JPA实体中读取
我知道触发器的使用，并希望避免这种情况。我希望有一种我不知道也没有发现的技术能比使用触发器减轻痛苦。
我试图重命名该表并在其上创建一个"简单视图"，但是JPA实体抱怨说，它找不到具有该视图名称的表。(因为它是一个视图，而不是一个表：(并且似乎没有@view/@table JPA注释可以处理这个问题(
我还没有尝试过这里列出的设施：http://wiki.postgresql.org/wiki/Replication，_Clustering，_and_Connection_Pooling似乎是关于池化、分片的，我需要一个简单的短期表副本，但我也会研究这些。
谢谢——当然，我想要最简单的选择，更喜欢内置的东西，而不是postgres/JPA，但我也会认真考虑第三方的选择。
数据库表
假设您有以下两个表：
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
}
休眠事件侦听器
现在，我们必须注册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;
}
}
可以使用HibernateIntegrator:注册3个事件侦听器
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上提供的代码。