[Java] Custom hibernate SQL queries

Sprint Data JpaRepository makes it easy to have simple custom queries. But more complex queries with custom output, grouping, aggregation and more are partly not possible and need the usage of other utilities.

Solution one uses a native query:

import javax.persistence.EntityManager;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.type.StringType;
import com.vladmihalcea.hibernate.type.json.JsonNodeBinaryType;

/// ...

public record CustomEntity(String name, List<MyEntity> myEntities) {}

public List<CustomEntity> findAllOfCloud(@NonNull final Cloud cloud) {
  return this.entityManager.createNativeQuery("""
            SELECT
              cloud.name,
              COALESCE(JSONB_AGG(my_entity), CAST('{}' AS JSONB)) AS my_entities
            FROM
             my_entity
            JOIN
              cloud
            ON
              my_entity.entity_group_id = cloud.entity_group_id
            AND
              cloud.id = :cloudId
            GROUP BY
              cloud.name
          """)
      .setParameter("cloudId", cloud.getId())
      .unwrap(NativeQuery.class)
      .addScalar("name", StringType.INSTANCE)
      .addScalar("my_entities", JsonNodeBinaryType.INSTANCE)
      .setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
      .getResultStream()
      .map(object -> this.cloudEntitiessByNameFromMap((Map<String, Object>) object))
      .toList();
}

private CustomEntity cloudEntitiessByNameFromMap(final Map<String, Object> map) {
  try {
    return new CustomEntity(
        (String) map.get("name"),
        this.objectMapper.readerForListOf(MyEntity.class)
            .readValue((JsonNode) map.get("my_entities")));
  } catch (final IOException exception) {
    throw new InternalErrorException("Exception while deserializing custom entities", exception);
  }
}

The interesting parts here are:

• using unwrap to get NativeQuery from hibernate so we can use the result transformer
• using addScalar to identify the output columns
• using the AliasToEntityMapResultTransformer to transform each row into a Map<String, Object> and then building the CustomEntity from map-entries
  
• using objectMapper.readForListOf to transform the aggregated JSON results into a List<MyEntity>
  

 

The second solution involves Projections and grouping inside Java:

import com.querydsl.core.types.Projections;

// ...

public record CustomEntity(@NonNull EntityOne entityOne, @NonNull EntityTwo entityTwo) {
}

public record CustomEntityGrouped(String name, List<EntityTwo> entityTwos) {
}

public List<CustomEntityGrouped> findAllOfCustomEntity(@NonNull final UUID cloudId) {
  return new JPAQuery<>(this.entityManager)
      .select(Projections.constructor(CustomEntity.class, QEntityOne.entityOne, QEntityTwo.entityTwo))
      .from(QEntityTwo.entityTwo)
      .join(QEntityOne.entityOne)
      .on(QEntityTwo.entityTwo.attachmentGroup.eq(QEntityOne.entityOne.attachmentGroup)
          .and(QEntityOne.entityOne.cloudId.eq(cloudId)))
      .stream()
      .collect(groupingBy(
          customEntity -> Option.of(customEntity.entityOne().getName()),
          mapping(CustomEntity::entityTwo, toSet())))
      .entrySet()
      .stream()
      .map(entry -> new CustomEntityGrouped(
          entry.getKey().getOrNull(),
          entry.getValue().stream().toList()))
      .toList();
}

The interesting parts here are:

• using Projections to define the output entity. There are other creating options available, like bean (which should work with interfaces) or list
• collecting the results by grouping them, and afterwards mapping the grouped results into the final data-structure