Java 在休眠 createSQlquery 结果中使用 sql 列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2605385/
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
Using sql column names in hibernate createSQlquery result
提问by Ricardo
I have a couple of sql views with composite primary keys that I want to query, and since Hibernate makes it a pain to work with composite keyes, I'm using createSQLQuery. The problem is that this method can only return a List, and I need to refer to the colums by their index.
我有几个 sql 视图，其中包含要查询的复合主键，并且由于 Hibernate 使使用复合键变得很痛苦，因此我使用createSQLQuery. 问题是这个方法只能返回一个List，我需要通过它们的索引来引用列。
Any chance I could do something like jdbc and refer to the columns by their sql name instead of their index?
我有没有可能做一些类似 jdbc 的事情，并通过他们的 sql 名称而不是他们的索引来引用列？
采纳答案by Adisesha
Query query=session.createSQLQuery("your query");
query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List<Map<String,Object>> aliasToValueMapList=query.list();
As you can figure out from code, the list contains Map objects representing each row. Each Map object will have column name as key and value as value.
从代码中可以看出，该列表包含代表每一行的 Map 对象。每个 Map 对象都将列名作为键和值作为值。
Note: This work for SQLQuery, if your using AliasToEntityMapResultTransformer on hql query without specifying aliases you will get index value as key.
注意：这适用于 SQLQuery，如果您在 hql 查询上使用 AliasToEntityMapResultTransformer 而不指定别名，您将获得索引值作为键。
If you are again transforming aliasToValueMapList to your POJO list, I advice you to create your own ResultTransformerand return your custom object from 'transformTuple' method.
如果您再次将 aliasToValueMapList 转换为您的 POJO 列表，我建议您创建自己的 ResultTransformer并从“transformTuple”方法返回您的自定义对象。
回答by Brian Deterling
Your question is ambiguous - in the first paragraph you want to refer to columns by index and in the second, by sql name. Since by index is easy, I'll assume by name.
你的问题是模棱两可的 - 在第一段中，你想通过索引引用列，在第二段中，通过 sql 名称。由于按索引很容易，我将按名称假设。
First of all, you can use the doWorkmethod to access the underlying JDBC connection and handle it as you would with pure JDBC:
首先，您可以使用该doWork方法访问底层 JDBC 连接并像使用纯 JDBC 一样处理它：
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
connection.prepareStatement(...
}
});
Or, you can use query.getReturnAliaseswhich returns a String[]of the column names. For effciency, I'd probably build a Mapof alias to index and then you can do something like result[map.get("column name")].
或者，您可以使用query.getReturnAliaseswhich 返回一个String[]列名。为了提高效率，我可能会Map为索引构建一个别名，然后您可以执行类似result[map.get("column name")].
But really, Hibernate handles composite keys pretty easily when using xml mappings (haven't tried with annotations). It's a little more work up front and there are a few issues with complex relationships (mainly when foreign key names/spans don't match), but once you create the id class and map it, you can stick with HQL/Criteriaand get all the benefits of lazy loading, simple joins, dirty checking, etc.
但实际上，Hibernate 在使用 xml 映射时很容易处理复合键（还没有尝试使用注释）。前面需要做更多的工作，并且存在一些复杂关系的问题（主要是外键名称/跨度不匹配时），但是一旦您创建了 id 类并映射了它，您就可以坚持使用HQL/Criteria并获得延迟加载、简单连接、脏检查等的所有好处。
回答by Ashique Sheikh
I got the same problem but it solved when i used this
我遇到了同样的问题，但是当我使用它时它解决了
Query query=session.createSQLQuery("your query");
query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
I get the result with header name but i got a new problem when i create a new column in sql query Select 'DCA5E3' as Shipmentcolor from Employee.class But in this case i got SHIPMENTCOLOR: "D". How to get whole value of SHIPMENTCOLOR.
我得到了带有标题名称的结果，但是当我在 sql 查询中创建一个新列时遇到了一个新问题 Select 'DCA5E3' as Shipmentcolor from Employee.class 但在这种情况下，我得到了 SHIPMENTCOLOR：“D”。如何获得 SHIPMENTCOLOR 的整体价值。
