Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 251
FrVaBe Avatar asked Dec 22 '10 11:12

FrVaBe


People also ask

How does JPA handle NULL values?

The JPA specification defines that during ordering, NULL values shall be handled in the same way as determined by the SQL standard. The standard specifies that all null values shall be returned before or after all non-null values. It's up to the database to pick one of the two options.

Is NULL in JPQL?

Following example shows how to use IS NULL to find properties values which have not been set.

How hibernate handle NULL values?

The best way to avoid Hibernate's attempts at setting null values to primitives is to use Wrapper classes (Integer, Long, Double...); and especially, if you need to tack on a column or 2 to an existing table.

Why NULL values are inserted in database?

When you execute the INSERT statement, the database server inserts a NULL value into any column for which you provide no value, as well as for all columns that have no default values and that are not listed explicitly.


2 Answers

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.

like image 127
bert Avatar answered Sep 24 '22 11:09

bert


Using the annotation

@Column(insertable = false) 

will prevent the value being generated in the sql.

like image 40
OrangeDog Avatar answered Sep 22 '22 11:09

OrangeDog