Home > JPA > Returning the result of a JPA native query as a simple Map or POJO

Returning the result of a JPA native query as a simple Map or POJO

I’ve spent quite some time on StackOverflow to answer one simple question: Can I make a JPA NativeQuery return the results as a Map or as a simple POJO? The common consensus looks like it’s not possible. This is unfortunate, but sometimes when people say something cannot be done, I get fired up. And I’ve found a solution, even though it involves a little Hibernate-Vendor-lock-in and a detour.

Let me explain the problem in detail.

Say you’ve got a query like this:

select * from users where  name = :familyName;  

First your query won’t specify any result column names, so you can’t extract them from the query string.

Plus, if you use the query and get a result like this:

final Query nativeQuery = em.createNativeQuery(queryString);  
nativeQuery.getResultList();   

Then the list you get is just a plain java.util.List, there is no information on the content – all you’ll get is a list of arrays.

You might be tempted to try this instead:

final Query nativeQuery = em.createNativeQuery(queryString, resultClass);  

Unfortunately “resultClass” needs to be a properly annotated Entity class (which maps to a table or view in the database), which is probably not what you want for ad-hoc–queries where a simple POJO would be enough. This method can’t map to queries however, unless you annotate an Entity with some weird annotation-stuff that looks dead ugly, unreadable and overly complicated.

See: http://docs.oracle.com/javaee/5/api/javax/persistence/SqlResultSetMapping.html

Getting the result column names

The basic idea is to use a java.sql.PreparedStatement to determine the result meta data. But unfortunately there are some hurdles in the way.

First the javax.persistence.EntityManager class doesn’t have a method to create a PreparedStatement. In order to create a PreparedStatement you would need the java.sql.Connection, but there is no getConnection() method in the EntityManager either.

Is this a dead end street? Not quite! With a little vendor lock-in we can do this:

final Session session = em.unwrap(Session.class); // ATTENTION! This is Hibernate-specific!  
session.doWork((connection) -> {  
  connection.prepareStament(queryString);  
});  

This is where we hit another brick wall: A PreparedStatement doesn’t work with named parameters, like so:

select * from users where customer_id = :customerId  and (email = :email or '' = :email );  

So the idea is to automatically transform this query to something like this:

select * from users where customer_id = ?  and (email ? or '' = ? );  

This is where a nice little class from this article comes handy:

http://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html?page=2

We don’t need all of it, just the stuff that parses the query and replaces the parameter names:

import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.SQLException;  
  
/** 
 * @author Adam Crume for JavaWorld.com, original code 
 * @author Wanja Gayk for brixomatic.wordpress.com, reduced code extract 
 * @see http://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html?page=2 
 */  
public class PreparedStatementFactory {  
  
  public static PreparedStatement create(final Connection connection, final String queryStringWithNamedParameters) throws SQLException {  
       final String parsedQuery = parse(queryStringWithNamedParameters);  
       return connection.prepareStatement(parsedQuery);  
  }  
  
  private static final String parse(final String query) {  
       // I was originally using regular expressions, but they didn't work well for ignoring  
            // parameter-like strings inside quotes.  
       final int length = query.length();  
       final StringBuffer parsedQuery = new StringBuffer(length);  
       boolean inSingleQuote = false;  
       boolean inDoubleQuote = false;  
       
       for (int i = 0; i < length; ++i) {  
            char c = query.charAt(i);  
            if (inSingleQuote) {  
                 if (c == '\'') {  
                      inSingleQuote = false;  
                 }  
            } else if (inDoubleQuote) {  
                 if (c == '"') {  
                      inDoubleQuote = false;  
                 }  
            } else {  
                 if (c == '\'') {  
                      inSingleQuote = true;  
                 } else if (c == '"') {  
                      inDoubleQuote = true;  
                 } else if (c == ':' && i + 1 < length && Character.isJavaIdentifierStart(query.charAt(i + 1))) {  
                      int j = i + 2;  
                      while (j < length && Character.isJavaIdentifierPart(query.charAt(j))) {  
                           ++j;  
                      }  
                      final String name = query.substring(i + 1, j);  
                      c = '?'; // replace the parameter with a question mark  
                      i += name.length(); // skip past the end of the parameter  
                 }  
            }  
            parsedQuery.append(c);  
       }  
       return parsedQuery.toString();  
  }  
  
}  

