Spring JPA Criteria with Repository, ManyToOne, Select on field of joined table

Question

I'm trying to create a JPA predicate to use as an argument in my JPA repository in order to return results based on a field in the joined table. It never actually filters anything. I get all objects of the primary table back. JPA criteria seems like the right tool for the job because the actual applied application has a number of different conditions and is a dynamic query.

Code:

@Getter
@Setter
@Entity
@Table(name = "FOO")
public class Foo {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String name;

    @OneToMany(mappedBy = "foo")
    private Set<Bar> bars;
}

@Getter
@Setter
@Entity
@Table(name = "BAR")
public class Bar {
    @Id
    @Column(name="id")
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private int id;

    private String name;

    @ManyToOne
    @JoinColumn(name = "foo_id", nullable = false)
    private Foo foo;
}


public class FooSpecs {
    public Specification<Foo> barName(String name) {
        return (root, query, builder) -> {
            Root<Foo> foo = query.distinct(true).from(Foo.class);
            Join<Foo, Bar> bar = foo.join("bars", JoinType.LEFT);

            Predicate pred = builder.equal(bar.get("name"), name);

            return pred;
        };
    }
}

public interface FooRepository extends JpaRepository<Foo, Integer>, JpaSpecificationExecutor {
}

Tests

@RunWith(SpringRunner.class)
@DataJpaTest
@SpringBootTest
public class JpaDemoApplicationTests {

    @Autowired
    FooRepository repo;

    @Test
    public void contextLoads() {
        FooSpecs specs = new FooSpecs();
        Specification<Foo> spec = specs.barName("test 2");
        List<Foo> foos =  repo.findAll(spec);
        // List<Foo> foos =  repo.findAll();

        assertThat(foos)
                .isNotEmpty()
                .hasSize(1);
    }
}

Data

insert into Foo(id, name) values (1, 'foo 1');
insert into Foo(id, name) values (2, 'foo 2');
insert into Foo(id, name) values (3, 'foo 3');

insert into Bar (id, name, foo_id) values (1, 'test 1', 2);
insert into Bar (id, name, foo_id) values (2, 'test 2', 1);

Hibernate query output:

Hibernate: select distinct foo0_.id as id1_1_, foo0_.name as name2_1_ from foo foo0_ cross join foo foo1_ left outer join bar bars2_ on foo1_.id=bars2_.foo_id where bars2_.name=?

I'm not the most hip with joins but that cross join seems out of place and based on my understanding could be a part of the issue.

Test output:

Expected size:<1> but was:<3> in:
<[com.example.jpademo.dto.Foo@6e0e5dec,
    com.example.jpademo.dto.Foo@56476c16,
    com.example.jpademo.dto.Foo@497b560e]>, mergedContextConfiguration = [MergedContextConfiguration@525f1e4e testClass = JpaDemoApplicationTests, locations = '{}', classes = '{class com.example.jpademo.JpaDemoApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[[ImportsContextCustomizer@75f9eccc key = [org.springframework.boot.autoconfigure.cache.CacheAutoConfiguration, 
java.lang.AssertionError: 
Expected size:<1> but was:<3> in:
<[com.example.jpademo.dto.Foo@6e0e5dec,
    com.example.jpademo.dto.Foo@56476c16,
    com.example.jpademo.dto.Foo@497b560e]>

The result I expect would be Foo of name 'foo 1'

score 0 · Accepted Answer · answered Dec 01 '17 at 19:03

public class FooSpecs {
    public Specification<Foo> barName(String name) {
        return (root, query, builder) -> {
            Join<Foo, Bar> bar = root.join("bars");
            Predicate pred = builder.equal(bar.get("name"), name);
            return pred;
        };
    }
}

Spring JPA Criteria with Repository, ManyToOne, Select on field of joined table

1 Answers1