Hibernate native query - char(3) column

Question

I have a table in Oracle where column SC_CUR_CODE is CHAR(3)

When I do:

    Query q2 = em.createNativeQuery("select sc_cur_code, sc_amount from sector_costs");

    q2.setMaxResults(10);

    List<Object[]> rs2 = q2.getResultList();

    for (Object[] o : rs2) {
        System.out.println(">>> cur=" + o[0]);
    }

I see cur=E and cur=U instead of cur=EUR and cur=USD

o[0] is a java.lang.Character

How can I get the full value EUR and USD ?

Solution

It looks like Hibernate reads value of type CHAR(n) as Character. Try to cast it to VARCHAR(n):

Query q2 = em.createNativeQuery(
    "select cast(sc_cur_code as VARCHAR2(3)), sc_amount from sector_costs");  

When using Hibernate via Session interface, you can explcitly set a type of result with addScalar() instead (also accessible via unwrap() in JPA 2.0):

Query q2 = em.createNativeQuery(
    "select sc_cur_code, sc_amount from sector_costs");
q2.unwrap(SQLQuery.class).addScalar("sc_cur_code", StringType.INSTANCE);

There are plenty of unresolved issues related to this problem in Hibernate JIRA, starting from HHH-2220.

Here is an explanation by Max Rydahl Andersen from HHH-2220's comments:

Currently Hibernate supports a kind of "automagic" mapping from SQL types to Hibernate/Java types - because of the many ambiguities in doing such mapping it will sometime not match what you actually want.

That is why we always recommend to use explicit addScalar OR if you don't want that all over your code use the subclassing of Dialect to dictate which of the multiple possible mappings do you want.

The issue with CHAR is the most problematic one, but it is not easy to fix - we would need a registerType(type, from, to, typename) to map a range instead of a specific length...but even then you could bump into mapping ambiguities (E.g. sometime you want an array other times string etc.) Hence using .addScalar is recommended for any native sql querying - depending on automatic discovery will always be risky and should only be used to a minimum.

If you have your native query described in Hibernate mappings configuration file, then you need to define <return-scalar ...> for each value returned. Note: You have to enumerate all returned values, as when you define the return types explicitly, autodiscovery is switched off and only declared columns are returned.

<sql-query name="myQuery">
    <query-param name="days" type="int" />
    <return-scalar column="count" type="int" />
    <return-scalar column="section_name" type="string" />
    <![CDATA[select count(id) as count, section_name from document where days <= :days]]>
</sql-query>

Comments

[A.W.]

Yep, that's it. Found the according jira issue for this feature. opensource.atlassian.com/projects/hibernate/browse/HHH-2304

[kommradHomer]

i have a column as char(10). and i get error when i try cast ( ..as VARCHAR) . it works fine when i use cast(.... as CHAR)

[Stefano Scarpanti]

I have a column CHAR(1) and when using hibernate and simple createQuery it can be handled correctly by hibernate into string, but when I am using createNativeQuery, so skipping hibernate engine, I have to adopt your suggestion in the query: select cast(my_flag as VARCHAR2(1)). It works

[Eagle]

Incredible. I'm using Hibernate 3.2 and had this issue while trying to select a "literal": select 'Hello' from dual (Oracle) gets simply 'H', what a joke!! The unique solution in my case was apply .addScalar, none of the CAST options worked for me.