select rows using pair of columns with JPA (provider eclipselink) and Spring Data

SJ1 0
Viewed 24

I have a table like below in MSSQL -

create table t1 (

  id   int   IDENTITY(1,1),
  col1 int   not null,
  col2 int   not null, 
  
  constraint t1_UK UNIQUE (col1, col2)

)

and data like below -

   id    col1     col2
    1      25      661
    2      25      741
    3      89      661
    4      89      741

how do I select rows with id 1 and id 4 with where clause only on col1 and col2 ?

Entity Def-

@Entity
@Table
class T1Entity {
   @Id
   @GeneratedValue
   private int id;

   @Column
   private int col1;

   @Column
   private int col2;

   // getters, setters
}

Note that assuming I have T1EntityRepository, I can't define a method like findByCol1InAndCol2In(List.of(25, 89), List.of(661, 741) as it will return all rows in sample data.

I know I need something like Select col1, col2 from t1 where (col1=25 and col2 = 661) OR (col1=89 and col2=741) but how to do this using JPA. (also, is there a way in SQL itself without using OR of AND)

eclipselink spring-data-jpa jpa spring-data java
1 Answers

I had same problem. I am writing the custom query using @Query to solve this problem. You can write custom where causes,

  @Query("SELECT wftr FROM WorkflowActivityRequestEntity wftr WHERE wftr.status IN :status")
List<WorkflowActivityRequestEntity> findWorkflowActivityRequestsByStatus(
        @Param("status")
        List<String> status);

@Query("SELECT wftr FROM WorkflowActivityRequestEntity wftr WHERE wftr.owner NOT IN :owners")
List<WorkflowActivityRequestEntity> findWorkflowActivityRequestsNotOwnedBy(
        @Param("owners")
        List<String> owners);

For AND and OR combination you can also extend your repository interface with JpaSpecificationExecutor

and then use Criteria builder. I referred https://www.baeldung.com/spring-data-criteria-queries while doing this in my projects.

Hope it helps.

Sagar Gandhi0
Related