hibernate nativequery and uuid

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

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

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

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