Dynamic query criteria

[raderio]

At the moment query criteria is hard coded in the method name, so if we need in some cases to find products by created time starts with, and in another case to find products by created time ends with, we need to have 2 methods.

fun findByCreatedTimeStartsWith(createdTime: DateTime)
fun findByCreatedTimeEndsWith(createdTime: DateTime)

As solution can be dynamic query criteria

sealed class OrderedCriteria<T> {
    class Gt<T>(value: T) : OrderedCriteria // greater than this value
    class Gte<T>(value: T) : OrderedCriteria // greater than or equal to this value
    class Lt<T>(value: T) : OrderedCriteria // less than this value
    class Lte<T>(value: T) : OrderedCriteria // less than or equal to this value
    class Match<T>(value: T) : OrderedCriteria // match the value
    // ...
}

fun findBy(createdTime: OrderedCriteria<DateTime>)
fun countBy(createdTime: OrderedCriteria<DateTime>)

In this case we can have just one method for searching and cover all the cases.
This solution is just an alternative way, it doesn't mean the actual approach should be deleted.

Also, we can add sorting

enum class Sorting {
    ASC, DESC, NONE
}

sealed class OrderedCriteria<T> {
    class Gt<T>(value: T, sort: Sorting = Sorting.NONE) : OrderedCriteria
    // ...
}

And we also can add some annotation to method parameters to configure more specific projection if needed.

[graemerocher]

This is the same concept more or less of specifications in Spring Data JPA. We may at some point support these, but it is undecided whether to do so at compilation time or runtime query computation.

[raderio]

This approach is more flexible than Spring Data JPA, where the query is hard-coded in the method name. So if you need to filters entities by created time in some case greater than and in some case less than, we need to have 2 methods, and choose one of depending on that user sends to us.

That I am proposing is to generate a method at compile time that will create the query at run-time based on the parameters passed to this method. In this case, a lot of boilerplate will be eliminated.

[raderio]

I am thinking of something like

// Entities & Projections

class Address(id: Long, country: CountryCode)
class Author(id: Long, name: String, address: Address)
class Book(id: Long, title: String, author: Author)

sealed class BookDTO {
    class Title(title: String) : BookDTO
    class Preview(id: Long, title: String, authorName: String) : BookDTO
    class Searched(id: Long, title: String) : BookDTO
}

sealed class Criteria<T> {
    class Gt<T>(value: T) : Criteria // greater than this value
    class Gte<T>(value: T) : Criteria // greater than or equal to this value
    class Lt<T>(value: T) : Criteria // less than this value
    class Lte<T>(value: T) : Criteria // less than or equal to this value
    class Match<T>(value: T) : Criteria // match the value
    // ...
}

interface CrudRepository {
    fun findOne<T>(title: Criteria<String>,
                   result: Class<T>) : T?

    fun findList<T>(title: Criteria<String>, 
                    result: Class<T>) : List<T>

    fun findPage<T>(title: Criteria<String>,
                    pageable: Pageable,
                    result: Class<T>) : Page<T>

    fun findSlice<T>(title: Criteria<String>, 
                     pageable: Pageable,
                     result: Class<T>) : Slice<T>

    fun count(Criteria<String> title) : Long

    // ...
}

result: Class<T> is the projection that must be returned

// call example
findOne(title=Match("Micronaut"), result=BookDTO.Title)

[raderio]

Any feedback about he approach?

[graemerocher]

@raderio Thanks for the feedback, yes a DSL like that would probably be nice and something we can consider in the future. We certainly need a "Criteria"-like API to allow for dynamic queries as well as static queries.

We first want to get the initial support for static queries stable and released however, so this one is definitely on the radar for the future.

[chenjpu]

mybatis-dynamic-sql

Does this framework make any reference sense?

[chenjpu]

Could there be any progress or plans during 2021?🤔️

[dstepanov]

@chenjpu Are you looking for JPA or JDBC/R2DBC criteria?

[chenjpu]

@dstepanov JDBC/R2DBC criteria

[dstepanov]

I will investigate possible solutions for release 3.2

[graemerocher]

@dstepanov rather than build a new API might be worthy integrating QueryDSL or Immutables

[chenjpu]

I have seen a PR has merged and when will version 3.2 be released?

[dstepanov]

This week

[chenjpu]

1.How to set the parameter ???

