hibernatemulti-table

Multi-Table join in hibernate


I have a case in which I have to join 3 tables. The situation is like as below:

  1. Asset table.
  2. Transaction table.
  3. Employee table.

Now,

a) Asset can have multiple Transactions there is a relationship of OneToMany.
b) One Transaction can belong to only one Employee So, one to one relation .

--> I have to get List of Assets for one Employee.

My schema is:

a) AssetTbl:

@Id
@ColumnName("ASSETID")
int assetId;

@OneToMany()
@JoinColumn(name="ASSET_ID",referncedColumnName="AssetId")
List<TransactionTbl> trans;

b) TransactionTbl:

@Id
@ColumnName("TRANS_ID")
int transId;

@ColumnName("ASSET_ID")
int assetId;

@OneToOne()
@JoinColumn(name="Emp_ID",referncedColumnName="Emp_Id")
EmployeeTbl emp;

c) EmployeeTbl:

@Id
@ColumnName("Emp_ID")
int empId;

When I join and run Query it gives Exception employeeTbl does not exist in AssetTbl. Is there any problem in my schema?


Solution

  • I believe AssetTbl has transactionId. Transaction table has employeeId as reference.

    Please find the below queries to get the assets for an emp.

    @Query("select assetTbl from AssetTbl as assetTbl
    inner join assetTbl.trans as trans
    inner join trans.emp as emp
    where emp.empId = :empId)
    

    or

    <query name="findAssetByEmployee">
        <query-param name="empId" type="long"/>
        select assetTbl from AssetTbl as assetTbl
        inner join assetTbl.trans as trans
        inner join trans.emp as emp
        where emp.empId = :empId
    </query>