Reusing fields for JPA entities in native query

[alrem mashayekhi]

We use JPA in our project but there are certain complex queries that need to be done straightforward in native query. We make use JPA native queries for this, like the one below

Query query = em.createNativeQuery(sql.toString(), "SomeEntityQuery");

then we created an entity called SomeEntity which has a code like the one below

@SqlResultSetMapping(
        name="SomeEntityQuery",
        entities={
            @EntityResult(
                entityClass=SomeEntity.class,
                fields={
                    @FieldResult(name="fieldOne", column="FieldOne"),
                    @FieldResult(name="fieldTwo", column="FieldTwo"),
                    @FieldResult(name="fieldThree", column="FieldThree")
                }
            )
        }
    )


@Entity
public class SomeEntity{

//fields listed below

My question is, what if we have a dao method that has the following methods that we translated to native queries

methodA()

which returns

SomeEntityObject

but only fills up fieldOne, fieldTwo, fieldThree

methodB()

which returns

SomeEntityObject

but only fills up fieldFour, fieldFive, fieldSix

we noticed that when creating

@SqlResultSetMapping

, all fields need to be defined or else an exception would be thrown if not.

What if the dao methods (which has native queries) stretches up to methodE() and we don't want to have a different Entity return for every dao method? we would want to use the same SomeEntity object for all.

Is there a workaround for my dilemma?

By the way, the sql statement for the above example is:

SELECT a.someColumn as FieldOne , b.someColumn as FieldTwo , c.someColumn as FieldThree --etc..etc..

[Roel De Nijs]

alrem mashayekhi wrote:What if the dao methods (which has native queries) stretches up to methodE() and we don't want to have a different Entity return for every dao method? we would want to use the same SomeEntity object for all.

Is there a workaround for my dilemma?

When I have some doubts/questions about JPA, I always have a look at the (excellent) Java Persistence WikiBook. I think the Native SQL Queries section might be very useful for you.

It seems you could try using the @SqlResultSetMapping annotation together with the @ConstructorResult annotation as explained in this article. I have never tried (used) it myself, so I have no idea if it will solve your problem. As a last resort you could always create a few helper methods to do a manual mapping of a list of Object[] elements to a list of SomeEntity objects with the appropriate fields being set.

Hope it helps!
Kind regards,
Roel

[Tim Holloway]

I have an app that does stuff like that (actually, there's very little that JPA can do that it hasn't ended up doing sooner or later).

One solution is not to assign an Entity class to your SqlResultSetMapping, let it return a list of lists of Objects (one per column), instantiate your own mini-entity instances transferring values from the list.

I would be cautious using a one-size-fits-all return entity, though, since parts of it would only be valid in certain contexts and you'd have to keep that in mind. Plus, dead fields take up memory space when returning a result list.