JPQL IN clause with @Convert

Question

In non normalized PostgreSQL database: Two tables "Document" and "Record"

Document has an id, and a comma-separated column recodeIds

--------------------------------------
| id (VARCHAR) | recordIds (VARCHAR) |
--------------------------------------
| 1            | 3,1                 |
| 2            | 2                   |
--------------------------------------

Record has id and name

---------------------------------
| id (VARCHAR) | name (VARCHAR) |
---------------------------------
| 1            | X              |
| 2            | Y              |
| 3            | Z              |
---------------------------------

DocumentModel with @Convert

@Entity
@Table(name = "Document")
public class DocumentModel {
    @Id
    @Column(name = "id")
    private String id;

    @Column(name = "recodeIds")
    @Convert(converter = RecordsConverter.class)
    private Set<String> records;
}

RecordsConverter

public class RecordsConverter implements AttributeConverter<Set<String>, String> {
    @Override
    public String convertToDatabaseColumn(Set<String> recordTypes) {
        return String.join(",", recordTypes);
    }
    @Override
    public Set<String> convertToEntityAttribute(String recordTypes) {
        return Arrays.stream(recordTypes.split(",")).collect(Collectors.toSet());
    }
}

JPQL Now I'm trying to use records in @Query

@Query("SELECT D.id, R.name)"
    + " FROM DocumentModel D"
    + " JOIN RecordModel R"
    + " ON R.id IN D.records"
    + " WHERE D.id = :docId"

This only works if recordIds has single value (Example: docId = 2)

It's not working if recordIds has multiple values (Example: docId = 1) even if:

• Nested SELECT not working
• IN clause with and without () not working
• MEMBER OF clause not working
• Resulted native SQL directly not working
• @Formula not working

And it seems the problem is that records should be single-quoted-comma-separated and not only comma-separated as they are strings. (Native SQL query works in this case)

Q: How to make this work using JPQL (non native query) without changing the RecordsConverter, and hopefully without JPA Specifications?

Answer 1

Seems like I was affected by Hibernate bug

As a workaround, use collection wrappers:

@Data
@Builder
public class MyCollection implements Serializable {
    private final Set<String> collection;
}

and change your Attribute Converter:

public class MyConverter implements AttributeConverter<MyCollection, String> {
    @Override
    public String convertToDatabaseColumn(MyCollection myCollection) {
        return String.join(",", myCollection.getCollection());
    }
    @Override
    public MyCollection convertToEntityAttribute(String myCollection) {
        return MyCollection.builder()
                .collection(Arrays.stream(myCollection.split(",")).collect(Collectors.toSet()))
                .build();
    }
}

and you Model:

@Entity
@Table(name = "MY_MODEL")
public class MyModel {
    @Column(name = "MY_COLLECTION")
    @Convert(converter = MyConverter.class)
    private MyCollection myCollection;
}

That worked for me.