Hibernate: Mapping custom column names in stored procedure named query

Question

I currently have the following named query that wraps around a stored procedure:-

<hibernate-mapping>
    <sql-query name="mySp">
        <return-scalar column="name_first" type="string" />
        <return-scalar column="name_last" type="string" />

        { call some_sp :param }
    </sql-query>
</hibernate-mapping>

The columns name_first and name_last are the exact column names returned by the stored procedure. I created a bean that contains the same column names so that I can map the queried result into that bean.

public class MyBean {
    private String  name_first;
    private String  name_last;  

    ...
}

The Hibernate code that calls the named query and map the result into the bean:-

MyBean myBean = (MyBean) sessionFactory.getCurrentSession()
                        .getNamedQuery("mySp")
                        .setParameter("param", param)
                        .setResultTransformer(Transformers.aliasToBean(MyBean.class))
                        .uniqueResult();

All of these work fine, but instead of relying on the column names from the stored procedure, I want to use my own column names in MyBean, for example:-

public class MyBean {
    private String  firstName; // instead of name_first
    private String  lastName;  // instead of name_last  

    ...
}

How do I map my column names against the stored procedure's columns in my named query above?

Thanks.

UPDATE - I added my final solution below.

Answer 1

You'll need to implement your own ResultTransformer. It's really simple, and you can look at the source of the bundled implementations for inspiration.

http://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/transform/ResultTransformer.html

https://github.com/hibernate/hibernate-core/tree/master/hibernate-core/src/main/java/org/hibernate/transform

Answer 2

Just build your bean manually :

Object[] columns = (Object[]) sessionFactory.getCurrentSession()
                        .getNamedQuery("mySp")
                        .setParameter("param", param)
                        .uniqueResult();
MyBean myBean = new MyBean((String) columns[0], (String) columns[1]);

This has one additional advantage : it allows you to make your MyBean immutable.

Answer 3

Based on @partenon's answer on using a custom ResultTransformer, here's the final solution:-

MyBean myBean = (MyBean) sessionFactory.getCurrentSession()
                    .getNamedQuery("mySp")
                    .setParameter("param", param)
                    .setResultTransformer(new BasicTransformerAdapter() {
                        private static final long   serialVersionUID    = 1L;

                        @Override
                        public Object transformTuple(Object[] tuple, String[] aliases) {
                            String firstName = (String) tuple[0];
                            String lastName = (String) tuple[1];

                            return new MyBean(firstName, lastName);
                        }
                    })
                    .uniqueResult();

Answer 4

For non-managed and non-entity types you still want to use types transformer that do support @Column annotation. Here's how:

Here's entity type:

@Data /* lombok */
public class MyType {
    @Column(name = "field1")
    private String normalFieldName;
    @Column(name = "field2")
    private String normalFieldNameAnother;
}

Here's repository function code:

  // alias mapper that do convert from column manes to field names based on @Column annotation 
    Function<String[], String[]> aliasesTransformer = new Function<String[], String[]>() {
        private final Map<String, Field> fieldAliasToField = Stream.of(FieldUtils.getFieldsWithAnnotation(MyType.class, Column.class))
                .collect(Collectors.toMap(f -> f.getAnnotation(Column.class).name(), Functions.identity()));
        @Override
        public String[] apply(String[] o) {
            return Stream.of(o).map(el -> {
                if (fieldAliasToField.containsKey(el)) {
                    return fieldAliasToField.get(el).getName();
                } else {
                    return el;
                }
            }).toArray(String[]::new);
        }
    };

String sql = "select\n"
            + "    h.field1, "
            + "    s.field2, "
            + "from "
            + "    table1 s, "
            + "    table2 h "
            + "where "
            + "    s.common_key = h.common_key";
    EntityManager em = emf.createEntityManager();
    //noinspection unchecked
    List<MyType> res = (List<MyType>)em.createNativeQuery(sql)
            .unwrap(org.hibernate.query.Query.class)
            .setResultTransformer(new AliasToBeanResultTransformer(MyType.class) {
                @Override
                public Object transformTuple(Object[] tuple, String[] aliases) {
                    return super.transformTuple(tuple, aliasesTransformer.apply(aliases));
                }
            }).list();