Getting column names from a JPA Native Query

Question

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?

Answer 1

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