How to get column names from Hibernate query result in Java?

Question

I'm writing a Java app that uses Hibernate to get data, in my app I have an input text area that takes an user typed-in sql command string and run that through Hibernate to get whatever data the user queries, so I don't know beforehand what the result table might look like and therefore don't know the column names, but I do need to display user query result in a table with column names relate to the data fields, how to achieve that in Hibernate ? I've tried the following code :

  Session session=HibernateUtil.getSession();
  session.beginTransaction();
  Query q=session.createQuery(hql);

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

  for (Map<String,Object> map : aliasToValueMapList)
    for (Map.Entry<String,Object> entry : map.entrySet()) System.out.println(entry.getKey()+" - "+entry.getValue());

It gave me the following error message : Exception in thread "AWT-EventQueue-0" java.lang.ClassCastException: sakila.entity.Actor cannot be cast to java.util.Map

It's pointing to the 1st for loop, since I'm new to Hibernate, don't know if it's doable in it, how to fix the above code ? Could someone show me some sample code that works in my case ?

Edit : As Marcel Stör mentioned below, I need to be able to allow both situations to happen and not limit users' ability to query any data, what's the best way to do it ?

score 1 · Accepted Answer · answered Jan 12 '13 at 20:25

I don't quite understand but still dare to answer...

If you use HQL like this Query q=session.createQuery(hql); suggests you get back objects and not individual fields by default. This means you're out of luck anyway trying to map back the results to the query BUT you can simply use the object's field names as column names.

If what you get from the text area is plain SQL though, then you have to use session.createSQLQuery(sql). What you get back is a list of object arrays. However, here too you only get the data. You'd have to prohibit your users to use select * queries. Then you can use the name of the field/column in query as your output column names.

score 1 · Answer 2 · answered May 21 '13 at 08:53

I've got the same kind of problem for HQL query like

select new Map(id as id, name as name) from Person

that I use as view DTO

With one or more records I can iterate through the map that is element of List<Map<String, Object>. The problem only when I need to manage situation with empty dataset. For that case something like

public List<String> getAliases(String queryString) {
   ArrayList<String> list = 
       new ArrayList<String>(Arrays.asList(queryString.split("as ")));
   List<String> result = new ArrayList<String>();
   list.remove(0);
   for (String str : list) {
        StringTokenizer st = new StringTokenizer(str, ",) ");
        result.add(st.nextToken());
    }
    return result;
}

score 0 · Answer 3 · edited May 23 '17 at 10:33

0

Does the user type in the SQL or HQL query? There's a big difference between those.

If the user types in HQL query, you can call hqlQuery.getReturnTypes(), and then for each type you can do whatever suggested in this post to find out the table metadata.

edited May 23 '17 at 10:33

Community

1
1

answered Jan 13 '13 at 00:40

mindas

26,463
15
97
154

How to get column names from Hibernate query result in Java?

3 Answers3