Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring data JPA query with varying params

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,...);
like image 380
BohdanZ Avatar asked Apr 26 '16 08:04

BohdanZ


People also ask

How do I use the same parameter multiple times in my query using JPA?

If for some reason we do need to use the same parameter many times within the same query, we just need to set it once by issuing the “setParameter” method. At runtime, the specified values will replace each occurrence of the parameter.

How do I create a dynamic native query in Spring data JPA?

If you need to write dynamic queries to retrieve a single JPA entity, you would need to implement: <T> T findOne(QueryCallback<T> callback); If you need to write dynamic queries with pagination support, you would need to implement: <T> Page<T> findAll(Pageable pageable, QueryCallback<Page<T>> callback);

What is difference between JpaRepository and CrudRepository?

CrudRepository provides CRUD functions. PagingAndSortingRepository provides methods to do pagination and sort records. JpaRepository provides JPA related methods such as flushing the persistence context and delete records in a batch.


1 Answers

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();
like image 102
BohdanZ Avatar answered Nov 15 '22 05:11

BohdanZ