blocks|key|409840|text|JDBC+不是java.sql.Types类中列出的标准类型之一。我知道如果你调用resultSet.getObject("interval_column")，当它被强制转换时，它是一个PGInterval，所以看起来PG驱动程序可能会强迫你这样处理它，除非你按照Glenn说的做，并在你的SQL中把它转换成一个字符串，或者一个数字。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|BOLD|entityRanges|data|409841|在我们的应用程序中，我们使用JodaTime进行所有数据管理，并且编写了Hibernate类型，将bean属性与PGInterval进行来回转换，并使用getObject和setObject与JDBC进行通信。我怀疑这些代码会帮助你处理你在这里寻找的东西，但如果你感兴趣的话，我可以和你分享。|409842|更新:这是在Joda+Time和PGInterval之间进行转换的Hibernate+Type类。我知道这并没有回答这个问题，但是最初的发帖要求提供示例代码。|409843|package+com.your.package.hibernate.types;

import+java.io.Serializable;
import+java.sql.PreparedStatement;
import+java.sql.ResultSet;
import+java.sql.SQLException;
import+java.sql.Types;

import+org.hibernate.HibernateException;
import+org.hibernate.usertype.UserType;
import+org.joda.time.DurationFieldType;
import+org.joda.time.Period;
import+org.joda.time.ReadableDuration;
import+org.joda.time.ReadablePeriod;
import+org.postgresql.util.PGInterval;

public+class+JodaTimeDurationType
++++implements+UserType+{

++++public+Class<?>+returnedClass()+{
++++++++return+ReadableDuration.class;
++++}


++++public+int[]+sqlTypes()+{
++++++++return+new+int[]+{Types.OTHER};
++++}


++++public+Object+nullSafeGet(ResultSet+resultSet,+String[]+names,+Object+owner)
++++++++throws+HibernateException,+SQLException+{

++++++++try+{
++++++++++++final+PGInterval+pgi+=+(PGInterval)resultSet.getObject(names[0]);

++++++++++++final+int+years+=+pgi.getYears();
++++++++++++final+int+months+=+pgi.getMonths();
++++++++++++final+int+days+=+pgi.getDays();
++++++++++++final+int+hours+=+pgi.getHours();
++++++++++++final+int+mins+=+pgi.getMinutes();
++++++++++++final+double+secs+=+pgi.getSeconds();

++++++++++++return+new+Period(years,+months,+0,+days,+hours,+mins,+(int)secs,+0).toStandardDuration();

++++++++}
++++++++catch+(Exception+e)+{
++++++++++++return+null;
++++++++}
++++}


++++public+void+nullSafeSet(PreparedStatement+statement,+Object+value,+int+index)
++++++++throws+HibernateException,+SQLException+{

++++++++if+(value+==+null)+{
++++++++++++statement.setNull(index,+Types.OTHER);
++++++++}
++++++++else+{
++++++++++++final+ReadablePeriod+period+=+((ReadableDuration)value).toPeriod();

++++++++++++final+int+years+=+period.get(DurationFieldType.years());
++++++++++++final+int+months+=+period.get(DurationFieldType.months());
++++++++++++final+int+days+=+period.get(DurationFieldType.days());
++++++++++++final+int+hours+=+period.get(DurationFieldType.hours());
++++++++++++final+int+mins+=+period.get(DurationFieldType.minutes());
++++++++++++final+int+secs+=+period.get(DurationFieldType.seconds());

++++++++++++final+PGInterval+pgi+=+new+PGInterval(years,+months,+days,+hours,+mins,+secs);
++++++++++++statement.setObject(index,+pgi);
++++++++}
++++}


++++public+boolean+equals(Object+x,+Object+y)
++++++++throws+HibernateException+{

++++++++return+x+==+y;
++++}


++++public+int+hashCode(Object+x)
++++++++throws+HibernateException+{
++++++++return+x.hashCode();
++++}


++++public+Object+deepCopy(Object+value)
++++++++throws+HibernateException+{
++++++++return+value;
++++}


++++public+boolean+isMutable()+{
++++++++return+false;
++++}


++++public+Serializable+disassemble(Object+value)
++++++++throws+HibernateException+{
++++++++throw+new+HibernateException("not+implemented");
++++}


++++public+Object+assemble(Serializable+cached,+Object+owner)
++++++++throws+HibernateException+{
++++++++throw+new+HibernateException("not+implemented");
++++}


++++public+Object+replace(Object+original,+Object+target,+Object+owner)
++++++++throws+HibernateException+{
++++++++throw+new+HibernateException("not+implemented");
++++}
}|code-block|syntax|javascript|409844|entityMap^0|7|E|15|12|2L|A|S|3V|0|0|42|1K|A|24|9|2E|9|0|0|27|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|D]]|E|@]|F|$]]|$1|G|3|H|5|6|7|10|8|@$9|11|A|12|B|D]|$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]]|E|@]|F|$]]|$1|I|3|J|5|6|7|19|8|@$9|1A|A|1B|B|D]]|E|@]|F|$]]|$1|K|3|L|5|M|7|1C|8|@]|E|@]|F|$N|O]]|$1|P|3|-4|5|6|7|1D|8|@]|E|@]|F|$]]]|Q|$]]

