java - print - manejando DATETIME values 0000-00-00 00:00:00 en JDBC

java.sql.date format

Question

Obtengo una excepción (ver abajo) si trato de hacer

resultset.getString("add_date");

para una conexión JDBC a una base de datos MySQL que contenga un valor DATETIME de 0000-00-00 00:00:00 (el valor casi nulo para DATETIME), a pesar de que estoy tratando de obtener el valor como cadena, no como una objeto.

Lo solucioné haciendo

SELECT CAST(add_date AS CHAR) as add_date

que funciona, pero parece tonto ... ¿hay una mejor manera de hacer esto?

Mi punto es que solo quiero la cadena DATETIME sin procesar, así que puedo analizarla tal como está .

nota: aquí es donde entra el 0000: (desde http://dev.mysql.com/doc/refman/5.0/en/datetime.html )

Los valores DATETIME, DATE o TIMESTAMP ilegales se convierten al valor "cero" del tipo apropiado ("0000-00-00 00:00:00" o "0000-00-00").

La excepción específica es esta:

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)

Answer 1

Mi punto es que solo quiero la cadena DATETIME sin procesar, así que puedo analizarla tal como está.

Eso me hace pensar que su "solución alternativa" no es una solución, sino que de hecho es la única manera de obtener el valor de la base de datos en su código:

SELECT CAST(add_date AS CHAR) as add_date

Por cierto, algunas notas más de la documentación de MySQL:

Restricciones de MySQL sobre datos inválidos :

Antes de MySQL 5.0.2, MySQL es indulgente con los valores de datos ilegales o inapropiados y los coacciona a valores legales para la entrada de datos. En MySQL 5.0.2 y versiones posteriores, ese sigue siendo el comportamiento predeterminado, pero puede cambiar el modo SQL del servidor para seleccionar un tratamiento más tradicional de los valores incorrectos, de modo que el servidor los rechace y anule la declaración en la que ocurren.

[..]

Si intenta almacenar NULL en una columna que no toma valores NULL, se produce un error para las instrucciones INSERT de una sola fila. Para sentencias INSERT de múltiples filas o para instrucciones INSERT INTO ... SELECT, el servidor MySQL almacena el valor implícito predeterminado para el tipo de datos de columna.

Tipos de fecha y hora de MySQL 5.x:

MySQL también le permite almacenar ''0000-00-00'' como una "fecha ficticia" (si no está utilizando el modo SQL NO_ZERO_DATE). En algunos casos, esto es más conveniente (y utiliza menos datos y espacio de índice) que usar valores NULL.

[..]

De forma predeterminada, cuando MySQL encuentra un valor para un tipo de fecha u hora que está fuera de rango o ilegal para el tipo (como se describe al principio de esta sección), convierte el valor en el valor "cero" para ese tipo.

Answer 2

Luché con este problema e implementé las soluciones ''convertToNull'' discutidas anteriormente. Funcionó en mi instancia MySql local. Pero cuando desplegué mi aplicación Play / Scala a Heroku ya no funcionaría. Heroku también concatena varias args a la URL DB que proporcionan a los usuarios, y esta solución, debido a la concatenación del uso de Heroku de "?" antes de su propio conjunto de argumentos, no funcionará. Sin embargo, encontré una solución diferente que parece funcionar igualmente bien.

SET sql_mode = ''NO_ZERO_DATE'';

Puse esto en las descripciones de mi mesa y resolvió el problema de ''0000-00-00 00:00:00'' no puede ser representado como java.sql.Timestamp

Answer 3

Me encontré con esto tratando de resolver el mismo problema. La instalación con la que estoy trabajando usa JBOSS e Hibernate, así que tuve que hacer esto de otra manera. Para el caso básico, debería poder agregar zeroDateTimeBehavior=convertToNull a su URI de conexión según esta página de propiedades de configuración .

Encontré otras sugerencias en todo el país que se refieren a poner ese parámetro en su configuración de hibernación:

