Fetching specific columns from multiple joined tables in Spring Data JPA-CodePudding

my company recently decided to switch to using Spring Data JPA instead of Mybatis for new projects, so I am pretty new to using Spring Data JPA in general.

I am trying to execute the following sql query

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';

I have created 3 entities each corresponding to the table in my mariadb database

@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;
}

And I am trying to map the info that I got from the query to the following object

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;
}

I get that I have to make and interface that extends JpaRepository, but the examples that I have seen seems to just fetch the entire table instead of a select few columns, and I am a little bit confused on how I will be able to map the result to List.

Any feedback will be deeply appreciated!

Thank you in advance!!

CodePudding user response：

You can hql joins instead of inner joins through entity models. like that;

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

You can use 'Data Transfer Objects (DTO)' for fetch specific columns.

CodePudding user response：

For projections, you can try a "select new". I assume the MessageData is in 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 ...