How to use specification to make two "OR" condition with multiple "AND" condition in mysql query

Question

I want to make a following query with help of Specification

select * from table where (order_quantity > 0 or product_verified = false) and sku = "12345";

following is my code but i got a wrong result set

Specification<JitOrderItem> specification = JitOrderItemSpecification.findAll();

specification = Specifications.where(specification).and(
                    Specifications.where(specification).or(JitOrderItemSpecification.filterByJitOrderQuantityGreaterThan(0))
                    .or(Specifications.where(specification).and(JitOrderItemSpecification.filterByProductVerified(false)))

                );
specification = Specifications.where(specification).and(JitOrderItemSpecification.filterBySku(sku));

Put show sql=true to be able to verify your hibernate query. — CRISTIAN ROMERO MATESANZ, Aug 30 '18 at 09:14

Ken S · Accepted Answer · 2018-08-30T05:45:47.980

I'm assuming you are using JPA, probably hibernate. You could enable Logging (for hibernate it's "org.hibernate.sql" to debug). That way you could see the generated query, which might help you in debugging these yourself.

Looking at your code, your final specification would look something like this:

Specifications.where(
    Specifications.where(JitOrderItemSpecification.findAll())
        .and(
            Specifications.where(JitOrderItemSpecification.findAll())
                .or(JitOrderItemSpecification.filterByJitOrderQuantityGreaterThan(0))
                .or(Specifications.where(JitOrderItemSpecification.findAll()).and(JitOrderItemSpecification.filterByProductVerified(false)))
        )
)
.and(JitOrderItemSpecification.filterBySku(sku));

You are basically querying the following (WHERE clause only):

(
    ALL JitOrderItems
    AND
    (
        ALL JitOrderItems
        OR 
        OrderQuantityGreaterThan 0
        OR 
        (
            ALL JitOrderItems
            AND
            ProductVerified false
        )
    )
)
AND
JitOrderItemSpec.filterBySku(sku)

What you want probably is the following:

Specifications.where(
    JitOrderItemSpecification.filterByJitOrderQuantityGreaterThan(0).or(JitOrderItemSpecification.filterByProductVerified(false)))
    .and(JitOrderItemSpecification.filterBySku(sku));

How to use specification to make two "OR" condition with multiple "AND" condition in mysql query

1 Answers1