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

问题描述

我在 Oracle 中有一个表，其中 SC_CUR_CODE 列是 CHAR(3)

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

当我这样做时:

    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]);
    }

我看到 cur=E 和 cur=U 而不是 cur=EUR 和 cur=USD

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

o[0] 是一个 java.lang.Character

o[0] is a java.lang.Character

我怎样才能获得EUR 和USD 的全部价值?

How can I get the full value EUR and USD ?

推荐答案

看起来 Hibernate 将 CHAR(n) 类型的值读取为 Character.尝试将其转换为 VARCHAR(n):

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");  

当通过 Session 接口使用 Hibernate 时，您可以使用 addScalar() 显式设置结果类型(也可以通过 unwrap() 访问)> 在 JPA 2.0 中):

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);

Hibernate JIRA 中有大量与此问题相关的未解决问题，从 开始HHH-2220.

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

以下是 Max Rydahl Andersen 对 HHH-2220 评论的解释:

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

目前 Hibernate 支持一种自动"功能.从 SQL 类型映射到 Hibernate/Java 类型 - 由于在进行此类映射时存在许多歧义，因此有时与您实际想要的不匹配.

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.

这就是为什么我们总是建议使用显式 addScalar 或者如果您不希望在整个代码中使用 Dialect 的子类来指示您想要多个可能映射中的哪一个.

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.

CHAR 的问题是最有问题的，但它并不容易解决 - 我们需要一个 registerType(type, from, to, typename) 来映射一个范围而不是一个特定的长度......但即便如此您可能会遇到映射歧义(例如，有时您想要一个数组，其他时候是字符串等.)因此，建议对任何本机 sql 查询使用 .addScalar - 依赖于自动发现总是有风险的，并且应该只使用到最低限度.

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.

如果您在 Hibernate 映射配置文件中描述了您的本机查询，那么您需要为每个返回的值定义 .注意:您必须枚举所有返回的值，因为当您明确定义返回类型时，自动发现将关闭，并且只返回声明的列.

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屋！