Reading from multiple tables and populating multiple entities using JPA SqlResultSetMapping

Question

I have been building a JSF application using JPA to access the DB. There are a number of entities.

I want to create a search results screen that shows columns from more than one entity in a table. These entities do not have foreign key relationships.

To achieve this I have used a EnttityManager.createNativeQuery and specified an EntityMapping, like so:

 Query q = em.createNativeQuery(
     "select t.id as id1, t.bb as bb1, t.cc as cc1," +
     "t2.id as id2, t2.aa as aa2, t2.bb as bb2 " +
     " from table1 t, table2 t2 where t.cc = '22' and t2.id = 2", "TestMapping");

 result = q.getResultList();

TestMapping looks like this:

 @SqlResultSetMapping(name =
 "TestMapping", entities = {
     @EntityResult(entityClass = Table1.class, fields = {
         @FieldResult(name = "id", column = "id1"),
         @FieldResult(name = "bb", column = "bb1"),
         @FieldResult(name = "cc", column = "cc1")}
         ),
     @EntityResult(entityClass = Table2.class, fields = {
         @FieldResult(name = "id", column = "id2"),
         @FieldResult(name = "aa", column = "aa2"),
         @FieldResult(name = "bb", column = "bb2")}
         )
     } )

This works if I specify all column names from both of the entities in the query, which is fine in this small example but the search results must select from 4 entities, all of which have a large number of columns. If I don't specify all of the columns in the EntityMapping an exception is thrown in the call to getResultList().

My question is this: Is it possible to only specify the columns in the EntityMapping that are selected in the query rather than having to specify all from the entities?

score 0 · Answer 1 · answered Mar 26 '13 at 15:51

Or you can also use the syntax entity.* to specify all columns of 'entity' at once:

Query q = em.createNativeQuery("select t.*, t2.*" + " from table1 t, table2 t2"+
                               "where t.cc = '22' and t2.id = 2", "TestMapping");

And TestMapping:

@SqlResultSetMapping(name = "TestMapping", 
entities = { @EntityResult(entityClass =Table1.class), 
             @EntityResult(entityClass =Table2.class)})

score 0 · Accepted Answer · answered Dec 17 '10 at 20:14

I don't know if this works with all JPA implementations. If you are using Hibernate you can map all of your properties with lazy fetch type:

@Basic(fetch = FetchType.LAZY)
Date dateCreated;

@Column(length=100)
@Basic(fetch = FetchType.LAZY)
String name;

You then need to instrument your classed or else it will ignore the lazy properties:

<target name="instrument" depends="compile">
<taskdef name="instrument" classname="org.hibernate.tool.instrument.InstrumentTask">
    <classpath path="${jar.path}"/>
    <classpath path="${classes.dir}"/>
    <classpath refid="lib.class.path"/>
</taskdef>

<instrument verbose="true">
    <fileset dir="${testclasses.dir}/org/hibernate/auction/model">
        <include name="*.class"/>
    </fileset>
</instrument>

Reading from multiple tables and populating multiple entities using JPA SqlResultSetMapping

2 Answers2