I am experiencing strange behavior in the process of upgrading to Hibernate 6.x. It seems @Where annotation on entity class is ignored when using with entity graph or fetch join. Despite the same entity classes and queries, wrong SQL is generated after upgrading to Hibernate 6.x, which is not what I expected.
I have created a sample project to test the issue and show the situation in detail. Here is GitHub repository for the sample project:
- Sample project (with Hibernate 5.6.14.Final): GitHub - dev-jaehoonlee/hibernate-orm-demo at hibernate-5.6
- Sample project (with Hibernate 6.1.6.Final): GitHub - dev-jaehoonlee/hibernate-orm-demo at hibernate-6.1
Entity
Here are my entity class - User, UserDetail and UserSkill.
User
@NamedEntityGraph(
name = "user-entity-graph",
attributeNodes = {
@NamedAttributeNode(value = "detail"),
@NamedAttributeNode(value = "skills")
})
@Table(name = "users")
@Entity(name = "User")
public class User {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "user_id")
private Long id;
@Column(name = "user_name")
private String name;
@OneToOne(mappedBy = "user", fetch = LAZY)
private UserDetail detail;
@OneToMany(mappedBy = "user", fetch = LAZY)
private Set<UserSkill> skills = new HashSet<>();
// getter/setters, equals, hashCode, toString...
}
UserDetail
@Where(clause = "is_active = true")
@Table(name = "user_details")
@Entity(name = "UserDetail")
public class UserDetail {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "detail_id")
private Long id;
@Column(name = "city")
private String city;
@Column(name = "is_active")
private Boolean active;
@OneToOne(fetch = LAZY)
@JoinColumn(name = "user_id")
private User user;
// getter/setters, equals, hashCode, toString...
}
UserSkill
@Where(clause = "has_deleted = false")
@Table(name = "user_skills")
@Entity(name = "UserSkill")
public class UserSkill {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "skill_id")
private Long id;
@Column(name = "skill_name")
private String skillName;
@Column(name = "has_deleted")
private Boolean deleted;
@ManyToOne(fetch = LAZY)
@JoinColumn(name = "user_id")
private User user;
// getter/setters, equals, hashCode, toString...
}
Repository
I have two method findAllUsers() and findUserByName().
public List<User> findAllUsers() {
EntityManager entityManager = entityManagerFactory.createEntityManager();
EntityGraph<?> entityGraph = entityManager.getEntityGraph("user-entity-graph");
TypedQuery<User> query = entityManager.createQuery("FROM User", User.class)
.setHint("jakarta.persistence.loadgraph", entityGraph);
List<User> users = query.getResultList();
entityManager.close();
return users;
}
public User findUserByName(String name) {
EntityManager entityManager = entityManagerFactory.createEntityManager();
EntityGraph<?> entityGraph = entityManager.getEntityGraph("user-entity-graph");
TypedQuery<User> query = entityManager.createQuery("SELECT u FROM User u WHERE u.name = :name", User.class)
.setParameter("name", name)
.setHint("jakarta.persistence.loadgraph", entityGraph);
User user = query.getSingleResult();
entityManager.close();
return user;
}
Generated SQL
Hibernate 5.6
This is SQL queries that Hibernate 5.6.14.Final generated:
# findAllUsers
select
user0_.user_id as user_id1_2_0_,
skills1_.skill_id as skill_id1_1_1_,
userdetail2_.detail_id as detail_i1_0_2_,
user0_.user_name as user_nam2_2_0_,
skills1_.has_deleted as has_dele2_1_1_,
skills1_.skill_name as skill_na3_1_1_,
skills1_.user_id as user_id4_1_1_,
skills1_.user_id as user_id4_1_0__,
skills1_.skill_id as skill_id1_1_0__,
userdetail2_.is_active as is_activ2_0_2_,
userdetail2_.city as city3_0_2_,
userdetail2_.user_id as user_id4_0_2_
from
users user0_
left outer join
user_skills skills1_
on user0_.user_id=skills1_.user_id
and (
skills1_.has_deleted = false
)
left outer join
user_details userdetail2_
on user0_.user_id=userdetail2_.user_id
and (
userdetail2_.is_active = true
)
# findUserByName
select
user0_.user_id as user_id1_2_0_,
skills1_.skill_id as skill_id1_1_1_,
userdetail2_.detail_id as detail_i1_0_2_,
user0_.user_name as user_nam2_2_0_,
skills1_.has_deleted as has_dele2_1_1_,
skills1_.skill_name as skill_na3_1_1_,
skills1_.user_id as user_id4_1_1_,
skills1_.user_id as user_id4_1_0__,
skills1_.skill_id as skill_id1_1_0__,
userdetail2_.is_active as is_activ2_0_2_,
userdetail2_.city as city3_0_2_,
userdetail2_.user_id as user_id4_0_2_
from
users user0_
left outer join
user_skills skills1_
on user0_.user_id=skills1_.user_id
and (
skills1_.has_deleted = false
)
left outer join
user_details userdetail2_
on user0_.user_id=userdetail2_.user_id
and (
userdetail2_.is_active = true
)
where
user0_.user_name=?
As you can see, the clause that I specified in @Where annotation is applied on join statement.
Hibernate 6.1
This is SQL queries that Hibernate 6.1.6.Final generated:
# findAllUsers
select
u1_0.user_id,
d1_0.detail_id,
d1_0.is_active,
d1_0.city,
u1_0.user_name,
s1_0.user_id,
s1_0.skill_id,
s1_0.has_deleted,
s1_0.skill_name
from
users u1_0
left join
user_details d1_0
on u1_0.user_id=d1_0.user_id
left join
user_skills s1_0
on u1_0.user_id=s1_0.user_id
and (
s1_0.has_deleted = false
)
# findUserByName
select
u1_0.user_id,
d1_0.detail_id,
d1_0.is_active,
d1_0.city,
u1_0.user_name,
s1_0.user_id,
s1_0.skill_id,
s1_0.has_deleted,
s1_0.skill_name
from
users u1_0
left join
user_details d1_0
on u1_0.user_id=d1_0.user_id
left join
user_skills s1_0
on u1_0.user_id=s1_0.user_id
and (
s1_0.has_deleted = false
)
where
u1_0.user_name=?
- As you can see,
is_active = trueclause is missing.
Am I doing something wrong, or is this a bug in Hibernate 6?
I think there may be some similarities with the following issues in that some of join condition is missing in generated sql:
- HHH-15902 -
@OneToManyrelationship with@Whereon child table generates wrong sql - HHH-15829 - Malformed query generated when using
@DiscriminatorValue