在 JDBC 中处理 DATETIME 值0000-00-0000:00:00

如果我尝试这样做，我会得到一个例外(见下文)

resultset.getString("add_date");

对于包含0000-00-0000:00:00:00:00(DATETIME 的准空值)的 MySQL 数据库的 JDBC 连接，即使我只是试图以字符串而不是对象的形式获取该值。

我通过做

SELECT CAST(add_date AS CHAR) as add_date

有效，但看起来很傻... 有更好的方法吗？

我的观点是，我只需要原始的 DATETIME 字符串，这样我就可以自己解析它 保持原样。

注意: 这里是0000出现的地方: (来自 http://dev.mysql.com/doc/refman/5.0/en/datetime.html)

非法的日期、日期或时间戳 值转换为“零” 适当类型的值 (’0000-00-0000:00:00:00’或 ’0000-00-00’)。

唯一的例外是:

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)
112119 次浏览
小开

I suggest you use null to represent a null value.

What is the exception you get?

BTW:

There is no year called 0 or 0000. (Though some dates allow this year)

And there is no 0 month of the year or 0 day of the month. (Which may be the cause of your problem)

小开

My point is that I just want the raw DATETIME string, so I can parse it myself as is.

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 Constraints on Invalid Data:

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.x Date and Time Types:

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.

小开
最佳答案

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=convertToNull to your connection URI as per this configuration properties page.

I found other suggestions across the land referring to putting that parameter in your hibernate config:

In hibernate.cfg.xml:

<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>

In hibernate.properties:

hibernate.connection.zeroDateTimeBehavior=convertToNull

But I had to put it in my mysql-ds.xml file for JBOSS as:

<connection-property name="zeroDateTimeBehavior">convertToNull</connection-property>

Hope this helps someone. :)

小开 
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.

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]

小开

Alternative answer, you can use this JDBC URL directly in your datasource configuration:

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

Edit:

Source: MySQL Manual

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.

Update: Alexander reported a bug affecting mysql-connector-5.1.15 on that feature. See CHANGELOGS on the official website.

小开

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"));
小开

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.

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

小开

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:

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"...

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:

@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.

小开

To add to the other answers: If yout want the 0000-00-00 string, you can use noDatetimeStringSync=true (with the caveat of sacrificing timezone conversion).

The official MySQL bug: https://bugs.mysql.com/bug.php?id=47108.

Also, for history, JDBC used to return NULL for 0000-00-00 dates but now return an exception by default. Source

小开

you can append the jdbc url with

?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.

eg:

jdbc:mysql:<host-name>/<db-name>?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

提交答案