how to return Map with HQL

Question

i have a table

Permission:

• id
• name
• desc

what i am doing right now is to make a query that returns a permission object then put the values in the map programmatically

1- But i was wondering if it's possible to make an HQL (or native sql if not possible) to select the permission_id, permission_name and return them in a map.

2- is it possible to return map in one to many relationship instead of following list or set

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(name = "perm_cat_map", joinColumns = { @JoinColumn(name = "perm_cat_id") }, inverseJoinColumns = { @JoinColumn(name = "permission_id") })
    private List<Permission> permissions = new ArrayList<Permission>(0);

is it possible to have something like:

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
        @JoinTable(name = "perm_cat_map", joinColumns = { @JoinColumn(name = "perm_cat_id") }, inverseJoinColumns = { @JoinColumn(name = "permission_id") })
        private Map<String,String> permissions = new ArrayList<String,String>(0);

where the two strings are permission_id, permission_name.

Answer 1

In JPA 2.0 (which recent versions of Hibernate support), you can map collections of primitives using an @ElementCollection annotation.

For some samples of such mappings see the hibernate collections docs.

If you're not actually mapping it in this way but want to create a map using either HQL or a Criteria query, you can create a ResultTransformer to create a map from the returned result set.

Judging from Xavi's answer, I guess there is also support in HQL for creating a map without using a transformer.

Answer 2

1- But i was wondering if it's possible to make an HQL (or native sql if not possible) to select the permission_id, permission_name and return them in a map.

its posible with Resulttransformer

String queryString="select id, name from Permission ";
List<List<Object>> permission= session.createQuery(queryString)
      .setResultTransformer(Transformers.TO_LIST).list();
//now you just expect two columns 
HashMap<Integer,String> map= new HashMap<Integer,String>();
for(List<Object> x: permission){ 
     map.put((Integer)x.get(0),(String)x.get(1))
}

Answer 3

String sqlQuery="select userId,name,dob from user"

Pass the query to following method.

public List<Map<String,Object>> getDataListBySQL(final String sql, final Long adId){

    List<Map<String,Object>> list=(List<Map<String,Object>>)getHibernateTemplate().executeFind(new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException,SQLException {
            Query query=session.createSQLQuery(sql);
            query.setParameter("adId", adId);               
            return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        }
    }); 
    return list;
}

Iterate this list in this way-
for(int i=0;i<list.size();i++){

        Map<String,Object> map=list.get(i);

        System.out.println(map.get("userId"));
        System.out.println(map.get("name"));
    }

Answer 4

try like this,

Session session = sessionFactory.getCurrentSession();
String HQL_QUERY = "select new map(user.id as id, user.firstName as fullName) from User user";        
List<Map<String,String>> usersList = session.createQuery(HQL_QUERY).list(); 

Answer 5

1. Use the select new map syntax in HQL to fetch the results of each row in a Map. Take a look at the following question, that addresses the issue: How to fetch hibernate query result as associative array of list or hashmap. For instance, the following HQL: select new map(perm.id as pid, perm.name as pname) from Permission perm will return a List of Maps, each one with keys "pid" and "pname".

2. It is not possible to map an association to a Map<String, String>. It is possible to map the key of the Map to a column with the @MapKeyColumn annotation in the association. See this question, that also addresses the issue, for an example: JPA 2.0 Hibernate @OneToMany + @MapKeyJoinColumn. Here is another example.

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = "perm_cat_map", 
    joinColumns = { @JoinColumn(name = "perm_cat_id") }, 
    inverseJoinColumns = { @JoinColumn(name = "permission_id") })
@MapKeyColumn(name="permission_id")
private Map<String, Permission> permissions = new HashMap<String,Permission>(0);