Close

JPQL - Using keywords KEY, VLUE, ENTRY to navigate Map element collections

[Updated: Sep 3, 2018, Created: Sep 3, 2018]

In JPQL KEY, VALUE, and ENTRY keywords may be applied to identification variables that correspond to map-valued associations or map-valued element collections.

Example

Entity

@Entity
public class Customer {
  @Id
  @GeneratedValue
  private int id;
  private String name;
  @ElementCollection
  private Map<String, Integer> itemQtyMap;
    .............
}

Accessing maps in JPQL

public class ExampleMain {

  public static void main(String[] args) {
      EntityManagerFactory emf = Persistence.createEntityManagerFactory("example-unit");
      try {
          persistEntity(emf);
          findItemQty(emf);
          findItemQtyEntry(emf);
          findCustomerByItemName(emf);
      } finally {
          emf.close();
      }
  }

  private static void persistEntity(EntityManagerFactory emf) {
      System.out.println("-- Persisting entities --");
      EntityManager em = emf.createEntityManager();

      Customer c1 = new Customer();
      c1.setName("Lindsey Craft");
      Map<String, Integer> itemPurchased = new HashMap<>();
      itemPurchased.put("XYZ Blender", 1);
      itemPurchased.put("ZZZ Beer Glass", 4);
      c1.setItemQtyMap(itemPurchased);
      System.out.println(c1);

      Customer c2 = new Customer();
      c2.setName("Morgan Philips");
      Map<String, Integer> orderMap2 = new HashMap<>();
      orderMap2.put("AA Glass Cleaner",3);
      c2.setItemQtyMap(orderMap2);
      System.out.println(c2);

      em.getTransaction().begin();
      em.persist(c1);
      em.persist(c2);
      em.getTransaction().commit();
      em.close();
  }

  private static void findItemQty(EntityManagerFactory emf) {
      System.out.println("-- Loading items and keys --");
      EntityManager em = emf.createEntityManager();
      TypedQuery<Tuple> query =
              em.createQuery("SELECT c.name, KEY(map), VALUE(map) "
                      + "FROM Customer c JOIN c.itemQtyMap map", Tuple.class);
      query.getResultList().forEach(
              t -> System.out.printf("Customer: %s, Item: %s, Qty: %s%n", t.get(0), t.get(1), t.get(2)));
  }

  private static void findItemQtyEntry(EntityManagerFactory emf) {
      System.out.println("-- Loading item/keys entries  --");
      EntityManager em = emf.createEntityManager();
      TypedQuery<Map.Entry> query =
              em.createQuery("SELECT ENTRY(c.itemQtyMap) FROM Customer c", Map.Entry.class);
      query.getResultList().forEach(entry -> System.out.printf("Item: %s, Qty: %s%n",
              entry.getKey(), entry.getValue()));
  }

  private static void findCustomerByItemName(EntityManagerFactory emf) {
      System.out.println("-- Loading  customers where item name like %Beer% --");
      EntityManager em = emf.createEntityManager();
      TypedQuery<Customer> query =
              em.createQuery("SELECT c FROM Customer c"
                      + " WHERE KEY(c.itemQtyMap) LIKE '%Beer%'", Customer.class);
      query.getResultList().forEach(System.out::println);
  }
}
-- Persisting entities --
Customer{id=0, name='Lindsey Craft', itemQtyMap={XYZ Blender=1, ZZZ Beer Glass=4}}
Customer{id=0, name='Morgan Philips', itemQtyMap={AA Glass Cleaner=3}}
-- Loading items and keys --
Customer: Lindsey Craft, Item: XYZ Blender, Qty: 1
Customer: Lindsey Craft, Item: ZZZ Beer Glass, Qty: 4
Customer: Morgan Philips, Item: AA Glass Cleaner, Qty: 3
-- Loading item/keys entries --
Item: XYZ Blender, Qty: 1
Item: ZZZ Beer Glass, Qty: 4
Item: AA Glass Cleaner, Qty: 3
-- Loading customers where item name like %Beer% --
Customer{id=1, name='Lindsey Craft', itemQtyMap={XYZ Blender=1, ZZZ Beer Glass=4}}

Also check out example of navigating map key/values in map entity association.

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.3.5.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • JDK 1.8
  • Maven 3.5.4

JPQL - KEY, VLUE, ENTRY Example Select All Download
  • jpql-using-key-value-entry-for-map
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also