[FIXED] Unable to update date through sql query using hibernate

Issue

I have a database table as follows:

+----+------------+-----------+---------------------+---------+
| id | first_name | last_name | date_of_birth       | company |
+----+------------+-----------+---------------------+---------+
|  1 | Vyom       | Yadav     | 2002-09-28 00:00:00 | Google  |
+----+------------+-----------+---------------------+---------+

Here date_of_birth is of type DATETIME.

Configured annotations for dateOfBirth:

@Column(name = "date_of_birth")
@Temporal(TemporalType.DATE)
private Date dateOfBirth;

I am trying to update the date through Hibernate. I was able to update it the following way:

Date dob = DateUtils.parseDate("28/09/2002");
Employee foo = session.get(Employee.class, 1);
foo.setDateOfBirth(dob);
session.getTransaction().commit();

Here, DateUtils is a simple class that uses SimpleDateFormatter to format the date. When I try to update the date like:

Date dob = DateUtils.parseDate("28/09/2002");
session.createQuery("UPDATE Employee SET dateOfBirth='"+ dob +"' WHERE id=1").executeUpdate();
session.getTransaction().commit();

I get an error saying:

Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Sat Sep 28 00:00:00 IST 2002' for column 'date_of_birth' at row 1

If the datetime value is incorrect, then how am I able to update the date using setter?

Can anyone please help with this and tell me what is the best practice?

Note- Format of date used in Java code is dd/MM/yyyy

Solution

For the HQL query option, you should be binding the Java Date to a named parameter:

Date dob = DateUtils.parseDate("28/09/2002");
session.createQuery("UPDATE Employee SET dateOfBirth = :dob WHERE id = 1")
       .setParameter("dob", dob)
       .executeUpdate();
session.getTransaction().commit();

Answered By - Tim Biegeleisen
Answer Checked By - Gilberto Lyons (JavaFixing Admin)