Oracle converts empty string to null but JPA doesn't update entity cache correspondingly

Question

It's a well known fact, that Oracle treats empty strings as null.

However, I'm having an issue because of this behaviour due to JPA's caching.

First I persist using JPA (Toplink Essentials) an entity, which has an empty string as one field. Oracle converts this value to null when it stores it.

However, when I fetch the entity, JPA seems to return it from the cache, where this field is still an empty string. JPA doesn't seem to know, that what got stored into the database was actually a null value, and this incoherency is causing problems.

Is there a way to solve this issue on the JPA or application server (Oracle AS) configuration level? This something that I would not want to fix in the application level (but will do if it's necessary).

Answer 1

I think the problem is that Oracle does not store the string as a null value it stores it as an "unset"-value (let's call it super-null). so if you try to select the values that are null you will not retrieve that item because it is unset and not null. have you tried to insert specifically null and not an empty-string?

I had a project once - where I had a similar problem where the solution was to store specifically null into the database-column and not string.empty since it was treated as not set and not as null...

Answer 2

We have migrated to EclipseLink, which supports @ReturnInsert and @ReturnUpdate annotations. These help to refresh the field with the value that actually got stored into the database.