如何在 Hibernate 中映射 PostgreSQL Interval 列类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1945615/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to map the PostgreSQL Interval column type in Hibernate?
提问by adam
Under PostgreSQL, I'm using PersistentDurationfor the mapping between the sql type interval & duration but it doesn't work.
在 PostgreSQL 下，我PersistentDuration用于 sql 类型间隔和持续时间之间的映射，但它不起作用。
Another user found the same issue & come with his own class:
另一个用户发现了同样的问题并带着他自己的课来：
public void nullSafeSet(PreparedStatement statement, Object value, int index)
throws HibernateException, SQLException {
if (value == null) {
statement.setNull(index, Types.OTHER);
} else {
Long interval = ((Long) value).longValue();
Long hours = interval / 3600;
Long minutes = (interval - (hours * 3600)) / 60;
Long secondes = interval - (hours * 3600) - minutes * 60;
statement.setString(index, "'"+ hours +":"
+ intervalFormat.format(minutes) + ":"
+ intervalFormat.format(secondes)+"'");
}
}
But it doesn't work with the real format because it suppose the interval pattern is only "hh:mm:ss". That is not the case: see
但它不适用于真正的格式，因为它假设间隔模式只是“hh:mm:ss”。事实并非如此：见
Here some few real examples i need to parse from the database:
这里有一些我需要从数据库中解析的真实示例：
1 day 00:29:42 00:29:42 1 week 00:29:42 1 week 2 days 00:29:42 1 month 1 week 2 days 00:29:42 1 year 00:29:42 1 decade 00:29:42
http://www.postgresql.org/docs/current/interactive/datatype-datetime.html
http://www.postgresql.org/docs/current/interactive/datatype-datetime.html
Have you a clean solution?
你有一个干净的解决方案吗？
回答by bpgergo
This is a working solution for JPA, Hibernate (with annotations).
这是 JPA、Hibernate（带注释）的有效解决方案。
This is the beginning of the entity class (for the table that has Interval column):
这是实体类的开头（对于具有 Interval 列的表）：
@Entity
@Table(name="table_with_interval_col")
@TypeDef(name="interval", typeClass = Interval.class)
public class TableWithIntervalCol implements Serializable {
This is the interval column:
这是间隔列：
@Column(name = "interval_col", nullable = false)
@Type(type = "interval")
private Integer intervalCol;
And this is the Interval class:
这是 Interval 类：
package foo.bar.hibernate.type;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.postgresql.util.PGInterval;
/**
* Postgres Interval type
*
* @author bpgergo
*
*/
public class Interval implements UserType {
private static final int[] SQL_TYPES = { Types.OTHER };
@Override
public int[] sqlTypes() {
return SQL_TYPES;
}
@SuppressWarnings("rawtypes")
@Override
public Class returnedClass() {
return Integer.class;
}
@Override
public boolean equals(Object x, Object y) throws HibernateException {
return x.equals(y);
}
@Override
public int hashCode(Object x) throws HibernateException {
return x.hashCode();
}
@Override
public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
throws HibernateException, SQLException {
String interval = rs.getString(names[0]);
if (rs.wasNull() || interval == null) {
return null;
}
PGInterval pgInterval = new PGInterval(interval);
Date epoch = new Date(0l);
pgInterval.add(epoch);
return Integer.valueOf((int)epoch.getTime() / 1000);
}
public static String getInterval(int value){
return new PGInterval(0, 0, 0, 0, 0, value).getValue();
}
@Override
public void nullSafeSet(PreparedStatement st, Object value, int index)
throws HibernateException, SQLException {
if (value == null) {
st.setNull(index, Types.VARCHAR);
} else {
//this http://postgresql.1045698.n5.nabble.com/Inserting-Information-in-PostgreSQL-interval-td2175203.html#a2175205
st.setObject(index, getInterval(((Integer) value).intValue()), Types.OTHER);
}
}
@Override
public Object deepCopy(Object value) throws HibernateException {
return value;
}
@Override
public boolean isMutable() {
return false;
}
@Override
public Serializable disassemble(Object value) throws HibernateException {
return (Serializable) value;
}
@Override
public Object assemble(Serializable cached, Object owner)
throws HibernateException {
return cached;
}
@Override
public Object replace(Object original, Object target, Object owner)
throws HibernateException {
return original;
}
}
回答by Vlad Mihalcea
As I explained in this article, you don't have to write your own Hibernate custom type to map the PostgreSQL
intervalcolumn to a JavaDurationobject. All you need to do is use the hibernate-typesproject.
正如我在本文中所解释的，您不必编写自己的 Hibernate 自定义类型来将 PostgreSQL
interval列映射到 JavaDuration对象。您需要做的就是使用hibernate-types项目。
So, after adding the proper Hibernate dependency:
因此，在添加适当的 Hibernate 依赖项之后：
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>2.6.0</version>
</dependency>
You just have to use the @TypeDefannotation to register the PostgreSQLIntervalType:
您只需要使用@TypeDef注释来注册PostgreSQLIntervalType：
@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
typeClass = PostgreSQLIntervalType.class,
defaultForType = Duration.class
)
@TypeDef(
typeClass = YearMonthDateType.class,
defaultForType = YearMonth.class
)
public class Book {
@Id
@GeneratedValue
private Long id;
@NaturalId
private String isbn;
private String title;
@Column(
name = "published_on",
columnDefinition = "date"
)
private YearMonth publishedOn;
@Column(
name = "presale_period",
columnDefinition = "interval"
)
private Duration presalePeriod;
public Long getId() {
return id;
}
public Book setId(Long id) {
this.id = id;
return this;
}
public String getIsbn() {
return isbn;
}
public Book setIsbn(String isbn) {
this.isbn = isbn;
return this;
}
public String getTitle() {
return title;
}
public Book setTitle(String title) {
this.title = title;
return this;
}
public YearMonth getPublishedOn() {
return publishedOn;
}
public Book setPublishedOn(YearMonth publishedOn) {
this.publishedOn = publishedOn;
return this;
}
public Duration getPresalePeriod() {
return presalePeriod;
}
public Book setPresalePeriod(Duration presalePeriod) {
this.presalePeriod = presalePeriod;
return this;
}
}
Now, when persisting the Bookentity:
现在，当持久化Book实体时：
entityManager.persist(
new Book()
.setIsbn("978-9730228236")
.setTitle("High-Performance Java Persistence")
.setPublishedOn(YearMonth.of(2016, 10))
.setPresalePeriod(
Duration.between(
LocalDate
.of(2015, Month.NOVEMBER, 2)
.atStartOfDay(),
LocalDate
.of(2016, Month.AUGUST, 25)
.atStartOfDay()
)
)
);
Hibernate will execute the proper SQL INSERT statement:
Hibernate 将执行正确的 SQL INSERT 语句：
INSERT INTO book (
isbn,
presale_period,
published_on,
title,
id
)
VALUES (
'978-9730228236',
'0 years 0 mons 297 days 0 hours 0 mins 0.00 secs',
'2016-10-01',
'High-Performance Java Persistence',
1
)
When fetching the Bookentity, we can see that the Durationattribute is properly fetched from the database:
在获取Book实体时，我们可以看到该Duration属性已正确从数据库中获取：
Book book = entityManager
.unwrap(Session.class)
.bySimpleNaturalId(Book.class)
.load("978-9730228236");
assertEquals(
Duration.between(
LocalDate
.of(2015, Month.NOVEMBER, 2)
.atStartOfDay(),
LocalDate
.of(2016, Month.AUGUST, 25)
.atStartOfDay()
),
book.getPresalePeriod()
);
回答by pstanton
Why not just turn it into a numeric and map to a Long?
为什么不把它变成一个数字并映射到一个 Long 呢？
SELECT EXTRACT(epoch FROM my_interval)
回答by Fran?ois Beausoleil
PostgreSQL has a date_part / extract function which you can use to return different fields, epoch being one of them. When extracting the epoch from an interval, you receive the number of seconds contained in the interval, and from there you can convert however you wish. I lost my experience with Hibernate, but you can do it this way:
PostgreSQL 有一个 date_part/extract 函数，你可以用它来返回不同的字段，epoch 就是其中之一。从间隔中提取纪元时，您会收到该间隔中包含的秒数，然后您可以根据需要进行转换。我失去了使用 Hibernate 的经验，但您可以这样做：
SELECT
average_interval_between_airings
, date_part('epoch', average_interval_between_airings) / 60 as minutes
, date_part('epoch', average_interval_between_airings) as seconds
FROM shows;
回答by Jared
According to the link you should have a day followed by hours:minutes:seconds. So changing the code to something like the following assuming you never need to have more then 23 hours 59 minutes in the interval.
根据链接，您应该有一天，然后是小时：分钟：秒。因此，将代码更改为如下所示的内容，假设您的时间间隔永远不需要超过 23 小时 59 分钟。
statement.setString(index, "'0 "+ hours +":"
+ intervalFormat.format(minutes) + ":"
+ intervalFormat.format(secondes)+"'");
I am not able to test this code since I don't have PostGreSql installed. For another discussion on this same issue see the following link, although you would have to modify the code provided to handle seconds. That shouldn't be much of a problem though.
我无法测试此代码，因为我没有安装 PostGreSql。有关同一问题的另一个讨论，请参阅以下链接，尽管您必须修改提供的代码来处理秒。不过这应该问题不大。
https://forum.hibernate.org/viewtopic.php?p=2348558&sid=95488ce561e7efec8a2950f76ae4741c
https://forum.hibernate.org/viewtopic.php?p=2348558&sid=95488ce561e7efec8a2950f76ae4741c
