CODEWITHSUNDEEP
hibernatemulti-table
Rider
Rider

Reputation: 473

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?

Upvotes: 0

Views: 47

Answers (1)

Sudhakar
Sudhakar

Reputation: 3180

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>

Upvotes: 1

Related Questions