Java Spring JPA Reference and return only one result from join table-CodePudding

I believe the code below is working correctly but my logic is flawed and so I'm looking for help getting the results I actually want. In addition I think my attempt on the JPA method is overly convoluted and there's likely a cleaner solution. I know I can likely use @Query but I want to try and get the method name solution working as well so I can learn where I went wrong.

For simplicity I've stripped back the DB tables in this example, however I have Table1 which is the top level table and Table2 which sits below it and has a foreign key back to Table1.

Table 1 -> Table 2 is a one to many relationship, I am looking to write a JPA method that will pull back a record from Table 1 when given the Table1 ID and only the current effective record from Table2 (this will be where EffectiveDateTime column is most recent but NOT future dated). Using the example below I want it to only pull back ID 8.

Unfortunately, I believe with the code I have below it recognises one of those records for Table2 is within the date range required and is therefore pulling all the records back that have a relation with the ID from Table 1. I could be wrong on this though and the logic could be flawed in a different way.

Any help would be appreciated.

Table1 Entity Class

@Entity
@Table(name = "TABLE1")
public class Table1 {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", nullable = false, unique = true)
    private Integer id;
    
    @OneToMany(mappedBy = "table1", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @Fetch(value = FetchMode.SUBSELECT)
    @JsonManagedReference
    private List<Table2> table2; 

    //Plus getters & setters

Table2 Entity Class

@Entity
@Table(name = "TABLE2")
public class Table2{
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", nullable = false, unique = true)
    private Integer id;
    
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "Table1Id")
    @JsonBackReference
    private Table1 table1;
    
    @Column(name = "EffectiveDateTime", nullable = false)
    private LocalDateTime effectiveDateTime;

    //Plus getters & setters

Repo Class

@Repository
public interface Repository extends JpaRepository<Table1, Integer>, JpaSpecificationExecutor<Table1>  {
    
    public Optional<Table1> findTopByIdAndTable2EffectiveDateTimeLessThanEqualOrderByTable2EffectiveDateTimeDesc(int id, LocalDateTime now);
}

Current JSON return

{
    "id": 5
    "Table2": [
        {
            "id": 6,
            "effectiveDateTime": "2021-01-01T00:00:01"
        },
        {
            "id": 8,
            "effectiveDateTime": "2022-01-01T00:00:01"
        },
        {
            "id": 9,
            "effectiveDateTime": "2023-01-01T00:00:01"
        }
    ]
}

CodePudding user response：

Once you declared a oneToMany relationship, your entity will load all joined entity when calling the getTable method (wich is called by jackson when serializing). I believe if you activate debug mode, you will see 2 request, the first will be the one you describe in your repopsitory, the second the one JPA use to load all related table2 entity.

Their is some possible ways to accomplish what you want:

the first (and most obvious ?) is to manually make the 2 request equivalent to your HQL request (the table 1 find by ID and then the table 2 find by....) and then aggregate the 2 results
the second one would be to use jackson to serialize your data how you want to (by filtering useless table 2 data)
the third one would be to use jackson to serialize a table2 (wich give you the corresponding table1 data) into the appropriate JSON. It's a bit more complicated but probably more efficient if your table1 reference a lot of table2 data.
The last one would be to try using projections it allow you to declare only the data you want to retrieve when requesting data.

hope this help !

CodePudding user response：

In the end we completed this using a JPQL query. An example of the query is below in case it helps any others in the future.

@Query(value = "SELECT table1 FROM Table1 table1 " 
              " JOIN FETCH table1.table2 table2 "
              " WHERE table1.id = :table1Id" 
              "  AND table2.id = "
              "  ( SELECT table2.id FROM table2 " 
              "     WHERE table2.table1.id = :table1Id "
              "      AND table2.effectiveDateTime = "
              "      ( SELECT MAX(effectiveDateTime) FROM table2"
              "             WHERE table2.table1.id = :table1Id "
              "              AND table2.effectiveDateTime <= :currentTimestamp "
              "      ) "
              "  ) ")
Optional<Table1> getEffectiveDateTimeTable2(@Param("table1Id") int table1Id, @Param("currentTimestamp") LocalDateTime currentTimestamp);