Native query projection with records fail with `ConverterNotFoundException`

[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?

[mp911de]

For records, that map cleanly to the given query, it is possible to make these work by providing the target type to EntityManager.createNativeQuery(…) even without SqlResultSetMapping. The mapping is only necessary when the target type uses different property names.