Scenario Overview:
I encountered such a problem in development:
System and User relations: A user can belong to multiple systems, a system can have multiple users
Data structure: syssystem-system table, representing different system sysuser-user table, representing the user, attribute systems describes the Many-to-many relationship sys_user_system-relational table (not embodied as entity Class)
when the business logic is: Query a system under the user name contains "xbz" user, his hql should write ...
Hibernate's HQL provides an outer join –left join notation similar to SQL
The above problem solution:
SELECT user from Sysuser user left JOIN user.systems system
WHERE system.id =? And user.username like?
Then the possibility of learning about SQL is coming. Is the left join of SQL not to add an associated condition on?.
A: Personal view: Hibernate originally described the mapping relationship between the entity and the table, since it already knows the relationship between the two tables, why do you want to write the hql left join when you add the associated conditions ... So the hibernate itself does not support keywordon, but we can use the WITH keyword to filter the associated data.
such as:
SELECT user from Sysuser user left JOIN user.systems system with System.Id >100
Association Sysuser with Syssystem, filter condition is more than 100 system number