Support for Query by Example [DATAJPA-218]

[spring-projects-issues]

Joachim Sauer opened DATAJPA-218 and commented

We have a situation where (due to mapping to a legacy system) we occasionally find ourself using composite keys. Those keys are represented by dedicated ID DTOs (for example a FooIdDTO with the long properties barId and quuxId). Thus our repositories often contain methods that like findFooById(FooIdDTO id). While we do need to specify the query itself, it would be great if the mapping of the actual barId and quuxId values to the named parameters could be done in some declarative way instead of having to move the entire method implementation to a custom repository implementation. An alternative use case for this would be a bean that encapsulates a DB-based search: it's properties would "naturally" map onto the defined query, but it would still need to be written explicitly in a custom implementation.

I suspect that this feature would require support from Spring Data Common, but since our primary use case is in Spring Data JPA, I'm reporting it here

---

Issue Links:

• DATACMNS-810 Add core types for Query By Example support
  ("depends on")
• DATAJPA-844 Support for query-by-example in repositories
  ("is duplicated by")
• DATAMONGO-1245 Add support for Query-By-Example

Referenced from: pull request #164

[spring-projects-issues]

Oliver Drotbohm commented

What is the reason you're not using the compound key features of JPA for that. You could introduce a dedicated key class (which you already have actually)

@Embeddable
class FooId {
  Long bar;
  Long foo;
}

And then use that in your entity:

@Entity
class Foo {
  @EmbeddedId FooId id;
}

You could then declare your repository as follows and would get the findOne(…) method out of the box:

interface FooRepository extends CrudRepository<Foo, FooId> { … }

[spring-projects-issues]

Joachim Sauer commented

Actually we do that and in most of the cases it's sufficient.

But we also occasionally return DTOs directly from the repository, for example if we only needs some parts of the entity (to avoid loading the entire entity if only 5 fields are needed).

Since the frontend code never directly interacts with our entities, it provides the parameter as a FooIdDTO class which has the same structure as the FooId class. So passing in a FooId works just fine, but a FooIdDTO doesn't.

(by the way, thanks for fixing my markup)

[spring-projects-issues]

Joachim Sauer commented

(see comment above)

[spring-projects-issues]

Thomas Darimont commented

Hello,

I wonder whether this could be solved in a generic way by providing support for findByExample(exampleObject) / findOneByExample(exampleObject) Query-by-Example Methods?

I think of something like: The Spring Data JPA SQL Generator could generate an appropriate where-condition for every non-null (?)
Attribute of the given example object.

One could also define ad-hoc example queries like:

... someRepository.findByExample(new Example(){
String name = "thomas";
String countryCode = "de";
Boolean tired = true;
})

Best regards,
Thomas

[spring-projects-issues]

Thomas Darimont commented

I eventually gave this a spin - please see the attached PR. Would be great if we could align those efforts with the stuff from DATAMONGO-1245