a real production scenario. background: 6 tables: Fund, Account, period, periodweight, holding, position.
Fund: The fund information, for example: fund name, total Asset, start time.. etc.
Account: each fund has an account (one to one) to fund.
period: time period ( for example: 2000-01-01 to 2000-12-31)
periodweight: at a certain period, the target holding weight.
holding: IBM, Oracle, GE are stock holdings.
position: IBM($3000), oracle($2000), GE($5000)
if I have a fund name:fake fund, which has a target holding for IBM (30%),Oracle(20%), GE(50%) for the period(2000-01-01 to 2000-12-31), the actural position for the 2000-01-01 is 10%,10%, %80% and on 2000-01-02 is 20%,20%,60% will be represents as these records in table
Code:
Account: id account_Number Fund_id
* 1 0001 10
2 0002 11
Fund: id name other properties...
* 10 fake fund xxx
11 another fake one xxx
period: id start_time end_time fund_id
* 3 2000-01-01 2000-12-31 10
4 2001-01-01 2001-12-31 10
periodWeight: id target_weight holding_id period_id
*11 30% 21 3
*12 20% 22 3
*13 50% 23 3
holding: id name order other properties...
*21 IBM 1 xxx
*22 Oracle 2 xxx
*23 GE 3 xxx
position: id Account_id holding_id date actual_position
1 1 11 2000-01-01 10%
2 1 12 2000-01-01 10%
3 1 13 2000-01-01 80%
4 1 11 2000-01-02 20%
5 1 12 2000-01-02 20%
6 1 13 2000-01-02 60%
The java Class are
Account{
@onetoOne(mappedby="account")
Fund f;
@oneToMany
Set<Position> positions;
}
Fund{
@manyToOne
Account account;
@oneToMany(mappedby="fund")
Set<Period> periods;
}
Period{
@manyToOne
Fund fund;
@oneToMany(mappedby="period")
Set<PeriodWeight> periodWeights;
}
PeriodWeight{
@manyToOne
Period period;
@ManyToOne
Holding holding
}
Holding{
@OneToMany(mappedby="holding")
Set<PeriodWeight> periodWeights;
@OneToMany
Set<Position> positions;
}
Position{
@manyToOne
Account account;
@manyToOne
Holding holding;
}
I want to have a query: Based on the date (2000-01-01) and the fund name(fake fund). I want to build a Fund object, which contains the account and period(2000-01-01 to 2000-12-31), and period contains the periodWeight, and periodWeight contains holding, and holding contains postions for (2000-01-01). when there is no such position, for example, I query 2000-01-03 and fake fund, I want to have the structure, just the position is an empty set in the holding.
this hql can load the structure correctly if there is data.
Code:
select f from Fund f
inner join fetch f.account a
inner join fetch f.period p
inner join fetch p.periodWeight w
inner join fetch w.holding h
inner join fetch h.positions po
where f.name=:name and :date between p.start_date and p.end_date and :date=po.date and po.account= a
the problem is when there is no data at the position table for that day, it return null. I need a sql to give me the structure when there is no data, it can load everything except the position, just leave the position set empty.
another question is what is the better way to load such an complex structure? one hql like these or load part of the structure by one hql then other part by another hql? beause in sql, you alway load them one by one, first is the fund, then period, then weight,then holding, then position etc.. the weight need to be sort based on the holding order.
Code:
select f from Fund f
inner join fetch f.account a
inner join fetch f.period p
inner join fetch p.periodWeight w
inner join fetch w.holding h
left join fetch h.positions po with po.account= a and :date=po.date
where f.name=:name and :date between p.start_date and p.end_date
is something really close, but this give me error,
Code:
org.hibernate.hql.ast.QuerySyntaxException: with-clause not allowed on fetched associations
Login
FAQ
Search