问题描述
在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
您有干净的解决方案吗?
这是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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!