Using sql column names in hibernate createSQlquery result

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 refe开发者_开发问答r to the colums by their index.

Any chance I could do something like jdbc and refer to the columns by their sql name instead of their index?

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.

Note: This work for SQLQuery, if your using AliasToEntityMapResultTransformer on hql query without specifying aliases you will get index value as key.

If you are again transforming aliasToValueMapList to your POJO list, I advice you to create your own ResultTransformer and return your custom object from 'transformTuple' method.

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.

First of all, you can use the doWork method to access the underlying JDBC connection and handle it as you would with pure JDBC:

session.doWork(new Work() {
  public void execute(Connection connection) throws SQLException {
    connection.prepareStatement(...
  }
});

Or, you can use query.getReturnAliases which returns a String[] of the column names. For effciency, I'd probably build a Map of alias to index and then you can do something like 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/Criteria and get all the benefits of lazy loading, simple joins, dirty checking, etc.

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.