Using this new class, we can finally create our prepared statement, extract the metadata from it and return a column-name-to-column-index map:

private Map<String, Integer> getColumnNameToIndexMap(final String queryString) throws SQLException {  
  final Session session = em.unwrap(Session.class); // ATTENTION! This is Hibernate-specific!  
  final AtomicReference<ResultSetMetaData> msRef = new AtomicReference<>();  
  session.doWork((c) -> {  
       try (final PreparedStatement statement = PreparedStatementFactory.create(c, queryString)) {  
       // I'm not setting parameters here, because I just want to find out about the return values' column names  
       msRef.set(statement.getMetaData());  
       }  
  });  
  final ResultSetMetaData metaData = msRef.get();  
  // LinkedHashmap preserves order of insertion:  
  final Map<String, Integer> columnNameToColumnIndex = new LinkedHashMap<>();   
  for (int t = 0; t < metaData.getColumnCount(); ++t) {  
       // important, first index in the metadata is "1", the first index for the result array must be "0"  
       columnNameToColumnIndex.put(metaData.getColumnName(t + 1), t);  
  }  
  return columnNameToColumnIndex;  
}  

Mapping the result

Now as we have out map of column names to column indexes, we can write a method that transforms a single Object[] to a single column-name-to-column-value map:

private Map<String, Object> getColumNameToValueMapFromRowValueArray(final Object[] rowValueArray, final Map<String, Integer> columnNameToIndexMap) {  
  // stream().collect(toMap(keyFunct, valueFunct)...) will not accept "null" values, so we do it this way:  
  final Map<String, Object> result = new LinkedHashMap<>();  
  columnNameToIndexMap.entrySet().forEach(nameToIndexEntry -> result.put(nameToIndexEntry.getKey(), rowValueArray[nameToIndexEntry.getValue()]));  
  return result;  
}  

This method we can use to transform our list of column values (List) to a list of colum-name-to-column-value-maps (List):

import static java.util.Arrays.stream;  
import static java.util.stream.Collectors.mapping;  
import static java.util.stream.Collectors.toList;  
//…  
private List<Map<String, Object>> asListOfMaps(final List<Object[]> queryResultAsListOfObjectArrays, final Map<String, Integer> columnNameToIndexMap) {  
  final Function<Object[], Map<String, Object>> rowValueArrayToColumnNameToValueMap = rowValueArray -> {  
       return getColumNameToValueMapFromRowValueArray(rowValueArray, columnNameToIndexMap);  
  };  
  return queryResultAsListOfObjectArrays.stream().collect(mapping(rowValueArrayToColumnNameToValueMap, toList()));  
}  

Looking at this method, it screams in our face that we are basically free to come up with any other function to map an Object[] to a target object . So why not take it one step further and map to a POJO? Let’s transform the stuff a bit and make it eat an optional function that transforms a name-value-map to something else, we can then use the identity()-function for the simple case of returning a map for each row:

import static java.util.Arrays.stream;  
import static java.util.stream.Collectors.mapping;  
import static java.util.stream.Collectors.toList;  
//…  
public <T> List<T> asMapped(final List<Object[]> queryResultAsListOfObjectArrays, final Map<String, Integer> columnNameToIndexMap, final Function<Map<String, Object>, T> mapToObject) {  
  final Function<Object[], Map<String, Object>> arrayToMap = rowValueArray -> {  
       return getColumNameToValueMapFromRowValueArray(rowValueArray, columnNameToIndexMap);  
  };  
  final Function<Object[], T> mapper = arrayToMap.andThen(mapToObject);  
  return queryResultAsListOfObjectArrays.stream().collect(mapping(mapper, toList()));  
}  
  
public List<Map<String, Object>> asListOfMaps(final List<Object[]> queryResultAsListOfObjectArrays, final Map<String, Integer> columnNameToIndexMap) {  
  return asMapped(queryResultAsListOfObjectArrays, columnNameToIndexMap, Function.identity());  
}  
  
