Hibernate queries for combination of column values

Question

I'm designing a schema for products that have multiple variants:

@Entity
public class Product {
  @Id @GeneratedValue private long id;
  
  @Column(columnDefinition = "JSONB")
  @Type(JsonType.class)
  private List<ProductVariant> variants;
...

@Entity
public class ProductInstance {
  @Id @GeneratedValue private long id;

  @ManyToOne
  private Product product;

  @Column
  private String variantName;

I want to be able to query for ProductInstances by specific (product_id, variant_name) combinations. I haven't found a straightforward way to do this with JPA Repositories or prepared statements.

I could manually generate sql like:

SELECT * FROM product_instance 
WHERE (product_id = '1' and variant_name = 'A') 
   OR (product_id = '2' AND variant_name = 'B')
   ...
   OR (product_id = '15' AND variant_name = 'CC');

Or I could add a new column that duplicates all the data in the existing product_id and variant_name columns in order to use a standard JPA Repository:

    @Column
    private String productIdVariantName;

interface ProductInstanceRepository extends JPARepository<ProductInstance, Long> {
   findByProductIdVariantNameIn(Set<String> combos);

1. Are those the 2 best options?
2. Is there a name for this situation/pattern? It seems like it would come up relatively commonly, but I wasn't able to find articles about it or solutions for it.

Answer 1

Since the Spring Data JPA Documentation does not mention any CONCAT keyword for query creation, you can't do this directly.

You could use a custom query e.g.

public interface ProductInstanceRepository extends JpaRepository<ProductInstance, Long> {
    @Query(value="select * from product_instance where product_id || variant_name in :combos", nativeQuery = true)
    List<ProductInstance> findByProductIdVariantNameIn(Set<String> combos);
}

or you can use @Formula like this

public class ProductInstance {
    @Id @GeneratedValue private long id;

    @ManyToOne
    private Product product;

    private String variantName;

    @Formula("product_id || variant_name")
    private String productIdVariantName;
}

then you could use the query creation provided by spring like this

public interface ProductInstanceRepository extends JpaRepository<ProductInstance, Long> {
    List<ProductInstance> findByProductIdVariantNameIn(Set<String> combos);
}