Spring Data cannot fetch a record using UUID in postgresql

Question

I am using Spring data to manage a REST API. All tables in my postgresql database has a primary key of type UUID. I am able to persist this data but unable to fetch it. Below is the code I am using and the problem I am facing.

MyTableParent.java

@MappedSuperclass
public class MyTableParent {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(
            name = "uuid",
            strategy = "com.mypackage.UUIDGenerator",
            parameters = {
                    @Parameter(
                            name = UUID_NAMESPACE,
                            value = "something"
                    )
            }
    )
    private UUID id;
    // Constructor getter setter
}

MyTable.java

@Entity
@Table(name = "my_table")
public class MyTable extends MyTableParent {
    private String name;
    // constructor getter setter
}

This is my service class for the table

TableService.java

@Service("tableservice")
public class TableService {
    private TableRepository tableRepository;

    public TableService(TableRepository tableRepository) {
        this.tableRepository = tableRepository;
    }

    public List<MyTable> read(MultiValueMap<String, String> queryParams) {
        return this.tableRepository.findAll(TableSpecification.searchByParams(queryParams));
    }

    public MyTable read(UUID id) {
        return this.tableRepository.findById(id).orElse(null);
    }
}

As you can see I am using a repository object as well as a specification. I have defined the repository as such

@Repository
interface TableRepository extends JpaRepository<MyTable, UUID>, JpaSpecificationExecutor<MyTable> {
}

And the specification is created as

public class TableSpecification {
    public static <T> Specification<T> searchByParams(MultiValueMap<String, String> queryMap) {
        return (Specification<T>) (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) -> {
            Set<Map.Entry<String, List<String>>> fields = queryMap.entrySet();
            if (queryMap.size() != 0) {
                List<Predicate> predicates = new ArrayList<>();
                for (MultiValueMap.Entry<String, List<String>> field : fields) {
                    List<String> params = new ArrayList<>(field.getValue());
                    String property = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, field.getKey());
                    Expression<String> expression = root.get(property);
                    predicates.add(expression.in(params));
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[]{}));
            }
            return null;
        };
    }
}

The first issue arises when I call read(MultiValueMap). Say I have my request params as /my_table?id=12345678-1234-1234-1234-123456789012which triggers the code this.tableRepository.findAll(TableSpecification.searchByParams(queryParams)) where queryParams the MultiValueMap. This is throwing an error

java.lang.IllegalArgumentException: Parameter value [12345678-1234-1234-1234-123456789012] did not match expected type [java.util.UUID (n/a)]
    at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:54) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.spi.QueryParameterBindingValidator.validate(QueryParameterBindingValidator.java:27) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.internal.QueryParameterBindingImpl.validate(QueryParameterBindingImpl.java:90) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:55) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:486) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:104) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.criteria.internal.compile.CriteriaCompiler$1$1.bind(CriteriaCompiler.java:119) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.criteria.internal.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:368) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.query.criteria.internal.compile.CriteriaCompiler.compile(CriteriaCompiler.java:149) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3616) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:203) ~[hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350) ~[spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at com.sun.proxy.$Proxy125.createQuery(Unknown Source) ~[na:na]...

So I tried to check the findById method. This is causing this.tableRepository.findById(id).orElse(null) to return null which means that the find failed too.

I am at a loss how to proceed with this. Please advise.

N.B. I can fetch records by the name field correctly. I have a hunch that spring data is messing up the types somewhere.

EDIT Not working even for String type field.

score 7 · Answer 1 · answered Sep 26 '19 at 11:13

I'm using Hibernate 5.3.11 and had the same problem. Thanks to the answer Luca Basso Ricci in 'FindByUUID() using Spring Data's JPA Repository' it was fixed.

The solution (for me) was adding the following annotation to the UUID field:

import org.hibernate.annotations.Type

... 

@Type(type="org.hibernate.type.UUIDCharType")
private UUID uuid;

Karol Dowbecki · Accepted Answer · 2018-04-06T08:50:21.063

4

The Parameter value [12345678-1234-1234-1234-123456789012] did not match expected type [java.util.UUID (n/a)] error means that value read from the database failed to map to the corresponding field type.

See HHH-9562 Dialect specific UUID handling which improved UUID handling in Hibernate 5. Your problem might be solvable with @Column(columnDefinition = "uuid", updatable = false) annotation.

@Id
@Column(columnDefinition = "uuid", updatable = false)
@GeneratedValue(generator = "uuid")
@GenericGenerator(
        name = "uuid",
        strategy = "com.mypackage.UUIDGenerator",
        parameters = {
                @Parameter(
                        name = UUID_NAMESPACE,
                        value = "something"
                )
        }
)
private UUID id;

If you need to debug it further check PostgreSQL Dialect class that you configured in your code and ensure that field definition in your entity correctly declares PostgreSQL UUID column type. There have been a number of issues with UUID e.g. HHH-9577.

edited Apr 06 '18 at 08:50

answered Apr 06 '18 at 08:45

Karol Dowbecki

43,645
9
78
111

Unfortunately, I am still getting the error. One thing to note though, if my database is having an id say `12345678-1234-1234-1234-123456789012` and I am querying `111111111-1111-1111-1111-111111111111` , i.e. a value that does not exists, I am still getting the same error. This tells me that failure is happening before the value is read from the db. – Sayak Mukhopadhyay Apr 06 '18 at 08:53
Try declaring the `id` field as `private String id;` in your `@Entity` and modify the `TableRepository` to use `String` instead of `UUID`. This will either prove or disprove that you have problem with type mapping as `String` should always map. – Karol Dowbecki Apr 06 '18 at 09:00
I checked, and the response is empty. I know I said that normal string type queries were working but they aren't working now. I guess I must have changed something that broke this. Now I am at a loss as o what. – Sayak Mukhopadhyay Apr 06 '18 at 09:34
Now even `this.tableRepository.findAll()` is returning me an empty array even though I have records in the table. I must say that I am able to save records using `this.tableRepository.save(new MyTable(values))` – Sayak Mukhopadhyay Apr 06 '18 at 09:51

score 0 · Answer 3 · answered May 15 '19 at 04:47

0

Try to make sure your data type in your mapping entity class and also in your column table type. Please refer to this hibernate mapping type https://examples.javacodegeeks.com/enterprise-java/hibernate/hibernate-mapping-types-example/

answered May 15 '19 at 04:47

incodenito

1
2

Spring Data cannot fetch a record using UUID in postgresql

3 Answers3