关于解决Incorrect datetime value: '' for column '' at row 1的问题

时间:2023-01-28 11:19:47

一个User的类封装了save方法

public void save() {
Connection conn = DB.getConn();
String sql = "insert into user values (null, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = DB.prepare(conn, sql);
try {
pstmt.setString(1, username);
pstmt.setString(2, password);
pstmt.setString(3, phone);
pstmt.setString(4, addr);
ps.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.close(pstmt);
DB.close(conn);
}
}

调用save方法出现异常

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'rdate' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2973)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1125)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)
at com.gz.kl.TestDateWriteDB.main(TestDateWriteDB.java:25)

折腾了好久改成

public void save() {
Connection conn = DB.getConn();
String sql = "insert into user values (null, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = DB.prepare(conn, sql);
try {
pstmt.setString(1, username);
pstmt.setString(2, password);
pstmt.setString(3, phone);
pstmt.setString(4, addr);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Timestamp ts = new Timestamp(System.currentTimeMillis());
String strDate = sdf.format(ts);
pstmt.setTimestamp(5, Timestamp.valueOf(strDate));
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.close(pstmt);
DB.close(conn);
}


}

先将当前时间用SimpleDateFormat 格式化成字符串后再转换成Timestamp就正常了,但个中原因不甚了解,有谁能解答下吗?

另setTimestamp查api需要的第二个参数是Timestamp对象,而通过new Timestamp(System.currentTimeMillis())就是一个Timestamp对象,我的运行环境myeclipse2014,

mysql5.7