Query different models from the same set of tables with spring-data

Question

I would like to know how to best use Spring Data to query the same set of database tables differently depending on the use cases.

Scenario: Products can be bought by Users via Orders. Each Product can either be a standalone product or consist of multiple Subproducts (self referencing table).

The database schema is managed separately.

What’s the most elegant way to retrieve different models from the same set of tables using Spring Data? Considering the following queries:

1. Get all products including their subproducts
2. Get all the users that purchased a product
3. Get all products with the number of purchases for each.

From a framework agnostic view I would create separate models for each and then fill them with the result of an sql query. This would lead to

1. A CombiProduct entity that maintains references to other CombiProducts.
2. A UserProduct that maintains references to all users that bought it.
3. A PurchasedProduct that maintains a purchase count. Each model would be different, because the use case that needs the UserProduct doesn't need information about sub products for example. The use case would then define what's needed from the database.

But with Spring Data i can't find any easy-to-use solutions for scenarios like this. Because the whole Repository support in spring-data-jdbc requires me to define AggregateRoots. And with Aggregate Roots it seems like I can only ever have a table/entity in a single Aggregate (and not have three separate ones like described above) - Am I right?

And the other possibility is querying the database using JdbcTemplate which looks like it could end up beeing a lot of boilerplate code.

What is the spring-data way of creating solutions like that?

Answer 1

Spring Data JDBC manages aggregates as described in Domain Driven Design.

The primary aggregates in your model seem to be those that you already described: Product, Order, and User. Each of those would get their respective repository. For example

interface ProductRepository extends CrudRepository<Product, Long>{}

References between those are modelled via ids, not Java references. See https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates for more details.

With this number 2 in your list of examples is pretty straight forward

2. Get all the users that purchased a product

You would simply create a method in your UserRepository with a custom query.

interface UserRepository extends CrudRepository<User, Long> {

    @Query("""
        select * from User 
        where id in (
            select o.user_id 
            from Order o
            join Order_Item oi
            on o.id = oi.order_id
            where oi.product_id = :product_id
       )
    """
    List<User> findAllByOrderedProduct(Long productId)
}

Number three is a little more tricky.

3. Get all products with the number of purchases for each.

I see 3 main ways to do this.

1. You could either make the number of purchases a read only property of your Product and map the complete entity to a view containing that column. Of course you would need to make that view updatable.

2. You could make a separate ProductWithStatistics entity, for which the repository is a readonly one. You would map that to the view as in the previous variant, but would define your repository as:

interface ProductWithStatistics extends Repository<ProductWithStatistics, Long> { // <-- note: not a CrudRepository
    Optional<T> findById(ID id);

    boolean existsById(ID id);

    Iterable<T> findAllById(Iterable<ID> ids);

    long count();
}

These are the reading repository methods from the CrudRepository except findAll which probably doesn't make sense for Product.

3. The JdbcTemplate is always a good fallback, when Spring Data JDBC doesn't give you what you need. And with DataClassRowMapper the amount of boiler plate should be limited in most cases.

For the first use case

1. Get all products including their subproducts

I'm afraid you are limited to JdbcTemplate if you want to load them in one go, because I don't see a way how a self refence like this can be turned into a single aggregate.

Finally:

with Aggregate Roots it seems like I can only ever have a table/entity in a single Aggregate (and not have three separate ones like described above) - Am I right?

No, that is not correct. For example the Order aggregate almost certainly contains a LineItem entity of some sort, which will be stored in its own table.