@glassfishrobot Commented
Reported by @ldemichiel

@glassfishrobot Commented
This issue was imported from java.net JIRA JPA_SPEC-9

• Issue Imported From: https://github.com/javaee/jpa-spec/issues/9
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @ldemichiel

Even though this is issue #9, from 2011, it's actually been quite near the top of my priority list for quite a long time.

We absolutely need an API-based way to define SQL result set mappings!

How about something like this:

Result.Entity<Order> orderResultMapping = 
        Result.entity(Order.class, 
                Result.field(Order_.id "order_id"),
                Result.field(Order_.quantity, "order_quantity"),
                Result.field(Order_.item, "order_item")
       );
Query<Order> query = entityManager.createNativeQuery("...",  orderResultMapping);

The class Result would have interfaces Entity<T>, Constructor<T>, Field<T>, and Column<T> with the same members as @EntityResult, @ConstructorResult, @FieldResult, and @ColumnResult. And it would have matching constructor methods entity(), constructor(), field(), and column().

There would also be:

• a super interface Result.Typed<T> extended by these inner interfaces,
• a Result.Compound extending Result.Typed<Object[]>, and perhaps even
• a Result.Tuple extending Result.Typed<Tuple>.

Now, with use of static imports, the above code would simplify to:

Entity<Order> orderResultMapping = 
        entity(Order.class, 
                field(Order_.id "order_id"),
                field(Order_.quantity, "order_quantity"),
                field(Order_.item, "order_item")
       );
Query<Order> query = entityManager.createNativeQuery("...", orderResultMapping);

Hell, we could even in principle have Result.Entity<T> implement the annotation type EntityResult, Result.Field<T> implement the annotation type FieldResult, etc, which would mean that implementations would have almost no work to do.

On Twitter the idea of using string templates was suggested by Gunnar Morling.

I believe it would be possible to make something like this work:

NativeQuery query = 
        NATIVE."select o.id as \{Order_.id}, o.quantity as \{Order_.quantity}, o.item as \{Order_.item} from orders o";
Query<Order> query = entityManager.createNativeQuery(query);

Of course, something quite a bit more complicated would be needed to handle multiple instances of the same entity in the result set, embeddables, etc. And ultimately this would probably end up needing some sort of "dynamic" result set mapping API.

Now, I know that looks like a completely different feature to the one proposed in the issue description, but my intuition is that this is actually a better solution to the most common use cases for a result set mapping API.

Anyway, the point is that we should keep this application in mind in the design of a result set mapping API.

See #691.