Spring data JPA query with varying params

Question

I´m trying to find necessary elements of the table by several parameters like

List <Person> findByLastname(String lastname);

But what if some of this parameters will be added/deleted in a future? How to make some parameters optional using Spring Data JPA, something like this

List <Person> findByOptionalLastnameAndOptionalFirstNameAnd...(String lastname, String firstname,...);

score 3 · Accepted Answer · edited May 23 '17 at 11:54

My task was solved by using querydsl framework. If you use gradle, you will need to add this in build.gradle:

dependencies {
  compile "com.mysema.querydsl:querydsl-jpa:3.6.3"

  compile "com.mysema.querydsl:querydsl-apt:3.6.3:jpa" // Magic happens here

  compile "org.hibernate:hibernate-entitymanager:4.3.5.Final"

  compile 'com.h2database:h2:1.4.187'
}

Also possible to use JPA Criteria API, very good example is here and good tutorial is here.

@Entity
public class A {
    @Id private Long id;    
    String someAttribute;
    String someOtherAttribute;
    ...
}

Query itself:

//some parameters to your method
    String param1 = "1";
    String paramNull = null;

    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery cq = qb.createQuery();
    Root<A> customer = cq.from(A.class);

    //Constructing list of parameters
    List<Predicate> predicates = new ArrayList<Predicate>();

    //Adding predicates in case of parameter not being null
    if (param1 != null) {
        predicates.add(
                qb.equal(customer.get("someAttribute"), param1));
    }
    if (paramNull != null) {
        predicates.add(
                qb.equal(customer.get("someOtherAttribute"), paramNull));
    }
    //query itself
    cq.select(customer)
            .where(predicates.toArray(new Predicate[]{}));
    //execute query and do something with result
    em.createQuery(cq).getResultList();

score 2 · Answer 2 · answered Apr 26 '16 at 08:17

2

I think this post is what you are looking for :

JPA Criteria API Queries

You can have dynamic conditions, based on inputs by adding multiple predicates to your query.

This reference might also useful Spring Data JPA Specifications

answered Apr 26 '16 at 08:17

Kristianto Yanuar

21
2

1

may be you can give example (snippet code) that answer more better :) – masadi zainul Apr 27 '16 at 02:50

score 2 · Answer 3 · edited May 23 '17 at 10:31

Use Query by Example

Person person = new Person();                         
person.setLastname("Smith");                          
Example<Person> example = Example.of(person);
List<Person> results = personRepository.findAll(example);

Docs http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example

This is answer of Adam Erstelle to Spring Data JPA: Query by Example?

cralfaro · Answer 4 · 2016-04-26T10:35:06.907

1

You can't use optional parameters and named method resolver of Spring Data, you should create on method for each possibility, that why to make it easier I proposed you to use Specification where you can build a query according to a list of conditions.

If you are not sure that yours parameters will be fixed, then I would use this aproach:

Use spring specifications where you can build your queries according some conditions. https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/
Create some JUinit tests to be sure yours queries are stable and if somebody add/remove some parameter should modify the JUnit to keep it updated.

If you have some doubt about how to use let me know and i could share you some others examples.

edited Apr 26 '16 at 10:35

answered Apr 26 '16 at 08:16

cralfaro

5,822
3
20
30

I edited the question, so the goal more understandable. Cause I have some doubt how to use this specification in my case. – BohdanZ Apr 26 '16 at 09:31
@BohdanZ I replied you in my response, but i am afraid what you want is not allowed with spring data named method resolver, you should create N-methods with all possible combinations, and you will agree is too complex, then i propose you to use Specifications will be cleaner and easier to understand scale,... – cralfaro Apr 26 '16 at 10:36

score -1 · Answer 5 · answered Apr 26 '16 at 11:35

-1

Yes it is possible but, then you need to make different queries according to given parameters.

This will be like :

@RequestMapping("find")
public List<Person> findByOptional...(@RequestParam(value = "fName", required = false) String fname,@RequestParam(value = "lName", required = false) String lname) {
    //here you need to check you data for not null and create methods accordingly 
}

*But this is not so advisable, if you are passing more optional parameters then need to put more if else conditions.

answered Apr 26 '16 at 11:35

Riddhi Gohil

1,758
17
17

This would be the worst way to go. Use Querydsl for a clean solution. – Marc Tarin Apr 27 '16 at 08:38
Thank you @Marc for better solution... I don't have an idea about "Querydsl" I will look after it. – Riddhi Gohil Apr 27 '16 at 08:39
Check the [Spring data JPA reference documentation](http://docs.spring.io/spring-data/jpa/docs/current/reference/html/) for valuable examples of (look for all occurences of "querydsl"). – Marc Tarin Apr 27 '16 at 08:46

Spring data JPA query with varying params

5 Answers5