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?



Comments

Popular posts from this blog

Spring Elasticsearch Operations

Hibernate Search - Elasticsearch with JSON manipulation

Today Walkin 14th-Sept