Using sql column names in hibernate createSQlquery result

Question

Hi all, 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, im using createSQLQuery. The problem is that this method can only return a List, and i need to refer to the colums by their index.

Any chance i could do like jdbc and refer to the columns by their sql name insted of their index? thanks

Answer 1

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.

Answer 2

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.