With Hibernate, how can I query a table and return

Question

I've got this table :

table name : Account
Fields : id (varchar), name(varchar), other fields...

I want to query this table with hibernate mechanism (to use the second cache level). The result of the hibernate query must be a hash map where the key is the field id and where the value is the field name.

How can I write it with HQL ?

If I use map, I can only use alias and if I use a constructor with an object, I must transform result to hashmap which is time consuming.

Example : 
Id | name | other fields
1 Jerome ...
2 Steve ...
3 Nick ...

the result of the query must be a hashmap :

1>Jerome
2>Steve
3>Nick

thanks

Answer 1

I think the closest you can get is to use this query:

select id, name from Account

which'll give you a result set of two length arrays. You'll have to build the map manually, like so:

for(Object[] row : rs) {
    map.put(row[0], row[1]);
}

Note that this will largely ignore the second level cache and get translated into a SQL query.

Answer 2

Default entity mode of Hibernate is EntityMode.POJO.

you can use EntityMode.MAP entity mode for retrieving the query output in Map format.

Answer 3

This question is old but this might still help other people. You can now use HQL to return maps with hibernate. Use something like this:

select new map( max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n ) from Cat cat

From hibernate docs: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-select

Answer 4

intead of using default entity name you can applied the entity-name to hbm.xml file.

for Example

using this, hibernate give the key/value pair that means return the map.

Answer 5

Hiii...

Below code might help you.

    Query query = session.createQuery("select id, name from table");
    List results = query.list();

To display the results as objects does work:

    int i;
    int j;
    Object object = null;
    for (i = 0; i < results.size(); i++) {

        System.out.println("-------");
        Object[] obj = (Object[]) results.get(i);
        for (j=0;j<obj.length;j++)
        {
            System.out.println(obj[j]);
        }
        System.out.println("-------");

    }

Edit : You can use that results objects as map and you'r done.

Answer 6

Assuming for the moment that Account doesn't have any non-lazy associations that would be loaded, the following is just the best you're going to get, performance-wise:

List<Account> accounts = (List) session.createQuery("from Account").list();
for (Account account : accounts) map.put(account.getID(), account.getName());

This may be "time consuming" but it's not like Hibernate can somehow magically avoid the step of putting each returned row into a map.

Unlike the other answer, this should benefit from the second-level cache.