java 从 JPA 本机查询中获取列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44626609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting column names from a JPA Native Query
提问by Master_T
I have an administrative console in my web application that allows an admin to perform a custom SQL SELECT query on our database.
我的 Web 应用程序中有一个管理控制台，允许管理员对我们的数据库执行自定义 SQL SELECT 查询。
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:
在下面，应用程序使用 Hibernate，但这些查询不是 HQL，它们是纯 SQL，所以我使用的是这样的 Native Query：
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?
有没有办法做到这一点？
采纳答案by Master_T
After a long time without answers, and based on my own further research, it seems this cannot be done ufortunately.
很长时间没有答案，根据我自己的进一步研究，不幸的是，这似乎无法做到。
回答by Riyad
This code worked for me
这段代码对我有用
DTO Class :
DTO 类：
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;
}
}
回答by Nicolas
Ryiad's answer DTO adds some confusion, you should have kept it away. You should have explained htat it works only with hibernate.
Ryiad 的回答 DTO 增加了一些混乱，您应该将其远离。您应该已经解释了 htat 它仅适用于休眠状态。
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:
如果像我一样需要保持列的顺序，则可以指定自己的转换器。我从 hibernate 复制代码并将 HashMap 更改为 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:
有了这个转换器，你可以在 Hibernate 中使用 Ryiad 的解决方案：
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();
回答by JesusIniesta
2020
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.
使用 hibernate 5.2.11.Final 实际上很容易。在我的示例中，您可以看到我如何获取每一行的列名。以及我如何按列名获取值。
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");
}
}
回答by ltlBeBoy
If the JPA provider does not support the retrieval of query metadata, another solution could be the use of a SQL parser like JSQLParser, ZQLor General SQL Parser(comercial), which extracts the fields from the SELECTstatement.
如果 JPA 提供程序不支持查询元数据的检索，另一种解决方案可能是使用 SQL 解析器，如JSQLParser、ZQL或General SQL Parser（商业），它从SELECT语句中提取字段。
回答by utkarsh pandey
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.
我在使用 JPA 时也遇到了类似的问题。JPA 中没有直接访问结果集元数据的方法。解决方案可以是从查询本身中提取列名或使用 JDBC 来获取元数据。
回答by Luke Cheung
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());
