CODEWITHSUNDEEP
spring-bootspring-mvcjava-8spring-data-jpa
Ajith Deivam
Ajith Deivam

Reputation: 766

how to use postgresql array_agg function in JpaRepository spring boot?

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

PostgreSQL Query

Upvotes: 1

Views: 3909

Answers (1)

Lucia
Lucia

Reputation: 855

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.

Upvotes: 2

Related Questions