log4j:ERROR Failed to excute sql错误~

Question

异常信息如下：
log4j:ERROR Failed to excute sql
java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ']' not found.
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1155)
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:684)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1166)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1119)
at org.apache.log4j.jdbc.JDBCAppender.execute(JDBCAppender.java:178)
at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(JDBCAppender.java:250)
at org.apache.log4j.jdbc.JDBCAppender.append(JDBCAppender.java:146)
at org.apache.log4j.AppenderSkeleton.doAppend(AppenderSkeleton.java:251)
at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders(AppenderAttachableImpl.java:66)
at org.apache.log4j.Category.callAppenders(Category.java:206)
at org.apache.log4j.Category.forcedLog(Category.java:391)
at org.apache.log4j.Category.log(Category.java:856)
at org.apache.commons.logging.impl.Log4JLogger.warn(Log4JLogger.java:234)
at com.opensymphony.xwork2.util.logging.commons.CommonsLogger.warn(CommonsLogger.java:60)
at com.opensymphony.xwork2.ognl.OgnlValueStack.handleOgnlException(OgnlValueStack.java:198)
at com.opensymphony.xwork2.ognl.OgnlValueStack.setValue(OgnlValueStack.java:162)
at com.opensymphony.xwork2.ognl.OgnlValueStack.setValue(OgnlValueStack.java:151)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.setParameters(ParametersInterceptor.java:288)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:199)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:207)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:190)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:94)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:243)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:270)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:190)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:498)
at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:619)执行的action如下：
public String findByEEscaleJson() throws Exception {
HttpServletResponse response = ServletActionContext.getResponse();
response.setCharacterEncoding("UTF-8");
PrintWriter pw = response.getWriter();
List allValue = this.viewEEpOtherServiceDAO.findByEEscale(new String(
escale.getBytes("ISO8859-1"), "UTF-8"));
result = new ArrayList();
Iterator it = allValue.iterator();
while (it.hasNext()) {
Object[] all = (Object[]) it.next();
ViewEEpOtherId v = new ViewEEpOtherId();
// id.eno,id.ename,id.eaddress,id.ephone,id.present,id.email,id.website,id.esuoyou,id.tno
v.setEno((String) all[0]);
v.setEname((String) all[1]);
v.setEaddress((String) all[2]);
v.setEphone((String) all[3]);
v.setEpresent((String) all[4]);
v.setEmail((String) all[5]);
v.setWebsite((String) all[6]);
v.setEsuoyou((String) all[7]);
v.setEscale((String) all[8]);
v.setEincome((Double) all[9]);
result.add(v);
}
JSONArray array = JSONArray.fromObject(result);
pw.print(array.toString());
return null;
}
DAOImpl 里面 ：public List findByEEscale(String scale) {
// TODO Auto-generated method stub
String hql ="select id.eno,id.ename,id.eaddress,id.ephone,id.epresent,id.email,id.website,id.esuoyou,id.escale,id.eincome from ViewEEpOther where id.escale like '"+"%"+scale+"%"+"'";
List result = this.getHibernateTemplate().find(hql);
return result;
}
其他的也是按照这样查询的呢，其他的不报错，就这个报错。

Answer 1

where id.escale like '"+"%"+scale+"%"+"'";估计是scale有乱码new String(escale.getBytes("ISO8859-1"), "UTF-8")，我没见过ISO8859-1，我都是用ISO-8859-1你试试看传一个英文或数字进去，如果没错的话 ，就是这个原因了。。

Answer 2

谢谢了先，
我这个 不管数字、字母、中文都抱这个错误呢~
ISO-8859-1改成这样了依然报错~

Answer 3

