SQLGrammarException：无法执行查询 [英] SQLGrammarException: could not execute query

查看：112
本文介绍了SQLGrammarException：无法执行查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我使用Struts2&当我使用字符串 test 搜索数据时，Hibernate出现以下错误，但当我使用数字 111 搜索时适用于我。



以下是我提供的代码：


$ b $ （）
{
字符串empId = p.getEmpId（）; $ public String retrieveRecords（）
String paramValue =;
if（empId！= null）
if（！（empId.isEmpty（）））
paramValue =where b.empId =+ empId;

String empName = p.getEmployeeName（）;
if（empName.isEmpty（）））{
if（paramValue ==）
if（empName！= null&& empName！=） ）
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）;
返回SUCCESS;
}

Bean类： 

  public class RequestBean {
 
 private Long id; 
 private String empId; 
私人字符串employeeName; 
私人字符串employeeType; 
私人字符串personnalNumber; 
私人字符串contactNumber; 
 private String companyName; 
私有字符串地址; 
私人字符串备注; 
 private String empStatus =E; 
私人日期joiningDate = null; 
私人创建日期; 
 
 / *************吸气剂************************ / 
 
 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; 
} 
 
 / *******************安装程序**************** *********** / 
 
 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（创建日期）{
 this.created = created; 
} 
 
}

映射： strong>

< hibernate-mapping>
< class name =com.ims.bean.RequestBeantable =EMPDETAILS>

< id name =idcolumn =id>
< generator class =increment/>
< / id>

<！ - < property name =idcolumn =id/> - >
< property name =empIdcolumn =empId/>
< property name =employeeNamecolumn =empName/>
< property name =employeeTypecolumn =empType/>
< property name =personnalNumbercolumn =personnalNum/>
< property name =contactNumbercolumn =contactNo/>
< property name =companyNamecolumn =empCompanyName/>
< property name =addresscolumn =address/>
< property name =remarkscolumn =remarks/>
< property name =joiningDatecolumn =joiningDate/>
< property name =empStatuscolumn =empStatus/>
< property name =createdcolumn =created/>

< / class>
< / hibernate-mapping>

错误：

org.hibernate.exception.SQLGrammarException：无法执行查询
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 （回覆
sun.reflect.GeneratedMethodAccessor76.invoke（Unknown Source）
sun.reflect.DelegatingMethodAccessorImpl.invoke（Unknown Source）
java.lang.reflect.Method.invoke（未知来源）
ognl.OgnlRuntime.invokeMethod（OgnlRuntime.java:891）
ognl.OgnlRuntime.callAppropriateMethod（OgnlRuntime.java:1293）
ognl.ObjectMethodAccessor.callMethod（ObjectMethodAccessor.java:68 ）

com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethod b $ b 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）


解决方案

抛出 SQLGrammarException 由Hibernate生成的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）; 
 //现在设置参数值
 if（empId！= null&&！empId.isEmpty（））
 query.setParameter（empId，empId）; 
 if（empName！= null&&！empName.isEmpty（））
 query.setParameter（empName，empName）; 
 recList =（List< RequestBean>）query.list（）;


I am using Struts2 & Hibernate and getting below error when I searched data with string test but 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.

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)

解决方案

The SQLGrammarException is 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 injection attack. Instead, you can use parameters in the query string

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

这篇关于SQLGrammarException：无法执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