Hibernate Query for Object based on key value pairs of the Object's Java Map<String, String>

Question

I am trying to query for a Java Object using HQL which is performing filters based on the Object's Java Map that it has.

Essentially what I want to ask is 'give me all the error reports where mapkey1=val_x and mapkey2=val_y'

I have this object (stripped down)

    @Entity
    @Table(name = "error_report")
    public class ErrorReport implements Serializable {
      private static final long serialVersionUID = 1L;

      @Id
      @Column(name = "id", length=50)
      private String id= UUID.randomUUID().toString();

      @ElementCollection
      @CollectionTable(name = "error_property", joinColumns = {@JoinColumn(name = "error_id", referencedColumnName = "id")})
      @MapKeyColumn(name = "prop", length=50)
      @Column(name = "prop_val")
      @Type(type="text")
      private Map<String, String> reportedProperties = new HashMap<>();

   }

So I want ErrorReports based on the reportedProperties. I have set up a unit test and everything works perfectly when the reportedProperties Map has only 1 entry per ErrorReport. This is the HQL I used:

 from ErrorReport as model where KEY(model.reportedProperties) = :A1 and VALUE(model.reportedProperties) = :A2 

When the ErrorReport has 2 entries in the reportedProperties Map the query fails with the following error:

could not extract ResultSet caused by HsqlException: cardinality violation

When I look at the generated SQL and try and run it manually I can see it will not work, because the innter select is returning multiple results.

SELECT error_report_.id AS id1_2_, error_report_.product_url AS product_2_2_, error_report_.audit_id AS audit_id3_2_, error_report_.category_id AS 
category4_2_, error_report_.error_desc AS error_de5_2_, error_report_.notifier_id AS notifier6_2_, error_report_.product_name AS 
product_7_2_, error_report_.product_version AS product_8_2_, error_report_.error_time AS error_ti9_2_ 
FROM error_report error_report_ 
 CROSS JOIN error_property reportedpr1_
 CROSS JOIN error_property reportedpr2_ 
 WHERE error_report_.id=reportedpr1_.error_id 
 AND error_report_.id=reportedpr2_.error_id 
 AND reportedpr1_.prop=? 
 AND 
(SELECT reportedpr2_.prop_val FROM error_property reportedpr2_ WHERE error_report_.id=reportedpr2_.error_id)=?

Clearly there is something wrong with my HQL, but it seems to follow other examples I have found. Does anyone know what the syntax is?

I am using hibernate 5.4.9.Final

Answer 1

For anyone in the future, I had similar problem and solved it by applying join on the collection table combined with INDEX().

SELECT DISTINCT(model.id), model.product_url, ...
FROM ErrorReport as model 

...

JOIN model.reportedProperties rProp 
WHERE INDEX(rProp) = :A1 AND rProp = :A2

Here the INDEX(rProp) is the key and rProp is the value. Also DISTINCT was needed because it was returning duplicate records for me due to the map.