Hibernate uses Criteria to do sub-queries.

为梦守侯

My user has two set attributes inside, role and station. When I want to check the user that contains the role and station attributes, the error is as follows
Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined

I read the sql statement, I know the cause of the error, but I don't know how to solve it!

The problem lies in as EmpID! (There are two as EmpID)
I use Criteria to query, there is no sql and hsql, so I don't know how to change the alias so that it does not conflict. Which expert can help! !! ? ? ?

select * from (select this_.id as id2_2_, this_.DelFlag as DelFlag2_2_, this_.EmpNm as EmpNm2_2_, this_.EmpNum as EmpNum2_2_, this_.LastUpdater as LastUpda5_2_2_, this_.LastUpdTm as LastUpdTm_2_2, Mobile. as Pswd2_2_, this_.Remark as Remark2_2_, this_.Status as Status2_2_, role4_.EmpID as EmpID1, role2_.id as RoleID, role2_.id as id8_0_, role2_.Role as Role8_0_, station6_.EmpID as EmpID, station1_.id as Sta , station1_.id as id6_1_, station1_.DelFlag as DelFlag6_1_, station1_.StaCode as StaCode6_1_, station1_.StaNm as StaNm6_1_ from tblUser this_, tblUserRole role4_, tblRole role2_, tblUserStation station6_, tblStation station_ID_le4 this place RoleID = role2_.id and this_.id = station6_.EmpID and station6_.StaID = station1_.id and station1_.id in (8, 9) and role2_.id in (1) and (1 = 1) and this_.DelFlag < > 6 and this_.id <> 2 order by this_.id asc) where rownum <= 10

为梦守侯

Thank you, brothers and sisters, who can help! ! !

hyysios

What is the Criteria query code?

为梦守侯

Criteria userCriteria = session.createCriteria(User.class);
Criteria stationCriteria = userCriteria.createCriteria("station");
Criteria roleCriteria = userCriteria.createCriteria("role");

哈哈大侠

select * from (

select
this_.id as id2_2_,
this_.DelFlag as DelFlag2_2_,
this_.EmpNm as EmpNm2_2_,
this_.EmpNum as EmpNum2_2_,
this_.LastUpdater as LastUpda5_2_2_,
this_.LastUpdTm as LastUpdTm2_2_,
this_.Mobile as Mobile2_2_,
this_.Pswd as Pswd2_2_,
this_.Remark as Remark2_2_,
this_.Status as Status2_2_,
role4_.EmpID as EmpID1, (as EmpID1)
role2_.id as RoleID,
role2_.id as id8_0_,
role2_.Role as Role8_0_,
station6_.EmpID as EmpID, (as EmpID)
station1_.id as StaID,
station1_.id as id6_1_,
station1_.DelFlag as DelFlag6_1_,
station1_.StaCode as StaCode6_1_,
station1_.StaNm as StaNm6_1_

from tblUser this_, tblUserRole role4_, tblRole role2_, tblUserStation station6_, tblStation station1_

where this_.id=role4_.EmpID and role4_.RoleID=role2_.id and this_.id=station6_.EmpID and station6_.StaID=station1_.id and station1_.id in (8, 9) and role2_.id in (1) and (1=1) and this_.DelFlag<>6 and this_.id<>2

order by this_.id asc)


where rownum <= 10

The as in sql means as an alias.

为梦守侯

This brother didn't understand what I meant, SQL was generated by HIBERNATE himself!

为梦守侯

as EmpID1 is modified by me to test SQL, HIBERNATE generates as EmpID (two!)

ivb260

Map out to see

为梦守侯

<set
            name="role"
            table="tblUserRole"
            lazy="false"
            cascade="none"
            sort="unsorted"
        >

            <key
                column="EmpID"
            >
            </key>

            <many-to-many
                class="com.fedex.qilin.sps.admin.entity.Role"
                column="RoleID"
                outer-join="auto"
             />

        </set>

<set
            name="station"
            table="tblUserStation"
            lazy="false"
            cascade="none"
            sort="unsorted"
        >

            <key
                column="'EmpID'"
            >
            </key>

            <many-to-many
                class="com.fedex.qilin.sps.admin.entity.Station"
                column="StaID"
                outer-join="auto"
             />

        </set>

为梦守侯

can somebody help me please? or how to find out this relationship using HSQL query