Java 在 JDBC 中处理 DATETIME 值 0000-00-00 00:00:00
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/782823/
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
handling DATETIME values 0000-00-00 00:00:00 in JDBC
提问by Jason S
I get an exception (see below) if I try to do
如果我尝试这样做，我会得到一个例外（见下文）
resultset.getString("add_date");
for a JDBC connection to a MySQL database containing a DATETIME value of 0000-00-00 00:00:00 (the quasi-null value for DATETIME), even though I'm just trying to get the value as string, not as an object.
对于到包含 DATETIME 值 0000-00-00 00:00:00（DATETIME 的准空值）的 MySQL 数据库的 JDBC 连接，即使我只是试图将值作为字符串而不是作为目的。
I got around this by doing
我解决了这个问题
SELECT CAST(add_date AS CHAR) as add_date
which works, but seems silly... is there a better way to do this?
哪个有效，但似乎很愚蠢......有没有更好的方法来做到这一点？
My point is that I just want the raw DATETIME string, so I can parse it myself as is.
我的观点是我只想要原始的 DATETIME 字符串，所以我可以按原样自己解析它。
note:here's where the 0000 comes in: (from http://dev.mysql.com/doc/refman/5.0/en/datetime.html)
注意：这里是 0000 的来源：（来自http://dev.mysql.com/doc/refman/5.0/en/datetime.html）
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00').
非法的 DATETIME、DATE 或 TIMESTAMP 值被转换为适当类型的“零”值（'0000-00-00 00:00:00' 或 '0000-00-00'）。
The specific exception is this one:
具体的例外是这个：
SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
SQLState: S1009
VendorError: 0
java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ResultSetImpl.getTimestampFromString(ResultSetImpl.java:6343)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5670)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5491)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5531)
采纳答案by sarumont
I stumbled across this attempting to solve the same issue. The installation I am working with uses JBOSS and Hibernate, so I had to do this a different way. For the basic case, you should be able to add zeroDateTimeBehavior=convertToNullto your connection URI as per this configuration properties page.
我偶然发现了这个试图解决同样的问题。我正在使用的安装使用 JBOSS 和 Hibernate，因此我必须以不同的方式执行此操作。对于基本情况，您应该能够zeroDateTimeBehavior=convertToNull根据此配置属性页面添加到您的连接 URI 。
I found other suggestions across the land referring to putting that parameter in your hibernate config:
我在整个土地上发现了其他建议，即将该参数放入您的休眠配置中：
In hibernate.cfg.xml:
在hibernate.cfg.xml 中：
<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>
In hibernate.properties:
在hibernate.properties 中：
hibernate.connection.zeroDateTimeBehavior=convertToNull
But I had to put it in my mysql-ds.xmlfile for JBOSS as:
但我不得不把它放在我的 JBOSS mysql-ds.xml文件中：
<connection-property name="zeroDateTimeBehavior">convertToNull</connection-property>
Hope this helps someone. :)
希望这可以帮助某人。:)
回答by Peter Lawrey
I suggest you use null to represent a null value.
我建议您使用 null 来表示空值。
What is the exception you get?
你得到的例外是什么？
BTW:
顺便提一句：
There is no year called 0 or 0000. (Though some dates allow this year)
没有称为 0 或 0000 的年份。（尽管今年某些日期允许）
And there is no 0 month of the year or 0 day of the month. (Which may be the cause of your problem)
并且没有一年中的 0 个月或一个月中的 0 天。（这可能是您问题的原因）
回答by Arjan
My point is that I just want the raw DATETIME string, so I can parse it myself as is.
我的观点是我只想要原始 DATETIME 字符串，所以我可以按原样自己解析它。
That makes me think that your "workaround" is not a workaround, but in fact the only way to get the value from the database into your code:
这让我认为您的“解决方法”不是一种解决方法，但实际上是将值从数据库中获取到您的代码中的唯一方法：
SELECT CAST(add_date AS CHAR) as add_date
By the way, some more notes from the MySQL documentation:
顺便说一下，MySQL 文档中的更多注释：
MySQL Constraints on Invalid Data:
MySQL对无效数据的限制：
Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur.
[..]
If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type.
在 MySQL 5.0.2 之前，MySQL 容忍非法或不正确的数据值，并将它们强制为合法值以进行数据输入。在 MySQL 5.0.2 及更高版本中，这仍然是默认行为，但您可以更改服务器 SQL 模式以选择更传统的错误值处理方式，以便服务器拒绝它们并中止它们出现的语句。
[..]
如果您尝试将 NULL 存储到不采用 NULL 值的列中，则单行 INSERT 语句会发生错误。对于多行 INSERT 语句或 INSERT INTO ... SELECT 语句，MySQL Server 存储列数据类型的隐式默认值。
MySQL 5.x Date and Time Types:
MySQL 5.x日期和时间类型：
MySQL also allows you to store '0000-00-00' as a “dummy date” (if you are not using the NO_ZERO_DATE SQL mode). This is in some cases more convenient (and uses less data and index space) than using NULL values.
[..]
By default, when MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type (as described at the beginning of this section), it converts the value to the “zero” value for that type.
MySQL 还允许您将 '0000-00-00' 存储为“虚拟日期”（如果您没有使用 NO_ZERO_DATE SQL 模式）。在某些情况下，这比使用 NULL 值更方便（并且使用更少的数据和索引空间）。
[..]
默认情况下，当 MySQL 遇到超出范围或其他类型非法的日期或时间类型的值时（如本节开头所述），它会将值转换为该类型的“零”值。
回答by sarumont
DATE_FORMAT(column name, '%Y-%m-%d %T') as dtime
Use this to avoid the error. It return the date in string format and then you can get it as a string.
使用它可以避免错误。它以字符串格式返回日期，然后您可以将其作为字符串获取。
resultset.getString("dtime");
This actually does NOT work. Even though you call getString. Internally mysql still tries to convert it to date first.
这实际上不起作用。即使您调用 getString。在内部 mysql 仍然尝试先将其转换为日期。
at com.mysql.jdbc.ResultSetImpl.getDateFromString(ResultSetImpl.java:2270)
~[mysql-connector-java-5.1.15.jar:na] at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5743)
~[mysql-connector-java-5.1.15.jar:na] at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5576)
~[mysql-connector-java-5.1.15.jar:na]
在 com.mysql.jdbc.ResultSetImpl.getDateFromString(ResultSetImpl.java:2270)
~[mysql-connector-java-5.1.15.jar:na] 在 com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5743)
~[mysql-connector-java-5.1.15.jar:na] 在 com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5576)
~[mysql-connector-java-5.1.15.jar:na]
回答by Brian Clozel
Alternative answer, you can use this JDBC URL directly in your datasource configuration:
替代答案，您可以直接在数据源配置中使用此 JDBC URL：
jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull
Edit:
编辑：
Source: MySQL Manual
资料来源：MySQL 手册
Datetimes with all-zero components (0000-00-00 ...) — These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.
Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are:
- exception(the default), which throws an SQLException with an SQLState of S1009.
- convertToNull, which returns NULL instead of the date.
- round, which rounds the date to the nearest closest value which is 0001-01-01.
具有全零分量的日期时间 (0000-00-00 ...) — 这些值在 Java 中无法可靠表示。当从 ResultSet 读取时，Connector/J 3.0.x 总是将它们转换为 NULL。
遇到这些值时，默认情况下，Connector/J 3.1 会抛出异常，因为这是根据 JDBC 和 SQL 标准最正确的行为。可以使用 zeroDateTimeBehavior 配置属性修改此行为。允许的值为：
- 异常（默认），它抛出一个 SQLException 的 SQLState 为 S1009。
- convertToNull，它返回 NULL 而不是日期。
- round，将日期四舍五入到最接近的值，即 0001-01-01。
Update: Alexander reported a bug affecting mysql-connector-5.1.15 on that feature. See CHANGELOGS on the official website.
更新：Alexander 在该功能上报告了影响 mysql-connector-5.1.15 的错误。请参阅官方网站上的 CHANGELOGS。
回答by Vadim
If, after adding lines:
如果在添加行后：
<property
name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>
hibernate.connection.zeroDateTimeBehavior=convertToNull
<connection-property
name="zeroDateTimeBehavior">convertToNull</connection-property>
continues to be an error:
仍然是一个错误：
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00').
find lines:
查找行：
1) resultSet.getTime("time"); // time = 00:00:00
2) resultSet.getTimestamp("timestamp"); // timestamp = 00000000000000
3) resultSet.getDate("date"); // date = 0000-00-00 00:00:00
replace with the following lines, respectively:
分别替换为以下几行：
1) Time.valueOf(resultSet.getString("time"));
2) Timestamp.valueOf(resultSet.getString("timestamp"));
3) Date.valueOf(resultSet.getString("date"));
回答by Aqume
I wrestled with this problem and implemented the 'convertToNull' solutions discussed above. It worked in my local MySql instance. But when I deployed my Play/Scala app to Heroku it no longer would work. Heroku also concatenates several args to the DB URL that they provide users, and this solution, because of Heroku's use concatenation of "?" before their own set of args, will not work. However I found a different solution which seems to work equally well.
我解决了这个问题并实施了上面讨论的“convertToNull”解决方案。它在我的本地 MySql 实例中工作。但是当我将我的 Play/Scala 应用程序部署到 Heroku 时，它不再起作用。Heroku 还将几个 args 连接到他们提供给用户的 DB URL，而这个解决方案，因为 Heroku 使用了“？”的连接。在他们自己的 args 之前，是行不通的。但是我发现了一个不同的解决方案，它似乎同样有效。
SET sql_mode = 'NO_ZERO_DATE';
SET sql_mode = 'NO_ZERO_DATE';
I put this in my table descriptions and it solved the problem of '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
我把它放在我的表描述中，它解决了'0000-00-00 00:00:00'不能表示为java.sql.Timestamp的问题
回答by EdU
I solved the problem considerating '00-00-....' isn't a valid date, then, I changed my SQL column definition adding "NULL" expresion to permit null values:
我解决了考虑 '00-00-....' 不是有效日期的问题，然后，我更改了 SQL 列定义，添加了“NULL”表达式以允许空值：
SELECT "-- Tabla item_pedido";
CREATE TABLE item_pedido (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
id_pedido INTEGER,
id_item_carta INTEGER,
observacion VARCHAR(64),
fecha_estimada TIMESTAMP,
fecha_entrega TIMESTAMP NULL, // HERE IS!!.. NULL = DELIVERY DATE NOT SET YET
CONSTRAINT fk_item_pedido_id_pedido FOREIGN KEY (id_pedido)
REFERENCES pedido(id),...
Then, I've to be able to insert NULL values, that means "I didnt register that timestamp yet"...
然后，我必须能够插入 NULL 值，这意味着“我还没有注册那个时间戳”......
SELECT "++ INSERT item_pedido";
INSERT INTO item_pedido VALUES
(01, 01, 01, 'Ninguna', ADDDATE(@HOY, INTERVAL 5 MINUTE), NULL),
(02, 01, 02, 'Ninguna', ADDDATE(@HOY, INTERVAL 3 MINUTE), NULL),...
The table look that:
该表看起来：
mysql> select * from item_pedido;
+----+-----------+---------------+-------------+---------------------+---------------------+
| id | id_pedido | id_item_carta | observacion | fecha_estimada | fecha_entrega |
+----+-----------+---------------+-------------+---------------------+---------------------+
| 1 | 1 | 1 | Ninguna | 2013-05-19 15:09:48 | NULL |
| 2 | 1 | 2 | Ninguna | 2013-05-19 15:07:48 | NULL |
| 3 | 1 | 3 | Ninguna | 2013-05-19 15:24:48 | NULL |
| 4 | 1 | 6 | Ninguna | 2013-05-19 15:06:48 | NULL |
| 5 | 2 | 4 | Suave | 2013-05-19 15:07:48 | 2013-05-19 15:09:48 |
| 6 | 2 | 5 | Seco | 2013-05-19 15:07:48 | 2013-05-19 15:12:48 |
| 7 | 3 | 5 | Con Mayo | 2013-05-19 14:54:48 | NULL |
| 8 | 3 | 6 | Bilz | 2013-05-19 14:57:48 | NULL |
+----+-----------+---------------+-------------+---------------------+---------------------+
8 rows in set (0.00 sec)
Finally: JPA in action:
最后：JPA 在行动：
@Stateless
@LocalBean
public class PedidosServices {
@PersistenceContext(unitName="vagonpubPU")
private EntityManager em;
private Logger log = Logger.getLogger(PedidosServices.class.getName());
@SuppressWarnings("unchecked")
public List<ItemPedido> obtenerPedidosRetrasados() {
log.info("Obteniendo listado de pedidos retrasados");
Query qry = em.createQuery("SELECT ip FROM ItemPedido ip, Pedido p WHERE" +
" ip.fechaEntrega=NULL" +
" AND ip.idPedido=p.id" +
" AND ip.fechaEstimada < :arg3" +
" AND (p.idTipoEstado=:arg0 OR p.idTipoEstado=:arg1 OR p.idTipoEstado=:arg2)");
qry.setParameter("arg0", Tipo.ESTADO_BOUCHER_ESPERA_PAGO);
qry.setParameter("arg1", Tipo.ESTADO_BOUCHER_EN_SERVICIO);
qry.setParameter("arg2", Tipo.ESTADO_BOUCHER_RECIBIDO);
qry.setParameter("arg3", new Date());
return qry.getResultList();
}
At last all its work. I hope that help you.
最后它的所有工作。我希望对你有帮助。
回答by damio
To add to the other answers: If yout want the 0000-00-00string, you can use noDatetimeStringSync=true(with the caveat of sacrificing timezone conversion).
添加到其他答案：如果0000-00-00你想要字符串，你可以使用noDatetimeStringSync=true（但要注意牺牲时区转换）。
The official MySQL bug: https://bugs.mysql.com/bug.php?id=47108.
MySQL 官方错误：https: //bugs.mysql.com/bug.php?id=47108。
Also, for history, JDBC used to return NULLfor 0000-00-00dates but now return an exception by default.
Source
此外，对于历史，JDBC用于返回NULL的0000-00-00日期，但现在默认返回异常。
来源
回答by Mukul Aggarwal
you can append the jdbc url with
你可以附加 jdbc url
?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8
With the help of this, sql convert '0000-00-00 00:00:00' as null value.
在此帮助下，sql 将 '0000-00-00 00:00:00' 转换为空值。
eg:
例如：
jdbc:mysql:<host-name>/<db-name>?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8
