0

I just noticed that Hibernate automatically assumes data types for query parameters based on names (maybe column or annotated class). I'm trying to store a unix timestamp as long in a BIGINT column in a MySQL database. When the name is timestamp in the database and in my (JPA) annotated class, Hibernate automatically assumes, that it is a temporal value and thus I get an exception, when I try to bind a long value for this column in a query, because Hibernate expects a java.util.Date. When I rename this column in the database and in the class to 'foobar' everything works as expected. How can I stop Hibernate doing such stupid assumptions?

Edit:

DB Table:

CREATE TABLE candle
(
   `open` decimal(25,15) NOT NULL,
   `high` decimal(25,15) NOT NULL,
   `low` decimal(25,15) NOT NULL,
   `close` decimal(25,15) NOT NULL,
   `volume` decimal(15,2) NOT NULL,
   `symbol` varchar(255) NOT NULL,
   `exchange` varchar(255) NOT NULL,
   `timestamp` bigint NOT NULL,
   `width` bigint NOT NULL,
   PRIMARY KEY(`symbol`, `exchange`, `timestamp`, `width`),
   KEY idx_symbol (`symbol`),
   KEY idx_exchange (`exchange`),
   KEY idx_timestamp (`timestamp`),
   KEY idx_width (`width`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

JPA class:

@Entity(name="candle")
public class JpaCandle {

    @EmbeddedId
    private Key id;

    @Column
    private BigDecimal open;

    @Column
    private BigDecimal high;

    @Column
    private BigDecimal low;

    @Column
    private BigDecimal close;

    @Column
    private BigDecimal volume;

    public Key getId() {
        return id;
    }

    @Embeddable
    public static class Key implements Serializable {
        @Column
        private String symbol;

        @Column
        private String exchange;

        @Column
        private long timestamp;

        @Column
        private long width;
    }
}

Query:

TypedQuery<JpaCandle> q = em.createQuery("FROM candle WHERE symbol = :symbol AND width = :width AND timestamp >= :from AND timestamp <= :until ORDER BY timestamp ASC", JpaCandle.class);
q.setParameter("from", from.toEpochMilli());
q.setParameter("until", until.toEpochMilli());
q.setParameter("symbol", symbol);
q.setParameter("width", width.getSeconds());
List<JpaCandle> jpaCandles = q.getResultList();
henrik
  • 708
  • 7
  • 14
  • 1
    Can you provide some code as a proof? At minimun all the cases you have tried: meaning the field containing the timestamp with possible annotations? And preferably also the exceptions/errors for each case? – pirho Dec 04 '17 at 19:07
  • I added the relevant code, which fails with java.lang.IllegalArgumentException: Parameter value [1512662224763] did not match expected type [java.util.Date (n/a)]. As I said, when I rename timestamp to something else in the database, JPA class and the query, it works as expected. – henrik Dec 07 '17 at 16:12
  • I guess v.ladynev has a point. I guess it is possible at least to try "escaping" `timestamp` like `\"timestamp\"` or so in your JPQL? Or change the name to see if it is so? – pirho Dec 07 '17 at 16:17

1 Answers1

0

TIMESTAMP is a column type in the MySQL. Better to not use such name for a column. Perhaps, MySQL returns the type of such value as Date.

v.ladynev
  • 19,275
  • 8
  • 46
  • 67
  • The names should be escaped properly by Hibernate's MySQL dialect. Furthermore the column is defined as BIGINT. I'm pretty sure this weird behavior comes from Hibernate and not MySQL. BTW. a very similar configuration works in another persistence unit without problems with the name 'timestamp'. I have no idea why. I compared everything several times and can't find a difference. – henrik Dec 04 '17 at 20:35
  • @henrik "The names should be escaped properly by Hibernate's MySQL dialect" I don't think so. Please check the SQL is generated by Hibernate in the logs. – v.ladynev Dec 04 '17 at 22:49
  • I checked the MySQL query log... The query does not appear in the log. Hibernate fails before executing it. The exception is: java.lang.IllegalArgumentException: Parameter value [1512662574127] did not match expected type [java.util.Date (n/a)] – henrik Dec 07 '17 at 16:20
  • @henrik I think hibernate will not even parse it because of that col name. Also longer stack trace in question would be nice. – pirho Dec 07 '17 at 16:27
  • @pirho I have a very similar setup in another persistence unit. The table there also contains a column named "timestamp" and in that case it works as expected. Something weird is going on. – henrik Dec 08 '17 at 11:41
  • @henrik yes but _very similar_ != the same. I have a very similar comment [here](https://stackoverflow.com/questions/47693156/hibernate-field-with-timestamp-doesnt-work-java-sql-sqlexception-field-times). See the last comment. – pirho Dec 08 '17 at 11:43