CODEWITHSUNDEEP
hibernatekotlincriteriacriteria-api
Sahbaz
Sahbaz

Reputation: 1272

Can not build query with criteria api using and with another And Or together

I want to build simple query like this:

SELECT * FROM configuration WHERE store_type = 'REGULAR' AND (country = 'SG' OR country = 'AU')

And i am trying to achieve this using criteria builder. What i am trying to do is to create dynamic query builder like this:

You can guess from the object name, it is object that i will use to generate query dynamically

    data class SearchCriteria (
            val property: String,
            val values: List<String>,
            val operation: String
    )

So in my rest controller, from UI i will get list of those objects and based on them i want to generate query.

I created Specification class where i will build CriteriaBuilder and Predicates, and it looks like this:

override fun toPredicate(root: Root<Configuration>, query: CriteriaQuery<*>, builder: CriteriaBuilder): Predicate? {
    val predicates: MutableList<Predicate> = mutableListOf()

    for (criteria in list) {
        if (criteria.operation == "EQUALS") {
            predicates.add(builder.equal(root.get<String>(criteria.key), criteria.values[0]))
        }

        if (criteria.operation == "EQUALS_OR") {
how to build below line dynamically ?
            predicates.add(builder.or(builder.like(root.get<String>(criteria.key), criteria.values[0]), builder.or(builder.like(root.get<String>(criteria.key), criteria.values[1]))))
        }
    }

    return builder.and(*predicates.toTypedArray())
}

So basically rules are "simple" if criteria operation is EQUALS i want to have simple EQUAL predicate, this is straight forward and it will work automatically.

Actual complication is to mix it with EQUALS_OR operation, where i need to add this:

AND (someProperty = "value" or some property = "another value")

In other words each time when i get EQUALS_OR operator when i get multiple values, i want to build AND (property = "value" OR property = "something else") if i have only one value, then it will be simple and equals

As you can asume, my current solution "works", but i had to hard code builder, does anyone knows how i can make this line dynamic ?

predicates.add(builder.or(builder.like(root.get<String>(criteria.key), criteria.values[0]), builder.or(builder.like(root.get<String>(criteria.key), criteria.values[1]))))

I created this code based on this guide: https://attacomsian.com/blog/spring-data-jpa-specifications, basically it is the same thing

Upvotes: 1

Views: 512

Answers (1)

Sahbaz
Sahbaz

Reputation: 1272

In case that anyone ever need this, i fixed this issue like in code below. Basically i had to figure out how to properly build criteria.

override fun toPredicate(root: Root<Configuration>, query: CriteriaQuery<*>, builder: CriteriaBuilder): Predicate? {
    val predicates: MutableList<Predicate> = mutableListOf()

    for (criteria in list) {
        if (criteria.operation == "EQUALS") {
            predicates.add(builder.equal(root.get<String>(criteria.key), criteria.values[0]))
        }

        if (criteria.operation == "EQUALS_OR") {
            val equalsOrPredicates: MutableList<Predicate> = mutableListOf()
            for (value in criteria.values) {
                equalsOrPredicates.add(builder.and(builder.like(root.get<String>(criteria.key), value)))
            }

            predicates.add(builder.or(*equalsOrPredicates.toTypedArray()))
        }
    }

    return builder.and(*predicates.toTypedArray())
}

Upvotes: 1

Related Questions