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
It could be about java.util.Date. Maybe this post helps you stackoverflow.com/questions/2305973/… — Semih Eker
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

Kaushik Gopalakrishnan Kaushik Gopalakrishnan · Accepted Answer · 2014-12-02T15:44:01

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 Answers