java - MySQL 不正确的日期值 : null

Question

我正在尝试从 Java 应用程序更新表，其中特定列可能 为 NULL。我尝试了几种不同的方法，但总是出现以下错误:

com.mysql.jdbc.MysqlDataTruncation:数据截断:日期值不正确:第 1 行的“scheidingsdatum”列为“null”

我确保表允许scheidingsdatum字段为NULL值，并且在MySQL中直接插入时可以插入NULL值

这是 PHPMyAdmin 中的表结构:

表使用innoDB

我尝试了以下解决方案:

1: 只需在参数中使用 NULL 变量

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setString(4, scheidingsdatum);
stmnt.setString(5, nummer);

2:在查询中硬编码 NULL(在 if/else block 内)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = NULL "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setString(4, nummer);

3:使用setNull(4, java.sql.Types.DATE)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setNull(4, java.sql.Types.DATE);
stmnt.setString(5, nummer);

4:使用setNull(4, java.sql.Types.NULL)

stmnt = conn.prepareStatement("UPDATE gezinnen SET "
                            + "ouder1 = ?," 
                            + "ouder2 = ?," 
                            + "huwelijksdatum = ?,"
                            + "scheidingsdatum = ? "
                            + "WHERE gezinsNummer = ?");

stmnt.setString(1, ouder1);
stmnt.setString(2, ouder2);
stmnt.setString(3, huwelijksdatum);
stmnt.setNull(4, java.sql.Types.NULL);
stmnt.setString(5, nummer);

以下是我的 database.properties 文件和连接创建:

database.properties

jdbc.drivers=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://IP:3306/TABLE_NAME
jdbc.username=USER
jdbc.password=PASSWORD

连接创建

Class.forName(props.getProperty("jdbc.drivers")).newInstance();
this.conn = (Connection) DriverManager.getConnection(props.getProperty("jdbc.url"),props.getProperty("jdbc.username"),props.getProperty("jdbc.password"));

Answer 1

我刚刚做了一个测试，它对我有用 stmnt.setNull(4, java.sql.Types.Date);，你确定 stmnt.setString(3 , huwelijksdatum); huwelijksdatum 的值是一个有效的 mysql 日期字符串而不是 "null"?