public <T> List<T> asListOfPojos(final List<Object[]> queryResultAsListOfObjectArrays, final Map<String, Integer> columnNameToIndexMap, final Supplier<T> targetPojoFactory) {  
  final Function<Map<String, Object>, T> mapToPojo = (rowMap) -> createPojoAndMapValues(targetPojoFactory, rowMap);  
  return asMapped(queryResultAsListOfObjectArrays, columnNameToIndexMap, mapToPojo);  
}  

The last method above expects some supplier for a target POJO, so we don’t need to rely on POJOS having a default constructor, though usually we would use “SomePojoClass::new” as a parameter. What this method also does is to use some “createPojoAndMapValues” method, which we still need to write. But let’s first make a few design decisions and go from there. We probably want to annotate a POJO for mapping, like we’re used to with JPA. For extended versatility, we’d probably also like to make use of existing JAXB or Jackson annotations for easy XML and JSON processing and if that fails, we might try map to the field names directly in that order. With the main problems solved above, this is very easy. Let’s first abstract how we’re creating a POJO and mapping the values to it:

private <T> T createPojoAndMapValues(final Supplier<T> targetPojoFactory, final Map<String, Object> rowMap) {  
  final T pojo = targetPojoFactory.get();  
  rowMap.entrySet().stream().forEach(columnNameValueEntry -> setToField(columnNameValueEntry, pojo));  
  return pojo;  
}  

That was easy. So we need to sets a value to a field, me might optionally need to convert a value, so we can map TimeStamps to Strings or so. That’s a piece of cake too:

private <T> void setToField(final Entry<String, Object> columnNameValueEntry, final T pojo) {  
  final String columnName = columnNameValueEntry.getKey();  
  Object columnValue = columnNameValueEntry.getValue();  
  try {  
       final Field field = getField(pojo.getClass(), columnName);  
       columnValue = optionallyConvertValueToFieldType(field, columnValue);  
       field.set(pojo, columnValue);  
  } catch (final IllegalAccessException | IllegalArgumentException | IllegalStateException e) {  
       throw new IllegalArgumentException("Could not set Value " + columnValue + (columnValue == null ? "" : "of type: " + columnValue.getClass())  
         + " to pojo of type " + pojo.getClass(), e);  
  }  
}  

Getting a field goes as outlined above: First check the JPA, JAXB and Jackson annotations and optionally fall back to the field name.

The idea is to be able to use any POJO class like so and have it mapped automatically:

class NameAndAge{  
 @Column(name=“user_name”)  
 public String name;  
 @Column(name=“user_age”)  
 public int age;  
}  

Please note that there is some Exception magic happening in the following method, I will explain that later.

import static UtilExceptions.declareToThrow;  
import static UtilExceptions.undeclareCheckedException;  
  
private Field getField(final Class<? extends Object> pojoClass, final String columnName) {  
  try {  
       declareToThrow(NoSuchFieldException.class); // because it sneakily does down at "pojoClass.getDeclaredField"  
       return stream(pojoClass.getDeclaredFields())//  
         .filter(field -> field.getAnnotation(Column.class) != null)// Column name is dominant  
         .filter(field -> columnName.equalsIgnoreCase(field.getAnnotation(Column.class).name()))//  
         .findAny()//  
         .orElseGet(() -> stream(pojoClass.getDeclaredFields())// @XmlAnnotation is a second choice  
                 .filter(field -> field.getAnnotation(XmlAttribute.class) != null)//  
                 .filter(field -> columnName.equalsIgnoreCase(field.getAnnotation(XmlAttribute.class).name()))//  
                 .findAny()//  
                 .orElseGet(() -> stream(pojoClass.getDeclaredFields())// @JsonProperty is a third choice  
                         .filter(field -> field.getAnnotation(JsonProperty.class) != null)//  
                         .filter(field -> columnName.equalsIgnoreCase(field.getAnnotation(JsonProperty.class).value()))//  
                         .findAny()//  
                         .orElseGet(undeclareCheckedException(() -> pojoClass.getDeclaredField(columnName))))); // field name is our last option  
  } catch (final SecurityException e) {  
       throw new IllegalStateException("Could not map column " + columnName + " to class " + pojoClass, e);  
  } catch (final NoSuchFieldException e) {  
       // ignore, because we will throw an exception anyway  
  }  
  throw new IllegalArgumentException("Could not map column to class " + pojoClass  
         + ". Reason: No declared field found that is either annotated with @Column(name=\"" + columnName + "\") or @XmlAttribute(name=\"" + columnName  
         + "\") or @JsonProperty(\"" + columnName + "\") ignoring case, and no declared Field is named \"" + columnName + "\".");  
}  

