How to join two entities based on NOT condition
I have 2 tables - TableA and TableB for example with some data in them as defined in this post - How to join results from 2 tables based on no condition
Now I am planning to create Hibernate objects for both tables and the entities are not related to each other.
Now I want to get results based on the NOT condition as stated in my previous post I mentioned.
As a result, an SQL query is used:
SELECT id, name, partNumber, Aid, Aname, Apart
FROM TableB AS t
CROSS JOIN (SELECT id AS Aid, name AS Aname, partNumber AS Apart
FROM TableA AS a
WHERE NOT EXISTS (SELECT 1
FROM TableB AS b
WHERE b.partNumber = a.partNumber)) AS c
ORDER BY id
Now, how to create an HQL query or queries for such a scenario. I have looked at the Hibernate documentation for HQL and Criteria Queries, but I have not been able to figure out how I can convert this SQL query to HQL and Criteria Queries. Could you please help me with this.
Update 1:
Based on Vlad's answer, I am not getting the correct output.
Here is the code I wrote:
List<Object[]> list = session.createQuery(
"select a, b " + "from TableB b, TableA a "
+ "where b.partNumber != a.partNumber "
+ "ORDER BY b.id").list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.println(object);
}
}
I got the result below:
A: id=2, name=a2, partNumber=20
B: id=5, name=b1, partNumber=10
A: id=3, name=a3, partNumber=30
B: id=5, name=b1, partNumber=10
A: id=4, name=a4, partNumber=40
B: id=5, name=b1, partNumber=10
A: id=1, name=a1, partNumber=10
B: id=6, name=b2, partNumber=20
A: id=3, name=a3, partNumber=30
B: id=6, name=b2, partNumber=20
A: id=4, name=a4, partNumber=40
B: id=6, name=b2, partNumber=20
A: id=1, name=a1, partNumber=10
B: id=7, name=b3, partNumber=60
A: id=2, name=a2, partNumber=20
B: id=7, name=b3, partNumber=60
A: id=3, name=a3, partNumber=30
B: id=7, name=b3, partNumber=60
A: id=4, name=a4, partNumber=40
B: id=7, name=b3, partNumber=60
A: id=1, name=a1, partNumber=10
B: id=8, name=b4, partNumber=70
A: id=2, name=a2, partNumber=20
B: id=8, name=b4, partNumber=70
A: id=3, name=a3, partNumber=30
B: id=8, name=b4, partNumber=70
A: id=4, name=a4, partNumber=40
B: id=8, name=b4, partNumber=70
As a result, I got records TableA
with id = 1,2,3,4
and for TableB
id = 5,6,7,8
.
But the desired output must have an id TableA
as 3&4
well as an TableB
id as 5,6,7,8
. Details are given in my other post: How to join results from 2 tables based on non-standard conditions
Hibernate generated query:
Hibernate:
/* select
a,
b
from
TableB b,
TableA a
where
b.partNumber != a.partNumber
ORDER BY
b.id */
select
tablea1_.id as id1_0_0_,
tableb0_.id as id1_1_1_,
tablea1_.name as name2_0_0_,
tablea1_.partNumber as partNumber3_0_0_,
tableb0_.name as name2_1_1_,
tableb0_.partNumber as partNumber3_1_1_
from
TableB tableb0_ cross
join
TableA tablea1_
where
tableb0_.partNumber<>tablea1_.partNumber
order by
tableb0_.id
Update 2:
The code I tried now:
List<Object[]> list = session.createQuery("select b, a "
+ "from TableB b, TableA a "
+ "where not exists ( "
+ "select 1 "
+ "from TableB b1, TableA a1 "
+ "where "
+ "b1.partNumber = a1.partNumber and "
+ "b1.id = b.id and "
+ "a1.id = a.id "
+ ") "
+ "order by b.id").list();
for (Object[] objects : list) {
for (Object object : objects) {
System.out.println(object);
}
}
Hibernate generated query:
Hibernate:
select
tableb0_.id as id1_1_0_,
tablea1_.id as id1_0_1_,
tableb0_.name as name2_1_0_,
tableb0_.partNumber as partNumb3_1_0_,
tablea1_.name as name2_0_1_,
tablea1_.partNumber as partNumb3_0_1_
from
TableB tableb0_ cross
join
TableA tablea1_
where
not (exists (select
1
from
TableB tableb2_ cross
join
TableA tablea3_
where
tableb2_.partNumber=tablea3_.partNumber
and tableb2_.id=tableb0_.id
and tablea3_.id=tablea1_.id))
order by
tableb0_.id
Output of this query:
B: id=5, name=b1, partNumber=10
A: id=4, name=a4, partNumber=40
B: id=5, name=b1, partNumber=10
A: id=2, name=a2, partNumber=20
B: id=5, name=b1, partNumber=10
A: id=3, name=a3, partNumber=30
B: id=6, name=b2, partNumber=20
A: id=1, name=a1, partNumber=10
B: id=6, name=b2, partNumber=20
A: id=4, name=a4, partNumber=40
B: id=6, name=b2, partNumber=20
A: id=3, name=a3, partNumber=30
B: id=7, name=b3, partNumber=60
A: id=3, name=a3, partNumber=30
B: id=7, name=b3, partNumber=60
A: id=1, name=a1, partNumber=10
B: id=7, name=b3, partNumber=60
A: id=4, name=a4, partNumber=40
B: id=7, name=b3, partNumber=60
A: id=2, name=a2, partNumber=20
B: id=8, name=b4, partNumber=70
A: id=3, name=a3, partNumber=30
B: id=8, name=b4, partNumber=70
A: id=1, name=a1, partNumber=10
B: id=8, name=b4, partNumber=70
A: id=4, name=a4, partNumber=40
B: id=8, name=b4, partNumber=70
A: id=2, name=a2, partNumber=20
source to share
You need to use a connection like theta:
select b, a
from TableB b, TableA a
where not exists (
select 1
from TableB b1, TableA a1
where
b1.partNumber = a1.partNumber and
b1.id = b.id and
a1.id = a.id
)
order by b.id
or you can also use a SQL query to retrieve entities:
List result = session.createSQLQuery("SELECT b.*, c.* \n" +
"FROM TableB b AS t\n" +
"CROSS JOIN (SELECT id AS Aid, name AS Aname, partNumber AS Apart\n" +
" FROM TableA AS a\n" +
" WHERE NOT EXISTS (SELECT 1\n" +
" FROM TableB AS b\n" +
" WHERE b.partNumber = a.partNumber)) AS c\n" +
"ORDER BY b.id ")
.addEntity("b", B.class)
.addEntity("a", A.class)
.list();
source to share