Mapping Query Results To Non Managed Objects Using JPA

December 30, 2009

I’ve used Hibernate in the past with EJB3 annotations. Frequently I needed to map query results that didn’t correspond to Entity objects to value objects that could then be passed to the business or view tier. This is fairly trivial using the Hibernate Query API’s:


 public List<CustomerCityInfo> getCustomerCityInfo()
 {
     Query query = this.sessionFactory.getCurrentSession().createQuery("SELECT c.name, c.address.city from Customer c");
     query.setResultTransformer(Transformers.aliasToBean(CustomerCityInfo.class));
     return query.list();
 }

I ran into this need again when I started using JPA with the Hibernate JPA provider. I didn’t want to write non-portable code so I didn’t want to have to access the underlying Hibernate Session object.

My first naive attempt looked something like this:


 public List<CustomerCityInfo> getCustomerCityInfo()
 {
     Query query = entityManager.createQuery("SELECT c.name, c.address.city from Customer c");
     List<Object[]> resultList = query.getResultList();
     List<CustomerCityInfo> customerList = new ArrayList<CustomerCityInfo>();
     for (Object[] obj : resultList)
     {
         CustomerCityInfo customer = new CustomerCityInfo();
         customer.setName(obj[0]);
         customer.setCity(obj[1]);
         customerList.add(customer);
     }

     return customerList;
 }

Yuck!

I had some spare cycles recently so I did some google searching and discovered the “SELECT NEW” syntax that JPQL supports. This allows you to do something like this instead of the monstrosity above:


 public List<CustomerCityInfo> getCustomerCityInfo()
 {
     Query query = entityManager.createQuery("SELECT NEW com.mycompany.model.CustomerCityInfo(c.name, c.address.city) from Customer c");
     return query.getResultList();
 }

Much cleaner syntax. Much easier to maintain.

Cheers!

Advertisements

6 Responses to “Mapping Query Results To Non Managed Objects Using JPA”

  1. Juan Says:

    Very useful, not sure where you found it, but i’ve been looking for this for a couple days now and this is exactly what I was looking for.

    Thanks por the post

  2. mehdi Says:

    Thanks for your tutorial. There are some issues with this way. Developers should care about the constructors and any change may cause problems, in hibernate you have a method in query.setResultTransformer(Transformers.aliasToBean(RefClass))
    and you can use ‘as’ operator in your query.Then you have the object directly. Is there any way to use this method in JPA?

    Also I tried to cast query to HIbernateQuery, but I used spring, so the returned class is a proxy class and can’t be cast.

  3. heuristicexception Says:

    Yes, this can cause problems if you change your constructors. However, if you have functional tests for your DAO’s (which you should) then these changes will cause build breaks which can help you resolve the breaks.

    As to the rest of the comments: JPA is NOT Hibernate and attempting to cast your JPA objects into the expected Hibernate implementations is a really bad idea if you expect your code to be portable.

  4. Lakshmi Hari Says:

    Thank you boss ! this is awesome.
    this is really weird way of working in JPA.
    I was also looking for this feature since many days, finally made it work.

  5. test Says:

    The answer is simple:
    use this
    TypedQuery createQuery(String qlString, Class resultClass);

    Like this
    Query query = entityManager.createQuery(“SELECT c.name, c.address.city from Customer c”,CustomerCityInfo.Class);

    #auleev

    • heuristicexception Says:

      This method you reference was added in JPA 2.0. This post was written in 2009…long before JPA 2.0 came out.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: