Querying for @Embeddable objects stored in a separate collection

Question

I have a simple relation, in which an entity has many addresses, specific to it, defined as:

@Entity
public class Corporation {

    @Id
    private Long id;

    @ElementCollection
    @CollectionTable(name = "addresses_table", joinColumns = @JoinColumn(name = "corporation_id"))
    private List<Address> addresses = new ArrayList<>();
}

The Address class is annotated with @Embeddable. This works great, as every update on a corporation deletes all of its addresses and then inserts the new ones. This is exactly the behaviour which I'm looking for. The other options which I tried (OneToMany, ManyToMany) lead to poorer performance as i need to jump through hoops and still don't get the simple delete all + insert all behaviour.

However, there's a simple requirement that I need to be able to query the addresses by some criteria. Basically this boils down to a simple findAll(Pageable pageable, Specification spec) method. And this will be enough for the current and the future use-cases.

And now the problem comes that embeddable objects are not Entitys, and therefore I can't create a Spring data repository for them. The only options i can think of are:

1. Implement a custom repo using the native entity manager, but I'm not sure how to most optimally do that in terms of code, and if it can support a generic Specification. If not I can still live with that, as the fields on which the address will be searched are not going to change.

2. Do some join query as in select sth from Corporation c join c.addresses and then limit the results based on the address properties. Here I'm again unsure if this would work and be as performant as the simple queuing of the addresses table directly

Any advice would be appreciated, both on the described options or for some other alternative.

Answer 1

A single table can be mapped to different classes .So why don't create another Address class that is an usual @Entity class such that you can create a repository for it and use the Specification you want to use.

The @Embeddable Address can be considered as an internal class of Corporation for providing that delete all + insert all behaviour. If you want the domain client only deals with one Address class , you can simply convert between the @Embeddable Address and @Entity Address.

Code wise it looks like :

@Entity
public class Corporation {

    @Id
    private Long id;

    @ElementCollection
    @CollectionTable(name = "addresses_table", joinColumns = @JoinColumn(name = "corporation_id"))
    private List<CorporationAddress> addresses = new ArrayList<>();


    public void addAddress(Address address){
       addresses.add(new CorporationAddress(address));
    }

    public List<Address> getAddresses(){
       return addresses.stream()
            .map(CorporationAddress::toAddress).collect(toList());
    }

}


//Or you can put it as the internal static nested class inside Corporation if you like
@Embeddable
public class CorporationAddress {

    //Create from Address
    public CorporationAddress(Address){
    }

    //Convert to Address
    public Address toAddress(){

    }

}

@Entity
public class Address {


} 

Answer 2

If using native queries is acceptable in your project, IMO it will be the simplest and the most performant way for you:

public interface CorporationRepo extends JpaRepository<Corporation, Long> {
    @Query(value = "select a.city as city, a.street as street from addresses_table a where a.corporation_id = ?1", nativeQuery = true)
    List<AddressProjection> getAddressesByCorpId(Long corpId);
}

Here I assume that Address is something like that:

@Data
@Embeddable
public class Address implements Serializable {
    private String city;
    private String street;

    @Tolerate
    public Address(String city, String street) {
        this.city = city;
        this.street = street;
    }
}

and AddressProjection is a Projection like the following:

public interface AddressProjection {
    String getCity();
    String getStreet();

    default Address toAddress() {
        return new Address(getCity(), getStreet());
    }
}

(Note that using aliases (ie a.city as city) in the select query with a projection is mandatory.)

Then you will be able to consume addresses:

corporationRepo.getAddressesByCorpId(corpId)
    .stream()
    .map(AddressProjection::toAddress)
    .forEach(System.out::println);

If you can't use native queries (I don't know why) you can use your 2nd option - a query with join:

public interface CorporationRepo extends JpaRepo<Corporation, Long> {

    @Query(value = "select a.city as city, a.street as street from addresses_table a where a.corporation_id = ?1", nativeQuery = true)
    List<AddressProjection> getAddressesByCorpId(Long corpId);

    @Query("select a from Corporation c join c.addresses a where c.id = ?1")
    List<Address> readAddressesByCorpId(Long corpId);
}

corporationRepo.readAddressesByCorpId(corpId).forEach(System.out::println);

It will produce such a query:

select 
  a.city as col0, 
  a.street as col1 
from 
  corporation c 
  inner join addresses_table a on c.id=a.corporation_id 
where 
  c.id=1

Of course, it's not such an optimal as the first one, but not entirely bad, because it has just one 'join' by the indexed field.

Your 1st option will be the same as the 2nd one because with Specification you will get the same 'joined' query.