An interval isn't one of the standard JDBC Types, as listed in the <code>java.sql.Types</code> class. I know that if you call <code>resultSet.getObject("interval_column")</code>, it is a <code>PGInterval</code> when casted, so it looks like the PG JDBC driver might be forcing your hand to deal with it as such, unless you do what Glenn says and convert it to a string, or maybe a number, in your SQL. 

In our application, we use JodaTime for all of our date management, and we have a Hibernate Type written that converts our bean property to and from a <code>PGInterval</code>, and use <code>getObject</code> and <code>setObject</code> to communicate with JDBC. I doubt that code would help you deal with what you are looking for here, but I can share it with you if you are interested.

UPDATED: Here is the Hibernate Type class that converts between Joda Time and PGInterval. I know this doesn't answer the question, but the original poster asked for the sample code.

<pre><code>package com.your.package.hibernate.types;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.joda.time.DurationFieldType;
import org.joda.time.Period;
import org.joda.time.ReadableDuration;
import org.joda.time.ReadablePeriod;
import org.postgresql.util.PGInterval;

public class JodaTimeDurationType
 implements UserType {

 public Class&lt;?&gt; returnedClass() {
 return ReadableDuration.class;
 }


 public int[] sqlTypes() {
 return new int[] {Types.OTHER};
 }


 public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)
 throws HibernateException, SQLException {

 try {
 final PGInterval pgi = (PGInterval)resultSet.getObject(names[0]);

 final int years = pgi.getYears();
 final int months = pgi.getMonths();
 final int days = pgi.getDays();
 final int hours = pgi.getHours();
 final int mins = pgi.getMinutes();
 final double secs = pgi.getSeconds();

 return new Period(years, months, 0, days, hours, mins, (int)secs, 0).toStandardDuration();

 }
 catch (Exception e) {
 return null;
 }
 }


 public void nullSafeSet(PreparedStatement statement, Object value, int index)
 throws HibernateException, SQLException {

 if (value == null) {
 statement.setNull(index, Types.OTHER);
 }
 else {
 final ReadablePeriod period = ((ReadableDuration)value).toPeriod();

 final int years = period.get(DurationFieldType.years());
 final int months = period.get(DurationFieldType.months());
 final int days = period.get(DurationFieldType.days());
 final int hours = period.get(DurationFieldType.hours());
 final int mins = period.get(DurationFieldType.minutes());
 final int secs = period.get(DurationFieldType.seconds());

 final PGInterval pgi = new PGInterval(years, months, days, hours, mins, secs);
 statement.setObject(index, pgi);
 }
 }


 public boolean equals(Object x, Object y)
 throws HibernateException {

 return x == y;
 }


 public int hashCode(Object x)
 throws HibernateException {
 return x.hashCode();
 }


 public Object deepCopy(Object value)
 throws HibernateException {
 return value;
 }


 public boolean isMutable() {
 return false;
 }


 public Serializable disassemble(Object value)
 throws HibernateException {
 throw new HibernateException("not implemented");
 }


 public Object assemble(Serializable cached, Object owner)
 throws HibernateException {
 throw new HibernateException("not implemented");
 }


 public Object replace(Object original, Object target, Object owner)
 throws HibernateException {
 throw new HibernateException("not implemented");
 }
}
</code></pre>

Java date &amp; time datatypes are as everybody knows, there's no need to focus on that.

However, when I'm using JDBC or Spring-based extensions, such as SimpleJdbcTemplate to retrieve and store interval values, what Java type should I use, if I don't want to use org.postgresql.util.PGInterval class? 

This class is internal of PostgreSQL driver so using it would make the code DB-specific. I think it should be possible to operate on intervals in DB-agnostic way, because it is one of standard SQL types.

How to operate on PostgreSQL interval datatype using jdbc/spring-jdbc not using PGInterval?

数据管理

PostgreSQL

Java

Java日期和时间数据类型是众所周知的，没有必要关注这一点。但是，当我使用JDBC或基于Spring的扩展(比如SimpleJdbcTemplate )来检索和存储interval Java值时，如果我不想使用org.postgresql.util.PGInterval类，我应该使用什么类型？此类是PostgreSQL...

问如何在不使用PGInterval的情况下使用jdbc/spring-jdbc操作PostgreSQL间隔数据类型？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不使用PGInterval的情况下使用jdbc/spring-jdbc操作PostgreSQL间隔数据类型？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不使用PGInterval的情况下使用jdbc/spring-jdbc操作PostgreSQL间隔数据类型？
EN