A filter condition for a many-to-many association is added to the where clause instead of to the left outer join on clause when doing a get by ID with eager join fetching. This means that the select returns no records, when there are associated entities, but they are all filtered out.
The example below generates this SQL:
{code:sql}
select
myuserdbo0_.id as id1_8_0_,
myuserdbo0_.active as active2_8_0_,
myuserdbo0_.name as name3_8_0_,
roles1_.MYUSER_id as MYUSER_i1_8_1_,
myroledbo2_.id as roles_id2_9_1_,
myroledbo2_.id as id1_7_2_,
myroledbo2_.active as active2_7_2_,
myroledbo2_.name as name3_7_2_
from
MYUSER myuserdbo0_
left outer join
MYUSER_MYROLE roles1_
on myuserdbo0_.id=roles1_.MYUSER_id
left outer join
MYROLE myroledbo2_
on roles1_.roles_id=myroledbo2_.id
where
myroledbo2_.active = 1
and myuserdbo0_.id=?
{code}
instead of
{code:sql}
select
myuserdbo0_.id as id1_8_0_,
myuserdbo0_.active as active2_8_0_,
myuserdbo0_.name as name3_8_0_,
roles1_.MYUSER_id as MYUSER_i1_8_1_,
myroledbo2_.id as roles_id2_9_1_,
myroledbo2_.id as id1_7_2_,
myroledbo2_.active as active2_7_2_,
myroledbo2_.name as name3_7_2_
from
MYUSER myuserdbo0_
left outer join
MYUSER_MYROLE roles1_
on myuserdbo0_.id=roles1_.MYUSER_id
left outer join
MYROLE myroledbo2_
on roles1_.roles_id=myroledbo2_.id and myroledbo2_.active = 1
where
and myuserdbo0_.id=?
{code}
BTW: createCriteria with ManyToMany association and Filter creates the correct SQL.
Example DBOs:
{code:java}
@MappedSuperclass
public abstract class AbstractMyDBO implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SequenceName.GENERATOR)
@SequenceGenerator(name = SequenceName.GENERATOR, sequenceName = SequenceName.SEQUENCE)
private Integer id;
...
}
{code}
{code:java}
@Entity
@Table(name = "MYUSER")
@FilterDef(name = "activeUserFilter", defaultCondition = "active = 1")
@Filter(name = "activeUserFilter")
public class MyUserDBO extends AbstractMyDBO {
private String name;
private int active;
@ManyToMany()
@Fetch(FetchMode.JOIN)
@Filter(name = "activeRoleFilter")
private Set<MyRoleDBO> roles;
...
}
{code}
{code:java}
@Entity
@Table(name = "MYROLE")
@FilterDef(name = "activeRoleFilter", defaultCondition = "active = 1")
@Filter(name = "activeRoleFilter")
public class MyRoleDBO extends AbstractMyDBO {
private String name;
private int active;
...
}
{code}
Example Test using above DBOs: testManyToManyFilterGet fails, while testManyToManyFilterGetAll succeeds.
{code:java}
public class HibernateFilterTest extends ... {
@Autowired
private TransactionTemplate transactionTemplate;
@Autowired
private SessionFactory sessionFactory;
@Test
public void testManyToManyFilterGet() {
final List<MyUserDBO> users = new ArrayList<>();
final List<MyRoleDBO> roles = new ArrayList<>();
this.doInTransaction(() -> {
roles.add(this.save(this.role("R1", false)));
roles.add(this.save(this.role("R2", false)));
users.add(this.save(this.user("A", true, roles.get(0), roles.get(1))));
});
this.doInTransaction(() -> {
final MyUserDBO dbUser = this.get(MyUserDBO.class, users.get(0).getId());
Assert.assertNotNull(dbUser);
Assert.assertThat(dbUser.getRoles(), Matchers.empty());
});
}
@Test
public void testManyToManyFilterGetAll() {
final List<MyUserDBO> users = new ArrayList<>();
final List<MyRoleDBO> roles = new ArrayList<>();
this.doInTransaction(() -> {
roles.add(this.save(this.role("R1", false)));
roles.add(this.save(this.role("R2", false)));
users.add(this.save(this.user("A", true, roles.get(0), roles.get(1))));
});
this.doInTransaction(() -> {
final List<MyUserDBO> dbUsers = this.getAll(MyUserDBO.class);
Assert.assertNotNull(dbUsers);
Assert.assertThat(dbUsers.size(), Matchers.is(1));
Assert.assertThat(dbUsers.get(0).getRoles(), Matchers.empty());
});
}
private void doInTransaction(final Runnable r) {
this.transactionTemplate.execute(new TransactionCallback<Object>() {
@Override
public Object doInTransaction(final TransactionStatus status) {
r.run();
return null;
}
});
}
private Session getSession() {
final Session session = this.sessionFactory.getCurrentSession();
session.enableFilter("activeUserFilter");
session.enableFilter("activeRoleFilter");
return session;
}
private <T extends AbstractMyDBO> T save(final T dbo) {
this.getSession().save(dbo);
return dbo;
}
@SuppressWarnings("unchecked")
private <T> T get(final Class<T> clazz, final Serializable id) {
return (T) this.getSession().get(clazz, id);
}
@SuppressWarnings("unchecked")
private <T> List<T> getAll(final Class<T> clazz) {
return this.getSession().createCriteria(clazz).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
}
private MyUserDBO user(final String name, final boolean active, final MyRoleDBO... roles) {
final MyUserDBO user = new MyUserDBO();
user.setName(name);
user.setActive(active ? 1 : 0);
user.setRoles(new HashSet<>(Arrays.asList(roles)));
return user;
}
private MyRoleDBO role(final String name, final boolean active) {
final MyRoleDBO role = new MyRoleDBO();
role.setName(name);
role.setActive(active ? 1 : 0);
return role;
}
}
{code}
|
|