En hibernate.cfg.xml :

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

En hibernate.properties :

hibernate.connection.zeroDateTimeBehavior=convertToNull

Pero tuve que ponerlo en mi archivo mysql-ds.xml para JBOSS como:

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

Espero que esto ayude a alguien. :)

Answer 4

Para agregar a las otras respuestas: si desea la cadena 0000-00-00 , puede usar noDatetimeStringSync=true (con la salvedad de sacrificar la conversión de la zona horaria).

El error oficial de MySQL: https://bugs.mysql.com/bug.php?id=47108 .

Además, para la historia, JDBC solía devolver NULL para las fechas de 0000-00-00 pero ahora devuelve una excepción por defecto. Source

Answer 5

Resolví el problema considerando que ''00 -00 -.... ''no es una fecha válida, luego, cambié mi definición de columna SQL y agregué la expresión "NULO" para permitir valores nulos:

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),...

Entonces, tengo que poder insertar valores NULL, eso significa "No registré esa marca de tiempo todavía" ...

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),...

La mesa parece que:

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)

Finalmente: JPA en acción:

@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();
    }

Por fin todo su trabajo. Espero que te ayude

Answer 6

Respuesta alternativa, puede usar esta URL JDBC directamente en la configuración de su fuente de datos:

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

Editar:

Fuente: Manual de MySQL

Datetime con componentes completamente cero (0000-00-00 ...) - Estos valores no se pueden representar de manera confiable en Java. Connector / J 3.0.x siempre los convirtió a NULL cuando se leen desde un ResultSet.

Connector / J 3.1 arroja una excepción por defecto cuando se encuentran estos valores ya que este es el comportamiento más correcto según los estándares JDBC y SQL. Este comportamiento se puede modificar utilizando la propiedad de configuración zeroDateTimeBehavior. Los valores permitidos son:

• excepción (el valor predeterminado), que arroja una SQLException con un SQLState de S1009.
• convertToNull , que devuelve NULL en lugar de la fecha.
• redondo , que redondea la fecha al valor más cercano más cercano que es 0001-01-01.

Actualización: Alexander informó un error que afecta a mysql-connector-5.1.15 en esa función. Ver CHANGELOGS en el sitio web oficial .

Answer 7

Si, después de agregar líneas:

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

hibernate.connection.zeroDateTimeBehavior=convertToNull

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

sigue siendo un 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'').

encontrar líneas:

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

reemplace con las siguientes líneas, respectivamente:

1) Time.valueOf(resultSet.getString("time"));
2) Timestamp.valueOf(resultSet.getString("timestamp"));
3) Date.valueOf(resultSet.getString("date"));

Answer 8

Sugiero que use null para representar un valor nulo.

¿Cuál es la excepción que obtienes?

Por cierto:

No hay un año llamado 0 o 0000. (Aunque algunas fechas lo permiten este año)

Y no hay 0 meses del año o 0 días del mes. (Que puede ser la causa de su problema)

Answer 9

puede agregar la url jdbc con

?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

Con la ayuda de esto, sql convert ''0000-00-00 00:00:00'' como valor nulo.

p.ej:

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

Answer 10

DATE_FORMAT(column name, ''%Y-%m-%d %T'') as dtime

Use esto para evitar el error. Devuelve la fecha en formato de cadena y luego puede obtenerla como una cadena.

resultset.getString("dtime");

Esto realmente NO funciona. Aunque llame a getString. Internamente, mysql aún intenta convertirlo a la fecha primero.

en com.mysql.jdbc.ResultSetImpl.getDateFromString (ResultSetImpl.java:2270)

~ [mysql-connector-java-5.1.15.jar: na] en com.mysql.jdbc.ResultSetImpl.getStringInternal (ResultSetImpl.java:5743)

~ [mysql-connector-java-5.1.15.jar: na] en com.mysql.jdbc.ResultSetImpl.getString (ResultSetImpl.java:5576)

~ [mysql-connector-java-5.1.15.jar: na]