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.

Take a look at this tutorial : http://www.thoughts-on-java.org/persist-postgresqls-jsonb-data-type-hibernate/ — Merve Sahin, Dec 14 '16 at 15:21
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, Dec 14 '16 at 15:33
I am dealing with a similar issue. See this: https://stackoverflow.com/questions/61371808/springbootkotlinpostgres-and-jsonb-org-hibernate-mappingexception-no-dialect Just a question, where do you implement @SqlResultSetMapping, in your repository? — panza, Apr 22 '20 at 22:36

score 0 · Accepted Answer · answered Dec 15 '16 at 08:20

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.

JPA native query mapping POJO class with JSONB value on PostgreSQL

1 Answers1