JPA native query mapping POJO class with JSONB value on PostgreSQL

Question

I was trying to retrieve joined values from multiple tables into a custom POJO using a native query. One of the values I want to retrieve is a JSONB field. While I'm able to get the entity with that field, I get a 'org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111' exception when I force it into the custom POJO. Here is what I used:

CREATE TABLE book (
  id BIGSERIAL NOT NULL PRIMARY KEY,
  data JSONB NOT NULL
);
CREATE TABLE price (
  book_id BIGSERIAL NOT NULL PRIMARY KEY,
  price NUMERIC(19,2) NOT NULL
);
INSERT INTO book (id, data) VALUES (0, '{"value": "someValue"}');
INSERT INTO price (book_id, price) VALUES (0, 10.00);

Using the POJO's:

@Entity
@TypeDef(defaultForType = MyJson.class, name = "MyJsonType", typeClass = MyJsonType.class)
@Data
@SqlResultSetMapping(name = "CustomMapping",
        classes = {
                @ConstructorResult(targetClass = CustomPOJO.class,
                        columns = {@ColumnResult(name = "id"),
                                @ColumnResult(name = "data"),
                                @ColumnResult(name = "price")})
        })
public class Book {

    @Id
    @GeneratedValue
    Long id;

    @Column(nullable = false, columnDefinition = "JSONB")
    MyJson data;
}

With it's MyJson class:

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MyJson implements Serializable {

    private String value;
}

And

@Entity
@Data
@NoArgsConstructor
public class Price {

    @Id
    private Long id;
    private BigDecimal price;
}

And my custom POJO

@AllArgsConstructor
@Data
public class CustomPOJO {

    private Long id;
    private MyJson data;
    private BigDecimal price;
}

The '@TypeDef' custom dialect mapping is implemented like this. The @SqlResultSetMapping I got from here. Now I try to query using my custom mapping:

String query = "SELECT id, data, price\n" +
               "FROM book, price\n" +
               "WHERE book.id = price.book_id;";
Query nativeQuery = em.createNativeQuery(query, "CustomMapping");
@SuppressWarnings("unchecked")
List<CustomPOJO> customPOJOS = nativeQuery.getResultList();

I realize that hibernate doesn't recognize the type definition when I use my custom SqlResultSetMapping annotation. How can I fix that? Note that I can't use TypedQueries since my actual queries are way to complex than I can handle with the JPA query syntax. If necessary I can upload an example project on github.

Thats what I used in the first place (I even linked it). The problem is, that the custom dialect isn't recognized as soon as I use the SqlResultSetMapping. — SlideM
– SlideM, Commented Dec 14, 2016 at 15:33
I am dealing with a similar issue. See this: stackoverflow.com/questions/61371808/… Just a question, where do you implement @SqlResultSetMapping, in your repository? — panza
– panza, Commented Apr 22, 2020 at 22:36

SlideM · Accepted Answer · 2016-12-15 08:20:59Z

0

I managed to fix it. As it turns out, you need to tell each column in the SqlResultSetMapping which type it should represent. For the Jsonb field of the field class MyJson you need to use the custom UserType class 'MyJsonType' (Implementation on this Site). This looks like this:

@SqlResultSetMapping(name = "CustomMapping",
        classes = {
                @ConstructorResult(targetClass = CustomPOJO.class,
                        columns = {@ColumnResult(name = "id", type = Long.class),
                                @ColumnResult(name = "data", type = MyJsonType.class),
                                @ColumnResult(name = "price", type = BigDecimal.class)})
        })

Obviously you need the appropriate constructor for the custom pojo class, in my example thats taken care of with the @AllArgsConstructor annotation.

answered Dec 15, 2016 at 8:20

SlideM

4515 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

JPA native query mapping POJO class with JSONB value on PostgreSQL

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