Search code examples
javahibernatehibernate-mapping

Query for JSON mapped field in isolation using hibernate


I have a Hibernate entity with a field that maps to a jsonb column. I can store and retrieve it using a Repository easily if I operate on the entire object:

@Entity
public class Parent {
...
  @Column(name = "children", nullable = false, columnDefinition = "JSONB")
  @Type(JsonType.class)
  List<Child> children;
...

I would like to add a Repository method to load that column in isolation. I have tried:


  @Query("""
      SELECT p.children
      FROM Parent p
      WHERE p.id = :parentId
      """)
  List<Child> getChildren(@Param("parentId") long parentId);

That gives me the error:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.example.Child]
    at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:322)
    at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:195)
    at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:175)
...

The converter must exist in my project because I can load the Parent class as a whole, and the List<Child> is there. How can I re-use that to allow me to load this column in isolation?


Solution

  • You have the following options here,

    Option 1

    Use JPQL something like this

    @Query("""
          SELECT new com.package.entity.ClassName(p.children)
          FROM Parent p
          WHERE p.id = :parentId
          """)
    

    Make sure that the constructer you are using exists in the class,

    Option 2

    Use a @NamedNativeQuery instead of normal @Query

    For Example: Entity Class:

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    @Entity
    @SqlResultSetMapping(
            name = "userEntityMapping",
            classes = {
                    @ConstructorResult(
                            targetClass = StatsDTO.class,
                            columns = {
                                    @ColumnResult(name = "campId", type = Integer.class),
                                    @ColumnResult(name = "userCount", type = Byte.class),
                                    @ColumnResult(name = "modifiedAt", type = Instant.class)
                            }
                    )
            }
    )
    @NamedNativeQuery(
            name = "UserEntity.getStatsDTO",
            query = "YOUR_QUERY",
            resultSetMapping = "userEntityMapping"
    )
    @Table(name = "user_campaign_objective")
    public class UserEntity implements Serializable {
        private static final long serialVersionUID = 1224483473794225719L;
    
        @Id
        @Column(name = "campaign_id")
        private BigInteger campId;
    }
    

    Repository Class:

    public interface UserRepository extends CrudRepository<UserEntity, Long> {
        @Query(nativeQuery = true)
        List<StatsDTO> getStatsDTO(Long campId);
    }
    

    Option 3

    Use Projection Interface

    public interface QueryResponse {
        String getColumn1();
        Byte getColumn2();
        Integer getColumn3();
    }
    

    Make sure the column names are exact in the getters