Обработка значений DATETIME 0000-00-00 00:00:00 в JDBC

Question

Я получаю исключение (см. Ниже), если я пытаюсь сделать

resultset.getString("add_date");

для соединения JDBC с базой данных MySQL, содержащей значение DATETIME 0000-00-00 00:00:00 (квази-нулевое значение для DATETIME), хотя я просто пытаюсь получить значение в виде строки, а не как объект.

Я обошел это, делая

SELECT CAST(add_date AS CHAR) as add_date

который работает, но кажется глупым... есть ли лучший способ сделать это?

Суть в том, что мне просто нужна необработанная строка DATETIME, чтобы я мог сам разобрать ее как есть.

примечание: вот где приходит 0000: (с http://dev.mysql.com/doc/refman/5.0/en/datetime.html)

Недопустимые значения DATETIME, DATE или TIMESTAMP преобразуются в "нулевое" значение соответствующего типа ("0000-00-00 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)

Answer 1

Я наткнулся на эту попытку решить ту же проблему. Установка, с которой я работаю, использует JBOSS и Hibernate, поэтому мне пришлось сделать это по-другому. Для основного случая вы должны быть в состоянии добавить zeroDateTimeBehavior=convertToNull к вашему URI соединения согласно этой странице свойств конфигурации.

Я нашел другие предложения по всей стране, касающиеся размещения этого параметра в вашей конфигурации hibernate:

В hibernate.cfg.xml:

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

В hibernate.properties:

hibernate.connection.zeroDateTimeBehavior=convertToNull

Но мне пришлось поместить его в мой файл mysql-ds.xml для JBOSS как:

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

Надеюсь, это кому-нибудь поможет.:)

Answer 2

Альтернативный ответ, вы можете использовать этот URL JDBC непосредственно в вашей конфигурации источника данных:

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

Редактировать:

Источник: MySQL Manual

Datetime с полностью нулевыми компонентами (0000-00-00 ...) - эти значения не могут быть надежно представлены в Java. Connector/J 3.0.x всегда преобразовывал их в NULL при чтении из ResultSet.

Connector / J 3.1 генерирует исключение по умолчанию, когда встречаются эти значения, так как это наиболее правильное поведение в соответствии со стандартами JDBC и SQL. Это поведение можно изменить с помощью свойства конфигурации zeroDateTimeBehavior. Допустимые значения:

• исключение (по умолчанию), которое вызывает исключение SQLException с состоянием SQLState S1009.
• convertToNull, который возвращает NULL вместо даты.
• round - округляет дату до ближайшего ближайшего значения 0001-01-01.

Обновление: Александр сообщил об ошибке, влияющей на mysql-connector-5.1.15 на эту функцию. Смотрите CHANGELOGS на официальном сайте.

Answer 3

Суть в том, что мне просто нужна необработанная строка DATETIME, чтобы я мог сам разобрать ее как есть.

Это заставляет меня думать, что ваш "обходной путь" - это не обходной путь, а фактически единственный способ получить значение из базы данных в ваш код:

SELECT CAST(add_date AS CHAR) as add_date

Кстати, еще несколько замечаний из документации по MySQL:

Ограничения MySQL на неверных данных:

До MySQL 5.0.2 MySQL прощает недопустимые или неправильные значения данных и приводит их к допустимым значениям для ввода данных. В MySQL 5.0.2 и выше это остается поведением по умолчанию, но вы можете изменить режим SQL сервера, чтобы выбрать более традиционную обработку неверных значений, чтобы сервер отклонял их и прерывал оператор, в котором они встречаются.

[..]

Если вы попытаетесь сохранить NULL в столбце, который не принимает значения NULL, произойдет ошибка для однострочных операторов INSERT. Для многострочных операторов INSERT или для операторов INSERT INTO ... SELECT MySQL Server сохраняет неявное значение по умолчанию для типа данных столбца.

MySQL 5.x Типы даты и времени:

MySQL также позволяет хранить "0000-00-00" как "фиктивную дату" (если вы не используете режим SQL NO_ZERO_DATE). В некоторых случаях это более удобно (и использует меньше места для данных и индекса), чем использование значений NULL.

[..]

По умолчанию, когда MySQL обнаруживает значение для типа даты или времени, которое выходит за пределы диапазона или иным образом недопустимо для типа (как описано в начале этого раздела), он преобразует значение в "нулевое" значение для этого типа.

Answer 4

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

Используйте это, чтобы избежать ошибки. Он возвращает дату в строковом формате, а затем вы можете получить ее в виде строки.

resultset.getString("dtime");

Это на самом деле не работает. Даже если вы вызываете getString. Внутренне mysql все еще пытается преобразовать его в дату первым.

на 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-разъем-Java-5.1.15.jar: на]

Answer 5

Я боролся с этой проблемой и реализовал решения "convertToNull", описанные выше. Это работало в моем локальном экземпляре MySql. Но когда я развернул свое приложение Play/Scala в Heroku, оно перестало работать. Heroku также объединяет несколько аргументов с URL-адресом БД, который они предоставляют пользователям, и это решение из-за использования Heroku конкатенации "?" прежде чем их собственный набор аргументов, не будет работать. Однако я нашел другое решение, которое, кажется, работает одинаково хорошо.

SET sql_mode = 'NO_ZERO_DATE';

Я поместил это в мои описания таблиц, и это решило проблему "0000-00-00 00:00:00" не может быть представлена ​​как java.sql.Timestamp

Answer 6

Если после добавления строк:

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

hibernate.connection.zeroDateTimeBehavior=convertToNull

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

продолжает быть ошибка:

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

найти строки:

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

заменить на следующие строки соответственно:

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

Answer 7

Я предлагаю вам использовать ноль для представления нулевого значения.

Какое исключение вы получаете?

КСТАТИ:

Нет года с названием 0 или 0000. (Хотя некоторые даты позволяют этот год)

И нет 0 месяца в году или 0 дня месяца. (Которая может быть причиной вашей проблемы)

Answer 8

Я решил проблему, полагая, что '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),...

Затем я должен иметь возможность вставлять значения 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),...

Из таблицы выглядят так:

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)

Наконец: 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();
    }

Наконец-то вся его работа. Я надеюсь, что это поможет вам.

Answer 9

Вы можете добавить JDBC URL с

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

С помощью этого sql convert '0000-00-00 00:00:00' в качестве нулевого значения.

например:

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

Answer 10

Чтобы добавить к другим ответам: Если вы хотите 0000-00-00 Строка, вы можете использовать noDatetimeStringSync=true (с оговоркой о том, что нужно жертвовать преобразованием часового пояса).

Официальная ошибка MySQL: https://bugs.mysql.com/bug.php?id=47108.

Кроме того, для истории, JDBC имел обыкновение возвращать NULL за 0000-00-00 даты, но теперь возвращают исключение по умолчанию. Источник