I'm piggy-backing off of How to join tables in unidirectional many-to-one condition?.
If you have two classes:
class A {
@Id
public Long id;
}
class B {
@Id
public Long id;
@ManyToOne
@JoinColumn(name = "parent_id", referencedColumnName = "id")
public A parent;
}
B -> A is a many to one relationship. I understand that I could add a Collection of Bs to A however I do not want that association.
So my actual question is, Is there an HQL or Criteria way of creating the SQL query:
select * from A left join B on (b.parent_id = a.id)
This will retrieve all A records with a Cartesian product of each B record that references A and will include A records that have no B referencing them.
If you use:
from A a, B b where b.a = a
then it is an inner join and you do not receive the A records that do not have a B referencing them.
I have not found a good way of doing this without two queries so anything less than that would be great.
Thanks.
As straightforward as it might be in a relational database, when it comes to JPA, the one-to-many database association can be represented either through a @ManyToOne or a @OneToMany association since the OOP association can be either unidirectional or bidirectional.
The <many-to-one> element is used to set the relationship between EMPLOYEE and ADDRESS entities. The name attribute is set to the defined variable in the parent class, in our case it is address. The column attribute is used to set the column name in the parent table EMPLOYEE.
In Many-To-One Unidirectional mapping, one table has a foreign key column that references the primary key of associated table.By Unidirectional relationship means only one side navigation is possible (STUDENT to UNIVERSITY in this example).
I've made an example with what you posted and I think this may work:
select a,b from B as b left outer join b.parent as a
in HQL.
I have to find a "criteria" way of doing that though.
You may do so by specifying the fetch attribute.
(10) fetch (optional) Choose between outer-join fetching and fetching by sequential select.
You find it at: Chapter 6. Collection Mapping, scroll down to: 6.2. Mapping a Collection
EDIT
I read in your question's comment that you wanted a way to perform a raw SQL query? Here a reference that might possibly be of interest:
Chapter 13 - Native SQL Queries
and if you want a way to make it possible through HQL:
Chapter 11. HQL: The Hibernate Query Language
In chapter 11, you want to scroll down to 11.3. Associations and joins.
IQuery q = session.CreateQuery(@"from A as ClassA left join B as ClassB");
I guess however that ClassB needs to be a member of ClassA. Further reasdings shall help.
Another thing that might proove to be useful to you are named queries:
<query name="PeopleByName">
from Person p
where p.Name like :name
</query>
And calling this query from within code like so:
using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction()) {
session.GetNamedQuery("PeopleByName")
.SetParameter("name", "ayende")
.List();
tx.Commit();
}
Please take a look at the referenced link by Ayende who explains it more in depth.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With