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

巴特西

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

概述

fetchType的选择

问题复现

数据库准备

数据表对应的实体类

测试

解决

扩展

总结

最新文章

热门文章