返回带有jsonb字段的投影

Question

我正在使用JPA作为项目的持久化API。它包含一个实体，该实体具有一个Postgres jsonb列，如下所示：

@Entity
@TypeDefs(value = [TypeDef(name = "jsonb", typeClass = JsonBinaryType::class)])
data class Post(
    //... irrelevant columns
    @Type("jsonb") @Column(columnDefinition = "jsonb") val postDefinitions: List<Map<String, Any>>
)

当从该实体获取所有数据时，这个方法完美地工作。但现在我不需要返回所有数据，而是一些字段，包括jsonb。 这就是问题所在。我有这个投影对象，并且正在使用以下本机查询来检索数据，但不知何故，JPA无法映射它。

interface PostProjection {
    val id: UUID
    val postDefinitions: List<Map<String, Any>>
}

@Query("SELECT CAST(id AS VARCHAR) AS id, jsonb_array_elements(post_definitions) AS postDefinitions " +
    "FROM post WHERE id = :postId", nativeQuery = true)
fun getPostDefinitionsById(val id: UUID): List<PostProjection>

它已经有了getters和setters。我尝试使用@TypeDefs注释接口，使用@Type注释列，并将其从interface更改为data class。

没有成功。找不到任何关于此的信息。我不想检索String，然后将其映射到相应的数据类型。

有人经历过这个吗？

- Leonardo

---

我使用了不同的实现（而非spring-data-jpa），但是将类型更改为Object对我有所帮助。val postDefinitions: Any? 此外，提供出现错误的代码片段会更有帮助。 - Thanh Nhan

Answer 1

我遇到了同样的问题并找到了解决方案：只需创建一个带有对象的构造函数，而不是你自己的构造函数，并在内部进行转换：这是我的投影。

@Getter
@Setter
@NoArgsConstructor
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class MobileProductDetails extends MobileProduct {

    public MobileProductDetails(...
                                Object technoCodes,
                                String keywords,
                                Object documents,
                                Object children,
                                String url) {
        ...
        this.technoCodes = (List<String>) technoCodes;
        this.keywords = keywords;
        this.documents = (List<Integer>) documents;
        this.children = (List<Integer>) children;
        this.url = url;
    }

   //....

    @Type(type = "jsonb")
    private List<String> technoCodes;

    private String keywords;

    @Type(type = "jsonb")
    private List<Integer> documents;

    @Type(type = "jsonb")
    private List<Integer> children;

    private String url;

}

这是我的查询

"SELECT new ru.*.repository.projection.MobileProductDetails(" +
            "       P.id," +
            "       P.article," +
            "       PI.title," +
            "       P.shortName," +
            "       P.name, " +
            "       P.unitName," +
            "       P.type," +
            "       PI.group.id," +
            "       P.passportRequired," +
            "       PD.textDescription," +
            "       PD.textInterpretation," +
            "       PD.textStatement," +
            "       PD.textPreparation," +
            "       PI.material," +
            "       PI.technoCodes," +
            "       PI.keywords," +
            "       PI.documents, " +
            "       PI.children, " +
            "       PI.url" +
            ") " +
            "FROM Product P " +
            "INNER JOIN ProductInfo PI ON PI.article = P.article " +
            "INNER JOIN ProductDescription PD ON PD.article = P.article " +
            "WHERE P.deleted IS FALSE " +
            "       AND PI.deleted IS FALSE " +
            "       AND P.id = :productId " +
            "       AND PI.country = :country " +
            "ORDER BY PI.sort ASC "

- Tolstov Maxim

---

大多数情况下，使用Object作为参数并不是一个好主意，因为可能会传递不包含所需字段的对象，从而导致错误。 - Alexander Mladzhov