Hibernate – dynamic-insert attribute example

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 the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
13 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
14 Comment authors
kiranyadav245Ian RobertsonDamienArunAjith Recent comment authors
newest oldest most voted
kiranyadav245
Guest
kiranyadav245

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.

Ian Robertson
Guest
Ian Robertson

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… Read more »

Damien
Guest
Damien

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 ?

Arun
Guest
Arun

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)

Ajith
Guest
Ajith

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… Read more »

oscar
Guest
oscar

now for Hibernate 4.0+ only works @DynamicUpdate and @DynamicInsert

Vikram
Guest
Vikram

If there is performance issue, why they did not have it as default true?????????

Selman
Guest
Selman

Thanks for the tutorials. Great work

Stephane
Guest
Stephane

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(“mitt@yahoo.se”);
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(“mitt@yahoo.se”);
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.

college dating rules
Guest
college dating rules

Can’t think of better ways to write this post , did a great job with this topic.

trackback
Hibernate Tutorials | Tutorials

[…] dynamic-insert attribute example Using dynamic-insert to avoid the include unmodified properties in the SQL INSERT statement. […]

Amit
Guest
Amit

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.

trackback
Hibernate – dynamic-update attribute example | Hibernate

[…] Hibernate – dynamic-insert attribute […]