Quarkus: SpringJPA: @Query(nativeQuery) support

[jesperpedersen]

Description
The current spring-data-jpa module (1.0.0.CR1) doesn't support the 'nativeQuery' attribute of org.springframework.data.jpa.repository.Query

@geoand WDYT ?

[geoand]

That is indeed true.

We don't have anything similar in Panache so I wanted to wait and see what we would do on that front before doing anything similar for Spring Data JPA.
@emmanuelbernard is there any "native" jdbc support on the radar?
What is your take on this?

[jesperpedersen]

@geoand Thanks

Example: https://github.com/My-Wine-Cellar/winecellar-webapp/blob/master/src/main/java/com/cellar/wine/repositories/CountryRepository.java

[geoand]

Yeah, that is undeniably useful!

If we add native query support to Panache, then transfering it to the Spring Data JPA module should be easy.

[jesperpedersen]

Question is if it can be transformed into a @NamedNativeQuery (https://docs.oracle.com/javaee/7/api/javax/persistence/NamedNativeQuery.html)

[geoand]

Sure it could. There would probably be a few more things involved, but I don't anticipate there being many problems.

[Sanne]

@NamedNativeQuery should work as Hibernate ORM supports it; were you expecting Panache to add something to it?

[geoand]

I didn't really want to add things to Spring Data JPA that Panache doesn't support.
That said I see no good rule how to do this (since one could argue that the Spring Data JPA support already does have some things that Panache doesn't), so I'm obviously open to suggestions here :)

[Sanne]

I didn't really want to add things to Spring Data JPA that Panache doesn't support.

That's wise but Panache is building _additional_ helpers on top of Hibernate ORM. I don't expect it would need to also re-export features which ORM already provides?

Spring Data JPA could definitely use any feature that ORM has, and combine them with the additional stuff Panache adds.

That said, what's the semantics of spring.datasource.data=*scripts* ? Is the order of execution of such scripts undefined?

If so, should be easy to support this:

• load & converting them into a single script at build
• include it into the built app as a single resource
• push it into the ORM configuration for importing at the right time

Careful to not conflict with an explicitly configured import.sql on the ORM layer. They should either be merged, or don't rely on the import.sql facility of ORM and just import them yourself directly on boot by opening a connection directly.

however

while it's easy to implement, I'm not persuaded that we should. I guess it depends on the semantics and me not knowing what makes spring.datasource.data useful, but why not tell the user to simply use the existing import facility?

Only downside I see is that it accepts only a single script; which IMO is a sane constraint.

[Sanne]

Existing similar feature:

• https://quarkus.io/guides/hibernate-orm#quarkus-hibernate-orm_quarkus.hibernate-orm.sql-load-script

[geoand]

That said, what's the semantics of spring.datasource.data=*scripts* ? Is the order of execution of such scripts undefined?

If so, should be easy to support this:

• load & converting them into a single script at build
• include it into the built app as a single resource
• push it into the ORM configuration for importing at the right time

Careful to not conflict with an explicitly configured import.sql on the ORM layer. They should either be merged, or don't rely on the import.sql facility of ORM and just import them yourself directly on boot by opening a connection directly.

however

while it's easy to implement, I'm not persuaded that we should. I guess it depends on the semantics and me not knowing what makes spring.datasource.data useful, but why not tell the user to simply use the existing import facility?

Only downside I see is that it accepts only a single script; which IMO is a sane constraint.

Another thing to take into account here is that for the Spring compatiblity we haven't so far utilized any Spring configuration - it's all Quarkus specific.
We could support Spring native configuration, but we would need to be careful. If we do that, then the next step might have to be to support Spring's Java configuration DSLs which could be a real trap....

[Sanne]

Agreed, I don't think supporting all details of Spring is a wise goal, especially with configuration there's things which conceptually can't be mapped.

The goal of the "spring like" APIs should be to make it easy and familiar for people with a Spring background, no more.

So, up to you... maybe I'm missing something about the flexibility of such scripts; if so let's improve our flexibility as well, then explain to users how this configuration property is different in some migration guide.

[geoand]

Good idea!

@aureamunoz do you want to perhaps look into spring.datasource.data based on what @Sanne mentions above?

[aureamunoz]

Yes! sure!! @geoand

[geoand]

Thanks!

[jesperpedersen]

@geoand @aureamunoz Raised [#5371] for spring.datasource.data as it is separate from this.

[geoand]

Thanks @jesperpedersen!

[fluoxa]

Hello Guys. What is the status of this feature request. Are there any plans to implement nativequery support? regards

[binoysankar]

Hello Team,

Is there any update on the nativeQuery support. I am migrating springboot to quarkus and got stuck with native query. I am using hibernate spatial queries for postgres posgis extension. I was using a query template as below.

Currently there are 2 issues
1) I need to write "SELECT s FROM" not any specific fields like "SELECT s.name, s.title FROM"
2) nativeQuery support

@Query(value = "SELECT s.id, ST_DISTANCE(geom::geography, ST_SETSRID(ST_MAKEPOINT(), 4326)::geography) AS distance
FROM table_name s
WHERE geom && ST_MakeEnvelope(48.23666, 4326) ORDER BY distance ASC LIMIT 100", nativeQuery = true)

[geoand]

We haven't got around to this unfortunately