java com.mysql.jdbc.MysqlDataTruncation:数据截断:日期值不正确:“Date_Of_Birth”列在第 1 行

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/6652213/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-10-30 16:48:29  来源:igfitidea点击:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '' for column 'Date_Of_Birth' at row 1

javamysql

提问by ashima

I'm trying to update the personal detail of a user through a java panel.Panel has fields like user_id(autogenerated),name,date of birth. problem is when i enter nothing to the date of birth field in java panel and then save it. It gives me the above mentioned error. i tried to verify it by inserting null to the date of birth(Date datatype) field directly using the mysql database.There it gives no error. Why is it not taking null string when i insert through java panel but is taking when insert directly using mysql.

我正在尝试通过 java panel.Panel 更新用户的个人详细信息。Panel 具有 user_id(autogenerated),name,date ofbirth 等字段。问题是当我在 java 面板中的出生日期字段中不输入任何内容然后保存时。它给了我上面提到的错误。我试图通过直接使用 mysql 数据库将 null 插入到出生日期(日期数据类型)字段来验证它。它没有给出错误。为什么当我通过 java 面板插入时不取空字符串,而直接使用 mysql 插入时取空字符串。

CREATE TABLE `userpersonaldetail` (
  `User_Id` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(45) default NULL,
  `Date_Of_Birth` date default NULL,
  `Address` varchar(300) default NULL,
  `Phone` varchar(20) default NULL,
  `Mobile` varchar(20) default NULL,
  `Email` varchar(50) default NULL,
  PRIMARY KEY  (`User_Id`),
  CONSTRAINT `FK_userpersonaldetail_1` FOREIGN KEY (`User_Id`) REFERENCES `usermaster` (`User_Id`)
) 

And the portion of the code where exception occurs is:

发生异常的代码部分是:

  try
     {
        con=(Connection) DBConnection.getConnection();
        pstmt=(PreparedStatement) con.prepareStatement("Update userpersonaldetail set "+
               "name=?,date_of_birth=?,address=?,phone=?,mobile=?,email=? where user_id=?");

            pstmt.setInt(7,perBean.getUserId());
            pstmt.setString(1,perBean.getName());
            pstmt.setString(2,perBean.getDateOfBirth());
            pstmt.setString(3,perBean.getAddress());
            pstmt.setString(4,perBean.getPhone());
            pstmt.setString(5,perBean.getMobile());
            pstmt.setString(6,perBean.getEmail());

            int i=pstmt.executeUpdate();
 }

here perBean is the javaBean which retrieves values from the gui.In one of the test case i kept the date_of_birth text box null which is giving error while storing in DB.

这里 perBean 是从 gui 中检索值的 javaBean。在其中一个测试用例中,我将 date_of_birth 文本框保留为 null,这在存储在 DB 中时会出错。

回答by Brian

My initial guess would be the field has been defined as 'NOT NULL' which means it will force you to enter a value...

我最初的猜测是该字段已被定义为“NOT NULL”,这意味着它将强制您输入一个值...

If you were to do a mysql dump of the table (or view it in some tool) you'll probably find it defined such as:

如果您要对表进行 mysql 转储(或在某些工具中查看它),您可能会发现它的定义如下:

`someDT` datetime NOT NULL

回答by DIM

I replaced older version of my-sql-connector jar (to be found in lib folder of the server) with the latest. That solved my problem.

我用最新版本替换了旧版本的 my-sql-connector jar(可在服务器的 lib 文件夹中找到)。那解决了我的问题。

回答by Brian

Ahh, i see now, you can't insert '' as a date. you will need to pass a date.

啊,我现在明白了,你不能插入 '' 作为日期。你需要通过一个日期。

回答by eis

If an attribute does not have a value but you have it mentioned in the column list, forcing you to give something there, you need to use

如果一个属性没有值,但你在列列表中提到了它,迫使你在那里给出一些东西,你需要使用

statement.setNull(index, datatype)

statement.setNull(索引,数据类型)

to it. Setting to "" is not the same thing as setting to null.

到它。设置为 "" 与设置为 null 不同。