java - hibernate - 错误 : Data truncation: Incorrect datetime value

Question

我正在使用这段代码将数据从一个表插入到另一个表，

Session session = HibernateUtil.getSessionFactory().openSession();
session.createSQLQuery(
                "INSERT INTO news_statistics (crime_type, crime_district, crime_date, crime_year, crime_yearquarter, crime_count) " +
                        "(SELECT crime_type,(SELECT district FROM location_district_mapper ldm WHERE ceg.location = ldm.location) , crime_date, YEAR (crime_date), CONCAT(YEAR (crime_date), ' - ', QUARTER(crime_date)), count(id) " +
                        "FROM crime_entity_group ceg " +
                        "WHERE crime_date >= '2012-01-01' AND crime_date <= '2014-12-31' " +
                        "GROUP BY crime_type, district, crime_date " +
                        "ORDER BY YEAR ('crime_date'))").executeUpdate();

这会引发错误，

ERROR: Data truncation: Incorrect datetime value: 'crime_date'
Exception in thread "main" org.hibernate.exception.DataException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:69)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
    at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:211)
    at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1310)
    at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:389)
    at com.cse10.analyzer.StatGenerator.generateStats(StatGenerator.java:22)
    at com.cse10.analyzer.Analyzer.main(Analyzer.java:13)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'crime_date'
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3556)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2643)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    ... 10 more

但是当我直接使用 phpmyadmin 运行查询时，查询运行良好，没有显示任何错误......!

当我只运行查询的 select 部分并使用 list() 列出结果时，它没有给出任何错误。因此，我可以假设问题出在查询的 insert 部分。当我从查询中删除 crime_date 时，查询运行时没有显示任何错误。

两个表中crime_date的类型都是date，并且在两个hibernate xml文件中定义如下。

<property name="crimeDate" type="date">
        <column name="crime_date" not-null="false"/>
</property>

模式是，

CREATE TABLE IF NOT EXISTS `crime_entity_group` (
  `id` int(11) NOT NULL,
  `crime_article_id` int(11) DEFAULT NULL,
  `crime_type` varchar(50) DEFAULT NULL,
  `crime_date` date DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  `district` varchar(20) DEFAULT NULL,
  `police` varchar(50) DEFAULT NULL,
  `court` varchar(50) DEFAULT NULL,
  `criminal` varchar(200) DEFAULT NULL,
  `victim` varchar(50) DEFAULT NULL,
  `victim_count` int(11) DEFAULT NULL,
  `possession` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `news_statistics` (
  `id` int(11) NOT NULL,
  `crime_type` varchar(50) DEFAULT NULL,
  `crime_district` varchar(20) DEFAULT NULL,
  `crime_date` date DEFAULT NULL,
  `crime_year` varchar(10) DEFAULT NULL,
  `crime_yearquarter` varchar(20) DEFAULT NULL,
  `crime_count` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

有人知道解决方案吗？

提前致谢...

Answer 1

您需要确保 news_statistics.crime_date 和 crime_entity_group.crime_date 具有相同的 SQL 类型(例如 DATE、TIME、TIMESTAMP、DATETIME)。

如果一个是 DATE，另一个是 DATETIME，您的语句将因为违反类型不匹配约束而失败。

来自您的异常消息:

Incorrect datetime value: 'crime_date'

news_statistics.crime_date 似乎是 DATETIME 列，而 crime_entity_group.crime_date 可能是 DATE。