With Hibernate, How to Query a Table and Return a Hashmap with Key Value Pair Id>Name

With Hibernate, how can I query a table and return a hashmap with key value pair id name?

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

how to return Map Key, Value with HQL

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

How to return name,value pair via Java Map in Hibernate (using mapping file)

<key> element inside <map> refers to the foreign key, so it should be

<map name="properties" table="zoomProperty">
    <key column="parentZoomProperties"/>
    <map-key column="name" type="string"/>
    <element column="value" type="string"/>
</map>

id in zoomProperty table is not needed at all.

how can i return map as jpa query?

You have to make a map list at least, which means list cannot be mapped to single map.

Hibernate: Map Two Columns to a HashMap's Key and Value

I managed to fix it =)

You shouldn't use @OneToMany.

What I used:

public class Object {

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "object_translation",
                 foreignKey = @ForeignKey(value = ConstraintMode.CONSTRAINT, name = "fk_object_translation_object"),
                 joinColumns = @JoinColumn(name = "object_id"))
    @MapKeyColumn(name = "language", nullable = false)
    @MapKeyEnumerated(EnumType.STRING)
    @Column(name = "translation", nullable = false)
    private Map<Language, String> translations;
}

How to return HashMap from JPA query?

It appears that you were trying to execute a query which return types not mapped to any Java entities you have (or if they be present you never mentioned them). In this case, you want to use createNativeQuery(), which will return a List of type Object[].

Try using this version of the method:

public HashMap<String,String> getCount(Date start,Date end) {
    HashMap<String, String> map=new HashMap<String, String>();
    Query q = em.createNativeQuery(
                    "select count(i.uuid),i.username from Information i" +
                    "where i.entereddt between :start and :end group by i.username");
    q.setParameter("start",new Timestamp(start.getTime()));
    q.setParameter("end",new Timestamp(end.getTime()));

    List<Object[]> list = query.getResultList();

    for (Object[] result : list) {
        map.put(result[0].toString(), result[1].toString());
    }

    return map;
}

Writing a HashMap to database using Hibernate

So there a couple ways to go about doing this. You can persist a json string to your database that represents the key/value pairs. do not use json if you need to query against it as of 2013

{key: 'myKey', values:[{'1'},{'2'},{'3'}]}

you can persist the java object to a blob (binary object).

id number,
map blob

This will allow you to store the data in one column. I recommend the json string as it will not force you into using java for the rest of your life and others can also use your data.

---

Related Topics