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

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. — Steven Jeuris
– Steven Jeuris, Commented Mar 23, 2011 at 15:05
It's a performance issue. Location could potentially have hundreds of thousands of rows in the database. — Jason
– Jason, Commented Mar 23, 2011 at 15:09
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
– Steven Jeuris, Commented Mar 23, 2011 at 15:18
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. — Steven Jeuris
– Steven Jeuris, Commented Mar 23, 2011 at 15:28
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. — Jason
– Jason, Commented Mar 23, 2011 at 15:43

Michael K · Accepted Answer · 2011-03-25 18:08:01Z

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

JPA (the Java API, anyway) is also pretty undocumented. I had to use reflection just to find out the return type. Fun exercise! — Michael K
– Michael K, Commented Mar 25, 2011 at 18:09

Stack Exchange Network

JPA Query to Return a Map

1 Answer 1

You must log in to answer this question.

Hot Network Questions

JPA Query to Return a Map

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions