stalwartwill
2012-10-24 23:45
浏览 242
已采纳

Hibernate联合主键一对多映射无法执行select count(distinct parent) from parent

问题是这样的,Parent对象和Child对象存在一对多的关系,其定义如下:
[code="java"]
Parent {
public static class CompositeId {
private String keyCol1;
private String keyCol2;
//set get方法省略了
}

  private CopositeId compId;
  private String parentProp;
  private Set<Child> children = new HashSet<Child>();
  //set get....

}

Child {
private Long id;
private String childCol1;
private String childCol2;
private String childProp;
//set get...
}
[/code]

配置文件如下:
[code="java"]















<class name="pkgpath.Child" table="Child">
    <id name="id" type="java.lang.Long">
        <column name="ID"/>
        <generator class="sequence">
            <param name="sequence">ID_SEQ</param>
        </generator>
    </id>
    <property name="childCol1" type="java.lang.String">
       <column name="Child_Col1"/>
    </property>
    <property name="childCol2" type="java.lang.String">
       <column name="Child_Col2"/>
    </property>
    <property name="childProp" type="java.lang.String">
       <column name="Child_Prop"/>
    </property>
</class>
[/code]

在使用
select count(distinct parent) from Parent parent left join parent.children child where child.childProp='xxx'
时,程序报了如下异常信息:Caused by: java.sql.SQLException: ORA-00907: 缺失右括号
查看了Hibernate执行的SQL脚本,发现转化成的SQL为:
select * from (
select count(distinct(parent.keyCol1,parent.keyCol2)) from Parent parent,Child child Where parent.keyCol1=child.child_col1(+) and parent.keycol2=child.child_col2(+) and child.child_prop='xxx'

请教怎么才能获取真实的不重复的Parent的个数呢?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • jinnianshilongnian 2012-10-25 22:52
    已采纳

    复合主键惹得祸

    1通过字符串连接拼成1个
    select count(distinct (parent.keyCol1 || parent.keyCol2)) from Parent parent left join parent.children child where child.childProp='xxx'

    2、分组统计
    select count(1) from Parent parent left join parent.children child where child.childProp='xxx' group by parent.keyCol1,parent.keyCol2

    打赏 评论

相关推荐 更多相似问题