I have 2 questions about HQL. Before I listing them, let me give some example data:
Code:
A
- A_ID
- NAME
B
- B_ID
- A_ID
C
- C_ID
- B_ID
D
- D_ID
- C_ID
- LENGTH
- UNIT
Here are my classes:
Code:
@Entity
@Table(name="A")
class A
{
@Id
@Column(name="A_ID", updatable=false)
private Long id;
@Column(name="NAME", nullable=false, length=10, updatable=false)
private String name;
@OneToMany(mappedBy="a", fetch=FetchType.LAZY, cascade={CascadeType.ALL})
@JoinColumn(name="A_ID", nullable=false)
private Set<B> bs;
// Setters and getters
...
}
@Entity
@Table(name="B")
class B
{
@Id
@Column(name="B_ID", updatable=false)
private Long id;
@ManyToOne
@JoinColumn(name="A_ID", nullable=false, insertable=true, updatable=false)
private A a;
@OneToMany(mappedBy="b", fetch=FetchType.LAZY, cascade={CascadeType.ALL})
@JoinColumn(name="B_ID", nullable=false)
private Set<C> cs;
@Transient
private Double length;
@Transient
private String unit;
// Setters and getters
...
}
@Entity
@Table(name="C")
class C
{
@Id
@Column(name="C_ID", updatable=false)
private Long id;
@ManyToOne
@JoinColumn(name="B_ID", nullable=false, insertable=true, updatable=false)
private B b;
@OneToMany(mappedBy="c", fetch=FetchType.LAZY, cascade={CascadeType.ALL})
@JoinColumn(name="C_ID", nullable=false)
private Set<D> ds;
// Setters and getters
...
}
@Entity
@Table(name="D")
class D
{
@Id
@Column(name="D_ID", updatable=false)
private Long id;
@ManyToOne
@JoinColumn(name="C_ID", nullable=false, insertable=true, updatable=false)
private C b;
@Column(name="LENGTH", nullable=false, updatable=false)
private Double length;
@Column(name="UNIT", nullable=false, length=10, updatable=false)
private String unit;
// Setters and getters
...
}
My requirement is:
Quote:
I need to get a list of "B" with aggregated "length" and "unit".
My questions:
1. By reading a lot of documents, I assume this is the only way of doing this grouping, right?
HQL:
Code:
select b.id, sum(d.length) as length, min(d.unit) as unit
from B b
left outer join b.c
left outer join c.d
group by
b.id,
b.a,
(and all of the fields of B)
It is very inconvenience to list all of the fields in the "group by".
2. After getting the list of B, I need to get the related A's name. So I have to do this:
Code:
String name = b.getA().getName();
But since I didn't put the A in the "select" part, this field won't be queried out and assigned to the "b.a" and I will get a NPE. How can I do this?
I know this is so complicated. Thank you for you patience reading this. Please help. I'm waiting for your answer online.
Login
FAQ
Search