CODEWITHSUNDEEP
javahibernateuuid
BrandonKowalski
BrandonKowalski

Reputation: 118

Hibernate named query with UUID as parameter

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?

Upvotes: 3

Views: 7682

Answers (2)

yglodt
yglodt

Reputation: 14551

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);

Upvotes: 2

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153710

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

Upvotes: 1

Related Questions