The methods “declareToThrow()” and ”undeclareCheckedException()” are weird and some people will hate everything about it, but it really makes things easier when dealing with lambdas that are mapping to methods that throw checked exceptions. It’s a combination of these two ideas:

https://brixomatic.wordpress.com/2010/04/29/hack-of-the-day-unchecking-checked-exceptions/

The Mute Design Pattern

The method undeclareCheckedException (..) will take a functional interface that throws a checked exception and return a lambda that will sneakily throw the same checked exception like it was a RuntimeException, so it must not be declared by the calling method. Of course we must catch the Exception and deal with it, but if we tried to catch, it the compiler would state that this specific checked Exception would never be thrown. This is where the method “declareToThrow(..)” comes in. This method does nothing but to declare to throw a specific exception, so if we call that, the compiler sees the exception is declared to be thrown in this block of code and won’t complain if you try to catch it. This is black Java magic, use wisely!
I’ll just leave this to you as is:

import java.util.function.BiConsumer;  
import java.util.function.Supplier;  
  
public class UtilExceptions {  
  
  @FunctionalInterface  
  public interface CheckedSupplier<X> {  
       X get() throws Throwable;  
  }  
  
  public static <X> Supplier<X> undeclareCheckedException(final CheckedSupplier<X> supplier) {  
       return () -> {  
            try {  
                 return supplier.get();  
            } catch (final Throwable checkedException) {  
                 return rethrowUnchecked(checkedException);  
            }  
       };  
  }  
  
  public static <R> R rethrowUnchecked(final Throwable checkedException) {  
       return UtilExceptions.<R, RuntimeException> thrownInsteadOf(checkedException);  
  }  
  
  @SuppressWarnings("unused")  
  public static <T extends Throwable> void declareToThrow(final Class<T> clazz) throws T {  
       // do nothing  
  }  
  
  @SuppressWarnings("unchecked")  
  private static <R, T extends Throwable> R thrownInsteadOf(final Throwable t) throws T {  
       throw (T) t;  
  }  
  
}  

Now everything that is missing is a method to optionally convert values to the field’s type, it does things like unboxing primitives and using an optional Type converter:

@SuppressWarnings("unchecked")  
private Object optionallyConvertValueToFieldType(final Field field, final Object columnValue) {  
  final Class<?> fieldType = field.getType();  
  if (columnValue == null) {  
       //@formatter:off  
       return Long.TYPE.equals(fieldType) ? Long.valueOf(0l)  
            : Integer.TYPE.equals(fieldType) ? Integer.valueOf(0)  
            : Short.TYPE.equals(fieldType) ? Short.valueOf((short)0)  
            : Byte.TYPE.equals(fieldType) ? Byte.valueOf((byte)0)  
            : Double.TYPE.equals(fieldType) ? Double.valueOf(0d)  
            : Float.TYPE.equals(fieldType) ? Float.valueOf(0f)  
            : (Object) null; //note for some reason we get an NPE without the cast  
  }//@formatter:on  
  final Class<?> valueType = columnValue.getClass();  
  if (!fieldType.isAssignableFrom(valueType)) {  
       @SuppressWarnings("rawtypes")  
       final Optional<TypeConverter> optionalConverter = converters.stream().filter(c -> c.canConvert(valueType, fieldType)).findFirst();  
       if (optionalConverter.isPresent()) {  
            return optionalConverter.get().convert(columnValue);  
       }  
   }  
   return columnValue;  
}  

I won’t get into more detail on the type converters here, they are trivial. So here we go: Some said it couldn’t be done, good I didn’t give up.

TL;DR

Once the major hurdles of getting a connection and creating a PreparedStatement from a query string with named parameters is solved, mapping the query result to another data structure is a piece of cake.

Advertisements
Categories: JPA Tags: , , , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: