在 JPA 存储库中连接两个表

我要抛出 spring boot 教程并得到这个要求

    @Entity
    @Table(name = "transiction")
    public class Transictions {
        
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long Id;
        
        @Column(name = "userId")
        private Long userId;
        
        @Column(name = "productName")
        private String productName;
        
        @Column(name = "quantity")
        private int quantity;
        
        @Column(name = "price")
        private double price;
        
        @Column(name = "transictionDate")
        private Date transictionDate;
    
// Getter and Setter method with constructor.

我的第二个模型类

@Entity
@Table(name = "product")
public class product {
    
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    @Column(name = "product")
    private String productName;
    
    @Column(name = "ltp")
    private float LTP;
    
    @Column(name = "exchange")
    private String exchange;

我以同样的方式创建了不同的 Controller 和两个不同的 JPA 存储库来从每个表中获取数据并且工作正常。

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.springboot.Ole.Model.Transictions;

public interface TransictionRepository extends JpaRepository<Transictions, Long>{
    
    
}

产品模型类

package com.springboot.Ole.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.springboot.Ole.Model.Product;

@Repository
public interface ProducttRepository extends JpaRepository<Instrument, Long>{

}

现在我得到了需要加入这两个表的 secanrio。

MySql 命令

SELECT transiction.user_id, transiction.quantity,transiction.product_name, transiction.Price,product.LTP             
FROM product
INNER JOIN transiction
ON product.product=transiction.product_name;

我可以在 sql 中看到适当的数据。但我不知道如何用 Java 代码编写此查询。

我要抛出这个教程 https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections但不太清楚。

这就是我在我的存储库中尝试的

package com.springboot.Ole.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import com.springboot.Ole.Model.Transictions;

public interface TransictionRepository extends JpaRepository<Transictions, Long>{
    
    @Query(value = "SELECT transiction.user_id, transiction.quantity,transiction.product_name, transiction.Price,product.LTP"
            + "FROM product"
            + "INNER JOIN transiction"
            + "ON product.product=transiction.product_name")
            
}

但运气不好，因为我没有将它存储到某个列表中，或者可能是其他不确定的东西。

我有问题

1. 我是否需要编写服务类或类似的东西。

谁能帮帮我。

最佳答案

如果你想在 spring jpa 中使用表连接，你必须使用 spring 提供的关系模型，它们是众所周知的一对一、一对多和多对多。在 spring data rest relationships解释了您可以在项目中使用的不同类型的关节。

例如，如果您想要一对一的关系，您的代码如下:

@Entity
@Table(name = "transiction")
public class Transictions {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long Id;
    
    @Column(name = "userId")
    private Long userId;
    
    @OneToOne(mappedBy = "transiction")
    private Product product;
    
    @Column(name = "quantity")
    private int quantity;
    
    @Column(name = "price")
    private double price;
    
    @Column(name = "transictionDate")
    private Date transictionDate;
    
@Entity
@Table(name = "product")
public class Product {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    
    @Column(name = "product")
    private String productName;
    
    @Column(name = "ltp")
    private float LTP;
    
    @Column(name = "exchange")
    private String exchange;
    
    @OneToOne
    @JoinColumn(name = "transiction_id")
    @RestResource(path = "product-transiction", rel="transiction")
    private Transictions transiction;

https://stackoverflow.com/questions/68212371/