220515-hibernate关联对象查不到问题.md 10 KB
问题复现
-- 创建数据表
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定义实体之间的关系有如下几种：
在定义它们的时候可以通过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