JPA @Query gives NonUniqueResultException-CodePudding

i have entity like this

@Builder
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@Table(name = "fulfillment_purchase_orders")
public class FulfillmentPurchaseOrder extends Audit implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Integer id;

    @Column(name = "purchase_order_id")
    private Integer purchaseOrderId;

    @Column(name = "version")
    private Integer version;

but i want to get the latest data from version. i have try use this code :

@Query("select f from FulfillmentPurchaseOrder f where f.purchaseOrderId =?1 order by f.version desc")
    Optional<FulfillmentPurchaseOrder> findByPurchaseOrderId(Integer purchaseOrderId);

but i got error like this

query did not return a unique result: 2; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 2",

my question is how to make query for getting only the latest use order by Version ?

CodePudding user response：

use subquery like :

@Query("select * from FulfillmentPurchaseOrder f where f.purchaseOrderId = (SELECT max(fp.purchaseOrderId) FROM FulfillmentPurchaseOrder fp)")
    Optional<FulfillmentPurchaseOrder> findByPurchaseOrderId(Integer purchaseOrderId);

CodePudding user response：

Your query is returning more then one result. Try making the query native and adding LIMIT 1 at the end like so:

@Query(nativeQuery = true, value = "select f from FulfillmentPurchaseOrder f where f.purchaseOrderId =?1 order by f.version desc LIMIT 1")
    Optional<FulfillmentPurchaseOrder> findByPurchaseOrderId(Integer purchaseOrderId);

LIMIT 1 means return the first row that matches.