Oracle 中 CHAR 主键列的休眠和填充
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1909483/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate and padding on CHAR primary key column in Oracle
提问by jthg
I'm having a little trouble using Hibernate with a char(6) column in Oracle. Here's the structure of the table:
我在 Oracle 中使用带有 char(6) 列的 Hibernate 时遇到了一些麻烦。这是表的结构：
CREATE TABLE ACCEPTANCE
(
USER_ID char(6) PRIMARY KEY NOT NULL,
ACCEPT_DATE date
);
For records whose user id has less than 6 characters, I can select them without padding the user id when running queries using SQuirreL. I.E. the following returns a record if there's a record with a user id of "abc".
对于用户 ID 少于 6 个字符的记录，我可以在使用 SQuirreL 运行查询时选择它们而无需填充用户 ID。如果存在用户 id 为“abc”的记录，IE 以下将返回一条记录。
select * from acceptance where user_id = "abc"
Unfortunately, when doing the select via Hibernate (JPA), the following returns null:
不幸的是，当通过 Hibernate (JPA) 进行选择时，以下返回 null：
em.find(Acceptance.class, "abc");
If I pad the value though, it returns the correct record:
如果我填充该值，它会返回正确的记录：
em.find(Acceptance.class, "abc ");
The module that I'm working on gets the user id unpadded from other parts of the system. Is there a better way to get Hibernate working other than putting in code to adapt the user id to a certain length before giving it to Hibernate? (which could present maintenance issues down the road if the length ever changes)
我正在处理的模块从系统的其他部分获取未填充的用户 ID。除了在将用户 ID 提供给 Hibernate 之前放入代码使用户 ID 适应特定长度之外，还有没有更好的方法让 Hibernate 工作？（如果长度发生变化，可能会出现维护问题）
采纳答案by ChssPly76
That's God's way of telling you to never use CHAR() for primary key :-)
这是上帝告诉你永远不要使用 CHAR() 作为主键的方式:-)
Seriously, however, since your user_idis mapped as String in your entity Hibernate's Oracle dialect translates that into varchar. Since Hibernate uses prepared statements for all its queries, that semantics carries over (unlike SQuirreL, where the value is specified as literal and thus is converted differently).
然而，说真的，因为您user_id在实体 Hibernate 的 Oracle 方言中被映射为 String 将其转换为varchar. 由于 Hibernate 对其所有查询都使用准备好的语句，因此该语义会继续存在（与 SQuirreL 不同，其中值被指定为文字，因此转换方式不同）。
Based on Oracle type conversion rulescolumn value is then promoted to varchar2and compared as such; thus you get back no records.
然后根据 Oracle类型转换规则将列值提升varchar2并进行比较；因此你没有得到任何记录。
If you can't change the underlying column type, your best option is probably to use HQL query and rtrim()function which is supported by Oracle dialect.
如果您无法更改基础列类型，最好的选择可能是使用rtrim()Oracle 方言支持的HQL 查询和函数。
回答by ewernli
How come that your module gets an unpadded value from other parts of the system?
为什么您的模块从系统的其他部分获得未填充的值？
According to my understanding, if the other part of the system don't alter the PK, they should read 6 chars from the db and pass 6 chars all along the way -- that would be ok. The only exception would be when a PK is generated, in which case it may need to be padded.
根据我的理解，如果系统的其他部分不改变 PK，他们应该从 db 读取 6 个字符并一路传递 6 个字符——那没问题。唯一的例外是生成 PK 时，在这种情况下可能需要填充它。
You can circumvent the problem (by trimming or padding the value each time it's necessary), but it won't solve the problem upfront that your PK is not handled consistently. To solve the problem upfront you must eiher
您可以规避该问题（通过在每次需要时修剪或填充该值），但它不会预先解决您的 PK 处理不一致的问题。要预先解决问题，您必须要么
- always receive 6 chars from the other parts of the module
- use
varchar2to deal with dynamic size correctly
- 始终从模块的其他部分接收 6 个字符
- 用于
varchar2正确处理动态尺寸
If you can't solve the problem upfront, then you will indeed need to either
如果您不能预先解决问题，那么您确实需要
- add trimming/padding all around the place when necessary
- add trimming/padding in the DAO if you have one
- add trimming/padding in the user type if this works (suggestion from N. Hughes)
- 必要时在整个地方添加修剪/填充
- 如果你有，在 DAO 中添加修剪/填充
- 如果可行，请在用户类型中添加修剪/填充（来自 N. Hughes 的建议）
