jpa 多表关联查询

一碗南瓜粥♪(^∇^*)

已于 2023-03-03 11:29:04 修改

阅读量2.4k

点赞数 2

分类专栏： java 文章标签： java sql 数据库

于 2023-03-02 13:49:49 首次发布

本文链接：https://blog.csdn.net/srj904711648/article/details/129299099

版权

java 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章详细介绍了在Java中使用JPA进行对象关系映射（ORM）时，如何处理单向和双向的manyToOne与oneToMany关联。在单向关联中，展示了@ManyToOne注解的使用，以及在查询时如何构建SQL语句。对于双向关联，指出了可能导致堆栈溢出的问题，并提供了解决方案，包括使用@JsonIgnore注解、重写getter方法以及使用DTO来避免无限递归。

摘要由CSDN通过智能技术生成

单向manyToOne

manyToOne many方

@AllArgsConstructor
@NoArgsConstructor
@Entity
@Builder
@Getter
@Setter
@Table(
        name = "product"
)
public class Product{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "productCode")
    private String productCode;
    /**
     * 商品名
     */
    @Column(name = "productName")
    private String productName;
    /**
     * 商品价格
     */
    @Column(name = "productPrice")
    private BigDecimal productPrice;
    /**
     * 商品数量
     */
    @Column(name = "productStock")
    private Integer productStock;

    @Column(name = "foreignCode")
    private String foreignCode;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "foreignCode",referencedColumnName = "code",insertable=false,updatable=false,foreignKey = @ForeignKey(name = "none", value =ConstraintMode.NO_CONSTRAINT))
    private JoinTable joinTable;
}

注：

  @Column(name = "foreignCode")
    private String foreignCode;

当前many方关联的one方字段，对应下方@JoinColumn 中的name = “foreignCode”

@JoinColumn(name = "foreignCode",referencedColumnName = "code",insertable=false,updatable=false,foreignKey = @ForeignKey(name = "none", value =ConstraintMode.NO_CONSTRAINT))

name：当前表格关联one方的字段
referencedColumnName：one方字段
insertable=false,updatable=false 不自动更新新增

one方

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Builder
@Table(
        name = "join_table"
)
public class JoinTable implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    // 所对应
    @Column(name = "code")
    private String code;

    @Column(name = "joinInfo")
    private String joinInfo;
}

注：

@Column(name = "code")
private String code;

与many方对应的字段，
many方 referencedColumnName = “code”

查询sql语句

select * from product left join join_table on product.foreignCode = join_table.code where join_table.joinInfo = ?

实现

List<Product> list = productRepository.findAll(
                new Specification<Product>() {
                    @Override
                    public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                        List<Predicate> list = new ArrayList<>();


                        Join<Product, JoinTable> join = root.join("joinTable", JoinType.LEFT);
						list.add(criteriaBuilder.equal(join.get("joinInfo"), "123"));


                        Predicate[] pre = new Predicate[list.size()];
                        criteriaQuery.where(list.toArray(pre));
                        return criteriaBuilder.and(list.toArray(pre));
                    }
                });

注
1.

 Join<Product, JoinTable> join = root.join("joinTable", JoinType.LEFT);

“joinTable” ：many方关联的实体类名

 @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "foreignCode",referencedColumnName = "code",insertable=false,updatable=false,foreignKey = @ForeignKey(name = "none", value =ConstraintMode.NO_CONSTRAINT))
    private JoinTable joinTable;

list.add(criteriaBuilder.equal(join.get("joinInfo"), "123"));

“joinInfo”：one方需要查询字段名 sql为 where join_table.joinInfo = ?

@Column(name = "joinInfo")
private String joinInfo;

双向manyToOne oneToMany

many方

public class Product{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "productCode")
    private String productCode;
    /**
     * 商品名
     */
    @Column(name = "productName")
    private String productName;
    /**
     * 商品价格
     */
    @Column(name = "productPrice")
    private BigDecimal productPrice;
    /**
     * 商品数量
     */
    @Column(name = "productStock")
    private Integer productStock;

    @Column(name = "foreignCode")
    private String foreignCode;

//    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "foreignCode",referencedColumnName = "code",insertable=false,updatable=false,foreignKey = @ForeignKey(name = "none", value =ConstraintMode.NO_CONSTRAINT))
    private JoinTable joinTable;


    public BigDecimal getProductPrice() {
        return productPrice;
    }

    public Integer getId() {
        return id;
    }

    public Integer getProductStock() {
        return productStock;
    }

    public JoinTable getJoinTable() {
        joinTable.setProductList(null);
        return joinTable;
    }

    public String getForeignCode() {
        return foreignCode;
    }

    public String getProductCode() {
        return productCode;
    }

    public String getProductName() {
        return productName;
    }

    public void setForeignCode(String foreignCode) {
        this.foreignCode = foreignCode;
    }

    public void setId(Integer id) {
        this.id = id;
    }


    public void setJoinTable(JoinTable joinTable) {
        this.joinTable = joinTable;
    }

    public void setProductCode(String productCode) {
        this.productCode = productCode;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public void setProductPrice(BigDecimal productPrice) {
        this.productPrice = productPrice;
    }

    public void setProductStock(Integer productStock) {
        this.productStock = productStock;
    }
}

one方

public class JoinTable implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    // 所对应
    @Column(name = "code")
    private String code;

    @Column(name = "joinInfo")
    private String joinInfo;

    @OneToMany(fetch = FetchType.LAZY,mappedBy = "joinTable")
    List<Product> productList;

    @Override
    public String toString() {
        return "";
    }
}

注
@OneToMany(fetch = FetchType.LAZY,mappedBy = "joinTable")

“joinTable” ： many方对应的字段名，仅双向关联有用

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "foreignCode",referencedColumnName = "code",insertable=false,updatable=false,foreignKey = @ForeignKey(name = "none", value =ConstraintMode.NO_CONSTRAINT))
private JoinTable joinTable;

问题，双向依赖会导致堆栈溢出

解决：

注解 @JsonIgnore（fastjson情况）
注解到不希望展示的字段

2.重写get方法
举例：

   public JoinTable getJoinTable() {
        joinTable.setProductList(null);
        return joinTable;
    }

3.使用dto接收

举例：
查询时，返回

 List<Product> list = productRepository.findAll();
        List<ProductVo> result = new ArrayList<>();


        for(int i=0;i<list.size();i++){

            ProductVo productVo = new ProductVo();

            BeanUtils.copyProperties(list.get(i),productVo,"joinTable");
//            productVo.setForeignCode(list.get(i).getForeignCode());
//            productVo.setProductCode(list.get(i).getProductCode());
//            productVo.setProductName(list.get(i).getProductName());
//            productVo.setProductPrice(list.get(i).getProductPrice());
//            productVo.setProductStock(list.get(i).getProductStock());
            result.add(productVo);
        }
        return result;

ProductVo 去除会依赖的内容

public class ProductVo {
    private String productCode;
    /**
     * 商品名
     */
    private String productName;
    /**
     * 商品价格
     */
    private BigDecimal productPrice;
    /**
     * 商品数量
     */
    private Integer productStock;

    private String foreignCode;
}