Get dynamic SQL column names from Hibernate

Question

I have an Oracle table that has a CLOB in it. Inside this CLOB can be a SQL statement. This can be changed at any time.

I am currently trying to dynamically run these SQL statements and return the column names and data back. This is to be used to dynamically create a table on the web page.

Using Hibernate, I create the query and get the data like so:

List<Object[]> queryResults = null;
SQLQuery q = session.createSQLQuery(sqlText);
queryResults = q.list();

This gets the data I need, but not the column names. I have tried using the getReturnAliases() method, but it throws an error that the "java.lang.UnsupportedOperationException: SQL queries do not currently support returning aliases"

So my question is: Is there a way through Hibernate to get these values dynamically?

Answer 1

You can use :

q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List<Map<String,Object>> aliasToValueMapList=query.list();

to get column names in createSQLQuery.

For more details please refer to this question.

Answer 2

You can use the addScalar method to define the columns.

Look at 16.1.1 https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/querysql.html

Answer 3

You could implement a ResultTransformer ( http://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/transform/ResultTransformer.html ) and set it on the native query. I think with a native SQL query you get the aliases as specified in the SQL as alias parameter in the callback method.

Answer 4

In 2018 I would suggest using NativeQueryTupleTransformer with native queries.

query.setResultTransformer(new NativeQueryTupleTransformer());

The result format is List<Tuple>. This format is very convenient to work with native SQL queries.