Spring boot JPA: search entities by field that represent list of embedded objects

Question

I have an entity Order with List of options inside, like this:

@Entity
@Table(name = "orders")
public class OrderEntity extends AuditableEntity {
    private Long passengerId;
    private OrderType type;
    private OrderStatus status;
    @ElementCollection()
    @CollectionTable(name = "options", joinColumns = @JoinColumn(name = "order_id"))
    private List<OrderOptionEntity> options = new ArrayList<>(0);
...

And I want to find all orders, matches specified list of options. I'm using JpaRepository<OrderEntity, Long> for CRUD operations. Unfortunately, when I add method findByOptions, like this:

public interface OrderRepository extends JpaRepository<OrderEntity, Long> {
    List<OrderEntity> findAllByOptions(List<OrderOptionEntity> options);
}

In tests it throws

SqlNode's text did not reference expected number of columns;

So now I just do findAll() and filter all orders manually. Is there any more elegant way for obtain entities, matching by all elements of list inside it?

UPDATE: When I run

@Query("SELECT ord FROM OrderEntity ord WHERE :options MEMBER OF ord.options")
    List<OrderEntity> findAllByOptions(@Param(value = "options") List<OrderOptionEntity> options);

It works fine, but only if ord.options and options in query has size 1, if more - it throws

o.h.engine.jdbc.spi.SqlExceptionHelper : Invalid argument in JDBC call: parameter index out of range: 3

Generated SQL is

 /* SELECT
        ord 
    FROM
        OrderEntity ord 
    WHERE
        :options MEMBER OF ord.options */ select
            orderentit0_.id as id1_3_,
            orderentit0_.version as version2_3_,
            orderentit0_.create_time as create_t3_3_,
            orderentit0_.update_time as update_t4_3_,
            orderentit0_.comment as comment5_3_,
            orderentit0_.distance_to_order as distance6_3_,
            orderentit0_.passenger_id as passenge7_3_,
            orderentit0_.price as price8_3_,
            orderentit0_.route_distance as route_di9_3_,
            orderentit0_.status as status10_3_,
            orderentit0_.type as type11_3_ 
        from
            orders orderentit0_ 
        where
            (
                ? , ?
            ) in (
                select
                    options1_.key,
                    options1_.value 
                from
                    options options1_ 
                where
                    orderentit0_.id=options1_.order_id
            )

All what I want - get all Orders, containing some subset of options.

Yuo can get the OrderEntity form OrderOptionEntity. Will that work? — Ashish Lohia, Aug 01 '17 at 07:26

Abdullah Khan · Answer 1 · 2017-08-01T07:55:32.893

2

You probably forgot the In keyword in your query method.

Try this

public interface OrderRepository extends JpaRepository<OrderEntity, Long> {

    List<OrderEntity> findAllByOptionsIn(List<OrderOptionEntity> options);

}

Take a look at the docs.

edited Aug 01 '17 at 07:55

answered Aug 01 '17 at 07:36

Abdullah Khan

12,010
6
65
78

1

Thanks, I read. But It throws same exception: org.springframework.orm.jpa.JpaSystemException: SqlNode's text did not reference expected number of columns; nested exception is org.hibernate.HibernateException: SqlNode's text did not reference expected number of columns – Владимир Сухарев Aug 01 '17 at 08:34
Is the column you are joining on is correct `joinColumns = @JoinColumn(name = "order_id")`? – Abdullah Khan Aug 01 '17 at 08:45
Can you please debug the code and post the query that runs?! – Abdullah Khan Aug 01 '17 at 08:51
Unfortunately no, my skills is not enough, and whith spring.jpa.properties.hibernate.show_sql=true it's not shown. – Владимир Сухарев Aug 01 '17 at 10:55
Is `OrderOptionEntity` an `@Entity` or an `@Embeddable`? If it is an `@Embeddable` you are probably not able to query for it because of https://stackoverflow.com/a/3709012/923560 . – Abdull Dec 11 '18 at 17:53

Spring boot JPA: search entities by field that represent list of embedded objects

1 Answers1