2023-11-10

Join statements with multiple filters in Spring Data JPA

I am using Spring Data JPA to query records from a database.

This is my SQL query

SELECT t1.id
FROM test1 t1 LEFT OUTER JOIN test2 t2 ON t1.id = t2.id
WHERE t2.key = 'keyNames'  
and t2.value  IN 'a,b,c'
and to_timestamp(t1.createdtime,'YYYY-MM-DD"T"HH24:MI:SSxff3"Z"') >= (SYSDATE - INTERVAL '12' HOUR);

I have created test1 and test2 entities with @OnetoMany association and the repositories.

public interface Test1Repository extends JpaRepository<Test1, Long>, JpaSpecificationExecutor<Test1> {
}

public interface Test2Repository extends JpaRepository<Test2, Long>, JpaSpecificationExecutor<Test2> {
}

public class Test1 {
 @Id
 @Column(name = "ID", nullable = false)
 private Long Id;

 @Column(name = "CREATED_DATE", nullable = false)
 @JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss'Z'")
 @CreationTimestamp
 private LocalDateTime createdDate;

 @OneToMany(mappedBy = "t1", fetch = FetchType.EAGER)
 @ToString.Exclude
 private Set<Test2> test2;

}


public class Test2{
 @Id
 @Column(name = "ID", nullable = false)
 private Long Id;

 @ManyToOne(optional = false)
 @JoinColumn(name = "id", nullable = false)
 @ToString.Exclude
 @JsonIgnore
 private Test1 test1;

 @Column(name = "key", length = 256)
 private String key;

 @Column(name = "value", length = 256)
 private String value;
}

I have the t1 specifications for the join condition as below:

public class Test1Specifications {
    public static Specification<Test1> hasTestWithValue(List<String> values){
        return (root, query, criteriaBuilder) -> {
            Join<Test1, Test2> test1test2Join = root.join("ids");
            return criteriaBuilder.equal(test1test2Join.get("key"),"keyNames");
        };
    }
}

public class Test1Service{
 private final Test1Repository test1Repository;
 private final Test2Repository test2Repository;

 public Test1Service(Test1Repository test1Repository, Test2Repository test2Repository){
 this.test1Repository = test1Repository;
 this.test2Repository = test2Repository;
}

public List<String> getIds()(List<String> values){
 List<String ids = null;
 Specification<Test1> filters = Test1Specifications.hasTestWithValue(values);
 ids = test1Repository.findAll(filters)
 .stream()
 .map(Test1::getId()
 .collect(Collectors.toList());

 return ids;
}

}

I am not able to figure out how to add the remaining two filters. I would really appreciate if someone could help me understand how to add multiple filter conditions as mentioned in the Query above?



No comments:

Post a Comment