java - Spring Data - Query By Example result different from JPQL result

Question

During a Unit test i found a small "odd behavior" on query example.

I have the following entity definition (Keycloak entity model).

the second result list is inversed, usually i don't use examples on nested attributes but since it worked it is just odd. that the list of example is not the same as the query. also i mention that is not an issue for me... i just tried to simplify the talk with the DB with an Example.of...

public class RoleEntity {
    @Id
    @Column(name="ID", length = 36)
    @Access(AccessType.PROPERTY) // we do this because relationships often fetch id, but not entity.  This avoids an extra SQL
    private String id;

    @Nationalized
    @Column(name = "NAME")
    private String name;
    @Nationalized
    @Column(name = "DESCRIPTION")
    private String description;

    // hax! couldn't get constraint to work properly
    @Column(name = "REALM_ID")
    private String realmId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "REALM")
    private RealmEntity realm;

    @Column(name="CLIENT_ROLE")
    private boolean clientRole;

    @Column(name="CLIENT")
    private String clientId;

    // Hack to ensure that either name+client or name+realm are unique. Needed due to MS-SQL as it don't allow multiple NULL values in the column, which is part of constraint
    @Column(name="CLIENT_REALM_CONSTRAINT", length = 36)
    private String clientRealmConstraint;

    @ManyToMany(fetch = FetchType.LAZY, cascade = {})
    @JoinTable(name = "COMPOSITE_ROLE", joinColumns = @JoinColumn(name = "COMPOSITE"), inverseJoinColumns =   @JoinColumn(name = "CHILD_ROLE"))
    private Set<RoleEntity> compositeRoles;

    @OneToMany(cascade = CascadeType.REMOVE, orphanRemoval = true, mappedBy="role")
    @Fetch(FetchMode.SELECT)
    @BatchSize(size = 20)
    protected Collection<RoleAttributeEntity> attributes;
}

 @Table(name = "ROLE_ATTRIBUTE")
@Entity
public class RoleAttributeEntity {

    @Id
    @Column(name = "ID", length = 36)
    @Access(AccessType.PROPERTY)
    protected String id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ROLE_ID")
    protected RoleEntity role;

    @Column(name = "NAME")
    protected String name;

    @Nationalized
    @Column(name = "VALUE")
    protected String value;
}

@Repository
public interface RoleEntityRepository extends JpaRepository<RoleEntity, String> {
    
    @Query(value = "select r from RoleEntity r inner join r.attributes ra where ra.name = 'action'")
    List<RoleEntity> getSysmatActionRoles();
    
}


I executed this unit test as bellow. 

@Test
    void assertQueryResultRoleAttributeEntity() {
        RoleEntity roleEntitySample = new RoleEntity();
        RoleAttributeEntity rae = new RoleAttributeEntity();
        rae.setName("action");
        roleEntitySample.getAttributes().add(rae);
        Example<RoleEntity> exampleRole = Example.of(roleEntitySample);
        List<RoleEntity> lr1 = roleEntityRepository.findAll(exampleRole);
        List<RoleEntity> lr = roleEntityRepository.getSysmatActionRoles();
        assertNotNull(lr);
        assertTrue(lr.size() > 0);
        lr
        .stream()
        .forEach(r -> {
            System.err.println(r.getName());
        });
        //utializando example resultado incorreto.
        assertNotNull(lr1);
        assertTrue(lr1.size() > 0);
        lr1
        .stream()
        .forEach(r -> {
            System.err.println(r.getName());
        });
        
    }

Just Extented the Unit Test to determine the problem. Spring Data Example does not verify the Lazy Collections (attribute in question). Second unit test is bellow. Problem solved using the RoleAttributeEntity on example

@Test 
void assertQueryResultRoleAttributeEntity() {
    RoleAttributeEntity rae = new RoleAttributeEntity(); 
    rae.setName(SYSMAT_ACTION_NAME);
    Example<RoleAttributeEntity> ex = Example.of(rae);
    List<RoleAttributeEntity> list = roleAttributeEntityRepository.findAll(ex);
    list
    .stream()
    .forEach(r -> {
        System.err.println(r.getRole());
        //assertTrue(list1.size() == 1);
    });
}


Regards. 

question from:https://stackoverflow.com/questions/65833222/spring-data-query-by-example-result-different-from-jpql-result

Answer 1

Waitting for answers