How to transform a flat result set using Hibernate

Question

Is it possible to map result of SQL to not flat object?

List<Customer> customers = hibernateSession().createCriteria(CustomerDetailsView.class)
                .add(Restrictions.in("userName", userName))
                .setProjection(buildProjection())
                .setResultTransformer(Transformers.aliasToBean(Customer.class))
                .list();

In my case CustomerDetailsView has flat structure. But I need to map it to object like this:

public class Customer {
    private String userName;
    private String title;
    private String firstName;
    private String lastName;
    private String type;
    private String companyName;
    private AddressDetails addressDetails;
}

and

public class AddressDetails {
    private String countryCode;
    private String addressLine1;
    private String zipOrPostCode;
    private String city;
    private String countryDivisionName;
    private String countryDivisionCode;
    private String countryDivisionTypeCode;
    private String residentialAddress;
}

v.ladynev · Accepted Answer · 2017-11-14T12:26:49.910

Yes it is possible. You can use a custom transformer for it: FluentHibernateResultTransformer.

You can copy paste code, or add the jar by Maven: fluent-hibernate-core.

You need to use Criteria with Projections. Please, don't forget to specify projection aliases (userName, addressDetails.countryCode)

Criteria criteria = session.createCriteria(Customer.class);
criteria.createAlias("addressDetails", "addressDetails", JoinType.LEFT_OUTER_JOIN);

criteria.setProjection(Projections.projectionList()
        .add(Projections.property("userName").as("userName"))
        .add(Projections.property("addressDetails.countryCode")
        .as("addressDetails.countryCode")));

List<Customer> customers = criteria.setResultTransformer(
        new FluentHibernateResultTransformer(Customer.class)).list();

Using with HQL

It is impossible to use it with HQL, because of Hibernate doesn't allow nested aliases in HQL

select addressDetails.countryCode as addressDetails.countryCode

It will be an error with the addressDetails.countryCode alias.

Using with a native SQL

The transformer can be used for a native SQL with the nested projections (opposite HQL). It is need to use the aliases with the quotes in this case:

String sql = "select c.f_user_name as userName, d.f_country_code as \"addressDetails.countryCode\" "
        + "from customers c left outer join address_details d on c.fk_details = d.f_pid";

List<Customer> customers = session.createSQLQuery(sql)
        .setResultTransformer(new FluentHibernateResultTransformer(Customer.class))
        .list();

Awesome, but this is not working for stuff like when you have a Set , object Child extends Parent, and you want just specific field of Child, it will throw an exception that parent does not have such attributes, it does not deal with inheritance. Any solution to this? — hocikto, Jan 25 '18 at 09:56
@hocikto It should work. Probably your set has a `Parent` not `Child`. If that is not it, you can add an issue with an example https://github.com/v-ladynev/fluent-hibernate/issues — v.ladynev, Jan 26 '18 at 14:26
@v.ladynev I found the problem was that, the fluent is trying to find a setter for a field that returns Set. In your class NestedSetter you are calling somewhere.in the code class.getSuperClass(), which is looking for Set's super class not 'Somethings' super class. I managed to get the object type from Set with generics but it was not useful at all in the end because of other problems that I faced in my app. — hocikto, Jan 29 '18 at 14:47

How to transform a flat result set using Hibernate

1 Answers1

Linked