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

• Steven Jeuris: I don't know about JPA and which queries it supports, but wouldn't it already be faster to do SELECT location FROM Location location and do the distinct programmatically? Not saying this is the best solution, but right now you are querying all locations twice.
• Jason: It's a performance issue. Location could potentially have hundreds of thousands of rows in the database.
• Steven Jeuris: So I'm guessing JPA allows you to use indexes? Otherwise it would be a memory issue, and not as much a performance issue.
• Steven Jeuris: Again, I might be entirely wrong here, but what about SELECT DISTINCT location.mtfcc, location.name FROM Location location. :) Perhaps consider phrasing this as a question on distinct on Stack Overflow if you don't get any knowledgeable responses here.
• Jason: I've considered it, but I'm having a major brain fart in my query-fu here trying to decide it it would work. I think it would, so I'll just have to try it once the code is runnable.

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

• Michael K: JPA (the Java API, anyway) is also pretty undocumented. I had to use reflection just to find out the return type. Fun exercise!