Join statements with multiple filters in Spring Data JPA

Question

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?

Answer 1

I'm not sure how to work correctly with dates and to_timestamp function, but in general you should be able to do something similar. There was a lot of code, so I moved it into a separate class

import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;
import java.time.LocalDateTime;
import java.util.LinkedList;
import java.util.List;

public class Test1Specification implements Specification<Test1> {

    private String key;
    private List<Integer> values;
    private Integer hour;

    public Test1Specification(String key, List<Integer> values, Integer hour) {
        this.key = key;
        this.values = values;
        this.hour = hour;
    }

    @Override
    public Predicate toPredicate(Root<Test1> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        List<Predicate> predicates = new LinkedList<>();

        Join<Test1, Test2> test2Join = root.join("ids");

        predicates.add(
            cb.equal(test2Join.get("key"), this.key)
        );

        predicates.add(
            test2Join.get("value").in(this.values)
        );

        Expression<LocalDateTime> function = cb.function("to_timestamp",
            LocalDateTime.class,
            root.get("createdtime"),
            cb.literal("YYYY-MM-DD\"T\"HH24:MI:SSxff3\"Z\"")
        );

        predicates.add(
            cb.lessThanOrEqualTo(
                function,
                LocalDateTime.now().minusHours(hour)
            )
        );

        return cb.and(predicates.toArray(new Predicate[0]));
    }
}

You can use this class like this:

Specification<Test1> filters = new Test1Specification('keyNames', values, 12);
test1Repository.findAll(filters);

Instead of such complex code, you can write a query in hql or use native sql and implement it using the @Query annotation in the repository.

public interface Test1Repository extends JpaRepository<Test1, Long> {

    @Query(nativeQuery = true,
            value = "SELECT t1.id " +
                    "FROM test1 t1 LEFT OUTER JOIN test2 t2 ON t1.id = t2.id " +
                    "WHERE t2.key = :key " +
                    "AND t2.value IN :values " +
                    "AND TO_TIMESTAMP(t1.createdtime,'YYYY-MM-DD\"T\"HH24:MI:SSxff3\"Z\"') >= (SYSDATE - INTERVAL :hour HOUR)")
    List<Test1> findCustom1(@Param("key") String key, @Param("values") List<Integer> values, @Param("hour") Integer hour);

    //not sure if TO_TIMESTAMP will work in HQL
    @Query("from Test1 t1 join t1.test2 t2 " +
            "where t2.key = :key " +
            "and t2.value in :values " +
            "and TO_TIMESTAMP(t1.createdtime,'YYYY-MM-DD\"T\"HH24:MI:SSxff3\"Z\"') >= (SYSDATE - INTERVAL :hour HOUR)")
    List<Test1> findCustom2(@Param("key") String key, @Param("values") List<Integer> values, @Param("hour") Integer hour);
}