org.springframework.dao.DataIntegrityViolationException 引起：java.sql.BatchUpdateException：数据截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30492118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
org.springframework.dao.DataIntegrityViolationException Caused by: java.sql.BatchUpdateException: Data truncation
提问by JavaJoe
I have a running java batch process with the following:
我有一个正在运行的 java 批处理，其中包含以下内容：
ApplicationContext:
应用上下文：
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
<property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver"/>
<property name="url" value="${example.connection.url}"/>
<property name="username" value="${example.connection.username}"/>
<property name="password" value="${example.connection.password}"/>
<property name="maxActive" value="100"/>
<property name="maxWait" value="1000"/>
</bean>
DAOImpl:
DAOImpl:
public void batchInsertUsers(final List<UserInfo> userDataList) {
logger.info("userList size::"+userDataList.size());
try{
jdbcTemplate.batchUpdate(insertUserQuery,
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
java.util.Date date= new java.util.Date();
UserInfo userData = userDataList.get(i);
ps.setString(1, userData.getUid());
ps.setString(2, userData.getFirstName());
ps.setString(3, userData.getMiddleName());
ps.setString(4, userData.getLastName());
ps.setTimestamp(5, null);
ps.setString(6, "");
ps.setTimestamp(7, null);
ps.setBoolean(8, false);
ps.setString(9, null);
ps.setString(10, userData.getUserID());
ps.setTimestamp(11,new Timestamp(date.getTime()));
userData = null;
}
public int getBatchSize() {
return userDataList.size();
}
} );
}catch(Exception e){
logger.error("Exception occured while inserting in Users_Temp table::",e);
logger.error(e);
e.printStackTrace();
}
}
insertUserQuery=INSERT INTO USERS_temp(uid, FirstName, MI, LastName, SyncDate, Status, DisabledDateTime, Exclude, Notes, UpdateID, UpdateDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?)
insertUserQuery=INSERT INTO USERS_temp(uid, FirstName, MI, LastName, SyncDate, Status, DisabledDateTime, Exclude, Notes, UpdateID, UpdateDateTime) VALUES (?,?,?,?,?,?,?,?,?,?, ?)
It runs fine everytime but once in a while it stops with below exception and I am clueless because it doesn't say anything about any data size exceeding allowed column size or violation of any constraint. Below is the exception trace:
它每次都运行良好，但有时它会因以下异常而停止，我一无所知，因为它没有说明任何超过允许的列大小或违反任何约束的数据大小。以下是异常跟踪：
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO USERS_temp(uid, FirstName, MI, LastName, SyncDate, Status, DisabledDateTime, Exclude, Notes, UpdateID, UpdateDateTime) 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.doInPreparedStatement(JdbcTemplate.java:899)
at org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
... 5 more
Its a batch process, so there are thousands of records going into DB. Has anyone come across similar situation? If yes, please shed some light.
它是一个批处理过程，因此有数千条记录进入数据库。有没有人遇到过类似的情况？如果是，请说明一下。
Table structure
column name data type allow nulls
UID char(9) N
FirstName varchar(50) N
MI varchar(1) Y
LastName varchar(50) N
SyncDate datetime Y
Status char(1) N
DisabledDateTime datetime Y
Exclude bit N
Notes varchar(250) Y
UpdateID varchar(10) N
UpdateDateTime datetime N
UID is primary key and is indexed.
UID 是主键并被索引。
采纳答案by Tobb
You are trying to insert (probably) a Stringwith a length that exceeds the corresponding columns capacity. Either log the information about the user that you try to insert, or validate that all the data to be input is in correspondance with the size of the columns in the database. (If I were to guess, I would say that the middle name is the most likely culprit, as it only has room for 1 character.)
您正在尝试插入（可能）String长度超过相应columns 容量的 a 。要么记录有关您尝试插入的用户的信息，要么验证要输入的所有数据是否与数据库中的列大小相符。（如果我猜的话，我会说中间名是最有可能的罪魁祸首，因为它只有 1 个字符的空间。）
As an aside, read this: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
顺便说一句，请阅读：http: //www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
As another aside, the world's longest name consists of over 600 characters.
另一方面，世界上最长的名字由 600 多个字符组成。
