Hibernate, JPA

[Solved] java.sql.BatchUpdateException: Data truncation

Leave a comment

BatchUpdateException is sub class of SQLException. BatchUpdateException occurred when any queries fail during batch update. BatchUpdateException also return the values of counts for each queries update before queries fail.

A SQL queries can fail because of  data constraints violations or syntax issues etc.

Below is some common situation where ConstraintViolationException occurs:

  • Insert a record with primary key as null.
  • Insert/ Update primary key value is not unique.
  • Insert data value size is more than predefine column size.
  • The inserted record data type is not compatible with predefined column type.

To see a complete list of Database integrity constraints follow this link :

Database Integrity Constraints List

BatchUpdateException Example

Here this exception is occurring because inserting  value of title  with size more than predefined as 100 characters.

@Entity
public class Publication {
	@Id
	@GeneratedValue
	protected Long id;

	@NotNull
	@Size(max = 100)
	@Column(unique = true)
	private String title;

	private int year;
	//getter and setter methods
	}

Exception Stacktrace

 
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO PUBLICATIONS(id, title, year) VALUES (?,?,?)]; Data truncation; nested exception is java.sql.BatchUpdateException: Data truncation
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
        at com.example.UserDAOImpl.batchInsertUsers(UsersDAOImpl.java:175)
        at com.example.UserServiceImpl.processData(UserServiceImpl.java:184)
        at com.example.UserMain.main(UserMain.java:23)
Caused by: java.sql.BatchUpdateException: Data truncation
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899)
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)

Solutions

As title column configured is unique with size of 100 characters. Because here inserting value for title as more than 100 characters that’s what causing issues  and throwing DataIntegrityConstraintViolationException and BatchUpdateException but main cause of this exception is data truncation.

To resolve this issue pass title column value as less than 100 characters or increase size of column.

You would like to see

Follow below link to see more JDBC, Hibernate and JPA issues solutions.