Native query projection with record

[ah1508]

If the @Query is native, the results can only be projected with an interface.

Unlike jpa queries, there is not room for select new MyProjection(... . where MyProjection would be a record

Any chance to see support for record for native queries ?

Something like:

public interface Persons extends JpaRepository<Person, Long> {

    record PersonExcerpt(long id, String firstname, String lastname) {}

    @Query(nativeQuery = true, value = "select id, firstname, lastname from fts_persons(?)")
    List<PersonExcerpt> find(String name);
}

interface based projection are a little bit less expressive and cannot be used in Thymeleaf template engine, which does not like proxies.

Don't you think that

Session session = entityManager.unwrap(Session.class);
var mapper = DataClassRowMapper.newInstance(PersonExcerpt.class /*dynamically guessed*/);

var results = session.doReturningWork(connection -> {
    // get ResultSet from query and parameters, 
    // iterate on ResultSet and use mapper for each row to get the instances of the the record
    // return the list of instances
};

should work ?

[schauder]

This should work if you define the proper @SqlResultMapping. Unfortunately it doesn't as I discovered when I tried to produce an example.

Reproducer can be found here: https://github.com/schauder/issue-jpa-2757-sqlresultmapping

I guess the problem is that we ask for a Tuple but for native queries we should check if a result mapping is available and if so request the proper type directly.

[ah1508]

It would be even better if it could work without @SqlResultMapping, like with spring-data-jdbc ? @SqlResultMapping usage is very verbose.

How about using entityManager.createNativeQuery if @SqlResultMapping exists, entityManager.unwrap(Session.class).doReturningWork otherwise ?

[schauder]

While certainly possible, I'd vote against it in the general case. In Spring Data JPA, JPA does the mapping. If Spring Data starts to create DTOs you'll end up with a mixture of JPA mapping and Spring Data mapping, which is going to cause a lot of trouble.

One variant we could consider I guess, if we could create a @SqlResultMapping programmatically, based the target class. But I'm not sure if the supported JPA implementations even offer an API for this. In any case I would consider that a separate issue and leave this one for fixing, what really should already work.

[hpoettker]

As far as I understand, the mapping of projections (from e.g. native query results) to interface proxies is not a JPA feature but a Spring Data feature. And it's the ConversionService and not JPA that does the mapping here already.

The way I understand the issue is that it would feel more natural in today's Java if I could use records for the results of projections instead of interface proxies. Where the interface definitions are scanned for properties according to bean naming conventions of accessors, I could now consider the RecordComponents as the source of the mapping information.

Are there plans to add something like a RecordProjectionFactory as an alternative to the currently existing ProxyProjectionFactory?