MySQL 使用 Hibernate 本机查询插入不适用于 java.util.Date
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5157051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert with Hibernate native query does not work for java.util.Date
提问by Ikthiander
I am using Hibernate JPA and Spring with a Mysql database and I want to insert using a SQL statement like this:
我将 Hibernate JPA 和 Spring 与 Mysql 数据库一起使用，我想使用这样的 SQL 语句插入：
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.
附注。我知道以非本地方式使用 hibernate，但我需要使用本地方式。我也是 insert ...from... ，但我认为这不会有帮助。
Finally I think the problem is mainly with the date. How do you guys pass on MySQL a datetimetype using Java?
最后我认为问题主要在于日期。你们如何datetime使用 Java传递 MySQL类型？
Update:
更新：
The following works fine, I guess it is a java date to mysql datetime conversion problem.
以下工作正常，我猜这是一个java日期到mysql日期时间的转换问题。
("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.Dateto MySQL datetime?
任何人都可以帮助我如何转换java.util.Date为 MySQLdatetime吗？
回答by axtavt
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();
回答by Nicholas Hirras
Looks like a few issues. Some of your fields should have quotes around them. Also, possibly you need to format the timestamp in a different way, not sure how mysql expects it?
好像有几个问题。你的一些字段应该有引号。此外，您可能需要以不同的方式格式化时间戳，不确定 mysql 的期望如何？
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 + "')");
As far as formatting the date, I suspect you will need to look at the SimpleDateFormat class, which will let you get the date into whatever format mysql expects. See http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html
至于格式化日期，我怀疑您需要查看 SimpleDateFormat 类，它可以让您将日期转换为 mysql 期望的任何格式。请参阅http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html
回答by Bogdasya
You can send parameter in method save, or what you use and use named SQL queries
您可以在方法保存中发送参数，或者您使用什么和使用命名的 SQL 查询
Query persistableQuery = entityManager.createNativeQuery("INSERT INTO TASK_ASSESSMENT (ACTIVE_FLAG, ASSESSMENT_DATE, DESCRIPTION, TITLE, NEEDS_LEVEL_ID, PATIENT_ID, USER_ID) VALUES (":active_flag",":timeStampDate", ":description", ":title", ":needsLevelId", ":patientId", ":userId" )").setParameter("active_flag", your_object.getactive_flag).setParametr and etc
persistableQuery.executeUpdate();
但在某处创建具有所有这些字段的对象。回答by sumit
In hibernate 5.3 and above positional parameters are deprecated so we need to use keys for parameter. Hql does not support insert with parameter. We need to follow below approch
在 hibernate 5.3 及以上版本中，位置参数已被弃用，因此我们需要使用键作为参数。Hql 不支持带参数插入。我们需要遵循以下方法
import org.hibernate.query.Query;
public void insertData() {
String sql = "insert into employee(id,name,age,salary) values(:0,:1,:2,:3)";
List<Object> paramList = new ArrayList<Object>();
paramList.add(1); // id
paramList.add("sumit"); // name
paramList.add("23"); // age
paramList.add(10000); // salary
Session session = null;
try {
session = getSessionfactory().openSession();
Query query= session.createNativeQuery(sql);
for(int i=0;i<paramList.size();i++) {
query.setParameter(""+i,paramList.get(i)); // remember to add "" before i , we need to maintain key value pair in setParameter()
}
query.executeUpdate();
}
catch(Exception e) {
System.out.println(e);
}
}