class Specifications {

    static PredicateSpecification<Demo> nameEquals(String userName) {
        //TODO: How to set the parameter userName ???
        return (root, criteria) -> criteria.equal(root.get("name"), criteria.parameter(String.class));
    }

    static PredicateSpecification<Demo> ageIsLessThan(int age) {
        return (root, criteria) -> criteria.lessThan(root.get("age"), age);
    }

}

2.Otherwise the following error

11:41:25.825 [default-nioEventLoopGroup-1-2] DEBUG io.micronaut.data.query - Executing Query: SELECT t_."id",t_."name",t_."age" FROM "tpl_demo_t" t_ WHERE (t_."name" = ?)
11:41:25.908 [default-nioEventLoopGroup-1-2] ERROR i.m.http.server.RouteExecutor - Unexpected error occurred: SQL Error preparing Query: null
io.micronaut.data.exceptions.DataAccessException: SQL Error preparing Query: null
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findStream(DefaultJdbcRepositoryOperations.java:390)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.lambda$findAll$6(DefaultJdbcRepositoryOperations.java:526)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.lambda$executeRead$18(DefaultJdbcRepositoryOperations.java:705)
	at io.micronaut.transaction.support.AbstractSynchronousTransactionManager.executeRead(AbstractSynchronousTransactionManager.java:162)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.executeRead(DefaultJdbcRepositoryOperations.java:705)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findAll(DefaultJdbcRepositoryOperations.java:525)
	at io.micronaut.data.runtime.intercept.criteria.FindAllSpecificationInterceptor.intercept(FindAllSpecificationInterceptor.java:48)
	at io.micronaut.data.intercept.DataIntroductionAdvice.intercept(DataIntroductionAdvice.java:115)
	at io.micronaut.data.intercept.DataIntroductionAdvice.intercept(DataIntroductionAdvice.java:89)
	at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:137)

[chenjpu]

@dstepanov Is there any documentation for the above question?

[dstepanov]

There is an official link to the JPA criteria API https://micronaut-projects.github.io/micronaut-data/latest/guide/#dbcCriteriaSpecifications

There are some examples in the repo https://github.com/micronaut-projects/micronaut-data/blob/master/doc-examples/jdbc-example-java/src/main/java/example/PersonRepository.java

[dstepanov]

Closing this issue as there is a way to use the criteria

[chenjpu]

@dstepanov Thanks for this quick reply！Very sorry,Maybe my question is not clear.
I want to generate the sql in the way of parameter-binding.

class Specifications {

    static PredicateSpecification<Demo> nameEquals(String name) {
        return (root, criteria) -> criteria.equal(root.get("name"), name);
    }
}

1.
demoRepository.findAll(Specifications.nameEquals("123"))
log:Executing Query: SELECT t_."id",t_."name",t_."age" FROM "tpl_demo_t" t_ WHERE (t_."name" = '123')

2.
demoRepository.findAll(Specifications.nameEquals("'123'"))
log:Executing Query: SELECT t_."id",t_."name",t_."age" FROM "tpl_demo_t" t_ WHERE (t_."name" = ''123'')

Caused by: org.postgresql.util.PSQLException: ERROR: at or near "123": syntax error
  详细：source SQL:
SELECT t_."id",t_."name",t_."age" FROM "tpl_demo_t" t_ WHERE (t_."name" = ''123'')
 

In the Criteria API mode，how to make name bind as parameter instead of directly in sql, like below

SELECT t_."id",t_."name",t_."age" FROM "tpl_demo_t" t_ WHERE (t_."name" = ?)

[dstepanov]

@chenjpu We don't support that case yet. Can you please describe why are you using 2. and why parameter-binding is something you want to have?

[chenjpu]

Prepare to plan to migrate from the mybatis framework to microanut-data, the previous business has a dynamic query requirements, want to achieve through the Criteria API mode

[chenjpu]

In the query parameter, may include the ' character, if the parameter binding mode is not supported, the user's query will appear sql error.

[dstepanov]

Are you reporting that the ' is not escaped in the query?

[chenjpu]

The query string does not do any escaped

[dstepanov]

Please fill a new issue

[chenjpu]

@dstepanov

#1412

[chenjpu]

@dstepanov
Based on spring-data, using the same test set, there is no such problem as above.

[dstepanov]

@chenjpu I will try to improve it after the next release