hibernate查询不到关联对象列表-fetchType的选择

概述

昨天排查问题的时候，发现使用hibernate关联对象时，订单实体类对象死活无法获取关联的订单明细列表

Order order = orderDao.findById(201L);
//明明数据库表里有关联的订单明细，但是此处的就是查询不到
List<OrderItem> orderImemList = order.getOrderItemList();

原因在于，OrderItem对象中关联了产品对象product，在它头上的注解是这样的：

    /**
     * 产品
     */
    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    @JoinColumn(name = "product_id", nullable = false)
    private Product product;

fetch = FetchType.EAGER 且 nullable=false，执行 order.getOrderItemList()时，hibernate底层的查询语句是inner join产品表的，但是订单明细中恰好有1个产品id是空的，结果就是本来有3条订单明细，却只查出了2条

   select
        orderiteml0_.order_id as order3_0_2_,
        orderiteml0_.id as id1_2_,
        orderiteml0_.id as id1_1_,
        orderiteml0_.order_id as order3_1_1_,
        orderiteml0_.product_id as product4_1_1_,
        orderiteml0_.QUANTITY as QUANTITY1_1_,
        product1_.id as id2_0_,
        product1_.PRODUCT_NAME as PRODUCT2_2_0_ 
    from
        T_ORDER_ITEM orderiteml0_ 
    inner join
        T_PRODUCT product1_ 
            on orderiteml0_.product_id=product1_.id 
    where
        orderiteml0_.order_id=? 
    order by
        orderiteml0_.id

fetchType的选择

JPA定义实体之间的关系有如下几种：

• @OneToOne
• @ManyToOne
• @OneToMany
• @ManyToMany

在定义它们的时候可以通过fetch属性指定加载方式，有两个值:

• FetchType.LAZY：延迟加载，等到get的时候才会去查询
• FetchType.EAGER：急加载，在查询主对象的时候就一起查询出来了

问题复现

数据库准备

-- region 创建表
-- 订单表
create table T_ORDER(
	id	INTEGER PRIMARY KEY
);
COMMENT ON TABLE T_ORDER is '订单表';
COMMENT ON COLUMN T_ORDER.id is '订单id';
-- 订单明细表
create table T_ORDER_ITEM(
	id	INTEGER PRIMARY KEY,
	order_id INTEGER,
	product_id INTEGER,
	quantity INTEGER
);
COMMENT ON TABLE T_ORDER_ITEM is '订单明细表';
COMMENT ON COLUMN T_ORDER_ITEM.order_id is '订单id';
COMMENT ON COLUMN T_ORDER_ITEM.product_id is '产品id';
COMMENT ON COLUMN T_ORDER_ITEM.quantity is '数量';
-- 产品表
create table T_PRODUCT(
	id	INTEGER PRIMARY KEY,
	product_name varchar2(50)
);
COMMENT ON TABLE T_PRODUCT is '产品表';
COMMENT ON COLUMN T_PRODUCT.product_name is '产品名称';
-- endregion
 
-- region 插入测试数据
insert into T_PRODUCT(id, product_name) values (101, '产品A');
insert into T_PRODUCT(id, product_name) values (102, '产品B');
 
insert into T_ORDER (id) values (201);
 
insert into T_ORDER_ITEM(id, order_id, product_id, quantity) values (301, 201, 101, 10);
insert into T_ORDER_ITEM(id, order_id, product_id, quantity) values (302, 201, 102, 2);
-- 注意：为了复现问题，一条记录的 productId 设置为空，一个设置为不存在的productId
insert into T_ORDER_ITEM(id, order_id, product_id, quantity) values (303, 201, '', 2);
insert into T_ORDER_ITEM(id, order_id, product_id, quantity) values (304, 201, 999, 12);
--endregion

数据表对应的实体类

订单类

@Entity
@DynamicInsert
@DynamicUpdate
@Table(name = "T_ORDER")
public class Order implements Serializable {
 
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
 
    /**
     * 订单明细
     */
    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "order")
    @OrderBy(value = "id")
    private List<OrderItem> orderItemList = new ArrayList<>();
    
    //...setter 和 getter
}

