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
Post a Comment