Multi-Table join in hibernate

Question

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

Asset table.
Transaction table.
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?

If an asset has many transactions, why do you use a OneToOne? Why do you store the ID of an asset in Transaction, instead of a ManyToOne to the asset? And of course, why don't you post the exception stack trace, and the code that causes it? — JB Nizet, May 22 '17 at 07:03

score 1 · Accepted Answer · answered May 22 '17 at 14:56

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>

Multi-Table join in hibernate

1 Answers1