Using @SQLInsert to insert entity in Hibernate with custom SQL


I have to work with legacy database with tables having literally 10s of columns that are not needed by the system I am developing. It is fine when all I have to do is to read the data. I just map the entity with the columns I need with Hibernate and ignore the rest. Inserting an entity on the other hand posts a problem if the column is declared non nullable and a default is not set in the database schema. As the non-nullable column is not known to Hibernate, the insert SQL statement generated when saving an entity will not assign any value to the column and a SQL error will result.


There are 3 solutions I can think of to handle the above:

(1) Modify the database scheme to either remove unwanted columns or change column  definition to set a default value if it is null

(2) Add Hibernate mapping to the non nullable columns and set default values in entity.

(3) Modify SQL insert statement in Hibernate.

Solution (1) is not available to me as the database schema cannot be modified. Solution (2) may work for others if only a handful of columns need to be mapped. In my case I have too many unmapped columns needed to be added to make this solution practical. I will explain in rest of this blog on how to solve this problem with solution (3).


Hibernate supports using customer SQL for create, update and delete operations. See the documentation for more details. For example, I have the following entity

public class Share {

private String code;

private String name;

private BigDecimal price;

By default, Hibernate will generate the following insert statement:

insert into share (name, price, code) values (?, ?, ?)

If the underlying table share has another column, e.g.  dummy, which is non nullable, the above SQL will not work and you will not be able to insert a new object into the database. Hibernate will throw the following exception:

Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into share (name, price, code) values (?, ?, ?)]; SQL state [HY000]; error code [1364]; Field ‘dummy’ doesn’t have a default value; nested exception is java.sql.BatchUpdateException: Field ‘dummy’ doesn’t have a default value

To fix the above error, add the custom SQL using @SQLInsert:

@SQLInsert(sql=”Insert into share (name, price, code, dummy) value (?, ?, ?, ”)”, check=ResultCheckStyle.COUNT)
public class Share {

private String code;

Now Hibernate will use the custom SQL and will insert a default value (empty string) into the dummy column.

Note the order of the fields in the SQL is important. To find the correct order used by Hibernate, turn on log4j for the persister, e.g.

// file

to get the following messages:

DEBUG [main] entity.AbstractEntityPersister (     – Static SQL for entity: com.rlee.myapp.domain.Share
DEBUG [main] entity.AbstractEntityPersister (     –  Version select: select code from share where code =?
DEBUG [main] entity.AbstractEntityPersister (     –  Snapshot select: select share_.code, as name3_, share_.price as price3_ from share share_ where share_.code=?
DEBUG [main] entity.AbstractEntityPersister (     –  Insert 0: insert into share (name, price, code) values (?, ?, ?)
DEBUG [main] entity.AbstractEntityPersister (     –  Update 0: update share set name=?, price=? where code=?
2012-01-07 11:41:33,361 DEBUG [main] entity.AbstractEntityPersister (     –  Delete 0: delete from share where code=?

Make sure the field orders in the @SQLInsert is the same as that in the log message.

Hibernate also supports customer SQL for update and delete with @SQLUpdate and @SQLDelete respectively.

About Raymond Lee
Professional Java/EE Developer, software development technology enthusiast.

Comments are closed.