Why does not Hibernate set @DynamicInsert by default

Question

Could anyone explain to me why Hibernate does not set the @DynamicInsert annotation by default and allow entities to generate an INSERT based on the currently set properties?

What's the reason for not using @DynamicInsert and, therefore, including all entity properties by default?

Solution 1

What @jb-nizet said.

Also, dynamic-insert="true" is a bad idea in my book.

Skipping null fields from the generated SQL, soon you will find yourself in a situation where you will be declaring columns not null default which in fact causes the persistent data to be different than the entity data that hibernate knows about. This will cause frustration and likely make you resort to expensive session.refresh() calls.

For example suppose column

MESSAGE varchar(64) not null default ''

and you save an entity with null value for the property that is mapped to this column.

With dynamic-insert you will end up with an entity that has null value for the property message in-memory while at the same time the corresponding database row has ''.

I hope I made sense. Especially if you are considering dynamic-insert for such a scenario (to get away from having to set all properties for not null columns and rely on a default constraint), think again.

Solution 2

The dynamic inserts and updates are very useful for two reasons:

• reducing OptimisticLockException false positives on non-overlapping write-concern property sets
• avoiding some processing overhead related to updating indexes

However, the dynamic insert/update have drawbacks as well:

• you cannot reuse server-side and client-side prepared statements
• it reduces the likelihood of benefiting from batch updates] since the statement might change from one entity to the other.

So, there is no good way or bad way. It just depends on your data access patterns to figure out which of these two makes sense for a given entity.

Solution 3

Because it doesn't add any significant performance improvement to skip null fields when inserting an entity.

To the contrary, it can decrease performance because, instead of being able to always use the same prepared statement to insert a row in a table, and be able to execute several inserts into a single batch, Hibernate must create a specific prepared statement for each entity to insert, which depends on the fields that are null and the fields that are not.

So, more complexity, for a reduced performance.

Comments

[Stefan Steinegger]

It is not only about prepared statements. It is also about the query cache in Sql Server (and probably other DBMSs). It remembers queries if they are exactly the same and doesn't compile again. If the insert is different every time, it has to compile it again and it fills up its "most frequently used" cache making it unusable.

[JB Nizet]

Agreed. You lose the performance benefits of prepared statements both at the Java side and at the DB side.

[Venkatesh]

Than what is the use of dynamic insert=true.In which type requirement it will come into picture.

[JB Nizet]

AFAIK, it's only useful if you really want to let the database generate default values if nothing is inserted, without defining this default value in the Java code. But this has the dangers described in dkateros's answer.

[Venkatesh]

Thnk u for ur replaying.Could you please tell me what value will going to store if I make my field as not null in my entity and making Dynamic-insert=true.This sentence is not understandable(With dynamic-insert you will end up with an entity that has null value for the property message in-memory while at the same time the corresponding database row has ''.)

[dkateros]

If the entity instance has a property with a null value the corresponding column will not be added to the SQL with dynamic-insert="true" when saving. If this column is declared as 'not null' in the database you will get a database error. However, if it is 'not null default' you will get no database error and the column will be written with the default value. This value will not be the same with the saved entity instance. This is problematic and you will have to synchronize the hibernate session and the db manually.

[Venkatesh]

if I use dynamic insert=true at the time if one property not setting any null values if I save entity instance without setting value to that property. By default that property will store default value based on data-type is it Correct.

[dkateros]

only if you defined a default value for the column during the table creation