Hibernate ResultTransformer unable to cast a single column

Question

I have some very complicated SQL (does some aggregation, some counts based on max value etc) so I want to use SQLQuery rather than Query. I created a very simple Pojo:

public class SqlCount {
    private String name;
    private Double count;
    // getters, setters, constructors etc

Then when I run my SQLQuery, I want hibernate to populate a List for me, so I do this:

Query hQuery = sqlQuery.setResultTransformer(Transformers.aliasToBean(SqlCount.class));

Now I had a problem where depending on what the values are for 'count', Hibernate will variably retrieve it as a Long, Double, BigDecimal or BigInteger. So I use the addScalar function:

sqlQuery.addScalar("count", StandardBasicTypes.DOUBLE);

Now my problem. It seems that if you don't use the addScalar function, Hibernate will populate all of your fields with all of your columns in your SQL result (ie it will try to populate both 'name' and 'count'). However if you use the addScalar function, it only maps the columns that you listed, and all other columns seem to be discarded and the fields are left as null. In this instance, it wouldn't be too bad to just list both "name" and "count", but I have some other scenarios where I need a dozen or so fields - do I really have to list them all?? Is there some way in hibernate to say "map all fields automatically, like you used to, but by the way map this field as a Double"?

Mohamd Ali · Answer 1 · 2017-11-08T10:30:15.977

Is there some way in hibernate to say "map all fields automatically.

No, check the document here, find 16.1.1. Scalar queries section

The most basic SQL query is to get a list of scalars (values).

sess.createSQLQuery("SELECT * FROM CATS").list(); sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

These will return a List of Object arrays (Object[]) with scalar values for each column in the CATS table. Hibernate will use ResultSetMetadata to deduce the actual order and types of the returned scalar values. To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar():

sess.createSQLQuery("SELECT * FROM CATS") .addScalar("ID", Hibernate.LONG) .addScalar("NAME", Hibernate.STRING) .addScalar("BIRTHDATE", Hibernate.DATE)

i use this solution, I hope it will work with you.

with this solution you can populate what you select from the SQL, and return it as Map, and cast the values directly.

since hibernate 5.2 the method setResultTransformer() is deprecated but its work fine to me and works perfect.

if you hate to write extra code addScalar() for each column from the SQL, you can implement ResultTransformer interface and do the casting as you wish.

ex: lets say we have this Query:

/*ORACLE SQL*/

SELECT
  SEQ                 AS "code",
  CARD_SERIAL         AS "cardSerial",
  INV_DATE            AS "date",
  PK.GET_SUM_INV(SEQ) AS "sumSfterDisc"
FROM INVOICE
ORDER BY "code";

note: i use double cote for case-sensitive column alias, check This

after create hibernate session you can create the Query like this:

/*Java*/

List<Map<String, Object>> list = session.createNativeQuery("SELECT\n" +
                    "  SEQ                 AS \"code\",\n" +
                    "  CARD_SERIAL         AS \"cardSerial\",\n" +
                    "  INV_DATE            AS \"date\",\n" +
                    "  PK.GET_SUM_INV(SEQ) AS \"sumSfterDisc\"\n" +
                    "FROM INVOICE\n" +
                    "ORDER BY \"code\"")
                    .setResultTransformer(new Trans())
                    .list();

now the point with Trans Class:

/*Java*/

public class Trans implements ResultTransformer {

    private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US);

    @Override
    public Object transformTuple(Object[] objects, String[] strings) {

        Map<String, Object> map = new LinkedHashMap<>();
        for (int i = 0; i < strings.length; i++) {

            if (objects[i] == null) {
                continue;
            }

            if (objects[i] instanceof BigDecimal) {
                map.put(strings[i], ((BigDecimal) objects[i]).longValue());
            } else if (objects[i] instanceof Timestamp) {
                map.put(strings[i], dateFormat.format(((Timestamp) objects[i])));
            } else {
                map.put(strings[i], objects[i]);
            }
        }
        return map;
    }

    @Override
    public List transformList(List list) {
        return list;
    }
}

here you should override the two method transformTuple and transformList, in transformTuple you have two parameters the Object[] objects its the columns values of the row and String[] strings the names of the columns the hibernate Guaranteed the same order of of the columns as you order it in the query.

now the fun begin, for each row returned from the query the method transformTuple will be invoke, so you can build the row as Map or create new object with fields.

Hibernate ResultTransformer unable to cast a single column

1 Answers1