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

Oracle Magazine (Free)

Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world\'s largest enterprise software company.

Selman says:

May 22, 2010 at 6:34 am

Thanks for the tutorials. Great work

Stephane says:

May 19, 2010 at 6:07 pm

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 says:

March 31, 2010 at 2:06 pm

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

Hibernate Tutorials | Tutorials says:

March 31, 2010 at 8:23 am

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

Amit says:

March 6, 2010 at 6:45 am

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.

mkyong says:

March 7, 2010 at 3:35 pm

“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?

- Marco says:
  
  March 10, 2010 at 11:00 pm
  
  Prepared statements, maybe?