postgresql“没有时区的时间戳”的hibernate映射？

I'm trying to map java.util.Date to postgresql timestamp without time zone type. I'm using a custom sql-insert statement (because the table is partitioned and postgresql won't return number of rows updated on a normal insert), and I keep getting an error saying the function does not exist. I suspect this is due to a problem mapping these Date fields.

我正在尝试将java.util.Date映射到没有时区类型的postgresql时间戳。我正在使用自定义的sql-insert语句(因为表是分区的,而postgresql不会返回正常插入时更新的行数),并且我一直收到错误,说该函数不存在。我怀疑这是由于映射这些Date字段时出现问题。

From the hibernate mapping:

从hibernate映射:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="false">
    <class ...snip...
        <property name="dateCreated" type="timestamp">
            <column name="DATE_CREATED"/>
        </property>
        <property name="dateUpdated" type="timestamp">
            <column name="DATE_UPDATED"/>
        </property>
...snip...
        <sql-insert callable="true">{call insert_wal (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>
    </class>
</hibernate-mapping>

From the application log:

从应用程序日志中:

Hibernate: 
    {call insert_wal (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
12/06/09 17:22:15.409  WARN hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null
12/06/09 17:22:15.425 ERROR hibernate.util.JDBCExceptionReporter - Batch entry 0 select * from insert_wal (foo, 439, ...snip... 2009-06-12 17:22:15.315000 -07:00:00, 2009-06-12 17:22:15.378000 -07:00:00, ...snip...) as result was aborted.  Call getNextException to see the cause.
12/06/09 17:22:15.425  WARN hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 42883
12/06/09 17:22:15.425 ERROR hibernate.util.JDBCExceptionReporter - ERROR: function insert_wal(character varying, integer, ...snip... unknown, unknown, ...snip...) does not exist
12/06/09 17:22:15.425 ERROR event.def.AbstractFlushingEventListener - Could not synchronize database state with session

(The Exception returned from getNextException referenced in the first ERROR line simply repeats the second ERROR line with a stack trace.) As you can see, for some reason the function signature JDBC is looking for has "unknown" as the data types where the timestamps go. I've tried every combination of property type and column sql-type I can think of, but it shows up as "unknown" every time. Here's the signature of the function definition in the DB:

(从第一个ERROR行引用的getNextException返回的异常只是重复带有堆栈跟踪的第二个ERROR行。)正如您所看到的,由于某种原因,JDBC正在寻找的函数签名具有“未知”作为时间戳的数据类型走。我已经尝试了我能想到的属性类型和列sql类型的每种组合,但它每次都显示为“未知”。这是DB中函数定义的签名:

CREATE OR REPLACE FUNCTION insert_wal(p_action character varying, p_partner_id numeric, ...snip... p_date_created timestamp without time zone, p_date_updated timestamp without time zone, ...snip...

The only other differences are that where there's a "numeric" in the function definition, the ERROR line shows various types like "integer", "double precision", and "bigint"; and one parameter in the function definition is type "text", where the error shows "character varying".

唯一的区别是,在函数定义中有“数字”的地方,ERROR行显示各种类型,如“整数”,“双精度”和“bigint”;函数定义中的一个参数是“text”类型,其中错误显示“字符变化”。

I can produce a similar error by calling the function in pgAdminIII, except that it's the string values (e.g. 'foo' with quotes) that are "unknown", while the date values (I just used now()) are treated as "timestamp with time zone".

我可以通过调用pgAdminIII中的函数来产生类似的错误,除了它是字符串值(例如带有引号的'foo')是“未知”,而日期值(我刚刚使用now())被视为“timestamp”与时区“。

So: am I doing something wrong in my hibernate mapping? Is it a problem with the function?

那么:我在hibernate映射中做错了吗?这个功能有问题吗?

1 个解决方案

#1

I don't know Hibernate enough to comment on that part, but you could perhaps work around the error by overriding the datatypes in the INSERT statement:

我不知道Hibernate是否足以对该部分发表评论,但您可以通过覆盖INSERT语句中的数据类型来解决错误:

<sql-insert callable="true">{call insert_wal (?::text, ?::numeric, <snip>?::timestamp, )::timestamp, <snip>)}</sql-insert>

You probably don't need to override all the fields, of course, just those causing problems.

您可能不需要覆盖所有字段,当然,只是那些导致问题的字段。

#1