how to use postgresql array_agg function in JpaRepository spring boot?

Question

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.

Error :

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

what things have you tried? As postgres returns datatype text[] - JPA /Hibernate will not have datatype for that. You need to do custom Data type mapping. — Lucia
– Lucia, Commented May 31, 2021 at 11:07
I just tried alternate queries only.if possible could give some example of custom data mapping @Lucia — Ajith Deivam
– Ajith Deivam, Commented May 31, 2021 at 11:09
vladmihalcea.com/postgresql-array-java-list You can check this — Lucia
– Lucia, Commented May 31, 2021 at 11:16
@Lucia is possible to get directly in native query itself??without adding additional works? — Ajith Deivam
– Ajith Deivam, Commented May 31, 2021 at 11:23

Lucia · Accepted Answer · 2021-06-01 06:10:40Z

2

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

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.

answered Jun 1, 2021 at 6:10

Lucia

8551 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use postgresql array_agg function in JpaRepository spring boot?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related