I've been attempting to use @ColumnResult as part of a @SqlResultSetMapping that combines both entity and scalar results. My environment is Glassfish 3.1 b43, w/JDK 1.6.0_22 and Oracle 10g.
The scalar column is aliased in the native query to "myCount" (using mixed case). When I constructed the SqlResultSetMapping my column mapping looked like so:
columns = { @ColumnResult(name="myCount") }
This did not work, the scalar column never showed up in the result set. After spending a lot of time banging my head against the wall I stumbled on section "Restrictions and Optimizations" section of the GF 3.1.1 Application Development Guide which contains a section titled "SQL Result Set Mapping". In that section there's this nugget:
Use the following elements:
<sql-result-set-mapping name="SRSMName" >
<entity-result entity-class="entities.someEntity" />
<column-result name="UPPERCASECOLUMNNAME" />
</sql-result-set-mapping>
Or specify an explicit column name for the upperCaseColumnName field.
If I hadn't looked closely I wouldn't have noticed the UPPERCASECOLUMN name which in not so obvious fashion is specifying that you need to use uppercase for the column name in your ColumnResult. So changing things to
columns = { @ColumnResult(name="MYCOUNT") }
worked, even though the native query still have that result column aliased with mixed case. This uppercase column requirement is non-obvious and confusing. The JPA 2.0 specification does not specify that ColumnResult name must be uppercase, case sensitive or insensitive. In fact Eclipselink by default is case sensitive, see this discussion [1] from 2009. Why is Glassfish even involved here? Shouldn't this be left up to the JPA provider?
-Noah
[1] -
http://old.nabble.com/Is-@ColumnResult-case-sensitive-by-design-or-is-it-a-bug--td26518000.html