带有@CONVERT的JPQL IN子句

Question

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

记录具有id和名称

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

带有@Convert的DocumentModel

@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;
}

记录转换器

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

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

如果recordIds有多个值（例如：docId=1）,即使：

• 嵌套选择不工作
• IN子句有和没有（）不工作
• 子句的成员不工作
• 导致本机SQL直接无法工作
• @公式不工作

而且问题似乎是记录应该是单引号-逗号分隔的，而不仅仅是逗号分隔的，因为它们是字符串。（本机SQL查询在这种情况下工作）

Answer 1

好像我被冬眠虫感染了

作为一种变通方法,请使用集合包装：

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

并更改属性转换器：

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

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