如何在 JpaRepository spring boot 中使用 postgresql array_agg 函数? [英] how to use postgresql array_agg function in JpaRepository spring boot?

问题描述

我用 postgresql 创建了新的 Spring Boot 项目.我喜欢使用 posgressql array_agg(例如:获取所有部门)使用 JPA 存储库本机查询，但它在发布时遇到一些错误.我尝试了一些其他解决方案，但无法获得预期的数据.

I have created new spring boot project with postgresql .I like to use posgressql array_agg(ex:get all department) using JPA Repository native query but its getting some error in blow posted. I have tried some alter solution but cant able to get expected data.

错误:

org.springframework.orm.jpa.JpaSystemException:JDBC 类型没有方言映射:2003；嵌套异常是 org.hibernate.MappingException:JDBC 类型没有方言映射:2003

org.springframework.orm.jpa.JpaSystemException: No Dialect mapping for JDBC type: 2003; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003

预期:应该得到数组或数据列表

Expected : Should get array or list of data

@Repository
public interface PostGroupRepository extends JpaRepository<PostGroup, Integer> {

    @Query(value = "SELECT array_agg(department) FROM boxinfo;", nativeQuery = true)
    public Object[] getDept();
}

推荐答案

第一个解决方案是使用以下依赖项:

First solution is to use below dependency:

<dependency>
            <groupId>com.vladmihalcea</groupId>
            <artifactId>hibernate-types-52</artifactId>
            <version>2.11.1</version>
        </dependency>

他已经编写了自定义类型并将其注册为自定义方言，如下所示

he has custom types already written and register that in the custom dialect like below

public class CustomPostgreDialect extends PostgreSQL10Dialect {
    public CustomPostgreDialect() {
        super();
        this.registerHibernateType(2003, StringArrayType.class.getName());
    }
} 

并在spring boot的application.yaml或application.properties中使用这个方言作为hibernate方言.

And use this dialect as the hibernate dialect in application.yaml or application.properties of spring boot.

spring.jpa.properties.hibernate.dialect: <packageName>.CustomPostgreDialect

第二种解决方案是自己编写自定义类型并在方言中注册，如上所示，如果您不想使用依赖项.

Second solution is to write the custom type yourself and register it in the dialect as shown above, if you don't want to use dependency.

这篇关于如何在 JpaRepository spring boot 中使用 postgresql array_agg 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！