Thread: hibernate nativequery and uuid

I'm currently using JPA with Hibernate as my ORM and have been able to
convince hibernate to play nicely with the Postgresql UUID.  Most of my
queries have been in EJBQL using the JPA entity manager's createQuery.
However when I try to do a UNION, JPA only returned the results of the
first query, and ignored the other UNION queries, which is in line with
what I have read online, in that JPA does not support UNIONS.

So I'm currently attempting to go via a createNativeQuery call.  The
keys on the target tables are PostgreSQL UUID data types.  When doing so
I get the following error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid
= character varying

The only relevant thing I have been able to find relating to it is
http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which
suggests adding a ::uuid cast to the parameter.

However, when doing that, hibernate thinks that it is a named parameter
and complains.

org.hibernate.QueryException: Not all named parameters have been set

Hibernate's functionality to declare an escape character for a LIKE
clause does not apply to this.  But in attempting to escape the colons,
getting various other hibernate parsing errors, so my attempts down this
path has not been of help.

Has anyone else run into this issue and been able to resolve it?

I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the
Postgresql 8.3-603 JDBC4 driver.

Thanks,

Andy

On Thu, Jul 31, 2008 at 5:57 PM, Andrew <archa@pacific.net.au> wrote:

> The only relevant thing I have been able to find relating to it is
> http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which
> suggests adding a ::uuid cast to the parameter.
>
> However, when doing that, hibernate thinks that it is a named parameter and
> complains.
> org.hibernate.QueryException: Not all named parameters have been set

Have you tried using the alternative (and more standard) CAST syntax
instead?  Perhaps that won't confuse Hibernate.

-Doug

Yeah, tried that, but get the following:

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

Thanks for the suggestion though.



Douglas McNaught wrote:

On Thu, Jul 31, 2008 at 5:57 PM, Andrew <archa@pacific.net.au> wrote:
 

The only relevant thing I have been able to find relating to it is
http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which
suggests adding a ::uuid cast to the parameter.

However, when doing that, hibernate thinks that it is a named parameter and
complains.
org.hibernate.QueryException: Not all named parameters have been set   

Have you tried using the alternative (and more standard) CAST syntax
instead?  Perhaps that won't confuse Hibernate.

-Doug
 

I have given it consideration, but haven't tried it as I have concluded
that I would still have the same issue.  The problem occurs with the
client attempting to apply a UUID value to filter the result set.  So I
would still have a datatype mismatch if I were to use a view.

I am confident that I can get it to work if I were to remove Hibernate
and make a direct JDBC call, as I have tested that approach previously.
That is a fall back position.  But I would like if I can to keep to a
single approach with my DB access for this application if I can.

The suggestion was appreciated though.



A.M. wrote:
>
> On Jul 31, 2008, at 5:57 PM, Andrew wrote:
>
>> I'm currently using JPA with Hibernate as my ORM and have been able
>> to convince hibernate to play nicely with the Postgresql UUID.  Most
>> of my queries have been in EJBQL using the JPA entity manager's
>> createQuery.  However when I try to do a UNION, JPA only returned the
>> results of the first query, and ignored the other UNION queries,
>> which is in line with what I have read online, in that JPA does not
>> support UNIONS.
>> So I'm currently attempting to go via a createNativeQuery call.  The
>> keys on the target tables are PostgreSQL UUID data types.  When doing
>> so I get the following error:
>>
>> org.postgresql.util.PSQLException: ERROR: operator does not exist:
>> uuid = character varying
>>
>> The only relevant thing I have been able to find relating to it is
>> http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which
>> suggests adding a ::uuid cast to the parameter.
>>
>> However, when doing that, hibernate thinks that it is a named
>> parameter and complains.
>> org.hibernate.QueryException: Not all named parameters have been set
>>
>> Hibernate's functionality to declare an escape character for a LIKE
>> clause does not apply to this.  But in attempting to escape the
>> colons, getting various other hibernate parsing errors, so my
>> attempts down this path has not been of help.
>>
>> Has anyone else run into this issue and been able to resolve it?
>>
>> I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the
>> Postgresql 8.3-603 JDBC4 driver.
>
> Could you use a view to hide the UNION?
>
> Cheers,
> M
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database:
> 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM
>
>
>

Oh, I see what you mean.  Use EJBQL on a view.  That would probably
work.  Have to get going, so will try that when I get back in several
hours.  I'll let you know how I go.

