0

I am trying to use a native query to avoid n+1 selects with an eagerly fetched @ElementCollection while limiting the result size at the same time.

Assuming the following (simplified) entities:

@Entity
@Table(name = "message")
class Message(
    @Id
    var id: String,
    var title: String,
    var body: String,
    var receivedAt: Instant,
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "message_tag")
    var tags: MutableSet<Tag> = mutableSetOf()
)

@Embeddable
data class Tag(
    @Column(name = "label")
    var label: String? = null
)

I create a native query that handles the row limit correctly

SELECT message.id,
       message.title,
       message.body,
       message.received_at,
       tag.message_id,
       tag.label
FROM (SELECT *
      FROM message
      WHERE message.title = ?
      ORDER BY message.received_at DESC
      OFFSET ? ROWS FETCH FIRST ? ROWS ONLY) message
     LEFT OUTER JOIN message_tag tag
                     ON base.id = tag.message_id

Now I want to execute the query and map the result to Message instances that have the tags initialized.

I managed to do this with native Hibernate API:

        val session = entityManager.unwrap(Session::class.java)
        session.createNativeQuery(
            """
SELECT message.id,
       message.title,
       message.body,
       message.received_at,
       tag.message_id,
       tag.label
FROM (SELECT *
      FROM message
      WHERE message.title = ?
      ORDER BY message.received_at DESC
      OFFSET ? ROWS FETCH FIRST ? ROWS ONLY) message
     LEFT OUTER JOIN message_tag tag
                     ON base.id = tag.message_id
"""
        )
            .apply {
                addRoot("message", Message::class.java)
                addFetch("tag", "message", "tags")
            }
            .setParameter(1, "some-title")
            .setParameter(2, 0)
            .setParameter(3, 10)
            .resultList

Is there any way to do this in the standard JPA API? I looked at @SqlResultSetMapping but that gives me no way to configure an @ElementCollection mapping.

lbilger
  • 304
  • 1
  • 8
  • 1
    You marked the relationship as eager and are saying now you will build the queries to return all the data required to build those entities, so are a bit stuck in what you can do to give it what it needs. I'd suggest you try making the relationship lazy so the results are not expected, and let JPA/Hibernate query for them when accessed. I'm not overly familiar with Hiberante's batch fetching operations, (see https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_batchfetch.htm for what would be nicer) but hibernate has BatchSize (https://stackoverflow.com/a/26843184/496099 ) – Chris Mar 23 '23 at 15:31
  • You're right that batch fetching might be a valid compromise here if there is no standard JPA way and I want to avoid native Hibernate API. Thanks for pointing that out! – lbilger Mar 24 '23 at 11:29

0 Answers0