CODEWITHSUNDEEP
javasqlhibernatejpa
Master_T
Master_T

Reputation: 7913

Getting column names from a JPA Native Query

I have an administrative console in my web application that allows an admin to perform a custom SQL SELECT query on our database.

Underneath, the application is using Hibernate, but these queries are not HQL, they're pure SQL, so I'm using a Native Query like this:

protected EntityManager em;

public List<Object[]> execute(String query) {
    Query q = em.createNativeQuery(query);
    List<Object[]> result = q.getResultList();
    return result;
}

This works correctly, but it only returns the rows of data, with no extra information. What I would like is to also get the column names, so when I print the results back to the user I can also print a header to show what the various columns are.

Is there any way to do this?

Upvotes: 27

Views: 42721

Answers (11)

Mahaveer K
Mahaveer K

Reputation: 11

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

Upvotes: 0

Nei Ortol&#225;
Nei Ortol&#225;

Reputation: 91

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;

Upvotes: 2

geeekfa
geeekfa

Reputation: 1445

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

Upvotes: 24

user16547619
user16547619

Reputation: 309

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

}

Upvotes: -1

Nicolas
Nicolas

Reputation: 346

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();

Upvotes: 5

Master_T
Master_T

Reputation: 7913

After a long time without any answer, And based on my own further research, It seems that it can not be possible, Unfortunately.

Upvotes: 1

Jesus Iniesta
Jesus Iniesta

Reputation: 12469

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

Upvotes: 6

Riyad
Riyad

Reputation: 111

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;

    }

    }

Upvotes: 7

Luke Cheung
Luke Cheung

Reputation: 497

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());

Upvotes: 0

utkarsh pandey
utkarsh pandey

Reputation: 1

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.

Upvotes: -1

ltlBeBoy
ltlBeBoy

Reputation: 1312

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.

Upvotes: 0

Related Questions