Andrew wrote:
> I have given it consideration, but haven't tried it as I have
> concluded that I would still have the same issue.  The problem occurs
> with the client attempting to apply a UUID value to filter the result
> set.  So I would still have a datatype mismatch if I were to use a view.
>
> I am confident that I can get it to work if I were to remove Hibernate
> and make a direct JDBC call, as I have tested that approach
> previously.  That is a fall back position.  But I would like if I can
> to keep to a single approach with my DB access for this application if
> I can.
>
> The suggestion was appreciated though.
>
>
>
> A.M. wrote:
>>
>> On Jul 31, 2008, at 5:57 PM, Andrew wrote:
>>
>>> I'm currently using JPA with Hibernate as my ORM and have been able
>>> to convince hibernate to play nicely with the Postgresql UUID.  Most
>>> of my queries have been in EJBQL using the JPA entity manager's
>>> createQuery.  However when I try to do a UNION, JPA only returned
>>> the results of the first query, and ignored the other UNION queries,
>>> which is in line with what I have read online, in that JPA does not
>>> support UNIONS.
>>> So I'm currently attempting to go via a createNativeQuery call.  The
>>> keys on the target tables are PostgreSQL UUID data types.  When
>>> doing so I get the following error:
>>>
>>> org.postgresql.util.PSQLException: ERROR: operator does not exist:
>>> uuid = character varying
>>>
>>> The only relevant thing I have been able to find relating to it is
>>> http://archives.postgresql.org/pgsql-bugs/2007-12/msg00061.php which
>>> suggests adding a ::uuid cast to the parameter.
>>>
>>> However, when doing that, hibernate thinks that it is a named
>>> parameter and complains.
>>> org.hibernate.QueryException: Not all named parameters have been set
>>>
>>> Hibernate's functionality to declare an escape character for a LIKE
>>> clause does not apply to this.  But in attempting to escape the
>>> colons, getting various other hibernate parsing errors, so my
>>> attempts down this path has not been of help.
>>>
>>> Has anyone else run into this issue and been able to resolve it?
>>>
>>> I'm using Postgresql 8.3.3 on Windows XP, Hibernate 3.2.6.GA and the
>>> Postgresql 8.3-603 JDBC4 driver.
>>
>> Could you use a view to hide the UNION?
>>
>> Cheers,
>> M
>>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database:
>> 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM
>>
>>
>>
>
>

oops, forgot to cc the mailing list again...

Andrew wrote:
> I know none of this relates directly to postgresql and on reflection
> is probably more appropriate for the hibernate forums.  So apologies
> for having raised the topic here.  Also, thanks for the suggestions
> that I have received on the topic.
>
> I have got the UNION working in a view via JPA.  However, it was not
> straightforward and the approach that I have taken is not that
> efficient.  So for those who are interested in some of the
> implementation details...
>
> Previously to get hibernate to use the postgresql uuid, I had to
> create a custom hibernate UserType as well as extend the JDBC
> postgresql driver.  I then referenced the custom data type and a
> custom UUID generator that I had created using JPA/hibernate
> annotations in the entities.
>
> However, the hibernate validation of views appears to be different
> than with tables, so after creating the corresponding view entity, on
> restarting the application server, I was getting the "No Dialect
> mapping for JDBC type: 1111" exception, despite the view entity being
> configured just like the table entities.  So I had to change my
> META-INF/persistence.xml to reference my extended JDBC postgresql
> driver, rather than the original JDBC driver.  For good measure, I
> also did the same to my hibernate-console.properties file as part of
> my eclipse environment.
>
> This addressed the 1111 exception.  But then I was hit with a
> "javax.persistence.PersistenceException:
> org.hibernate.HibernateException: Missing table: my_view" exception,
> as there is an outstanding defect in hibernate related to how it
> treats views.  See
> http://opensource.atlassian.com/projects/hibernate/browse/HHH-1329.
> Supposedly it was fixed in version 3.2.6, but I'm using version 3.2.6
> GA and it is patently still an issue.  So the only way around this at
> present is unfortunately to turn off validation with the
> hibernate.hbm2ddl.auto property in the META-INF/persistence.xml file.
>
> But after all of that, I can now use JPA entities in EJBQL's to use a
> view containing postgresql uuid's.  Nothing like speaking gibberish
> with an overuse of acronyms :-)
>
> Cheers,
>
> Andy
>
>
> A.M. wrote:
>>
>> Could you use a view to hide the UNION?
>>
>> Cheers,
>> M
>>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.comVersion: 8.0.138 / Virus Database:
>> 270.5.8/1582 - Release Date: 7/30/2008 6:37 PM
>>
>>
>>
>
>