What is dynamic-insert
The dynamic-insert attribute tells Hibernate whether to include null properties in the SQL INSERT statement. Let explore some examples to understand more clear about it.
Dynamic-insert example
1. dynamic-insert=false
The default value of dynamic-insert is false, which means include null properties in the Hibernate’s SQL INSERT statement.
For example, try set some null values to an object properties and save it.
StockTransaction stockTran = new StockTransaction();
//stockTran.setPriceOpen(new Float("1.2"));
//stockTran.setPriceClose(new Float("1.1"));
//stockTran.setPriceChange(new Float("10.0"));
stockTran.setVolume(2000000L);
stockTran.setDate(new Date());
stockTran.setStock(stock);
session.save(stockTran);
Turn on the Hibernate “show_sql” to true, you will see the following insert SQL statement.
Hibernate:
insert
into
mkyong.stock_transaction
(DATE, PRICE_CHANGE, PRICE_CLOSE, PRICE_OPEN, STOCK_ID, VOLUME)
values
(?, ?, ?, ?, ?, ?)
Hibernate will generate the unnecessary columns (PRICE_CHANGE, PRICE_CLOSE, PRICE_OPEN) for the insertion.
2. dynamic-insert=true
If set the dynamic-insert to true, which means exclude null property values in the Hibernate’s SQL INSERT statement.
For example, try set some null values to an object properties and save it again.
StockTransaction stockTran = new StockTransaction();
//stockTran.setPriceOpen(new Float("1.2"));
//stockTran.setPriceClose(new Float("1.1"));
//stockTran.setPriceChange(new Float("10.0"));
stockTran.setVolume(2000000L);
stockTran.setDate(new Date());
stockTran.setStock(stock);
session.save(stockTran);
Turn on the Hibernate “show_sql” to true. You will see the different insert SQL statement.
Hibernate:
insert
into
mkyong.stock_transaction
(DATE, STOCK_ID, VOLUME)
values
(?, ?, ?)
Hibernate will generate only the necessary columns (DATE, STOCK_ID, VOLUME) for the insertion.
Performance issue
In certain situations, such as a very large table with hundreds of columns (legacy design), or a table contains extremely large data volume, insert something not necessary definitely will drop down your system performance.
How to configure it
You can configure the dynamic-insert properties value through annotation or XML mapping file.
1. Annotation
@Entity
@Table(name = "stock_transaction", catalog = "mkyong")
@org.hibernate.annotations.Entity(
dynamicInsert = true
)
public class StockTransaction implements java.io.Serializable {
2. XML mapping
<class ... table="stock_transaction" catalog="mkyong" dynamic-insert="true">
<id name="tranId" type="java.lang.Integer">
<column name="TRAN_ID" />
<generator class="identity" />
</id>
Conclusion
This little “dynamic-insert” tweak may increase your system performance, and highly recommends to do it. However, one question in my mind is why Hibernate set it to false by default?
Follow up
1. Hibernate – dynamic-update attribute example
About Author
how it will be performance issue if dynamic-insert is false?
if dynamic-insert= false,
1. single query for n number of records
2. no null checks
if dynamic-insert= true
1. every property value checked in entity
2. every time query framed based on the values.
now for Hibernate 4.0+ only works @DynamicUpdate and @DynamicInsert
One reason that you might not want to set dynamic-insert to true is that for some databases (such as Oracle), each unique query takes up resources in the database. If you have a table with 20 columns, each of which could be present or not, that could yield as many as 1 million unique queries, which would certainly overflow any cache. By having a single prepared statement, it increases the likelihood that no new “hard parses” of the query will need to be performed by the database.
In contrast, sending null columns over the wire usually doesn’t make much of a difference in performance; often they end up fitting in the same TCP packet, and have minimal impact.
If performance is a concern, the best route is to measure the performance of both options. There are too many moving parts to be able to predict what will happen from theory alone.
I still have the problem.
@DynamicInsert seems not working with your settings
http://stackoverflow.com/questions/6328613/how-to-set-boolean-value-in-hibernate/17589200#17589200
Any tip ?
Hi
I have mapped dynamic-insert=”true” and actualElementAmount, remainingAmount are not set.
Why it is checking for checkNullability?
It should generate
insert into ChargeElement(term) values (1)
If i am missing something?
//test
ChargeElementDo chargeElementDo = new ChargeElementDo();
chargeElementDo.setTerm(1);
sess.saveOrUpdate(chargeElementDo);
Note: actualElementAmount and RemainingAmount is not null and default zero in sql server 2005
org.hibernate.PropertyValueException: not-null property references a null or transient value: my.hibernate.study.domain.ChargeElementDo.actualElementAmount
at org.hibernate.engine.Nullability.checkNullability(Nullability.java:72)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:290)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)
at my.hibernate.study.test.ChargeElementTest.main(ChargeElementTest.java:28)
We had the same question, too on why hibernate defaults dynamic_update and dynamic_insert to false. After close analysis, we found that the AbstractBatcher batch the SQLs based on the parameters in the insert/update. If you use dynamic insert/update false, it will batch everything for one table and there will be only that many DB hits as the number of tables that you are updating or inserting into. Else the number of batches will increase depending on how the parameters on these are. For example, if you have two different update statements in the same table, that updates two different fields on it, setting dynamic update to true will cause hibernate to sent two different update scripts in two batches, and setting it to false will cause hibernate to send both them in one batch!!!
If there is performance issue, why they did not have it as default true?????????
Thanks for the tutorials. Great work
For a MySql schema with 100 tables, each having some NOT NULL DEFAULT ” attributes, is it better to use dynamic insert true, or to make sure the persisted object contains empty strings ?
With the dynamic-insert attribute set to true, the object can be created and persisted like:
User user = new User();
user.setEmail(“[email protected]”);
user.setPassword(“apassword”);
user = userDao.makePersistent(user);
But with the dynamic-insert attribute set to false, the object must be created and persisted like:
User user = new User();
user.setEmail(“[email protected]”);
user.setPassword(“apassword”);
user.setFirstname(“”);
user.setLastname(“”);
user.setCompany(“”);
user = userDao.makePersistent(user);
In the second case, all the object properties need to be explicitly set to some non null values.
Can’t think of better ways to write this post , did a great job with this topic.
good article explaining the basics. I tried to read more about why the default setting for dynamic-insert & dynamic-update is false and I found that setting the property to true could degrade the performance in some cases. Here’s the extract from Hibernate in action
“Enabling either of these settings will cause Hibernate to generate some SQL at runtime, instead of using the SQL cached at startup time. The performance cost is usually small.”
This could be one of the reasons why the default setting is false.
“setting the property to true could degrade the performance in some cases”…
It’s just doesn’t make sense? The dynamic-insert=true will just ignore the unnecessary columns, how could it generate addition some SQLs statement at runtime? May be caused by Hibernate internal mechanism?
Did ‘Hibernate in action’ mention in what cases or what addition SQLs statement will generated at runtime?
Consider this case: You have an entity with
String a;
String b;
String c;
with dynamic-insert=true, it will generate and cache a single insert statement. But if we have dynamic-insert=false, it has to generate a new SQL insert statement for each case of one of the fields null (as many as 7=2^8-1 additional cases)
Prepared statements, maybe?