1 引言
在建表时,通常把外键的字段设为NOT NULL非空字段,然而在使用Hibernate进行一对多级联保存数据或者级联删除的时候,由于hibernate在默认情况下会把该外键字段设置为null,所以数据库会报异常。本文将对该问题进行详细分析。
2 分析问题
先看下数据库的关系,数据库将建立两张表,用户表和订单表。订单表有一个user_id的字段与用户表建立联系。
#用户表
CREATE TABLE `tb_user`(
`user_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`name` VARCHAR(32) NOT NULL COMMENT '用户姓名',
`age` INT DEFAULT 0 COMMENT '年龄',
PRIMARY KEY pk_user_id (`user_id`)
)ENGINE = INNODB AUTO_INCREMENT = 10000 DEFAULT CHARSET =utf8;
ALTER TABLE `tb_user` COMMENT '用户表';
#订单表
CREATE TABLE `tb_order`(
`order_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`user_id` BIGINT NOT NULL COMMENT '用户ID',
`total_price` DECIMAL(20,4) NOT NULL COMMENT '总额',
PRIMARY KEY pk_order_id (`order_id`)
)ENGINE = INNODB AUTO_INCREMENT = 100000 DEFAULT CHARSET =utf8;
ALTER TABLE `tb_order` COMMENT '订单表';
2.1 级联保存
级联保存,即保存其中一方的时候,同时保存另一方。
(1)下面尝试保存user的时候,同时保存它相关的order数据。配置如下:
User.hbm.xml
<set name="orders" cascade="save-update">
<key column="user_id"></key>
<one-to-many class="com.github.thinwonton.hibernate.sample.entity.Order"/>
</set>
Order.hbm.xml
<many-to-one name="user" class="com.github.thinwonton.hibernate.sample.entity.User"
column="user_id" >
</many-to-one>
测试代码
@Test
public void test6() {
User user = new User();
user.setName("小北");
user.setAge(33);
Order order = new Order();
order.setTotalPrice(new BigDecimal("73.2"));
user.getOrders().add(order);
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
session.save(user);
session.getTransaction().commit();
}
结果运行测试代码后,查看控制台,下面摘抄的是部分比较重要的信息。
insert
into
tb_user
(name, age)
values
(?, ?)
Hibernate:
insert
into
tb_order
(total_price, user_id)
values
(?, ?)
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper]-[WARN] SQL Error: 1048, SQLState: 23000
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper]-[ERROR] Column 'user_id' cannot be null
org.hibernate.exception.ConstraintViolationException: could not execute statement
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'user_id' cannot be null
上面的信息告诉我们在执行 ** insert into tb_order(total_price, user_id) values (?, ?) ** 这条语句的时候,抛出 user_id 不能为空的异常。其实,这个SQL语句是试图把order数据插入数据库,但此时order的user_id字段设置为null,把关联的order数据全部插入后,再使用update语句更新order的user_id字段。
(2)既然,上面通过保存user,级联保存order的方法行不通,那么,可以反过来保存order的时候,级联保存user,结果是可行的。** 解决问题 **
User.hbm.xml
<set name="orders" inverse="true">
<key column="user_id"></key>
<one-to-many class="com.github.thinwonton.hibernate.sample.entity.Order"/>
</set>
Order.hbm.xml
<many-to-one name="user" class="com.github.thinwonton.hibernate.sample.entity.User"
column="user_id" cascade="save-update">
</many-to-one>
测试代码
@Test
public void test66() {
User user = new User();
user.setName("小北");
user.setAge(33);
Order order = new Order();
order.setTotalPrice(new BigDecimal("73.2"));
order.setUser(user);
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
session.save(order);
session.getTransaction().commit();
}
控制台打印的SQL
Hibernate:
insert into tb_user (name, age) values (?, ?)
Hibernate:
insert into tb_order (total_price, user_id) values (?, ?)
可见,通过从表维护关系进行级联保存,会先保存user,或者user的id,然后带着user_id的值保存order记录。
2.2 级联删除
级联删除,即删除主表user的记录的时候,把从表中的相关记录都删除。
先看下测试代码
@Test
public void test7() {
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
User user = (User)session.get(User.class, 10012L);
session.delete(user);
session.getTransaction().commit();
}
(1)如果,配置User.hbm.xml如下,由user维护外键关系,它会先到order表中把外键字段设置为null,再删除主表的数据,最后删除从表的数据。因为之前在建表时,外键不能为null,所以数据库将会抛出异常。
<set name="orders" cascade="delete">
<key column="user_id"></key>
<one-to-many class="com.github.thinwonton.hibernate.sample.entity.Order"/>
</set>
报错异常如下:
Hibernate:
select
user0_.user_id as user_id1_1_0_,
user0_.name as name2_1_0_,
user0_.age as age3_1_0_
from
tb_user user0_
where
user0_.user_id=?
Hibernate:
select
orders0_.user_id as user_id3_1_0_,
orders0_.order_id as order_id1_0_0_,
orders0_.order_id as order_id1_0_1_,
orders0_.total_price as total_pr2_0_1_,
orders0_.user_id as user_id3_0_1_
from
tb_order orders0_
where
orders0_.user_id=?
Hibernate:
update
tb_order
set
user_id=null
where
user_id=?
09:56:46,932 [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl]-[INFO] HHH000010: On release of batch it still contained JDBC statements
09:56:46,932 [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]-[WARN] SQL Error: 1048, SQLState: 23000
09:56:46,932 [org.hibernate.engine.jdbc.spi.SqlExceptionHelper]-[ERROR] Column 'user_id' cannot be null
09:56:46,933 [org.hibernate.engine.jdbc.batch.internal.BatchingBatch]-[ERROR] HHH000315: Exception executing batch [could not execute batch]
(2)** 解决问题 **。既然由主表维护外键不能够级联删除,那么可以反转由从表进行外键维护,这时,在进行级联删除的时候,会先把从表的数据删除,再删除主表的数据。
<set name="orders" cascade="delete" inverse="true">
<key column="user_id"></key>
<one-to-many class="com.github.thinwonton.hibernate.sample.entity.Order"/>
</set>
控制台打印的SQL
Hibernate:
select
user0_.user_id as user_id1_1_0_,
user0_.name as name2_1_0_,
user0_.age as age3_1_0_
from
tb_user user0_
where
user0_.user_id=?
Hibernate:
select
orders0_.user_id as user_id3_1_0_,
orders0_.order_id as order_id1_0_0_,
orders0_.order_id as order_id1_0_1_,
orders0_.total_price as total_pr2_0_1_,
orders0_.user_id as user_id3_0_1_
from
tb_order orders0_
where
orders0_.user_id=?
Hibernate:
delete
from
tb_order
where
order_id=?
Hibernate:
delete
from
tb_user
where
user_id=?