mynotes/hibernate/220515-hibernate关联对象查不到问题.md

问题复现

-- 创建数据表
drop table if exists t_book;
create table t_book(
    id int(11) primary key auto_increment,
    name varchar(100) comment '名称',
    catalog_id int(11) comment '分类id'
) comment '书籍表';

drop table if exists t_book_catalog;
create table t_book_catalog(
    id int(11) primary key auto_increment,
    name varchar(100) comment '名称'
) comment '书籍分类表';

-- 插入数据
insert into t_book_catalog(name) values ('分类1');

insert into t_book(name) values ('Book_A');  
insert into t_book(name, catalog_id) values ('Book_B', 0);  
insert into t_book(name, catalog_id) values ('Book_C', 1);

实体类 Book

@Data  
@Entity  
@Table(name = "t_book", schema = "test", catalog = "")  
public class Book {  
    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    @Column(name = "id")  
    private int id;  
  
    @Basic  
    @Column(name = "name")  
    private String name;  
  
    @ManyToOne(fetch = FetchType.LAZY)  
    @JoinColumn(name = "catalog_id", referencedColumnName = "id", nullable = false)  
    private BookCatalog bookCatalog;  
  
   
    @Override  
    public String toString() {  
        return "Book{" +  
                "id=" + id +  
                ", name='" + name + '\'' +  
                '}';  
    }  
}

实体类 BookCatalog

@Data  
@Entity  
@Table(name = "t_book_catalog", schema = "test", catalog = "")  
public class BookCatalog {  
    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    @Column(name = "id")  
    private int id;  
  
    @Basic  
    @Column(name = "name")  
    private String name;  
  
    @OneToMany(mappedBy = "bookCatalog", fetch = FetchType.EAGER)  
    private List<Book> bookList;  
  
    @Override  
    public String toString() {  
        return "BookCatalog{" +  
                "id=" + id +  
                ", name='" + name + '\'' +  
                ", bookList=" + bookList +  
                '}';  
    }  
}

原因在于，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