Does JPA/EntityManager provide a mechanism similar to Spring's RowMapper

Question

I am working on standardizing a legacy code base and new development to both use EntityManager for persistence. In order to succeed however, I need to provide the ability to break out into native SQL and process results manually. JPA EntityManager almost provides this functionality through:

em.createNativeQuery("select ... from my_table where ...");

But what I am missing is the ability to do Spring style RowMappers. I can see that there are a few mechanisms that are close. I can of-course map entities using annotations, xml and so on but I need to put code into the processing of each row in the result set so this is not an option. I can see that I can pass in SQLResultSetMapping, but as best as I can understand, this supports again only meta data mapping. Ideally what I need is the following:

em.createNativeQuery("select ... from my_table where ...",  
  new RowMapper {  
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
         MyObject o = new MyObject();  
         o.setMyCustomProperty( rs.get...() );  
         //...  
         return b;  
    }  
  }  
);

Does the equivalent to the above exist? Can I construct a SQLResultSetMapping which takes full control of the mapping like the above.

If not, can I at least get the data source underlying the EM in a standard way so that I can wrap it using JdbcTemplate and not have two paths to configure data-sources?

DataSource ds = em.getDataSource();  
JdbcTemplate t = new JdbcTemplate(ds);

Thanks for your help.

Yes, thank you for that. I was trying to avoid that in order to avoid the overhead of looping over the result set twice. I assume JPA stack iterates over the result set to build the List — Shahin Askari, May 06 '15 at 21:52

score 2 · Answer 1 · answered May 07 '15 at 02:03

One way to achieve this is to go through Hibernate session (provided that you are using Hibernate) and use its https://docs.jboss.org/hibernate/core/3.3/api/org/hibernate/transform/ResultTransformer.html. The code should looks like below and you shouldn't need to iterate through the list twice:

List<MyDto> result = entityManager.unwrap(org.hibernate.Session.class)
        .createSQLQuery("select ... from my_table where ...")
        .setParameter("my_param", "my_param_value")
        .setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(MyDto.class))
        .list();

Hope this helps.

score 2 · Answer 2 · answered Mar 20 '16 at 01:40

JPA native query result can be mapped to the DTO POJO class:

DTO POJO class

@lombok.Getter
@lombok.AllArgsConstructor
public class MyDto {
    private String x;
    private Long y;
}

Repository bean:

@Repository
public class MyRepository {

    @PersistenceContext private EntityManager em;

    static final String MY_SQLMAP = "My-SQL-Mapping";

    public List<MyDto> findMy() {
        Query query = em.createNativeQuery("select x, y from my_table", MY_SQLMAP);
        return query.getResultList();
    }

    @SqlResultSetMapping(name= MY_SQLMAP, classes = {
        @ConstructorResult(targetClass = MyDto.class,
            columns = {
                @ColumnResult(name="x",type = String.class),
                @ColumnResult(name="y",type = Long.class)
            }
        )
    }) @Entity class MyCfgEntity{@Id int id;} // <- walkaround

}

score 0 · Answer 3 · answered Sep 05 '16 at 01:12

Another way of doing it, if you don't wan't to have that workaround entity, is to put it on top of any valid entity such as

@Entity
@SqlResult(...)

However you would be adding a mapping that is not really relevant to your entity. Unfortunately JPA (as JPA 2.1) won't process the SqlResult annotation if is not on an entity. Another option is to add it to an XML ORM mapping file, something like this.

<?xml version="1.0" encoding="UTF-8" ?>

<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="
                 http://xmlns.jcp.org/xml/ns/persistence/orm
                 http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd" version="2.1">

    <sql-result-set-mapping name="MySummaryResultMapping">
        <constructor-result target-class="model.MySummaryResult">
            <column name="x" class="java.lang.String" />
            <column name="average" class="java.lang.Double"/>
            <column name="foo" class="java.lang.Double"/>
            <column name="bar" class="java.lang.Integer"/>
        </constructor-result>
    </sql-result-set-mapping>
</entity-mappings>

Then in your spring context when setting up your entity manager you can load that mapping XML

<bean id="myEntityManagerFactory"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="myDataSource"/>
    <property name="packagesToScan" value="model"/>
    <property name="mappingResources" value="META-INF/persistenceMapping.xml" />
    ...
</bean>

This way you can do something like this and use a POJO to return the query results.

@Component
public class MyReportDAO {

@PersistenceContext
private EntityManager entityManager;

public MySummaryResult calculateReportFoo(String foo, LocalDate startDay, LocalDate endDay) {
    Query query = entityManager.createNativeQuery("SELECT x,  AVG(bar_column)  AS average, SUM(foo) AS foo," +
            " bar " +
            " FROM My_table WHERE FOO= :foo AND bar_DATE BETWEEN :startDay AND :endDay" +
            " GROUP BY x,bar", "MySummaryResultMapping");
    query.setParameter("foo", foo);
    query.setParameter("startDay", startDay);
    query.setParameter("endDay", endDay);
    return (MySummaryResult) query.getSingleResult();
  }
}

score 0 · Answer 4 · answered Sep 27 '22 at 15:27

I have used my DTO in our dao layer. It is not a clean way but was good for our performance. I did not want to use Tuple and have the mapping two times so i ended up using this to get the dto directly:

return entityManager.createQuery("SELECT new com.xxx.dto.tb.account.response.TypesResDto(x.id, f.id, f.xeroGroup, x.accountTypes, x.detailType, x.type, m.gifiCode, m.leadSheetId) " +
                "FROM XeroAccountMappingEntity x " +
                "JOIN x.finGroupEntity f " +
                "JOIN x.mappingSheetEntity m " +
                "WHERE x.isDeleted = FALSE AND (x.isAddable = TRUE OR x.isEditable = TRUE)", TypesResDto.class).getResultList();

But I came here to find a way to use row mappers with jpa. I do notlike to define them as annotation on the entity.

score 0 · Answer 5 · answered Mar 20 '23 at 08:02

I am not sure but if you really want to use rowmappers and are in a pragmatic mood wouldn't this be a possible solution ?

    protected JdbcTemplate getJdbcTemplate() {
      EntityManagerFactoryInfo info = (EntityManagerFactoryInfo) em.getEntityManagerFactory();
      DataSource dataSource = info.getDataSource();
      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      return jdbcTemplate;
    }

Now you have a simple jdbcTemplate to work with. Either way what you do it is not the most clean thing to do.

Does JPA/EntityManager provide a mechanism similar to Spring's RowMapper

5 Answers5