开发 - Cannot convert value '0000-00-00 00:00:00' from column 38 to TIMESTAMP.

最新推荐文章于 2022-11-16 19:24:43 发布

Wsqandgy

最新推荐文章于 2022-11-16 19:24:43 发布

阅读量516

点赞数 1

分类专栏：开发文章标签： mybatis 数据库

本文链接：https://blog.csdn.net/u012983826/article/details/51866922

版权

开发专栏收录该内容

8 篇文章 0 订阅

订阅专栏

### Error querying database. Cause: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 38 to TIMESTAMP.

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### Cause: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 38 to TIMESTAMP.

; SQL []; Cannot convert value '0000-00-00 00:00:00' from column 38 to TIMESTAMP.; nested exception is java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 38 to TIMESTAMP.

Exception in thread "main" java.lang.NullPointerException

at com.flush.service.impl.FrogServiceImpl.mySqlToEs(FrogServiceImpl.java:89)

at com.flush.service.impl.FrogServiceImpl.selectMySqlInfoToEs(FrogServiceImpl.java:77)

at com.flush.service.impl.FrogServiceImpl.selectMysqlToES(FrogServiceImpl.java:56)

at com.flush.controller.MySqlToESServer.main(MySqlToESServer.java:26)

2016-03-14 18:05:00,800 [ESClientShutdownHook] [INFO] [ESClient.java][run(117)] >>> Run ESClient shutdown hook now. 开始关闭创建的ESClient, 禁止使用kill -9 关闭 !!!

2016-03-14 18:05:00,833 [ESClientShutdownHook] [INFO] [ESClient.java][run(127)] >>> 关闭创建的[1]个ESClient结束!

以上的是两个问题，第一次是null 无法转换成 timestamp 然后使用在创建数据源的时候，加入这个参数转换 <value>jdbc:mysql://192.168.10.100:3306/trade?zeroDateTimeBehavior=convertToNull</value>

然后cannot convert的原因是因为在数据库中查出来的 0000-00-00 00：00：00 没有转换成timestamp的形式，具体可以参考

/**
 * 这个是转换类,通过这个转换类可以吧数据库的数据转换成自己想要的内容
 */
public class TimestampToStringHandler extends BaseTypeHandler{

   @Override
   public void setNonNullParameter(PreparedStatement ps, int i,
         Object parameter, JdbcType jdbcType) throws SQLException {
      // TODO Auto-generated method stub
   }

   @Override
   public Object getNullableResult(ResultSet rs, String columnName)
         throws SQLException {
      DateTime dateTime = null;
      Timestamp timestamp = rs.getTimestamp(columnName);
      if(timestamp != null){
         dateTime = new DateTime(timestamp.getTime());
         return dateTime.toString("yyyy-MM-dd HH:mm:ss");
      }
      return "";
   }
   @Override
   public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
      return null;
   }

   @Override
   public Object getNullableResult(CallableStatement cs, int columnIndex)
         throws SQLException {
      DateTime dateTime = null;
      Timestamp timestamp = cs.getTimestamp(columnIndex);
      if(timestamp != null){
         dateTime = new DateTime(timestamp.getTime());
         return dateTime.toString("yyyy-MM-dd HH:mm:ss");
      }
      return "";
   }

}

的写法，重写映射类，进行操作