entityManager.find() returns null

[tboswell]

I am using JBoss 4.0.4-GA with Oracle XE. I have two tables which have strings for their PK and the PK column is named the same as the table:

CREATE TABLE CURRENCY
(
 CURRENCY CHAR(8 CHAR) NOT NULL,
 ...
)

CREATE TABLE LANGUAGE
(
 LANGUAGE CHAR(8 CHAR) NOT NULL,
 ...
)

This is not ideal I know but it was what I was given to work with.

I find that when using the corresponding entity beans if I use the code:

entityManager.find(Language.class, "en");

this will return me a null value despite the row existing.

This is not a problem when using other entity manager functions A query like

entityManager.createQuery("from Language l").getResultList();

will run fine and result all the expected results.

If I call the same code from a test case which is wrapped within a single transaction, I can create an object, insert it and run the same code within the transaction and find it OK.
Only when I am trying to find records already existing outside the transaction do I get the problem.

With DEBUG I have checked the query that Hibernate runs and this runs fine in Oracle but still returns a null to me within my session bean code.

select language0_.LANGUAGE as LANG1_73_0_, language0_.NAME as NAME73_0_, language0_.SHORT_NAME as SHORT3_73_0_, language0_.CREATED as CREATED73_0_, language0_.INSERTED as INSERTED73_0_, language0_.MODIFIED as MODIFIED73_0_, language0_.ENABLED as ENABLED73_0_, language0_.ENABLED_DATE as ENABLED8_73_0_, language0_.COMMENTS as COMMENTS73_0_, language0_.TESTED as TESTED73_0_, language0_.SUPPORTED as SUPPORTED73_0_, language0_.TESTED_DATE as TESTED12_73_0_, language0_.SUPPORTED_DATE as SUPPORTED13_73_0_, language0_.DEFAULT_TO_LANGUAGE as DEFAULT14_73_0_ from LANGUAGE language0_ where language0_.LANGUAGE='en';

I have tried several permutations of changing the table and PK column names but to no avail. For example change the PK name I get this query

select language0_.LANG_CODE as LANG1_73_0_, language0_.NAME as NAME73_0_, language0_.SHORT_NAME as SHORT3_73_0_, language0_.CREATED as CREATED73_0_, language0_.INSERTED as INSERTED73_0_, language0_.MODIFIED as MODIFIED73_0_, language0_.ENABLED as ENABLED73_0_, language0_.ENABLED_DATE as ENABLED8_73_0_, language0_.COMMENTS as COMMENTS73_0_, language0_.TESTED as TESTED73_0_, language0_.SUPPORTED as SUPPORTED73_0_, language0_.TESTED_DATE as TESTED12_73_0_, language0_.SUPPORTED_DATE as SUPPORTED13_73_0_, language0_.DEFAULT_TO_LANGUAGE as DEFAULT14_73_0_ from LANGUAGE language0_ where language0_.LANG_CODE='en';

But the same result.

Is there something I am doing here that Hibernate doesn't expect or is there a known bug?


Thanks,

Tom Boswell

[app4you]

Hi Tom,

I can only suggest that use Number, in Oracle, as primary key. It's not a good practice to use character as primary key for reasons. Once you done that, make sure your entity bean has the @Id getPrimaryKey(){return primaryKey;}. They you can use the find method without a problem.

John

[tboswell]

Thanks John,

Using a NUMBER as the PK is my preference as a rule but I've been asked to do it this way and I can see know reason, looking at the EJB3 spec, it shouldn't work.

I find the differing behaviour depending on the transaction usage worrying and want to know if I should work around it (using a NUMBER column as you say) or am I doing something wrong or whether it's a bug?

Cheers

Tom Boswell

[alrubinger]

Some of my coworkers have reported similar behaviour of a sporadic nature...simple calls to EntityManager.find(Class,id) return null when the object is clearly in the DB. They've also stated that using EntityManager.createQuery("SELECT obj FROM ClassName obj WHERE obj.id=?") will sometimes return the object immediately after the finder fails.

Anyone have an easily deployable use case that reliably fails this query? Having problems believing that something as trivial as a "find by id" method has a bug.

S,
ALR

[wolfc]

What if you try:

entityManager.find(Language.class, "en ");

(en with 6 spaces)

Just a wild guess...

[epbernard]

"ALRubinger" wrote:
Some of my coworkers have reported similar behaviour of a sporadic nature...simple calls to EntityManager.find(Class,id) return null when the object is clearly in the DB. They've also stated that using EntityManager.createQuery("SELECT obj FROM ClassName obj WHERE obj.id=?") will sometimes return the object immediately after the finder fails.

Anyone have an easily deployable use case that reliably fails this query? Having problems believing that something as trivial as a "find by id" method has a bug.

S,
ALR

I find it hard to believe.

[alrubinger]

Yep, that's what I said. :)

Will try to get a use case and will post back here if indeed I can reproduce it.

S,
ALR

[tboswell]

Some of my coworkers have reported similar behaviour of a sporadic nature...simple calls to EntityManager.find(Class,id) return null when the object is clearly in the DB. They've also stated that using EntityManager.createQuery("SELECT obj FROM ClassName obj WHERE obj.id=?") will sometimes return the object immediately after the finder fails.

I tried getting the object by using a createQuery() myself and still found I was returned a null value. The behaviour I have seen with the find() function has not been sporadic but consistently failing, when the PK was a string.

entityManager.find(Language.class, "en ");

Ah, yes. This rings a bell...I seem to recall an issue with Oracle padding out char columns with spaces. I will change the column to a varchar and retest.

Thanks for the thoughts everyone.

Tom

[tboswell]

It turns out our friend wolfc is correct. Oracle will return either a CHAR(8) or VARCHAR2(8) value padded out with spaces till the end.

Only a search for:

entityManager.find(Language.class, "en ");

will return the correct row. This works fine when you are following relationships etc as the padded value is passed around.

Not sure how best to deal with this as trailing whitespace, although unlikely it is possible in a PK column...or any column you are querying (same problem surely?). So trimming the column value may be just as bad.

Any idea how to ensure consistency here? Could force the entity to always pad values as well i guess. Ultimately its Oracle's oversight I guess....

Tom.

[tboswell]

Sorry for the confusion but I have successfuly got it working with a VARCHAR2() column. First of all I set the table up like this:

CREATE TABLE CURRENCY
(
 CURRENCY VARCHAR2(8 CHAR) NOT NULL,
 ...
)

This yielded the same sorry result of padding chars.

Finally, using the following:

CREATE TABLE CURRENCY
(
 CURRENCY VARCHAR2(8) NOT NULL,
 ...
)

prevented the padding happening and I got consistent behaviour. I am not sure whether Hibernate can or should take this into account. Perhaps a warning about the use of CHAR based column types?

Tom.

[rangasamy]

Even I am also facing the same issue. I am using jboss-as-7.1.1.Final, hibernate 4.0 with Rest-easy framework. Issues is after persisting the record in DB immediately an update call is fired to update/insert child entity of the persisted object. So I am using em.find(record.class, primary key(long)) method to find the persisted record. It is returning null even though the record is exist in table. Any idea or suggestion!!!