渴望获取在休眠中执行左联接，但在springboot/JPA中触发单独的SQL查询 [英] Eager fetch performs left join in hibernate but fires seperate sql queries in springboot/JPA

问题描述

我看到很多帖子，其中Eager fetch在休眠状态下执行子表父表的左连接.但是当我使用springboot时，hibernate会触发单独的sql查询-意味着一个针对父表的选择查询和一个针对子表的选择查询.为什么有区别? springboot是否进行了升级，或者我做错了什么?

I see a lot of posts where Eager fetch performs left join of child table parent table in hibernate. But when I use springboot , hibernate fires seperate sql queries - means one select query for parent table and one select query for child table. Why is there a difference? Has there been any upgrades in springboot or is it something I am doing wrong ?

以下是我正在使用的实体:

Below are the entities I am using:

订单实体:

@Entity
@Table(name="Ordertable", schema="cf_2583f365_c3c6_499a_a60d_138e7e7023eb")

public class Order {

@Id
@Column(name = "ORDER_ID")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int orderId;


@Column(name = "DAT_SRC_ID")
private String dataSourceId;

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name = "ORDER_CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID") 
private Customer customer;
}

客户实体:

@Entity
@Table(name="Customer", schema="cf_2583f365_c3c6_499a_a60d_138e7e7023eb")
public class Customer {

    @Id
    @Column(name = "CUSTOMER_ID")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long customerId;

    @Column(name = "CUSTOMER_NAME")
    private String customer_name;


    @Column(name = "CUSTOMER_address_id")
    private int customer_address_id;

    @Column(name = "DAT_SRC_ID")
    private String dataSourceId;

    @OneToMany(fetch=FetchType.EAGER)
    @JoinColumn(name = "ORDER_CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID") 
    private List<Order> order;

}

控制器:

@RequestMapping(value="/getByCustid/{id}", method=RequestMethod.GET,produces=MediaType.APPLICATION_JSON_VALUE)
public Customer getByCustid (@PathVariable Long id) {

    Customer s1 = customerRepository.findByCustomerId(id);
    return s1;
}

存储库:

public interface CustomerRepository extends JpaRepository<Customer,Long> {

    public Customer findByCustomerId(Long customerId); 

}

下面是正在执行的查询:

Below are the queries that are getting executed:

select
    customer0_.CUSTOMER_ID as CUSTOMER1_0_,
    customer0_.CUSTOMER_address_id as CUSTOMER2_0_,
    customer0_.CUSTOMER_NAME as CUSTOMER3_0_,
    customer0_.DAT_SRC_ID as DAT_SRC_4_0_ 
from
    Customer customer0_ 
where
    customer0_.CUSTOMER_ID=?

select
    order0_.ORDER_CUSTOMER_ID as ORDER_CU3_5_0_,
    order0_.ORDER_ID as ORDER_ID1_5_0_,
    order0_.ORDER_ID as ORDER_ID1_5_1_,
    order0_.ORDER_CUSTOMER_ID as ORDER_CU3_5_1_,
    order0_.DAT_SRC_ID as DAT_SRC_2_5_1_ 
from
    Ordertable order0_ 
where
    order0_.ORDER_CUSTOMER_ID=?

这与@Fetch(FetchMode.JOIN)有关-链接:

EDIT : is this related to @Fetch(FetchMode.JOIN) -- Link:JPA eager fetch does not join

休眠:多个选择Hibernate对Fetch模式Eager进行的查询

要检查FetchMode.JOIN是否正常工作，我在实体中添加了FetchMode.JOIN，如下所示，但对于Join Query仍然没有成功:

To check whether FetchMode.JOIN works, I have added FetchMode.JOIN in entities as shown below but still no success with Join Query:

客户实体:

@Entity
@Table(name="Customer", schema="cf_2583f365_c3c6_499a_a60d_138e7e7023eb")
public class Customer {
    @OneToMany(fetch=FetchType.EAGER)
    @JoinColumn(name = "ORDER_CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID")
    @Fetch(FetchMode.JOIN)
    private List<Order> order;
}

订单实体:

@Entity
@Table(name="Ordertable", schema="cf_2583f365_c3c6_499a_a60d_138e7e7023eb")
public class Order {
    @JsonIgnore
    @ManyToOne()
    @JoinColumn(name = "ORDER_CUSTOMER_ID", referencedColumnName = "CUSTOMER_ID") 
    @Fetch(FetchMode.JOIN)
    private Customer customer;
}

推荐答案

findByCustomerId实际上将基于该方法而不是使用em.find生成查询.它将按照SELECT c FROM Customer c WHERE c.customerId=:customerId的方式创建一些东西.之后，它将注意到获取策略并获得所需的引用. 此处也对此进行了说明.该查询将完全按照您的指示进行操作.

The findByCustomerId will actually generate a query based on that method instead of using em.find. It will create something along the lines of SELECT c FROM Customer c WHERE c.customerId=:customerId. afterwards it will notice the fetch strategy and obtain the needed references. This is also explained here. The query will do exactlly what you instruct it to do.

如果您要急于加载引用，则需要按照SELECT c FROM Customer c JOIN FETCH c.orders o WHERE c.customerId=:customerId的行自己编写查询，这将自动检索订单.

If you want to eagerly load the reference you would need to write the query yourself along the lines of SELECT c FROM Customer c JOIN FETCH c.orders o WHERE c.customerId=:customerId, this will automatically retrieve the orders.

但是customerId实际上是您实体的主键或标识符，因此您实际上应该使用findById或findOne方法(取决于您的Spring Data JPA版本).这将使用 EntityManager.find ，它应考虑映射信息并创建适当的查询.

However the customerId is actually the primary key or identitifier for your entity and thus you should actually be using the findById or findOne method (depending on your Spring Data JPA version). This will use the EntityManager.find which should take the mapping information into account and create the appropriate query.

这篇关于渴望获取在休眠中执行左联接，但在springboot/JPA中触发单独的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！