How to write custom between spring data JPA query?

Question

I have an Entity

@Entity
public class PromoAmtRange implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "low")
private Integer low;

@Column(name = "high")
private Integer high;

@Column(name = "threshold")
private Integer threshold;

//
constructor & getters setters
}

and its Repository

public interface PromoAmtRangeRepository extends JpaRepository<PromoAmtRange,Long> {

@Query("SELECT pa FROM PromoAmtRange pa WHERE :intValue BETWEEN pa.low AND pa.high")
PromoAmtRange findByThreshold(@Param("intValue") Integer intValue);
}

Note : as you can see intValue is not a member variable of entity class.

I want to get data when intValue fall BETWEEN low and high?

for example I have database entry as shown in picture:

if my intValue is 50 I should get first row i.e.

1 | 0 | 100 | 30

How to achieve this?

did you try executing this? – prem kumar Jan 12 '16 at 11:00 — prem kumar, Jan 12 '16 at 11:00

ooozguuur · Accepted Answer · 2016-01-13T15:08:19.473

2

The results of query methods can be limited via the keywords first or top, which can be used interchangeably. An optional numeric value can be appended to top/first to specify the maximum result size to be returned.

Try:

public interface PromoAmtRangeRepository extends JpaRepository<PromoAmtRange,Long> {

@Query("SELECT pa FROM PromoAmtRange pa WHERE :intValue BETWEEN pa.low AND pa.high")
   List<PromoAmtRange> findByThreshold(@Param("intValue") Integer intValue,Pageable pageable);
}

Implementation class:

List<PromoAmtRange> result = jpaRepository.findByThreshold(intValue, new PageRequest(0,10));

edited Jan 13 '16 at 15:08

answered Jan 12 '16 at 11:53

ooozguuur

3,396
2
24
42

it works .. I used List instead of Page without these two i was getting follwing exception; org.springframework.dao.IncorrectResultSizeDataAccessException: result returns more than one elements; nested exception is javax.persistence.NonUniqueResultException: result returns more than one elements – Raj Jan 12 '16 at 12:40

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

You can user JPA Native query. (there is usable answers about native query and maping)

you can use native query like this

edited May 23 '17 at 12:15

Community

1
1

answered Jan 12 '16 at 11:34

23nikoloz

70
6

How to write custom between spring data JPA query?

2 Answers2