從SpringDataJPA中的多個連接表中獲取特定列

我的公司最近決定在新專案中改用 Spring Data JPA 而不是 Mybatis，所以我一般對使用 Spring Data JPA 還是很陌生。

我正在嘗試執行以下 sql 查詢

SELECT ab.status, buyer_rate, buyer_name, buyer_tel, bid_price, ADDTIME(complete_dt,"23:00:0.000000") AS send_time, brand, model 
FROM auction_bid ab 
INNER JOIN goods_auction ga ON ab.goods_auction_idx=ga.idx 
INNER JOIN auction_info ai ON ga.auction_info_idx=ai.idx 
WHERE is_success=1 and ab.status='008';

我創建了 3 個物體，每個物體對應于我的 mariadb 資料庫中的表

@Entity
@Getter
@Setter
@ToString
public class AuctionBid {
    @Id
    @Column(name = "auction_bid_idx")
    private Long idx;
    @Column
    private Long userIdx;
    @Column
    private String status;
    @Column
    private Long bidPrice;
    @Column
    private int isSuccess;
    @Column
    private int rank;
    @Column
    private Date createdAt;
    @Column
    private Date updatedAt;
    @ManyToOne(fetch = LAZY)
    @JoinColumn(name="goods_auction_idx")
    private GoodsAuction goodsAuction;
}

@Entity
@Getter
@Setter
public class AuctionInfo {
    @Id
    @Column(name = "auction_info_idx")
    private Long idx;
    @Column
    private Long goodsIdx;
    @Column
    private String brand;
    @Column
    private String model;
    @Column
    private String pattern;
    @Column
    private String size;
    @Column
    private String color;
    @Column
    private String tag1;
    @Column
    private String tag2;
    @Column
    private String tag3;
    @Column
    private String comment;
    @Column
    private String korName;
    @Column
    private String oColor;
    @Column
    private String iColor;
    @Column
    private String hColor;
    @Column
    private String oMaterial;
    @Column
    private String iMaterial;
    @Column
    private String origin;
    @Column
    private String realSize;
    @Column
    private Long minBid;
    @Column
    private Double sellerRate;
    @Column
    private Double buyerRate;
    @Column
    private Date createdAt;
    @Column
    private Date updatedAt;
}

@Entity
@ToString
@Getter
@Setter
public class GoodsAuction {
    @Id
    @Column(name = "goods_auction_idx")
    private Long idx;
    @Column
    private String auctionUid;
    @Column
    private String status;
    @Column
    private Date startDt;
    @Column
    private Date finishDt;
    @Column
    private Long step;
    @Column
    private int isReadyActive;
    @Column
    private int isFinishActive;
    @Column
    private int isRecommended;
    @Column
    private String invoice;
    @Column
    private String buyerName;
    @Column
    private String buyerTel;
    @Column
    private String buyerAddr1;
    @Column
    private String buyerAddr2;
    @Column
    private String buyerZipcode;
    @Column
    private Date completeDt;
    @Column
    private Date deliveryDt;
    @Column
    private Long returnTaxPrice;
    @Column
    private String returnInvoice;
    @Column
    private Date returnReqDt;
    @Column
    private Date returnCompDt;
    @Column
    private Date createdAt;
    @Column
    private Date updatedAt;
    @ManyToOne(fetch = LAZY)
    @JoinColumn(name="auction_info_idx")
    private AuctionInfo auctionInfo;
}

我正在嘗試將從查詢中獲得的資訊映射到以下物件

public class MessageData {
    private String status;
    private Double buyerRate;
    private String buyerName;
    private String buyerTel;
    private Long bidPrice;
    private Date sendTime;
    private String brand;
    private String model;
}

我知道我必須制作和擴展 JpaRepository 的介面，但是我看到的示例似乎只是獲取整個表而不是選擇的幾列，而且我對如何映射結果到串列。

任何反饋將不勝感激！

先感謝您！！

uj5u.com熱心網友回復：

您可以通過物體模型進行 hql 聯接而不是內部聯接。像那樣;

select a from auction_bid ab join ab.goodsAuction gA join auctionInfo aI

您可以使用“資料傳輸物件 (DTO)”來獲取特定列。

uj5u.com熱心網友回復：

對于預測，您可以嘗試“選擇新”。我假設 MessageData 在包 com.foo 中：

select new com.foo.MessageData(ab.status, buyer_rate, buyer_name, buyer_tel, bid_price, ADDTIME(complete_dt,"23:00:0.000000"), brand, model) FROM ...

標籤：爪哇 春天 jpa 弹簧数据-jpa 玛丽亚数据库

上一篇：春季事件生命周期

下一篇：發生例外時可選的orElse()無法回傳替代值