数据库:orcale9i
hibernate 3.0
left outer join语句在plsql 运行正常,改成hql语句后,却报错误,不知道什么原因,请大家帮忙解决!hql语句:
StringBuffer hsqlOneLine = new StringBuffer("select new Map(a.bank_code as bankCode,a.bank_name as bankName,a.bank_type as bankType,a.type_name " +
" as typeName,a.bif_code as bifCode,b.bankAcc as bankAcc,b.accName as accName,b.accType as accType,b.isgather as isGather,c.bankBal as bankBal,c.balDate as balDate ) from BtBankAcc b "+
" left outer join VBanKTypeAndBanK a on b.btBank.bankCode = a.bank_code "+
" left outer join BisAccHisBal c on b.bankAcc = c.id.bankAcc "+
" and b.validSign=1 and b.accountstate <> 4 "+
" and b.bisType=? "+
" and c.balDate=to_date(?,'yyyy-MM-dd')");
hibernate 3.0
left outer join语句在plsql 运行正常,改成hql语句后,却报错误,不知道什么原因,请大家帮忙解决!hql语句:
StringBuffer hsqlOneLine = new StringBuffer("select new Map(a.bank_code as bankCode,a.bank_name as bankName,a.bank_type as bankType,a.type_name " +
" as typeName,a.bif_code as bifCode,b.bankAcc as bankAcc,b.accName as accName,b.accType as accType,b.isgather as isGather,c.bankBal as bankBal,c.balDate as balDate ) from BtBankAcc b "+
" left outer join VBanKTypeAndBanK a on b.btBank.bankCode = a.bank_code "+
" left outer join BisAccHisBal c on b.bankAcc = c.id.bankAcc "+
" and b.validSign=1 and b.accountstate <> 4 "+
" and b.bisType=? "+
" and c.balDate=to_date(?,'yyyy-MM-dd')");
org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: on near line 1, column 346 [select new Map( a.bank_code as bankCode,a.bank_name as bankName,a.bank_type as bankType,a.type_name as typeName, a.bif_code as bifCode,b.bankAcc as bankAcc,b.accName as accName,b.accType as accType,b.isgather as isGather,c.bankBal as bankBal,c.balDate as balDate) from com.bmcc.fundmgt.bank.pojo.BtBankAcc b left outer join VBanKTypeAndBanK a on b.btBank.bankCode = a.bank_code left outer join FundOfflineaccounthisbalance c on b.bankAcc = c.id.bankAcc and b.validSign=1 and b.accountstate <> 4 and b.bisType= ? and c.balDate=to_date(?,'yyyy-MM-dd')]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 346 [select new Map( a.bank_code as bankCode,a.bank_name as bankName,a.bank_type as bankType,a.type_name as typeName, a.bif_code as bifCode,b.bankAcc as bankAcc,b.accName as accName,b.accType as accType,b.isgather as isGather,c.bankBal as bankBal,c.balDate as balDate) from com.bmcc.fundmgt.bank.pojo.BtBankAcc b left outer join VBanKTypeAndBanK a on b.btBank.bankCode = a.bank_code left outer join FundOfflineaccounthisbalance c on b.bankAcc = c.id.bankAcc and b.validSign=1 and b.accountstate <> 4 and b.bisType= ? and c.balDate=to_date(?,'yyyy-MM-dd')]
Caused by:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 346 [select new Map( a.bank_code as bankCode,a.bank_name as bankName,a.bank_type as bankType,a.type_name as typeName, a.bif_code as bifCode,b.bankAcc as bankAcc,b.accName as accName,b.accType as accType,b.isgather as isGather,c.bankBal as bankBal,c.balDate as balDate) from com.bmcc.fundmgt.bank.pojo.BtBankAcc b left outer join VBanKTypeAndBanK a on b.btBank.bankCode = a.bank_code left outer join FundOfflineaccounthisbalance c on b.bankAcc = c.id.bankAcc and b.validSign=1 and b.accountstate <> 4 and b.bisType= ? and c.balDate=to_date(?,'yyyy-MM-dd')]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java(Inlined Compiled Code))
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java(Compiled Code))
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java(Compiled Code))
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:842)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:840)
at com.bmcc.fundmgt.base.dao.BaseDao.getObjectList(BaseDao.java:273)
at com.bmcc.fundmgt.bank.dao.impl.BisAccBalDaoImpl.getAllBisAccBal(BisAccBalDaoImpl.java:112)
at com.bmcc.fundmgt.report.handler.MouthBankRollBranchHandler.getAllBankRollBranch(MouthBankRollBranchHandler.java:25)
at com.bmcc.fundmgt.report.handler.MouthBankRollBranchHandler$$FastClassByCGLIB$$36e464f7.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java(Compiled Code))
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java(Compiled Code))
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java(Compiled Code))
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java(Compiled Code))
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java(Compiled Code))
at com.bmcc.fundmgt.report.handler.MouthBankRollBranchHandler$$EnhancerByCGLIB$$3463b22a.getAllBankRollBranch(<generated>)
at com.bmcc.fundmgt.report.actions.MouthBankRollBranchAction.execute(MouthBankRollBranchAction.java:28)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1572)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1521)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:136)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:119)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:55)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:142)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:121)
at com.bmcc.framework.security.filter.SecurityFilter.doFilter(SecurityFilter.java:180)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:142)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:121)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
public Object doInHibernate(Session s) throws HibernateException,
SQLException {
StringBuffer hql = new StringBuffer();
hql.append("SELECT {a.*},{b.*} ");
hql.append("FROM TABLE_A a ");
hql.append("LEFT OUTER JOIN TABLE_B b ON a.ID=b.ID ");
hql.append("where ........ ");
SQLQuery query = s.createSQLQuery(hql.toString());
query.addEntity("a", BtBankAcc.class);
query.addEntity("b", VBanKTypeAndBanK.class);
return query.list();
}
});
return list;
参考这个写 ,里面的hql是sql语句,对应表和表中的字段,在后面关联pojo。
你那写法是否支持hql.append("SELECT {a.*},{b.*} "); 为什么要用大括号,能解释一下吗?
com.bmcc.fundmgt.bank.pojo.BtBankAcc b
这是什么?
我写这些只是给你个例子参考
大括号的意思是取实体类,我的写法是要取两个相关联的实体类
一般我是把需要取的实体都取出,然后从中得到我需要的值。
把这些需要的值再放到一个JavaBean中作为DTO。
<%@ page contentType="text/html;charset=gb2312" import="com.jspsmart.upload.*,java.util.*" %>
<%request.setCharacterEncoding("UTF-8");%>
<%
String dfilename=request.getParameter("dfilename");
//out.print("HI");
// 新建一个SmartUpload对象
SmartUpload su = new SmartUpload();
// 初始化
su.initialize(pageContext);
su.setContentDisposition(null); //禁止浏览器自动打开
// 下载文件
try{
su.downloadFile(""+dfilename);
}catch(Exception e){
out.print("<script>alert('下载文件不存在!');</script>");
}
out.clear();
out = pageContext.pushBody();
%> [/code]
<%@ page contentType="text/html;charset=gb2312" import="com.jspsmart.upload.*,java.util.*" %>
<%request.setCharacterEncoding("UTF-8");%>
<%
String dfilename=request.getParameter("dfilename");
//out.print("HI");
// 新建一个SmartUpload对象
SmartUpload su = new SmartUpload();
// 初始化
su.initialize(pageContext);
su.setContentDisposition(null); //禁止浏览器自动打开
// 下载文件
try{
su.downloadFile(""+dfilename);
}catch(Exception e){
out.print("<script>alert('下载文件不存在!');</script>");
}
out.clear();
out = pageContext.pushBody();
%> [/code]