Java SQLGrammarException: 无法执行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31461729/
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
SQLGrammarException: could not execute query
提问by Ravi Kukreja
I am using Struts2 & Hibernate and getting below error when I searched data with string testbut works for me when I searched with numeric 111. I am getting this value from bean class and defined property of string type in bean class.
我正在使用 Struts2 和 Hibernate 并在我使用 string 搜索数据时出现以下错误，test但在我使用 numeric 搜索时对我有用111。我从 bean 类中获取这个值，并在 bean 类中定义了字符串类型的属性。
Below I am providing code:
下面我提供代码：
public String retrieveRecords()
{
String empId = p.getEmpId();
String paramValue = "";
if(empId !=null)
if(!(empId.isEmpty()))
paramValue =" where b.empId="+empId;
String empName = p.getEmployeeName();
if(empName !=null && empName != "")
{
if(!(empName.isEmpty())){
if(paramValue == "")
paramValue =" where b.employeeName="+empName;
else
paramValue =paramValue + " and b.employeeName="+empName;
}
}
System.out.println("=========paramvalues===="+paramValue);
recList = (List<RequestBean>) session.createQuery("from RequestBean b"+paramValue).list();
request.setAttribute("rec", recList);
System.out.println("got size"+recList);
return SUCCESS;
}
Bean Class:
豆类：
public class RequestBean {
private Long id;
private String empId;
private String employeeName;
private String employeeType;
private String personnalNumber;
private String contactNumber;
private String companyName;
private String address;
private String remarks;
private String empStatus = "E";
private Date joiningDate = null;
private Date created;
/************* Getters ************************/
public Long getId() {
return id;
}
public String getEmpId() {
return empId;
}
public String getEmployeeName() {
return employeeName;
}
public String getEmployeeType() {
return employeeType;
}
public String getPersonnalNumber() {
return personnalNumber;
}
public String getContactNumber() {
return contactNumber;
}
public String getCompanyName() {
return companyName;
}
public String getAddress() {
return address;
}
public String getRemarks() {
return remarks;
}
public Date getJoiningDate() {
return joiningDate;
}
public String getEmpStatus() {
return empStatus;
}
public Date getCreated() {
return created;
}
/******************* Setters ***************************/
public void setId(Long id) {
this.id = id;
}
public void setEmpId(String empId) {
this.empId = empId;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public void setEmployeeType(String employeeType) {
this.employeeType = employeeType;
}
public void setPersonnalNumber(String personnalNumber) {
this.personnalNumber = personnalNumber;
}
public void setContactNumber(String contactNumber) {
this.contactNumber = contactNumber;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public void setAddress(String address) {
this.address = address;
}
public void setRemarks(String remarks) {
this.remarks = remarks;
}
public void setJoiningDate(Date joiningDate) {
this.joiningDate = joiningDate;
}
public void setEmpStatus(String empStatus) {
this.empStatus = empStatus;
}
public void setCreated(Date created) {
this.created = created;
}
}
Mapping:
映射：
<hibernate-mapping>
<class name="com.ims.bean.RequestBean" table="EMPDETAILS">
<id name="id" column="id">
<generator class="increment"/>
</id>
<!-- <property name="id" column="id" /> -->
<property name="empId" column="empId"/>
<property name="employeeName" column="empName"/>
<property name="employeeType" column="empType"/>
<property name="personnalNumber" column="personnalNum"/>
<property name="contactNumber" column="contactNo"/>
<property name="companyName" column="empCompanyName"/>
<property name="address" column="address"/>
<property name="remarks" column="remarks"/>
<property name="joiningDate" column="joiningDate"/>
<property name="empStatus" column="empStatus"/>
<property name="created" column="created"/>
</class>
</hibernate-mapping>
Error:
错误：
org.hibernate.exception.SQLGrammarException: could not execute query
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
org.hibernate.loader.Loader.doList(Loader.java:2231)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
org.hibernate.loader.Loader.list(Loader.java:2120)
org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
com.ims.DAO.RequestControllerDAO.retrieveRecords(RequestControllerDAO.java:60)
sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:891)
ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1293)
ognl.ObjectMethodAccessor.callMethod(ObjectMethodAccessor.java:68)
com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethodWithDebugInfo(XWorkMethodAccessor.java:117)
com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethod(XWorkMethodAccessor.java:108)
ognl.OgnlRuntime.callMethod(OgnlRuntime.java:1369)
ognl.ASTMethod.getValueBody(ASTMethod.java:90)
ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
ognl.SimpleNode.getValue(SimpleNode.java:258)
ognl.Ognl.getValue(Ognl.java:494)
ognl.Ognl.getValue(Ognl.java:458)
采纳答案by Roman C
The SQLGrammarExceptionis thrown because the SQL query generated by Hibernate has wrong SQL syntax. The way you built the query is wrong, you shouldn't concatenate values (especially string values) to the result query, because such code is vulnerable for possible SQL injectionattack. Instead, you can use parameters in the query string
在SQLGrammarException被抛出，因为Hibernate生成的SQL查询有错误的SQL语法。您构建查询的方式是错误的，您不应该将值（尤其是字符串值）连接到结果查询，因为这样的代码容易受到可能的SQL 注入攻击。相反，您可以在查询字符串中使用参数
String empId = p.getEmpId();
String paramValue = "";
if (empId !=null && !empId.isEmpty())
paramValue = " where b.empId=:empId";
String empName = p.getEmployeeName();
if (empName !=null && !empName.isEmpty()) {
if (paramValue == "")
paramValue =" where b.employeeName=:empName";
else
paramValue =paramValue + " and b.employeeName=:empName";
}
System.out.println("=========paramvalues===="+paramValue);
Query query = session.createQuery("from RequestBean b"+paramValue);
//now set parameter values
if(empId !=null && !empId.isEmpty())
query.setParameter("empId", empId);
if(empName !=null && !empName.isEmpty())
query.setParameter("empName", empName);
recList = (List<RequestBean>) query.list();
回答by diwinger
Your hql to sql converted query is generating:
您的 hql 到 sql 转换的查询正在生成：
"where employeeName=Name"
while it should be generating:
虽然它应该生成：
"where employeeName='Name'".
So your hql should be :
所以你的 hql 应该是：
"where b.employeeName= ' " +empName+ " ' ";
Note : You don't need to do that for integer values, only applies to String variables.
注意：您不需要对整数值执行此操作，仅适用于字符串变量。
