how to return Map<Key, Value> 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

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);