Spring-data-aop 多表联查返回自定义结果集

Spring-data-aop 多表联查返回自定义结果集

如果我们有两张表bill customer，它俩通过bill.customer_id = customer.id相关联，它俩对应的实体类分别如下

@Entity
@Table(name = "bill")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Bill {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "customer_id")
    private Long customerId;
    @Column(name = "bill_no")
    private String billNo;
    @Column(name = "bill_amount")
    private float billAmount;
    @Column(name = "bill_date")
    private LocalDate billDate;
}

和

@Entity
@Table(name = "customer")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "customer_name")
    private String customerName;
    @Column(name = "customer_age")
    private Integer customerAge;
    @Column(name = "customer_account")
    private String customerAccount;
    @Column(name = "customer_password")
    private String customerPassword;
}

现在查询一个结果，包括：账单金额，账单日期，账单ID，用户名 字段，对应自定义类如下

@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Content implements Serializable {
    private Float billAmount;
    private LocalDate billDate;
    private Long id;
    private String customerName;
}

查询方式如下

@Repository
public interface CustomerRepository extends JpaRepository<Customer,Long> {

    @Query("select new com.train.spr.entities.Content(b.billAmount, b.billDate, b.id, c.customerName) " +
            " from Bill b " +
            " join Customer c on b.customerId = c.id")
    List<Content> searchContent();
}

注意

⚠️

多表联查要给每张表加别名，即便是虚表，无论是不是多表联查，我都建议这么做，以免报空指针的错误。在使用JPQL语句时，查询结果一定要写成全类名，而且要用new，就像我写的这样

new com.train.spr.entities.Content(b.billAmount, b.billDate, b.id, c.customerName)

调用

@Slf4j
@Service
public class CustomerServer {
    private final CustomerRepository customerRepository;
    @Autowired
    public CustomerServer(CustomerRepository customerRepository) {
        this.customerRepository = customerRepository;
    }
    // service层调用
    public List<Content> searchContent(){
        return customerRepository.searchContent();
    }
}