Insert with Hibernate native query does not work for java.util.Date

Question

I am using Hibernate JPA and Spring with a Mysql database and I want to insert using a SQL statement like this:

Date saveDate = new Date();
java.sql.Timestamp timeStampDate = new Timestamp(saveDate.getTime());
Query persistableQuery = entityManager.createNativeQuery("INSERT INTO TASK_ASSESSMENT (ACTIVE_FLAG, ASSESSMENT_DATE, DESCRIPTION, "
        + "TITLE, NEEDS_LEVEL_ID, PATIENT_ID, USER_ID) VALUES ("
        + true +", " + timeStampDate + ", " + description + ", " + title + ", "
        + needsLevelId + ", " + patientId + ", " + userId + " )");
persistableQuery.executeUpdate();

But after running it I get the following error:

WARN : org.hibernate.util.JDBCExceptionReporter - SQL Error: -11, SQLState: 37000
ERROR: org.hibernate.util.JDBCExceptionReporter - Unexpected token: 15 in statement
    [INSERT INTO TASK_ASSESSMENT (ACTIVE_FLAG, ASSESSMENT_DATE, DESCRIPTION, TITLE,
    NEEDS_LEVEL_ID, PATIENT_ID, USER_ID)
    VALUES (true, 2011-03-01 15?, any description, , 193, 1, 3 )]

Could someone help me on this please?

PS. I am aware of using hibernate in non-native way, but I need to use native way. I am also of insert ...from... , but I don't think it will help.

Finally I think the problem is mainly with the date. How do you guys pass on MySQL a datetime type using Java?

Update:

The following works fine, I guess it is a java date to mysql datetime conversion problem.

("INSERT INTO TASK_ASSESSMENT "
     + "(ACTIVE_FLAG, ASSESSMENT_DATE, DESCRIPTION, TITLE, "
     + "NEEDS_LEVEL_ID, PATIENT_ID, USER_ID) "
     + "VALUES (true, 1999-12-22, '" + description + "', '" 
     + title + "', " + needsLevelId+", " + patientId 
     + ", " + userId + ")");

Could anyone please help me on how to convert java.util.Date to MySQL datetime?

Answer 1

Don't use concatenation to insert data into queries, use parameters instead. It solves problem with wrong representation of values, as well as many other problems:

entityManager.createNativeQuery(
    "INSERT INTO TASK_ASSESSMENT (ACTIVE_FLAG, ASSESSMENT_DATE, DESCRIPTION, "
    + "TITLE, NEEDS_LEVEL_ID, PATIENT_ID, USER_ID) VALUES (?, ?, ?, ?, ?, ?, ?)")
    .setParameter(1, true)
    .setParameter(2, saveDate, TemporalType.TIMESTAMP) // Since you want it to be a TIMESTAMP
    .setParameter(3, description)
    .setParameter(4, title)
    .setParameter(5, needsLevelId)
    .setParameter(6, patientId)
    .setParameter(7, userId) 
    .executeUpdate();