hibernate的left join外连接问题。

[beming]

Hsql语句：
select wp from TbWhPlanbill wp left join wp.tbCustSet as c

TbWhplanbill.hdm.xml:
...
<id name="planbillId" column="planbill_id" type="java.lang.Long">
<generator class="increment"/>
</id>
<property name="custId" column="cust_id" type="java.lang.Long" />

<set name="tbCustSet" inverse="true">
<key column="cust_id">
<one-to-many class="com.TbCust"/>
</set>
...

Hsql语句：
select wp from TbWhPlanbill wp left join wp.tbCustSet as c
翻译为标准的sql为：
select
tbwhplanbi0_.planbill_id as planbill1_954_,
tbwhplanbi0_.cust_id as order2_954_
from
tb_wh_planbill tbwhplanbi0_
left outer join
TB_CUST tbcustset1_
on tbwhplanbi0_.planbill_id=tbcustset1_.cust_id

问题在这里，默认是用TbWhPlanbill的主键来left join tb_cust的主键：
on tbwhplanbi0_.planbill_id=tbcustset1_.cust_id

实际上我是想
on tbwhplanbi0_.cust_id=tbcustset1_.cust_id
也就是用TbWhPlanbill的其中一个列cust_id来对应TbCust的主键cust_id。

可以这样实现吗？怎么配置？怎么弄。
请大家帮忙，谢谢

[majiaid]

学习

[idilent]

property-ref (optional): Specifies that the foreign key refers to columns that are not the primary key of the orginal table. (Provided for legacy data.)

[Saro]

把你的表结构，表关系和你想映射的方案贴出来啊。
从TbWhplanbill.hdm.xml来看TbWhPlanbill是不是和TbCust是one-to-many的关系,从"也就是用TbWhPlanbill的其中一个列cust_id来对应TbCust的主键cust_id"来看，TbWhPlanbill和TbCust是many-to-one的关系,这2个类是不是many-to-many???

[beming]

两个表没有什么实际性的关联的，一个是计划表，一个是客户表
计划表中可能有记录有客户，也可能没有。

一句话：TbWhPlanbill中的custId不一定有值，如果有值，则可以关联TbCust进行查询。

当然，这个查询可以用子查询来进行查询，但是我是在试外连接的时候发现这个问题，想具体了解一下如果用外连接进行查询的话，那么应该怎么配置和处理。

[Saro]

计划表中可能有记录有客户，也可能没有。
------------
那就是one-to-many关联了。

public class TbWhplanbill {
private Long planbillId;
private Cust cust;

//getter/setter method
}

TbWhplanbill.hbm.xml
<id name="planbillId" column="planbill_id" type="java.lang.Long">
<generator class="increment"/>
</id>
<many-to-one name="cust" column="cust_id" type="com.TbCust" />

hql:
select p from TbWhplanbill p left join fetch p.cust

[MagicianLiu]

试试
select wp from TbWhPlanbill wp left join TbCust as c on wp.…… = c.……

[idilent]

<set name="tbCustSet" inverse="true" property-fef="cust-id">
<key column="cust_id">
<one-to-many class="com.TbCust"/>
</set>

怎么不试试看？是因为我的级别低不值得一试吗？

[Saro]

<set property-fef="cust-id">
楼上的，好有自信....查查dtd吧，根本不能这样写的,one-to-many在one这端也能用于<key column="..." property-ref="...">。而且property-fef应为property-ref.

[idilent]

<set name="tbCustSet" inverse="true" >
<key column="cust_id" property-ref="cust-id">
<one-to-many class="com.TbCust"/>
</set>

[idilent]

不能举一反三，只能挑错，哎。

[beming]

谢谢，我会试的，我不是经常上线，呵呵，不好意思啊。

[梦幻圣者]

select wp from TbWhPlanbill wp left join wp.tbCustSet as c on wp.custId = c.custId

[miaoliujun]

可以用DetachedCriteria 或者Criteria
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(TbWhplanbill.class);

detachedCriteria.setFetchMode("custId",FetchMode.JOIN);

然后findByCriteria(detachedCriteria)

[interpb]

up

[jy03108434]

呀~星级人物的问题吗?

[beming]

大家要帮忙啊~

[Saro]

TbWhPlanbill 和TbCust应该是一对多的关系吧？那么应该是TbCust有外键引用TbWhPlanbill的主键planbillId才对，怎么TbWhPlanbill有列custId引用TbCust的主键？？

假设TbCust有列pbId引用TbWhPlanbill主键planbillId，那么以下hql
select wp from TbWhPlanbill wp left join wp.tbCustSet as c
应该翻译为：
select wp.* from TbWhPlanbill wp left outer join tbCustSet as c on wp.planbillId=c.pbId
才对。

感觉这2个类之间的关系和你的映射配置有点莫名其妙。把2个类的hbm文件及数据关系都贴出来看看。

[beming]

to 快乐鸟：
hsql是不支持这样的
你想看看报错吗：
直接用eclipse+hibernate tools
错误：
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 106 [select wp from com.ist.zhifawuliu.storagemanager.basedata.vo.TbWhPlanbill wp left join wp.tbCustSet as c on wp.custId=c.custId]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:240)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:151)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:101)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:470)
at org.hibernate.eclipse.console.views.DynamicQueryTranslatorView$2.execute(DynamicQueryTranslatorView.java:141)
at org.hibernate.console.execution.DefaultExecutionContext.execute(DefaultExecutionContext.java:35)
at org.hibernate.eclipse.console.views.DynamicQueryTranslatorView.generateSQL(DynamicQueryTranslatorView.java:136)
at org.hibernate.eclipse.console.views.DynamicQueryTranslatorView.updateText(DynamicQueryTranslatorView.java:117)
at org.hibernate.eclipse.console.views.DynamicQueryTranslatorView.access$3(DynamicQueryTranslatorView.java:111)
at org.hibernate.eclipse.console.views.DynamicQueryTranslatorView$4.run(DynamicQueryTranslatorView.java:193)
at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:35)
at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:123)
at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:3102)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2761)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1699)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1663)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:367)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:143)
at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:103)
at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:226)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:376)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:163)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.core.launcher.Main.invokeFramework(Main.java:334)
at org.eclipse.core.launcher.Main.basicRun(Main.java:278)
at org.eclipse.core.launcher.Main.run(Main.java:973)
at org.eclipse.core.launcher.Main.main(Main.java:948)
Caused by: line 1:106: unexpected token: on
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1765)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:234)
... 29 more


to 傲龙：
谢谢了，我试试。

[Saro]

detachedCriteria.setFetchMode("",FetchMode.JOIN);
是用于属性为关联实体类的抓取，不要误导人啊。

[beming]

请指教