从Spring Data JPA中的多个连接表中获取特定列

Question

我的公司最近决定在新项目中切换到使用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的接口，但我所看到的示例似乎只是获取整个表而不是选择几列，我对如何能够将结果映射到List有点困惑。

任何反馈将不胜感激!

提前感谢!!

Answer 1

您可以通过实体模型hql连接而不是内部连接。这样,

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

你可以使用'Data Transfer Objects (DTO)'来获取特定的列。

Answer 2

对于投影，您可以尝试使用" select new ";我假设MessageData在package 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 ...