3

I am attempting to use a named query against a Postgres database to select all rows with a given UUID (being used as a foreign key). Here is the named query that is being called.

  @NamedNativeQuery(name = "getAllXByFK",
  query = "SELECT * FROM table n WHERE FK = :param",
    resultClass = Foobar.class)})

I set the parameter using the java.util.UUID type.

query.setParameter(param.getKey(), param.getValue());

When I go to get the ResultSet with query.list() the following error is reported:

ERROR: operator does not exist: uuid = bytea

Any suggestions?

BrandonKowalski
  • 118
  • 2
  • 10
  • Please have a look at http://stackoverflow.com/questions/4495233/postgresql-uuid-supported-by-hibernate You could try to implement a usertype similar to the patch in HHH-3579 – blagerweij Feb 13 '15 at 06:15

2 Answers2

2

Not really beautiful, but this worked for me:

SQL:

SQLQuery query = session.createSQLQuery("... where cast(c.id as varchar) = :id ");

Parameter set like this:

query.setString("id", myUUID.toString());

I only wonder if PostgreSQL does still use the index on id if a cast is performed on the column... ?

Edit:

With Hibernate 5.0.9 I strangely do not need the ugly cast, I can set the parameter as expected with:

query.setParameter("id", myUUID);

Edit 2019-05-11:

For SQL queries I tend to use jdbcTemplate in my Hibernate projects now. Just autowire it and use it:

@Autowired
private NamedParameterJdbcTemplate  jdbcTemplate;

// ...

MapSqlParameterSource p = new MapSqlParameterSource("name", "value");
p.addValue("anotherParam", true);
List<Map<String, Object>> result = jdbcTemplate.queryForList("select ... where id = :name", p);
yglodt
  • 13,807
  • 14
  • 91
  • 127
  • 2
    It does not use index. Without cast when explain gives `Index Scan...` after cast explain gives `Seq Scan...` – Richeek May 19 '16 at 07:02
1

Usually this error is reported when you supply a null value for the bind parameter.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911