Hibernate本地查询 - char（3）列 [英] Hibernate native query - char(3) column

问题描述

我在Oracle中有一个表，其中列SC_CUR_CODE是CHAR（3）

当我这样做时：

 查询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]）; 
} 
  

我看到 cur = E 和 cur = U 而不是 cur = EUR 和 cur = USD
$ o [0] 是一个java.lang.Character

如何获得完整值 EUR 和 USD ？

解决方案

它看起来像Hibernate读取类型 CHAR（n）的值为字符。尝试将它转换为 VARCHAR（n）：

$ p $ 查询q2 = em.createNativeQuery（
select cast（sc_cur_code as VARCHAR2（3）），sc_amount from sector_costs）;


当通过 Session 界面使用Hibernate时，可以使用 addScalar（）来明确地设置结果类型（也可以通过JPA 2.0中的 unwrap（）访问）：

 查询q2 = em.createNativeQuery（
select sc_cur_code，sc_amount from sector_costs）; 
 q2.unwrap（SQLQuery.class）.addScalar（sc_cur_code，StringType.INSTANCE）; 
  

Hibernate JIRA中存在大量未解决的问题，从 HHH-2220 。

这里是Max Rydahl Andersen对HHH-2220的评论的解释：

目前Hibernate支持一种从SQL类型到Hibernate的automagic映射/ Java类型 - 因为在做这样的映射时存在许多含糊不清的地方，所以它有时候与实际需要的不匹配。

这就是为什么我们总是推荐使用显式的addScalar OR if你不希望所有的代码都使用方言的子类来决定你想要多个可能的映射中的哪一个。

CHAR的问题是最有问题的一个，但它不容易修复 - 我们需要一个registerType（type，from，to，typename）来映射范围而不是特定的长度......但是ev那么你可能会遇到映射歧义（例如，有时你想要一个数组，其他时间字符串等）因此，使用.addScalar推荐用于任何原生的SQL查询 - 取决于自动发现总是有风险的，应该只用于最低限度。

< blockquote>

如果您在Hibernate映射配置文件中描述了本地查询，那么您需要定义< return-scalar ...> 为每个返回的值。注意：您必须枚举所有返回值，因为当您明确定义返回类型时，autodiscovery将关闭，并且只返回已声明的列。

 < sql-query name =myQuery> 
< query-param name =daystype =int/> 
< return-scalar column =counttype =int/> 
< return-scalar column =section_nametype =string/> 
<！[CDATA [select count（id）as count，section_name from document where days< =：days]]> 
< / sql-query> 
  

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 ?

解决方案

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>

这篇关于Hibernate本地查询 - char（3）列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！