如何在Hibernate中映射类型Interval？ [英] How to map the type Interval in Hibernate?
问题描述
在PostgreSQL中，我使用 PersistentDuration 作为sql类型间隔&但它不起作用。
另一位用户发现了同样的问题&来自他自己的类：
pre $ public $ {code $} 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（）;
长时间=间隔/ 3600;
长时间=（间隔 - （小时* 3600））/ 60;
Long secondes =间隔 - （小时* 3600） - 分钟* 60;
statement.setString（index，'+ hours +：
+ intervalFormat.format（minutes）+：
+ intervalFormat.format（secondes）+'） ;
$ b
但它不起作用真正的格式，因为它假设间隔模式只是
hh：mm：ss。情况并非如此：请参阅
这里有几个我需要从数据库中解析出来的实例：
1天00:29:42
00:29:42
1周00:29:42
1周2天00:29:42
1个月1周2天00:29:42
1年00:29:42
1年十个月00:29:42
http://www.postgresql.org/docs /current/interactive/datatype-datetime.html
您有干净的解决方案吗？
这是JPA，Hibernate（带注释）的工作解决方案。
这是实体类的开始表格有Interval列）：
$ p $ @Entity
@Table（name =table_with_interval_col）
@TypeDef（name =interval，typeClass = Interval.class）
public class TableWithIntervalCol实现Serializable {
这是间隔列：
@Column（name =interval_col，nullable = false）
@Type（type =interval）
private Integer intervalCol;
这就是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;
$ b / **
* Postgres间隔类型
*
* @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;
$ b @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（）;
$ b @Override
public Object nullSafeGet（ResultSet rs，String [] names，Object owner）
抛出HibernateException，SQLException {
String interval = rs.getString（名称[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（）;
$ b @Override
public void nullSafeSet（PreparedStatement st，Object value，int index）
抛出HibernateException，SQLException {
if （value == null）{
st.setNull（index，Types.VARCHAR）;
} else {
// This 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;
$ b @Override
public可串行化反汇编（Object value）抛出HibernateException {
return（Serializable）value;
}
@Override
public Object assemble（Serializable cached，Object owner）
throws HibernateException {
return cached;
$ b @Override
public Object replace（Object original，Object target，Object owner）
throws HibernateException {
return original;
}
}
Under PostgreSQL, I'm using PersistentDuration for the mapping between the sql type interval & duration but it doesn't work.
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
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
Have you a clean solution?
This is a working solution for JPA, Hibernate (with annotations).
This is the beginning of the entity class (for the table that has Interval column):
@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:
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;
}
}
这篇关于如何在Hibernate中映射类型Interval？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
