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 stackoverflow.com/questions/2605385/… and see if it fills your need — user3487063
– user3487063, Commented Aug 4, 2014 at 19:11
Want to place your comment into an answer so I can accept it? :) — Ascalonian
– Ascalonian, Commented Aug 5, 2014 at 11:37

Community · Accepted Answer · 2017-05-23 12:31:59Z

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, 2017 at 12:31

CommunityBot

11 silver badge

answered Aug 5, 2014 at 13:19

user3487063

3,7121 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robs Over a year ago

If I just want the name without put in the map?

Anthony_Michael · Accepted Answer · 2014-08-04 19:11:18Z

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 4, 2014 at 19:11

Anthony_Michael

424 bronze badges

Comments

Maarten Winkels · Accepted Answer · 2014-08-04 21:27:44Z

1

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.

answered Aug 4, 2014 at 21:27

Maarten Winkels

2,41716 silver badges16 bronze badges

Comments

Xaltotun · Accepted Answer · 2018-11-23 13:12:28Z

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, 2018 at 13:12

Xaltotun

2842 silver badges7 bronze badges

Collectives™ on Stack Overflow

Get dynamic SQL column names from Hibernate

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related