query with date using entity manager

Question

Backgound: i use JPA (hibernate impl) + spring I have two tables, the first one is ok, it has one primary key and it works even with java.Util.Date fields. Another one has 3 parameters in primary key(it is first time i work with it):

PRIMARY KEY (`id_room`,`id_hotel`,`day`),

Full schema:

CREATE TABLE `hotels` (
  `id_room` int(11) NOT NULL DEFAULT '0',
  `id_hotel` int(11) NOT NULL DEFAULT '0',
  `day` date NOT NULL,
  PRIMARY KEY (`id_room`,`id_hotel`,`day`),
  KEY `id_hotel` (`id_hotel`,`day`),
  KEY `day` (`day`),
  KEY `day-hotel` (`day`,`id_hotel`),
  KEY `hotel` (`id_hotel`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So i added code to my Entity:

@Entity @IdClass(HotelId.class)
@Table(name="hotels")
public class Hotels implements Serializable {
    private static final Long serialVersionUID = 1L;

    @Id
    @Column(name="id_room")
    private int roomId;
    @Id
    @Column(name="id_hotel")
    private int hotelId;
    @Id
    @Temporal(TemporalType.DATE)
    private Date day;
....

HotelId.class

public class HotelId implements Serializable {
    private int roomId;
    private int hotelId;
    private Date day;

    public HotelId() {
    }

    public HotelId(int roomId, int hotelId, Date day) {
        this.roomId = roomId;
        this.hotelId = hotelId;
        this.day = day;
    }

    public int getRoomId() {
        return roomId;
    }

    public int getHotelId() {
        return hotelId;
    }

    public Date getDay() {
        return day;
    }
}

With this entity i stuck for a long time:

I spent almost all day today for this problem. I have database with date column in format(yyyy-mm-dd). I also have entity class in project with java.util.Date date field. Now i'm trying to make a select:

TypedQuery query = em.createQuery("Select h FROM Hotels h where h.roomId=:roomId and h.hotelId=:hotelId and h.day=:day", Hotels.class);
query.setParameter("roomId", roomId);
query.setParameter("hotelId", hotelId);
query.setParameter("day", date);

It says there is no data

Now i change date field from java.util.Date to String and change my query:

TypedQuery query = em.createQuery("Select h FROM Hotels h where h.roomId=:roomId and h.hotelId=:hotelId and h.day=:day", Hotels.class);
query.setParameter("roomId", roomId);
query.setParameter("hotelId", hotelId);
query.setParameter("day", new SimpleDateFormat("yyyy-mm-dd").format(date));

It finds data.

The question is how to send date(java.util.Date) to MySql DB and get back an information?

Update:

It doesnt work also

@Entity 
    @Table(name="hotels")
    public class Hotels implements Serializable {
        private static final Long serialVersionUID = 1L;

        @Id
        private int id;
        @Column(name="id_room")
        private int roomId;

        @Column(name="id_hotel")
        private int hotelId;

        private java.sql.Date day;
        ....
    }

Query:

    java.sql.Date sqlDate = new java.sql.Date(day.getTime());
    TypedQuery query = em.createQuery("Select h FROM Hotels h where h.roomId=:roomId and h.hotelId=:hotelId and h.day=:day", Hotels.class);
query.setParameter("roomId", roomId);
query.setParameter("hotelId", hotelId);
            query.setParameter("day", sqlDate);

Could you provide the `hotels` table schema (i.e. the `CREATE TABLE hotels...` query)? — sp00m, Dec 02 '14 at 13:32
It could be about java.util.Date. Maybe this post helps you http://stackoverflow.com/questions/2305973/java-util-date-vs-java-sql-date — Semih Eker, Dec 02 '14 at 13:38
changed field in entity to java.sql.Date and added it to query, doesnt help :( I guess Mysql gets date in another format, for instance: (dd-mm-yyyy) — Cooler, Dec 02 '14 at 13:47
The `@Id` annotation on many columns including the `Date` field? Did you decorate the `Date` field by the `@Temporal` annotation such as `@Temporal(TemporalType.Timestamp)` or `@Temporal(TemporalType.DATE)` in your real code? — Tiny, Dec 02 '14 at 13:56
Should i write my field as String but add a @Temporal(TemporalType.DATE)? I changed my field to java.util.Date and add this annotation, still doesnt work — Cooler, Dec 02 '14 at 14:16
It is `Date` (not `String`). This should work unless other concerns (like some configurations) are involved. http://en.wikibooks.org/wiki/Java_Persistence/Basic_Attributes#Temporal.2C_Dates.2C_Times.2C_Timestamps_and_Calendars — Tiny, Dec 02 '14 at 14:24
Ok, i checked it on another table, it works even with java.util.Date. I didnt mention, that im using 3 parameters in primary key, therefore i have three id annotation. I will add more details to my topic. — Cooler, Dec 02 '14 at 14:59
Does your JDBC-URL contain `useServerPrepStmts=true`? Try changing it to `useServerPrepStmts=false` and if possible post the URL/Params. See http://stackoverflow.com/a/25135670/1266906 — TheConstructor, Dec 02 '14 at 14:59
I think Tiny is right. 1) Keep java.utils.Date; 2) add @Temporal(TemporalType.DATE); 3) enable SQL trace to see the actual SQL statement. — Jama Djafarov, Dec 02 '14 at 15:12

score 0 · Answer 1 · edited May 23 '17 at 11:57

If you are using Java date, your named parameter day is set as Date with time stamp. So for confirming this, enable the hibernate logs to print sql parameters value

log4j.logger.org.hibernate.type=trace

Reference : How to print a query string with parameter values when using Hibernate

You can try to use the below query: "Select h FROM Hotels h where h.roomId=:roomId and h.hotelId=:hotelId and h.day=DATE_FORMAT(:day,format)"

The disadvantage of using the DATE_FORMAT function will have performance hit in your records retrieval if you have millions of records in hotels table.

So converting to string in java level and setting the same in named parameter is better option.

query with date using entity manager

Update:

1 Answers1