jpa 使用jsonb字段返回投影

slsn1g29 于 2023-05-07 发布在其他

关注(0)|答案(1)|浏览(113)

我正在使用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无法Map它。

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，然后将其Map到相应的数据类型。
有人翻过吗？

jpa

来源：https://stackoverflow.com/questions/72169589/returning-projection-with-jsonb-field

1条答案

按热度按时间

nzk0hqpo1#

我遇到了同样的问题，并找到了解决方案：只需要用对象创建构造函数，而不是你的构造函数，然后在里面强制转换：这是我的投影

@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 "

赞(0）回复(0）举报 2023-05-07

我来回答

jpa 使用jsonb字段返回投影

1条答案

相关问题

热门标签

最新问答