[SOLVED] Avoid insert 'null' values to database table via JPA

Avoid insert 'null' values to database table via JPA

im starting with JPA2 and feel quite comfortbale so far. But I have a problem when persisting Entities with null property values for NON NULL database fields with default value.

I would like to be able to leave the entity property null and let the database insert the default value.

My current setup is openJPA with PostgreSQL.

I have this VERSION database table (Vorgabewert = Default value):


     Spalte     |             Typ             |         Attribute
----------------+-----------------------------+----------------------------
 status_        | smallint                    | not null Vorgabewert 0
 time_          | timestamp without time zone | not null
 system_time    | timestamp without time zone | not null Vorgabewert now()
 version        | character varying(20)       | not null
 activationtime | timestamp without time zone |
 importtime     | timestamp without time zone |

I have an entity (Java DTO) which maps the database fields (except 'status') by xml configuration.

I hoped I could insert an entity without the system_time set and expected that the database will fill the current time as default value.

JPA constructs the following SQL-Query:

INSERT INTO public.version (version, activationtime, importtime, system_time, time_) VALUES (?, ?, ?, ?, ?) [params=?, ?, ?, ?, ?]

and Postgres reacts with:

FEHLER: NULL-Wert in Spalte »system_time« verletzt Not-Null-Constraint (sorry for German language but this message means the Not-Null-Constraint violation on 'system_time').

So what can I do? Is this a JPA or Database Problem. Can I configure JPA to exclude null properties from the INSERT SQL Statement.

I want to have the ability to set the 'system_time' in my entity or to let it be 'null' and let the database put the default value.

Any help is welcome!

Regads Klaus

Solution

I would not rely on default values in the database in conjunction with JPA. You would have to read the entity back after the insert otherwise you have a mismatch between the entity state and the db state.

Choose the pragmatic approach here and initialise all values in java. Never heard of a way to tell JPA/Hibernate to leave out null values in an insert/update.