Getting column names from a JPA Native Query

Query query = entityManager.createNamedQuery(namedQuery); NativeQueryImpl nativeQuery = (NativeQueryImpl) query; nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); List<Map<String,Object>> result = nativeQuery.getResultList(); 

And now you have Map<String,Object> . You can see your Column Names

This code worked for me

DTO Class :

 public class ItemResponse<T> { private T item; public ItemResponse() { } public ItemResponse(T item) { super(); this.item = item; } public T getItem() { return item; } public void setItem(T item) { this.item = item; } } 

Service Class is in the below

import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query; import org.springframework.stereotype.Service; import org.hibernate.transform.AliasToEntityMapResultTransformer; @Service public class ServiceClass{ @PersistenceContext public EntityManager entityManager; public ItemResponse exceuteQueryResponse(String queryString) { ItemResponse itemResponse=new ItemResponse(); Query jpaQuery = entityManager.createNativeQuery(queryString); org.hibernate.Query hibernateQuery =((org.hibernate.jpa.HibernateQuery)jpaQuery).getHibernateQuery(); hibernateQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); List<Map<String,Object>> res = hibernateQuery.list(); itemResponse.setItem(res); return itemResponse; } } 

2020

With hibernate 5.2.11.Final is actually pretty easy. In my example you can see how I get the column names for every row. And how I get values by column name.

Query q = em.createNativeQuery("SELECT columnA, columnB FROM table"); List<Tuple> result = q.getResultList(); for (Tuple row: result){ // Get Column Names List<TupleElement<Object>> elements = row.getElements(); for (TupleElement<Object> element : elements ) { System.out.println(element.getAlias()); } // Get Objects by Column Name Object columnA; Object columnB; try { columnA = row.get("columnA"); columnB= row.get("columnB"); } catch (IllegalArgumentException e) { System.out.println("A column was not found"); } } 

Ryiad's answer DTO adds some confusion, you should have kept it away. You should have explained that it works only with hibernate.

If like me you needs to keep the order of columns, you can specify your own transformer. i copied the code from hibernate and changed the HashMap to LinkedHashMap:

import java.util.LinkedHashMap; import java.util.Map; import org.hibernate.transform.AliasedTupleSubsetResultTransformer; import org.hibernate.transform.ResultTransformer; /** * {@link ResultTransformer} implementation which builds a map for each "row", made up of each aliased value where the * alias is the map key. Inspired by {@link org.hibernate.transform.AliasToEntityMapResultTransformer}, but kepping the * ordering of elements. * <p/> * Since this transformer is stateless, all instances would be considered equal. So for optimization purposes we limit * it to a single, singleton {@link #INSTANCE instance}. */ public class AliasToEntityMapResultTransformer extends AliasedTupleSubsetResultTransformer { public static final AliasToEntityMapResultTransformer INSTANCE = new AliasToEntityMapResultTransformer(); /** * Disallow instantiation of AliasToEntityMapResultTransformer. */ private AliasToEntityMapResultTransformer() { } @Override public Object transformTuple(Object[] tuple, String[] aliases) { Map result = new LinkedHashMap<>(tuple.length); for (int i = 0; i < tuple.length; i++) { String alias = aliases[i]; if (alias != null) { result.put(alias, tuple[i]); } } return result; } @Override public boolean isTransformedValueATupleElement(String[] aliases, int tupleLength) { return false; } /** * Serialization hook for ensuring singleton uniqueing. * * @return The singleton instance : {@link #INSTANCE} */ private Object readResolve() { return INSTANCE; } } 

With this transformer you can used Ryiad's solution with Hibernate:

 Query jpaQuery = entityManager.createNativeQuery(queryString); org.hibernate.Query hibernateQuery =((org.hibernate.jpa.HibernateQuery)jpaQuery).getHibernateQuery(); hibernateQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); List<Map<String,Object>> res = hibernateQuery.list(); 

This worked for me:

 final Query emQuery = em.createNativeQuery(query, Tuple.class); final List<Tuple> queryRows = emQuery.getResultList(); final List<Map<String, Object>> formattedRows = new ArrayList<>(); queryRows.forEach(row -> { final Map<String, Object> formattedRow = new HashMap<>(); row.getElements().forEach(column -> { final String columnName = column.getAlias(); final Object columnValue = row.get(column); formattedRow.put(columnName, columnValue); }); formattedRows.add(formattedRow); }); return formattedRows; 

If the JPA provider does not support the retrieval of query metadata, another solution could be the use of a SQL parser like JSQLParser, ZQL or General SQL Parser (comercial), which extracts the fields from the SELECT statement.

cast query to hibernate query, then use hibernate method

 //normal use, javax.persistence.Query interface Query dbQuery = entityManager.createNativeQuery(sql); //cast to hibernate query org.hibernate.Query hibernateQuery =((org.hibernate.jpa.HibernateQuery)dbQuery) .getHibernateQuery(); hibernateQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); List<Map<String,Object>> res = hibernateQuery.list(); List<TxTestModel> txTestModels = new ArrayList<>(); res.forEach(e->{ TxTestModel txTestModel = new ObjectMapper().convertValue(e, TxTestModel.class); // txTestModels.add(new TxTestModel().setIdd((Integer) e.get("idd")).setMmm((String) e.get("mmm")).setDdd((Date) e.get("ddd"))); txTestModels.add(txTestModel); }); System.out.println(txTestModels.size()); 

 This is the working solution Below example return the objectlist from the query. Looping the same and from the first object cast it to hasmap, and hashmap.keyset will give you all the coulmn names in a set. List dataList = session.createSQLQuery("SLECT * FROM EMPLOYEETABLE").setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list(); for (Object obj : dataList) { HashMap<String, Object> hashMap = (HashMap<String, Object>) obj; Set<String> keySet = hashMap.keySet(); break; } 

I also faced a similar problem working with JPA. There is no direct way in JPA to access the resultset metadata. The solution can be extracting column names from the query itself or use JDBC to get the metadata.

To enforce em.createNativeQuery(..).getResultList() to return List<Tuple> specify it with Tuple.class when creating native queries :

Query q = em.createNativeQuery("SELECT columnA, columnB FROM table", Tuple.class ); List<Tuple> result = q.getResultList(); for (Tuple row: result){ // Get Column Names List<TupleElement<Object>> elements = row.getElements(); for (TupleElement<Object> element : elements ) { System.out.println(element.getAlias()); } // Get Objects by Column Name Object columnA; Object columnB; try { columnA = row.get("columnA"); columnB= row.get("columnB"); } catch (IllegalArgumentException e) { System.out.println("A column was not found"); } }