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?

Please refer to http://stackoverflow.com/questions/2605385/using-sql-column-names-in-hibernate-createsqlquery-result and see if it fills your need — user3487063, Aug 04 '14 at 19:11
Want to place your comment into an answer so I can accept it? :) — Ascalonian, Aug 05 '14 at 11:37

score 14 · Accepted Answer · edited May 23 '17 at 12:31

14

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.

edited May 23 '17 at 12:31

Community

1
1

answered Aug 05 '14 at 13:19

user3487063

3,672
1
17
24

If I just want the name without put in the map? – Robs Dec 23 '22 at 14:53

score 1 · Answer 2 · answered Aug 04 '14 at 19:11

1

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

answered Aug 04 '14 at 19:11

Anthony_Michael

42
4

score 1 · Answer 3 · answered Aug 04 '14 at 21:27

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.

score 1 · Answer 4 · answered Nov 23 '18 at 13:12

1

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.

answered Nov 23 '18 at 13:12

Xaltotun

264
2
7

Get dynamic SQL column names from Hibernate

4 Answers4