Java Spring Data @Query with @OneToMany relation returns no result

Question

I have the following entities:

@Entity
public class Customer extends BaseEntity {

    private String firstname;
    private String lastname;
    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    private Set<Address> addresses;

    ...

@Entity
public class Address extends BaseEntity {

    private String street;
    private String houseNumber;
    private String zipCode;
    private String city;
    @ManyToOne
    private Customer customer;

    ...

And the following repository interface class:

@Repository
public interface CustomerRepository extends CrudRepository<Customer, Long> {

    @Query("select c from Customer c join c.addresses a where (a.city = :cityName)")
    List<Customer> findByCity(@Param("cityName")String city);

}

Now, I'm trying to run the following integration test, but it fails and I absolutely don't know why. Unfortunately, I'm a beginner with Spring and I'm trying to learn it ;-)

@Test
public void testFindCustomerByCity() {
    Customer customer = new Customer("Max", "Tester");
    Address address = new Address("Street", "1", "12345", "City");
    HashSet<Address> addresses = new HashSet<Address>();
    addresses.add(address);
    customer.setAddresses(addresses);
    Customer savedCustomer = customerRepository.save(customer);
    Assert.assertTrue(savedCustomer.getId() > 0);

    List<Customer> customerList = customerRepository.findByCity("City");
    Assert.assertThat(customerList.size(), is(1));
}

The error message is:

java.lang.AssertionError: Expected: is <1> but: was <0>

Why is the result empty. Is my test setting wrong? The entity relation? It would be fine, if you can help me.

Answer 1

You can use query method like this. Use underscore (_) to get property child.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {
    List<Customer> findByAddresses_City(String city);
}

Answer 2

You have @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL) on the addresses field in the Customer entity. This basically means that the relationship is managed by the value in the customer field in the Address entity.

In your test code you are only setting the addresses on the customer but not the customer on the address. It is still null, so probably there are 2 records in the database but there is no relation. Hence nothing will be returned from your query.

Setting the collection like you do with setAddresses is a really bad way of doing things in a JPA environment (when you do this on an already existing instance you will overwrite the persistent collection). Remove the setAddresses method and create an addAddress method on the Customer instead.

@Entity
public class Customer extends BaseEntity {

    private String firstname;
    private String lastname;

    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL)
    private final Set<Address> addresses = new HashSet<Address>();

    // No setter, only a getter which returns an immutable collection
    public Set<Address> getAddresses() {
        return Collections.unmodifiableSet(this.addresses);
    }

    public void addAddress(Address address) {
        address.setCustomer(this);
        this.addresses.add(address);
    }

}

This also cleans up your test code a little.

@Test
public void testFindCustomerByCity() {
    Customer customer = new Customer("Max", "Tester");
    customer.addAddress(new Address("Street", "1", "12345", "City"));
    Customer savedCustomer = customerRepository.save(customer);

    Assert.assertTrue(savedCustomer.getId() > 0);

    List<Customer> customerList = customerRepository.findByCity("City");
    Assert.assertThat(customerList.size(), is(1));
}