使用Hibernate插入本机查询不适用于java.util.Date [英] Insert with Hibernate native query does not work for java.util.Date

问题描述

我使用Hibernate JPA和Spring来创建一个Mysql数据库，我想用这样的SQL语句插入：

 日期saveDate =新日期（）; 
 java.sql.Timestamp timeStampDate = new Timestamp（saveDate.getTime（））; 
查询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（）; 
  

但运行后，我得到以下错误：

  WARN：org.hibernate.util.JDBCExceptionReporter  -  SQL错误：-11，SQLState：37000 
错误：org.hibernate.util.JDBCExceptionReporter  - 意外的标记：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 ?,任何描述，193 ，1，3）] 
  

有人可以帮我解决这个问题吗？ b

PS。我知道以非本地方式使用hibernate，但我需要使用本地方式。我也是插入......来自...，但我认为这不会有帮助。

最后我认为问题主要是与日期有关。你们如何通过Java传递MySQL datetime 类型？

更新：

以下工作正常，我猜这是java日期到mysql datetime转换的问题。

 （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 +） ）; 
  

任何人都可以请帮助我如何转换 java.util.Date 到MySQL datetime ？

解决方案

使用连接将数据插入到查询中，请改用参数。它解决了值的错误表示以及其他许多问题：

  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）//因为您希望它成为TIMESTAMP 
 .setParameter（3，description）
 .setParameter（4，title） 
 .setParameter（5，needsLevelId）
 .setParameter（6，patientId）
 .setParameter（7，userId）
 .executeUpdate（）; 
  

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?

解决方案

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();

这篇关于使用Hibernate插入本机查询不适用于java.util.Date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！