CODEWITHSUNDEEP
sqlspringhibernatespring-bootjpa
Truica Sorin
Truica Sorin

Reputation: 539

Spring Boot JPA Mapping an array from a returned query value

I need to run a query on a table which returns an array based of the values of a column.

The table looks like this: enter image description here

The result from the query looks like this:enter image description here

I've tried to map that array to a model class but it always throws No Dialect mapping for JDBC type: 2003 Are there any ways to do this?

My classes:

@Entity
public class Option {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private int option;

    @ManyToOne
    @JoinColumn(name = "user_id")
    User user;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getOption() {
        return option;
    }

    public void setOption(int option) {
        this.option = option;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }



}

Repository:

@Repository
public interface OptionRepo extends JpaRepository<Option, Integer>{
    @Query(value = "SELECT user_id, array_agg(option) FROM option GROUP BY user_id ORDER BY user_id", nativeQuery = true)
    public List<OptionQuery> getOptions(); 

}

OptionQuery Class:

public class OptionQuery {

    private int user_id;
    private int[] array_agg;

    public int getUser_id() {
        return user_id;
    }

    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }

    public int[] getArray_agg() {
        return array_agg;
    }

    public void setArray_agg(int[] array_agg) {
        this.array_agg = array_agg;
    }
}

How should i deal with this cause i'm kind of stuck at this point:(

Upvotes: 0

Views: 2487

Answers (1)

Vitor Santos
Vitor Santos

Reputation: 611

I tried to look for the error you posted. It looks like hibernate is having a hard time trying to convert the result from your query.

Hibernate doesn't know how to convert out of the box the string array that results from the DBMS function array_agg.

Consider implementing an UserType. Other alternative is to simply map this result to String. Inside your class you can convert the result.

I found useful info on this thread: No Dialect mapping for JDBC type: 2003

Upvotes: 1

Related Questions