我正在使用Struts2&Hibernate并在使用字符串搜索数据时出现以下错误,test但是在使用数字搜索时对我有用111。我从豆类和豆类中的字符串类型的已定义的属性中获取此值。
test
111
下面我提供代码:
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; }
豆类:
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; } }
对应:
<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>
错误:
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)
SQLGrammarException之所以引发,是因为Hibernate生成的SQL查询的SQL语法错误。构建查询的方式是错误的,不应将值(尤其是字符串值)连接到结果查询,因为此类代码容易受到SQL注入攻击的攻击。相反,您可以在查询字符串中使用参数
SQLGrammarException
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();