11
\$\begingroup\$

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;
}
\$\endgroup\$
7
  • \$\begingroup\$ 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. \$\endgroup\$ Mar 23 '11 at 15:05
  • \$\begingroup\$ It's a performance issue. Location could potentially have hundreds of thousands of rows in the database. \$\endgroup\$
    – Jason
    Mar 23 '11 at 15:09
  • \$\begingroup\$ So I'm guessing JPA allows you to use indexes? Otherwise it would be a memory issue, and not as much a performance issue. \$\endgroup\$ Mar 23 '11 at 15:18
  • 3
    \$\begingroup\$ 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. \$\endgroup\$ Mar 23 '11 at 15:28
  • 2
    \$\begingroup\$ 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. \$\endgroup\$
    – Jason
    Mar 23 '11 at 15:43
13
\$\begingroup\$

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;
}
\$\endgroup\$
1
  • \$\begingroup\$ JPA (the Java API, anyway) is also pretty undocumented. I had to use reflection just to find out the return type. Fun exercise! \$\endgroup\$
    – Michael K
    Mar 25 '11 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.