JPA StoredProcedureQuery: pass UUID as a parameter

Question

I use JPA 2.1 (Hibernate), Postgres 9.6, and I need to pass java.util.UUID as a parameter to StoredProcedureQuery like this:

StoredProcedureQuery proc = em.createStoredProcedureQuery(myProc)
        .registerStoredProcedureParameter(0, UUID.class, ParameterMode.IN)
        .registerStoredProcedureParameter(1, ...)
        .setParameter(0, myUuid)
        .setParameter(1, ...);

By default, the Java type UUID is interpreted as Postgres type bytea and I get something like:

ERROR: function my_function(bytea, ...) does not exist.

Of course it does not exist, because my function is:

my_function(UUID, ...)

So, is there any way to define explicitly, which database-level type must be used for a particular parameter?

Might be something like the one we use in entity classes with the annotation:

@Type(type="pg-uuid")
private UUID uuid;

One obvious workaround is to pass the value as a String, and then cast it to UUID inside the function, but...

Does it work if you use a simple query (using `select my_function(?,...)`) instead of a "stored procedure"? — a_horse_with_no_name, May 29 '18 at 14:03
No, it leads to the same error. And the same workaround helps (uuid.toString() in Java and casting TEXT -> UUID in Postgres). — NikS, May 29 '18 at 14:41
The annotation you refer to (`@Type`) is nothing to do with the JPA API. Perhaps you should tag your question based on which JPA provider you are using ... — , May 29 '18 at 14:54

dmatej · Answer 1 · 2020-06-24T07:56:51.450

With EclipseLink 2.7.6 and Postgres 11.8 it works, I expect it should work with Hibernate too; originally I ended with the "bytea" too. First I needed this trivial converter, I have no idea why I have to convert UUID to UUID, but it works.

import java.util.UUID;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true)
public class UUIDConverter implements AttributeConverter<UUID, UUID> {

@Override
public UUID convertToDatabaseColumn(final UUID uuid) {
    return uuid;
}

@Override
public UUID convertToEntityAttribute(final UUID uuid) {
    return uuid;
}

}

The function api is this:

create or replace function generate_namespace(idSubscription uuid) returns integer as 
...

Just a side note - I wasn't able to return the whole record as managed entity, because JPA will not receive all required metadata and does not know what (and if) is the primary key etc. So instead of returns Namespace I return only it's primary key and then I call entityManager.find to get the managed entity generated by the function:

final StoredProcedureQuery query = manager.createStoredProcedureQuery("generate_namespace");
query.registerStoredProcedureParameter(1, UUID.class, ParameterMode.IN);
query.setParameter(1, idSubscription);
final Object[] result = (Object[]) query.getSingleResult();
return manager.find(Namespace.class, result[0]);

JPA StoredProcedureQuery: pass UUID as a parameter

1 Answers1