Oracle converts an empty string to zero, but JPA does not update the object cache accordingly

Question

It is well known that Oracle treats empty strings as null.

However, I had a problem due to this behavior due to JPA caching.

First, I keep using a JPA (Toplink Essentials) object that has an empty string as a single field. Oracle converts this value to null when it is stored.

However, when I retrieve the object, the JPA seems to return it from the cache, where this field is still an empty string. JPA does not seem to know that what was written to the database was actually a null value, and this incoherence is causing problems.

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

Answer 1

We have moved to EclipseLink, which supports @ReturnInsert and @ReturnUpdate annotations. They help update the field with the value that was actually stored in the database.

Answer 2

I think the problem is that Oracle does not save the string as a null value, it stores it as an "unset" -value (let it be called super-null). therefore, if you try to select values ​​that are zero, you will not retrieve this element because it is not set and is not equal to zero. did you try to insert specifically null, not an empty string?

I had a project once - where I had a similar problem, when the solution was to store the specific null value in the database column, not in string.empty, because it was considered as not specified, and not as null ...