81,122
社区成员
发帖
与我相关
我的任务
分享100分解决Hibernate中的left outer join问题，在线等
数据库：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')");
...全文
请发表友善的回复…
发表回复
wangguojunmy 2010-11-14
- 打赏
- 举报
[code="JScript" ]{}[/code]
wangguojunmy 2010-11-14
- 打赏
- 举报
[code=JScript ]
<%@ 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]
wangguojunmy 2010-11-14
- 打赏
- 举报
[code={CSS}]
<%@ 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]
guixiaoyan 2010-06-10
- 打赏
- 举报
也遇到同样的问题了。
ee4456 2008-03-25
- 打赏
- 举报
其实完全可以利用HBM。XML来配置这种查询，或者不行直接用named query好了。。。何必搞的这么麻烦呢
allanen 2008-03-25
- 打赏
- 举报
我个人不太喜欢用new Map()这种方式
一般我是把需要取的实体都取出，然后从中得到我需要的值。
把这些需要的值再放到一个JavaBean中作为DTO。
一般我是把需要取的实体都取出，然后从中得到我需要的值。
把这些需要的值再放到一个JavaBean中作为DTO。
allanen 2008-03-25
- 打赏
- 举报
这样的写法用在spring+hibernate+struts项目中是没有问题的
我写这些只是给你个例子参考
大括号的意思是取实体类，我的写法是要取两个相关联的实体类
我写这些只是给你个例子参考
大括号的意思是取实体类，我的写法是要取两个相关联的实体类
zxm_dgcstars 2008-03-24
- 打赏
- 举报
访问实体也不用这样写啊，如果没有实体类，我可以new Map
yibunengjing 2008-03-24
- 打赏
- 举报
hql.append("SELECT {a.*},{b.*} "); 为什么要用大括号，能解释一下吗？
这个是hibernate的规定，查询entity的映射就是这么写的
这个是hibernate的规定，查询entity的映射就是这么写的
jmslyz 2008-03-24
- 打赏
- 举报
怎么错误信息和你贴得sql问不一样呢
com.bmcc.fundmgt.bank.pojo.BtBankAcc b
这是什么？
com.bmcc.fundmgt.bank.pojo.BtBankAcc b
这是什么？
zxm_dgcstars 2008-03-24
- 打赏
- 举报
此项目的是spring+hibernate+struts
你那写法是否支持
hql.append("SELECT {a.*},{b.*} "); 为什么要用大括号，能解释一下吗？
你那写法是否支持
hql.append("SELECT {a.*},{b.*} "); 为什么要用大括号，能解释一下吗？
allanen 2008-03-24
- 打赏
- 举报
List list = getHibernateTemplate().executeFind(new HibernateCallback() {
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。
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。
DiligencyMan 2008-03-23
- 打赏
- 举报
对spring不是很懂，关注下！
zxm_dgcstars 2008-03-23
- 打赏
- 举报
错误信息
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)
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)
