Cannot search nested list of objects with Spring Data JPA

Question

I am having trouble querying a nested list of objects with Spring Data JPA.

Here are my Entities:

public class Shipment {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(updatable = false, nullable = false)
   private Long id;

   @CreationTimestamp
   @Column(updatable = false)
   private Date createdAt;

   @UpdateTimestamp
   private Date updatedAt;

   private String orderId;
   private String deliveryId;

   @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
   @JoinColumn(name = "shipment")
   private List<Packet> packets;

   // constructors, getters, setters omitted
}

and the nested List:

@Entity
public class Packet {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(updatable = false, nullable = false)
   private Long id;

   @CreationTimestamp
   @Column(updatable = false)
   private Date createdAt;

   @UpdateTimestamp
   private Date updatedAt;

   private String packetId;
   @Enumerated(EnumType.STRING)
   private PacketType packetType;
}

Then I have a simple rest endpoint:

@GetMapping(path = "/shipments", produces = "application/json")
    public List<ShippingIds> getShipments(SearchCriteria searchCriteria) {
    List<Shipment> shipments = shipmentService.getShipmentIds(searchCriteria);
    return buildShipmentIds(shipments);
}

And I am using the SearchCriteria as queryParams (/shipments?packetId={packetId}&orderId={orderId}&deliveryId={deliveryId}...

And my Service where I use query by example:

public List<Shipment> getShipmentIds(SearchCriteria searchCriteria) {
    Shipment shipment = Shipment.builder()
            .orderId(searchCriteria.getOrderId())
            .deliveryId(searchCriteria.getDeliveryId())
            .packetId(searchCriteria.getPacketId())
            .build();

    // My problem is here - How do I do get the associated Shipment object if my search criteria is a packetId? 
    if (searchCriteria.getPacketId()!= null) {
        return shipmentRepository.findByPackets_Packets_packetId(searchCriteria.getPacketId());
    } else {
        Example<Shipment> example = Example.of(shipment);
        return shipmentRepository.findAll(example);
    }
}

And my Repository:

@Repository
public interface ShipmentRepository extends JpaRepository<Shipment, Long> {

    List<Shipment> findByPackets_Packets_packetId(String packetId);

}

Since my object Shipment has a list of Packet I cannot simple include it in the Example, or at least I am not sure how.

But I should be able to extract the corresponding Shipment based on a single String packetId, right?

I am getting an Exception:

Caused by: org.springframework.data.mapping.PropertyReferenceException: No property parcels found for type Packet! Did you mean 'packetId'? Traversed path: Shipment.packets.

I also tried to define the Query by myself:

@Query("select s from Shipment s left join s.packets p where s.id = p.shipment")

But then I get:

Caused by: org.hibernate.QueryException: could not resolve property: shipment of: com.shipment.persistence.model.Packet

Although shipment is my foreign key.

Any ideas what I am doing wrong? Or what I can maybe improve?

Yonatan Wilkof · Accepted Answer · 2020-02-24 16:27:06Z

You are in the right direction, but there are some mistakes.

Firstly, the error

Caused by: org.hibernate.QueryException: could not resolve property: shipment of: com.shipment.persistence.model.Packet

is telling you that shipment is not a field you defined on the Packet class, and therefore fails. It does not matter that on database side you have it, if you want to query using this field, it must be mapped on the class side.

Having said that, it is not necessary to add it to your class in order to acheive your original intent, which is to query all the shipments that have a packet with a certain id. The reason it is not necessary is that you have already mapped the relation on the shipment object side, so it is not necessary to map it again on the Packet object. It might be required in other scenarios, but not in yours.

You were right to add a @Query annotation on top of your repository method. However, it is missing the most important part - getting shipments with the specified packetId. Also, the join condition you supplied is not required and can be omitted, as that is taken care by Hibernate under the hood, based on the mapping annotated with @JoinColumn(name = "shipment").

Your @Query annotation should looks as follows:

@Query("from Shipment s join s.packets p where p.packetId = :packetId")

Explanation:

Omitted the 'Select s' part, it is redundant as you are selecting all fields.
removed left join and replaced with join, as there is no need to fetch Shipment objects that do not match the criteria. Inner join is enough to tell hibernate to prepare a condition on the linked objects.
Added the condition that will yield shipments which have a packet matching the give packet id.

This will yield the following query generated by Hibernate:

SELECT
    shipment0_.id AS id1_1_0_,
    packets1_.id AS id1_0_1_,
    packets1_.packet_id AS packet_i2_0_1_,
 // and the rest of the fields of both objects
FROM
    shipment shipment0_
INNER JOIN packet packets1_ ON
    shipment0_.id = packets1_.shipment
WHERE
    packets1_.packet_id =?

Collectives™ on Stack Overflow

Cannot search nested list of objects with Spring Data JPA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related