JDBC에서 DATETIME 값 0000-00-00 00:00:00 처리

JDBC에서 DATETIME 값 0000-00-00 00:00:00 처리

---

시도하면 예외가 발생합니다 (아래 참조).

resultset.getString("add_date");

DATETIME 값 0000-00-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에서 )

잘못된 DATETIME, DATE 또는 TIMESTAMP 값은 적절한 유형 ( '0000-00-00 00:00:00'또는 '0000-00-00')의 "0"값으로 변환됩니다.

구체적인 예외는 다음과 같습니다.

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)

---

나는 같은 문제를 해결하려는 시도를 우연히 발견했습니다. 내가 작업중인 설치는 JBOSS와 Hibernate를 사용하므로 다른 방식으로해야했습니다. 기본 사례의 zeroDateTimeBehavior=convertToNull경우이 구성 속성 페이지 에 따라 연결 URI 에 추가 할 수 있어야 합니다 .

나는 당신의 최대 절전 모드 구성에 해당 매개 변수를 넣는 것과 관련하여 땅에서 다른 제안을 찾았습니다.

에 있는 hibernate.cfg.xml :

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

에 hibernate.properties :

hibernate.connection.zeroDateTimeBehavior=convertToNull

하지만 JBOSS의 mysql-ds.xml 파일에 다음 과 같이 넣어야했습니다 .

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

이것이 누군가를 돕기를 바랍니다. :)

---

다른 대답으로, 데이터 소스 구성에서 직접이 JDBC URL을 사용할 수 있습니다.

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

편집하다:

출처 : MySQL 매뉴얼

구성 요소가 모두 0 인 날짜 시간 (0000-00-00 ...) — 이러한 값은 Java에서 안정적으로 표현할 수 없습니다. Connector / J 3.0.x는 ResultSet에서 읽을 때 항상 NULL로 변환했습니다.

Connector / J 3.1은 JDBC 및 SQL 표준에 따라 가장 올바른 동작이므로 이러한 값이 발생하면 기본적으로 예외가 발생합니다. 이 동작은 zeroDateTimeBehavior 구성 속성을 사용하여 수정할 수 있습니다. 허용되는 값은 다음과 같습니다.

• exception (기본값) : SQLState가 S1009 인 SQLException을 발생시킵니다.
• 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.

---

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.

---

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]

---

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

---

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

참고URL : https://stackoverflow.com/questions/782823/handling-datetime-values-0000-00-00-000000-in-jdbc