Hibernate and padding on CHAR primary key column in Oracle

Question

I'm having a little trouble using Hibernate with a char(6) column in Oracle. Here's the structure of the table:

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".

select * from acceptance where user_id = "abc"

Unfortunately, when doing the select via Hibernate (JPA), the following returns 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)

Answer 1

I don't think so. You can define a UserType to contain the damage to one spot, though.

Answer 2

That's God's way of telling you to never use CHAR() for primary key :-)

Seriously, however, since your user_id is 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).

Based on Oracle type conversion rules column value is then promoted to varchar2 and compared as such; thus you get back no records.

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.

Answer 3

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.

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

• always receive 6 chars from the other parts of the module
• use varchar2 to deal with dynamic size correctly

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)