0

How can I generate findBy method corresponding to below query in JPARepository? 

select * from person where name=?1  and eff_dt < ?2 and (exp_dt >?2 OR exp_dt IS NULL)
Jijil Kakkadathu
  • 343
  • 3
  • 17
  • is your query above correct? recheck. and also show your entity class – pvpkiran Jul 30 '18 at 14:04
  • You create a method, choosing the best method name you can think of, and you annotate it with `@Query("")`. Queries derived from method names are fine for simple stuff like findByName(). They're not for more complex queries like the one you have. – JB Nizet Jul 30 '18 at 14:16
  • Possible duplicate of [Spring data jpa - How to combine multiple And and Or through method name](https://stackoverflow.com/questions/35788856/spring-data-jpa-how-to-combine-multiple-and-and-or-through-method-name) – Jens Schauder Jun 24 '19 at 05:09

2 Answers2

1

You can technically do it. But its better to avoid it.

Assuming you entity class is like

class Person {
  String name;
  Integer effDt;
  Integer expDt;
  // getters and setters
}

your method name will be 

findByNameIsAndEffDtLessThanAndExpDtGreaterThanOrNameIsAndEffDtLessThanAndExpDtIsNull(String name, Integer effDt, Integer expDt, String name2, Integer effDt2);
//name and name2 will be same & effDt and effDt2 will be same.
//Boolean Algebra: A(B+C) = A.B + A.C

As you can see this is needlessly complicated and difficult to understand. Use a native query instead.

raiyan
  • 821
  • 6
  • 15
1

select * from person where name=?1 and eff_dt < ?2 and (exp_dt >?2 OR exp_dt IS NULL) Assuming the entity class by the names of the variables

@Entity
public class Person{
    @Id
    @GeneratedValue
    private long id;

    private String name;

    private Date eff_dt;

    private Date exp_dt;

}

The query can be 

public final static String FIND_ALL_PERSONS="select s from Person p where p.name=:name   and p.eff_dt < :eff_dt and (exp_dt > : exp_dt OR exp_dt is null)";
@Query(FIND_ALL_PERSONS)
List<Person> findAllPersons(@Param("name")String name,@Param("eff_dt") eff_dt,@Param("exp_dt") Date axp_dt);