原来是页面的问题
<td width="54%">
<table border="0" align="right" cellpadding="0"
cellspacing="0">
<tr>
<td width="52">
<table width="90%" border="0" cellpadding="0"
cellspacing="0">
<tr>
<td class="STYLE1">
<div align="center">
<input type="text" id="ename" name="ename"
value="请输入企业名称" onfocus="cls()" onblur="res()" />
</div>
</td>
<td class="STYLE1">
<div align="center">
<s:submit type="image" theme="simple"
name="findByEEname" action="findByEEname"
src="tab/images/search.jpg" value="按企业名称查询">
</s:submit>
</div>
</td>
</tr>
</table>
</td>
<td width="54">
<table width="90%" border="0" cellpadding="0"
cellspacing="0">
<tr>
<td class="STYLE1">
<div align="center">
<input type="text" id="eaddress" name="eaddress"
onfocus="cls()" onblur="res()" value="请输入企业地址" />
</div>
</td>
<td class="STYLE1">
<div align="center">
<s:submit type="image" theme="simple"
name="findByEEaddress" action="findByEEaddress"
src="tab/images/search.jpg" value="按企业地址查询">
</s:submit>
</div>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
原来是这样写的，其他的页面可以这样写。。唯独这个页面不能这样写。解决办法： 用form 把sumbit分别封装
用两个 form

Answer 4

JDBC还是建议用绑定变量的方式去操作数据库。
大型项目的话会优化很多运行效率。另外如果你那个变量是字符串的话。还需要写个方法对里面的特殊字符进行转义操作。
不然的话估计会有问题哦。比如说你的字符串里有个%怎么办。。

Answer 5

同一个form提交了两个相同属性，过去的值是数组你后台不是数组。所以传进去的参数是一串字符。。是这样的吧

Answer 6

sql  where id.escale like '"+"%"+scale+"%"+"'";改成
where id.escale like '%" + scale+ "%'";试试！

Answer 7

LZ啊 你这hql语句里面全部是id.字段 但是你这个id是什么啊？,id.website,id.esuoyou,id.escale,id.eincome from ViewEEpOther id  where... 

Answer 8

缺少在ViewEEpOther as id吧 查了下错误是 无效的sql语句

Answer 9

这个id 是视图，
通过hibernate逆向生成的视图  里面有 viewA，viewAid 类ViewEEpOther.java
/**
 * ViewEEpOther entity. @author MyEclipse Persistence Tools
 */public class ViewEEpOther implements java.io.Serializable { // Fields private ViewEEpOtherId id; // Constructors /** default constructor */
public ViewEEpOther() {
} /** full constructor */
public ViewEEpOther(ViewEEpOtherId id) {
this.id = id;
} // Property accessors public ViewEEpOtherId getId() {
return this.id;
} public void setId(ViewEEpOtherId id) {
this.id = id;
}}
ViewEEpOtherId.java
/**
 * ViewEEpOtherId entity. @author MyEclipse Persistence Tools
 */public class ViewEEpOtherId implements java.io.Serializable { // Fields private String eno;
private String ename;
private String etime;
private String eaddress;
private String ephone;
private String epresent;
private Integer ezipcode;
private String email;
private String website;
private Double emoney;
private String esuoyou;
private String escale;
private Double eincome;
private Long tno; // Constructors /** default constructor */
public ViewEEpOtherId() {
} /** minimal constructor */
public ViewEEpOtherId(String eno, String ename) {
this.eno = eno;
this.ename = ename;
} /** full constructor */
public ViewEEpOtherId(String eno, String ename, String etime,
String eaddress, String ephone, String epresent, Integer ezipcode,
String email, String website, Double emoney, String esuoyou,
String escale, Double eincome, Long tno) {
this.eno = eno;
this.ename = ename;
this.etime = etime;
this.eaddress = eaddress;
this.ephone = ephone;
this.epresent = epresent;
this.ezipcode = ezipcode;
this.email = email;
this.website = website;
this.emoney = emoney;
this.esuoyou = esuoyou;
this.escale = escale;
this.eincome = eincome;
this.tno = tno;
} // Property accessors get,set方法
public boolean equals(Object other) {
..../ } public int hashCode() {
.../ return result;
}}

Answer 10

不是很明白您的意思呢，绑定变量？就是原生是 sql语句的那种形式 带“？”这种的？
我程序中很多变量都是字符串啊，我后天也没有进行转义操作呢，我字符串里面我验证了的，不允许出现特殊字符呢~