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
?
Advertisement
Answer
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();