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

[g-apparence]

Any news on this?

I'm considering quarkus for a new project but without native query I'm not sure anymore.

[geoand]

One can use native queries with our Panache layer without much hassle. It's the Spring Data JPA layer that doesn't have support for them currently

[g-apparence]

Oh, that would be perfect.
Thank you answering so fast. Is there any example / documentation on this? Didn't found any.

[geoand]

There isn't anything in the documentation that I am aware of, but you could certainly do something like this:

@ApplicationScoped
public class FruitRepository implements PanacheRepository<Fruit> {

    private final EntityManager entityManager;

    public FruitRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
    
    public List<Fruit> nativeQuery() {
        return entityManager.createNativeQuery("select * from fruits where name = 'test'", Fruit.class).getResultList();
    }
}

[g-apparence]

Ok, I'm not blinded then.
Seems logic if effectively I have access to the EntityManager.
Thank you, will make a small POC using this.

[HugoVinhal98]

Any updates on the JPA layered to allow the usage of native queries? Currently migrating a project to Quarkus which has a some native querys so would be very usefull 😅

[abnayak]

There isn't anything in the documentation that I am aware of, but you could certainly do something like this:

@ApplicationScoped
public class FruitRepository implements PanacheRepository<Fruit> {

    private final EntityManager entityManager;

    public FruitRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
    
    public List<Fruit> nativeQuery() {
        return entityManager.createNativeQuery("select * from fruits where name = 'test'", Fruit.class).getResultList();
    }
}

@geoand

There are two things we are missing here:

1. How to pass parameters to the sql query: example name=$1, set the value of $1 from parameter
2. Columns return from the query should exactly match the colums in Fruit.class, what if sql query only returns the mandatory columns (above example will fail in this case).

[geoand]

I would suggest reading up on something like https://thorben-janssen.com/jpa-native-queries/

[murphye]

+1 on this. Very useful for migrating Spring apps. Here is an example for PostGIS:
https://github.com/murphye/spring-boot-postgis/blob/main/src/main/java/com/hin/spatial/postgis/repo/CityRepository.java#L15

[murphye]

Just as FYI for anyone considering using createNativeQuery for Hibernate Reactive, this is how you can add a method to a PanacheRepository to run native queries. Sorry, it's in Kotlin and not Java ;-)

    fun nativeQuery(query: String) = getSession().onItem()
        .transformToUni { session -> session.createNativeQuery<Customer>(query).resultList }!!

You need to call PanacheRepository's getSession() which returns Uni<Mutiny.Session>

For Hibernate Reactive, the entityManager is NOT available to do entityManager.createNativeQuery.

[stefvanderweldevolksbanknl]

Any update on this? I need to do a Postgres upsert. @Query(nativeQuery=true) would be nice.

[geoand]

Due to me working on some priority things, this has been moved back in the queue.

[geoand]

If anyone however wants to take a swing at this, I can certainly point them in the right direction