Include dynamic comments in repository queries

[Mumeii]

Hi 👋

Right now, in my team, whenever we use Jpa to access our DBs, we add some hard coded comments in our @Query annotation, such as :

	@Query( nativeQuery = true,
		value = """
			SELECT /* findStockByStoreId */
                            ...
                 """
         List< Stock > findStockByStoreId( int storeId );

We're heavily relying on those JPA annotations, in various modules, which all target the same DB. I know, it's an arguable design 😉

Even if we have an OTEL based Datadog solution to monitor a sample of our production & preproduction environment trafic, we also use other monitoring tools that directly spot at DB level the requests taking too much time, and produce a top 10 of the slowest one

Those hardcoded comments enable us to quickly spot from which modules & part of their code those slow queries are from

But it feels cumbersome having to do it ourself, on each @Query usage

• Sometime we edit the queries and loose the comment while copying it around in our IDE
• Sometime we change the method name, forgetting to update the corresponding query comment
• ...

Present feature request, if relevant, is aiming to add the possibility for spring-data-jpa users to have a centralized and / or dynamique way to add such comments

What we have considered using so far but is not working as intended :

• @Meta( comment = "a static comment" )
• @QueryHint( name = "org.hibernate.comment", value = "a static comment" )
• an hibernate StatementInspector

The first @Meta solution has the following drawbacks :

• necessitate to be placed next to each of our @Query usage
• its comment part doesn't support JPQL, so nothing dynamic can be produced
• even if it was supporting it, it would be counter productive from a performance POV to rerun the same JPQL formula over and over again on each calls to the annotated methods
• can only be applied on the ones we've created, but what if we'd like to produce custom comments for the ones that are dynamically produced by the Spring Boot Jpa framework ?

The second one suffer from the same problematic, and on top of that, it necessitate to know which underlying ORM is used

Regarding the last one, a good point is that it's a centralized way to modify all requests, no matter which one we're talking about, but :

• it's pure Hibernate solution, I.E. it's not no possible to know in the context of which method it got called without doing ugly and heavy hacks, such as getting the thread call stack, and analyzing it 😅
• manipulating the SQL as a mere String is not really developper friendly

What we'd like to get with this feature request :

The best would be to have a callback system that, according to various context informations, would produce a comment, cache it, and associate it to each subsequent SQL requests that JPA produce from the same source

If such a solution is not possible, would it be at least possible to have the @Meta's comment supporting SpEL ?

We'd like to have access to the following informations no matter which solution is implemented :

• The spring boot application name, if possible
• The full or simple class name of the object instance from which one the SQL query got triggered
• The method name from which one the SQL query got triggered

It would also be great if a cache is used by default, or if there is an option to activate such a cache, in order to avoid too much overhead upon comment generation if it's a deterministic formula the enduser is using

Hopping it's not a too crazy idea, we're looking forward for your feedback about it 🤞

[mp911de]

Thanks for reaching out.

@QueryHints(@QueryHint( name = "org.hibernate.comment", value = "a static comment" )) works for all query variants (JPQL and SQL @Query and derived queries) when hibernate.use_sql_comments is enabled.

JPQL Query parsers generally remove comments from a JPQL @Query and therefore, @Query("select u from User u /* my comment */") is not advisable.

When enabling hibernate.use_sql_comments, Hibernate generates quite some comments including the original JPQL or calling out dynamic native SQL if no comment has been provided. I wonder whether it would make sense to default to a comment if no comment/query hint is provided.

Here are a few samples of Hibernate's log output:

/* select count(*) from User x WHERE x.id = :id */ select count(*) from SD_User u1_0 where u1_0.id=?

/* <criteria> */ select u1_0.id,u1_0.DTYPE,u1_0.active …

/* insert for org.springframework.data.jpa.domain.sample.User */insert into SD_User (active,city,…

/* dynamic native SQL query */ SELECT firstname, lastname FROM SD_User WHERE id = ?

[Mumeii]

Hi @mp911de

Thanks for your feedback !

Regarding our comments usage in @Query, to be honest, we use 99% of the time their native form, so I guess most of the comments we add are kept as no JPQL is involved in such a case.

What is certain is that we manage to get those comments on the DB monitoring tool side 🥳

And indeed, I've noticed that whenever activating hibernate.use_sql_comments, not only the @Meta are sent, but as well loads other informations

But as mentioned in my initial message, the problematic part of those Hibernate generated comments is that they are out of any context of the hosting application.

That's why only activating this option is not a solution, as it's defeating our goal to have a easy way to link the queries spotted by the monitoring tool back to the code that is producing them.