JPA Query to Return a Map

Question

I wrote the following JPA method that queries a database to return a distinct list of a String field (called mtfcc) of a Location object. I then took the list of strings to query for the associated name for that MTFCC.

Could I just modify the first query to get the results, without having to use the for loop to get the associated name field?

@Override
public Map<String, String> getAvailableBorderTypes() {

    // create empty map to store results in. If we don't find results, an empty hashmap will be returned
    Map<String, String> results = new HashMap<String, String>();

    EntityManager em = entityManagerFactory.createEntityManager();

    // get the distinct mtfcc's
    String jpaQuery = "SELECT DISTINCT location.mtfcc FROM Location location";

    // get the distinct mtfcc codes
    List<String> mtfccList = (List<String>) em.createQuery(jpaQuery).getResultList();

    // cycle through the mtfcc list and get the name to associate with it
    for (String mtfcc: mtfccList) {
        String nameQuery = "SELECT DISTINCT location.name FROM Location location WHERE location.mtfcc = ?1";
        String name = (String) em.createQuery(nameQuery).setParameter(1, mtfcc).getSingleResult();
        results.put(mtfcc, name);
    }

    return results;
}

Answer 1

Ok, I've got a possible simplification for you. The goal I had was to get to using one query:

SELECT DISTINCT location.mftcc, location.name FROM Location location

It turns out that Query.getResultList() behaves funny: It returns a List of Object[]! Each entry in the list represents a row returned from the database, where the entries in the Object[] are each field specified in the SELECT. Also, to furthur complicate matters, each field is returned as the JPA type. So, you have to treat the whole array as an Object[] and cast the individual elements.

Here's the code to get it to work:

public Map<String, String> getAvailableBorderTypes() {
   // create empty map to store results in. If we don't find results, an empty hashmap will be returned
   Map<String, String> results = new HashMap<String, String>();

   EntityManager em = entityManagerFactory.createEntityManager();

   // Construct and run query
   String jpaQuery = "SELECT DISTINCT location.mftcc, location.name FROM Location location";
   List<Object[]> resultList = em.createQuery(jpaQuery).getResultList();

   // Place results in map
   for (Object[] borderTypes: resultList) {
      results.put((String)borderTypes[0], (String)borderTypes[1]);
   }

   return results;
}