本文详细介绍了在数据库设计中如何建立用户表与部门表之间的单向多对一关联，包括创建表结构、SQL语句实现及使用Hibernate进行映射与操作的实例。重点在于阐述了在实际应用中如何避免级联删除与查询部门时自动包含所有员工的策略。
有这样一个应用场景，有一张用户表(APM_USER），一张部门表(APM_DEPT)。用户和部门之间的关系是多对一（many to one)。考虑到其他一些特殊情况。虽然实际情况，部门对用户是多对一（many to one)。实际上，删除一个部门的时候，为保险起见，不需要级联删除用户信息，查出部门对联时，也不需要查出部门内的所有员工。因此做成了单向多对一关联。
具体建表SQL如下：
-- Create table
create table APM_USER
(
USER_ID VARCHAR2(18) not null,
USER_NAME VARCHAR2(300),
DEPT_ID VARCHAR2(18),
USER_PASS VARCHAR2(300),
USER_STATUS VARCHAR2(20),
EMAIL VARCHAR2(300),
MOBILE_PHONE VARCHAR2(300),
FCU VARCHAR2(18),
FCD VARCHAR2(18),
FCT VARCHAR2(20),
LUU VARCHAR2(18),
LUD VARCHAR2(18),
LUT VARCHAR2(20)
)
tablespace AMSTBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column APM_USER.USER_ID
is '用户编号';
comment on column APM_USER.USER_NAME
is '用户名称';
comment on column APM_USER.DEPT_ID
is '所在部门';
comment on column APM_USER.USER_PASS
is '用户密码';
comment on column APM_USER.USER_STATUS
is '用户状态';
comment on column APM_USER.EMAIL
is '邮件地址';
comment on column APM_USER.MOBILE_PHONE
is '手机号';
comment on column APM_USER.FCU
is '登记人';
comment on column APM_USER.FCD
is '登记部门';
comment on column APM_USER.FCT
is '登记时间';
comment on column APM_USER.LUU
is '更新人';
comment on column APM_USER.LUD
is '更新部门';
comment on column APM_USER.LUT
is '更新时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table APM_USER
add constraint PK_APM_USER primary key (USER_ID)
using index
tablespace AMSTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table APM_USER
add constraint FK_APM_USER_REFERENCE_APM_DEPT foreign key (DEPT_ID)
references APM_DEPT (DEPT_ID);
-- Create table
create table APM_DEPT
(
DEPT_ID VARCHAR2(18) not null,
DEPT_NAME VARCHAR2(300),
DEPT_LEVEL INTEGER,
DEPT_STATUS VARCHAR2(20),
FCU VARCHAR2(18),
FCD VARCHAR2(18),
FCT VARCHAR2(20),
LUU VARCHAR2(18),
LUD VARCHAR2(18),
LUT VARCHAR2(20)
)
tablespace AMSTBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column APM_DEPT.DEPT_ID
is '部门号';
comment on column APM_DEPT.DEPT_NAME
is '部门名';
comment on column APM_DEPT.DEPT_LEVEL
is '部门级别';
comment on column APM_DEPT.DEPT_STATUS
is '部门状态';
comment on column APM_DEPT.FCU
is '登记人';
comment on column APM_DEPT.FCD
is '登记部门';
comment on column APM_DEPT.FCT
is '登记时间';
comment on column APM_DEPT.LUU
is '更新人';
comment on column APM_DEPT.LUD
is '更新部门';
comment on column APM_DEPT.LUT
is '更新时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table APM_DEPT
add constraint PK_APM_DEPT primary key (DEPT_ID)
using index
tablespace AMSTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
hibernate映射文件配置如下：
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2015-3-13 8:24:33 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
<class name="com.amarsoft.apm.model.User" table="APM_USER">
<id name="userId" type="string">
<column name="USER_ID" length="18" />
<generator class="assigned" />
</id>
<!-- 单向一对多关联，不作级联删除或更新操作，对部门实体的操作，还是手动进行。查出用户对象的同时，一定要查出部门对象 -->
<many-to-one name="dept" class="com.amarsoft.apm.model.Dept" fetch="select" cascade="none" lazy="false">
<column name="DEPT_ID" length="18">
</column>
</many-to-one>
<property name="userName" type="string">
<column name="USER_NAME" length="300">
</column>
</property>
<property name="userPass" type="string">
<column name="USER_PASS" length="300">
</column>
</property>
<property name="userStatus" type="string">
<column name="USER_STATUS" length="20">
</column>
</property>
<property name="email" type="string">
<column name="EMAIL" length="300">
</column>
</property>
<property name="mobilePhone" type="string">
<column name="MOBILE_PHONE" length="300">
</column>
</property>
<property name="fcu" type="string">
<column name="FCU" length="18">
</column>
</property>
<property name="fcd" type="string">
<column name="FCD" length="18">
</column>
</property>
<property name="fct" type="string">
<column name="FCT" length="20">
</column>
</property>
<property name="luu" type="string">
<column name="LUU" length="18">
</column>
</property>
<property name="lud" type="string">
<column name="LUD" length="18">
</column>
</property>
<property name="lut" type="string">
<column name="LUT" length="20">
</column>
</property>
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 2015-3-13 8:24:33 by Hibernate Tools 3.4.0.CR1 -->
<hibernate-mapping>
<class name="com.amarsoft.apm.model.Dept" table="APM_DEPT">
<id name="deptId" type="string">
<column name="DEPT_ID" length="18" />
<generator class="assigned" />
</id>
<property name="deptName" type="string">
<column name="DEPT_NAME" length="300">
</column>
</property>
<property name="deptLevel" type="big_decimal">
<column name="DEPT_LEVEL" precision="22" scale="0">
</column>
</property>
<property name="deptStatus" type="string">
<column name="DEPT_STATUS" length="20">
</column>
</property>
<property name="fcu" type="string">
<column name="FCU" length="18">
</column>
</property>
<property name="fcd" type="string">
<column name="FCD" length="18">
</column>
</property>
<property name="fct" type="string">
<column name="FCT" length="20">
</column>
</property>
<property name="luu" type="string">
<column name="LUU" length="18">
</column>
</property>
<property name="lud" type="string">
<column name="LUD" length="18">
</column>
</property>
<property name="lut" type="string">
<column name="LUT" length="20">
</column>
</property>
</class>
</hibernate-mapping>
用户类，部门类代码如下：
package com.amarsoft.apm.model;
/**
* 用户类
* @author yangsong
*
*/
public class User implements java.io.Serializable {
private static final long serialVersionUID = 3393279804743607950L;
private String userId;
private Dept dept;
private String userName;
private String userPass;
private String userStatus;
private String email;
private String mobilePhone;
private String fcu;
private String fcd;
private String fct;
private String luu;
private String lud;
private String lut;
public User() {
}
public User(String userId) {
this.userId = userId;
}
public User(String userId, Dept dept, String userName, String userPass,
String userStatus, String email, String mobilePhone, String fcu,
String fcd, String fct, String luu, String lud, String lut) {
this.userId = userId;
this.dept = dept;
this.userName = userName;
this.userPass = userPass;
this.userStatus = userStatus;
this.email = email;
this.mobilePhone = mobilePhone;
this.fcu = fcu;
this.fcd = fcd;
this.fct = fct;
this.luu = luu;
this.lud = lud;
this.lut = lut;
}
public String getUserId() {
return this.userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public Dept getDept() {
return this.dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
public String getUserName() {
return this.userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPass() {
return this.userPass;
}
public void setUserPass(String userPass) {
this.userPass = userPass;
}
public String getUserStatus() {
return this.userStatus;
}
public void setUserStatus(String userStatus) {
this.userStatus = userStatus;
}
public String getEmail() {
return this.email;
}
public void setEmail(String email) {
this.email = email;
}
public String getMobilePhone() {
return this.mobilePhone;
}
public void setMobilePhone(String mobilePhone) {
this.mobilePhone = mobilePhone;
}
public String getFcu() {
return this.fcu;
}
public void setFcu(String fcu) {
this.fcu = fcu;
}
public String getFcd() {
return this.fcd;
}
public void setFcd(String fcd) {
this.fcd = fcd;
}
public String getFct() {
return this.fct;
}
public void setFct(String fct) {
this.fct = fct;
}
public String getLuu() {
return this.luu;
}
public void setLuu(String luu) {
this.luu = luu;
}
public String getLud() {
return this.lud;
}
public void setLud(String lud) {
this.lud = lud;
}
public String getLut() {
return this.lut;
}
public void setLut(String lut) {
this.lut = lut;
}
}
package com.amarsoft.apm.model;
import java.math.BigDecimal;
/**
* 部门类
* @author yangsong
*
*/
public class Dept implements java.io.Serializable {
private static final long serialVersionUID = -1210910164577152302L;
private String deptId;
private String deptName;
private BigDecimal deptLevel;
private String deptStatus;
private String fcu;
private String fcd;
private String fct;
private String luu;
private String lud;
private String lut;
public Dept() {
}
public Dept(String deptId) {
this.deptId = deptId;
}
public Dept(String deptId, String deptName, BigDecimal deptLevel,
String deptStatus, String fcu, String fcd, String fct, String luu,
String lud, String lut) {
this.deptId = deptId;
this.deptName = deptName;
this.deptLevel = deptLevel;
this.deptStatus = deptStatus;
this.fcu = fcu;
this.fcd = fcd;
this.fct = fct;
this.luu = luu;
this.lud = lud;
this.lut = lut;
}
public String getDeptId() {
return this.deptId;
}
public void setDeptId(String deptId) {
this.deptId = deptId;
}
public String getDeptName() {
return this.deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public BigDecimal getDeptLevel() {
return this.deptLevel;
}
public void setDeptLevel(BigDecimal deptLevel) {
this.deptLevel = deptLevel;
}
public String getDeptStatus() {
return this.deptStatus;
}
public void setDeptStatus(String deptStatus) {
this.deptStatus = deptStatus;
}
public String getFcu() {
return this.fcu;
}
public void setFcu(String fcu) {
this.fcu = fcu;
}
public String getFcd() {
return this.fcd;
}
public void setFcd(String fcd) {
this.fcd = fcd;
}
public String getFct() {
return this.fct;
}
public void setFct(String fct) {
this.fct = fct;
}
public String getLuu() {
return this.luu;
}
public void setLuu(String luu) {
this.luu = luu;
}
public String getLud() {
return this.lud;
}
public void setLud(String lud) {
this.lud = lud;
}
public String getLut() {
return this.lut;
}
public void setLut(String lut) {
this.lut = lut;
}
}
测试代码如下：
@Test
public void testUser(){
DateFormat df = new SimpleDateFormat("yyy/MM/dd HH:mm:ss");
try {
Configuration configuration = new Configuration();
configuration.configure("hibernate.cfg.xml");
SessionFactory sf = configuration.buildSessionFactory();
Session session = sf.openSession();
Transaction tx = session.beginTransaction();
//新增测试
User user = new User();
user.setUserId("demo001");
user.setUserName("示例测试");
user.setFct(df.format(new Date()));
Dept dept = new Dept();
dept.setDeptId("10");
dept.setDeptName("示例总部");
dept.setFct(df.format(new Date()));
user.setDept(dept);
session.saveOrUpdate(dept);//部门对象自行保存，防止通过user对象级联后被篡改
session.saveOrUpdate(user);
//查询实体
String hql = "from User where userId=:userId";
Query query=session.createQuery(hql);
query.setString("userId", user.getUserId());
User user1 = (User)query.uniqueResult();
Assert.assertNotNull(user1);
Assert.assertEquals(user.getUserName(), user1.getUserName());
Assert.assertNotNull(user1.getDept());
Assert.assertEquals(user.getDept().getDeptName(), user1.getDept().getDeptName());
tx.commit();
session.close();
} catch (HibernateException e) {
e.printStackTrace();
}
}
自己做个笔记记录下，同时，也给有需要或学习的同学参考。
扫一扫
803
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
