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.

Martin van Wingerden · Accepted Answer · 2019-09-26 11:13:45Z

7

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;

answered Sep 26, 2019 at 11:13

Martin van Wingerden

9917 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karol Dowbecki · Accepted Answer · 2018-04-06 08:50:21Z

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 6, 2018 at 8:50

answered Apr 6, 2018 at 8:45

Karol Dowbecki

45.3k9 gold badges83 silver badges118 bronze badges

4 Comments

Sayak Mukhopadhyay Over a year ago

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.

Karol Dowbecki Over a year ago

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.

Sayak Mukhopadhyay Over a year ago

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 Over a year ago

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

incodenito · Accepted Answer · 2019-05-15 04:47:00Z

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, 2019 at 4:47

incodenito

13 bronze badges

Collectives™ on Stack Overflow

Spring Data cannot fetch a record using UUID in postgresql

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related