订单明细类

@Entity
@DynamicInsert
@DynamicUpdate
@Table(name = "T_ORDER_ITEM")
public class OrderItem implements Serializable {
 
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
 
    /**
     * 订单
     */
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private Order order;
 
    /**
     * 产品
     */
    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    @JoinColumn(name = "product_id", nullable = false)
    private Product product;
 
    /**
     * 数量
     */
    @Column(name = "QUANTITY")
    private int quantity;
    
    //...setter和getter
    
}

产品类

@Entity
@DynamicInsert
@DynamicUpdate
@Table(name = "T_PRODUCT")
public class Product implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
 
    /**
     * 产品名称
     */
    @Column(name = "PRODUCT_NAME")
    private String productName;
    
    //...setter和getter
}

测试

public class App {
    private static SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
    public static void main(String[] args) {
        Long id = 201L;
        Session session = sessionFactory.getCurrentSession();
        session.beginTransaction();
        Order order = (Order) session.get(Order.class, id);
		//数据表里关联了3条订单明细，但查询结果总是只有2条
        System.out.println(order.getOrderItemList().size());
        session.getTransaction().commit();
    }
}

HibernateUtil 工具类

public class HibernateUtil {
    private static final SessionFactory sessionFactory = buildSessionFactory();
 
    private static SessionFactory buildSessionFactory() {
        try {
            // Create the SessionFactory from hibernate.cfg.xml
            return new Configuration().configure().buildSessionFactory();
        } catch (Throwable ex) {
            // Make sure you log the exception, as it might be swallowed
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }
 
    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
 
    public static void shutdown() {
        // Close caches and connection pools
        getSessionFactory().close();
    }
 
}

解决

问题点在于订单明细OrderItem属性product的加载方式

    /**
     * 产品
     */
    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    @JoinColumn(name = "product_id", nullable = false)
    private Product product;

这导致 order.getOrderItemList() 时总是 inner join 产品表，查询SQL如下：

   select
        orderiteml0_.order_id as order3_0_2_,
        orderiteml0_.id as id1_2_,
        orderiteml0_.id as id1_1_,
        orderiteml0_.order_id as order3_1_1_,
        orderiteml0_.product_id as product4_1_1_,
        orderiteml0_.QUANTITY as QUANTITY1_1_,
        product1_.id as id2_0_,
        product1_.PRODUCT_NAME as PRODUCT2_2_0_ 
    from
        T_ORDER_ITEM orderiteml0_ 
    inner join
        T_PRODUCT product1_ 
            on orderiteml0_.product_id=product1_.id 
    where
        orderiteml0_.order_id=? 
    order by
        orderiteml0_.id

**只需将 FetchType.EAGER 改为 FetchType.LAZY 即可，hibernate的底层查询SQL就会是：*

    select
        orderiteml0_.order_id as order3_0_1_,
        orderiteml0_.id as id1_1_,
        orderiteml0_.id as id1_0_,
        orderiteml0_.order_id as order3_1_0_,
        orderiteml0_.product_id as product4_1_0_,
        orderiteml0_.QUANTITY as QUANTITY1_0_ 
    from
        T_ORDER_ITEM orderiteml0_ 
    where
        orderiteml0_.order_id=? 
    order by
        orderiteml0_.id

扩展

当急加载时，如果 @JoinColumn 的属性nullable 为true时表示可以为空，关联查询时 left outer join ；false则不能为空，关联查询时 inner join

    /**
     * 产品
     */
    @ManyToOne(optional = false, fetch = FetchType.EAGER)
    @JoinColumn(name = "product_id", nullable = false)
    private Product product;

总结

昨天下午5点多遇到这个问题，知道晚上快8点才找到原因。期间尝试了很多，最后将hibernate的底册执行的SQL打印出来，才发现了问题所在。

hibernate输出SQL的配置如下：

	<!-- 输出sql-->
	<property name="show_sql">true</property>
	<!-- 格式化sql -->
 	<property name="format_sql">true</property>

演示代码 https://gitee.com/anyway2025/issueDemo/tree/master/hibernate-demo-211202