通過外來鍵連線多個表

codexwj發表於2020-05-04

背景

背景一：

當我們建立一個表格，其中的一項內容可以有多種選擇，可以利用外來鍵的方式繫結一個表。如下圖比如我們需要建立一個employee表，其中包含很多資訊，其中有一項民族可以以外來鍵的方式繫結。
在這裡插入圖片描述
在這裡插入圖片描述

接著來看一看sql語句

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '員工編號',
  `name` varchar(10) DEFAULT NULL COMMENT '員工姓名',
  `gender` char(4) DEFAULT NULL COMMENT '性別',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `idCard` char(18) DEFAULT NULL COMMENT '身份證號',
  `wedlock` enum('已婚','未婚','離異') DEFAULT NULL COMMENT '婚姻狀況',
  `nationId` int(8) DEFAULT NULL COMMENT '民族',
  `nativePlace` varchar(20) DEFAULT NULL COMMENT '籍貫',
  `politicId` int(8) DEFAULT NULL COMMENT '政治面貌',
  `email` varchar(20) DEFAULT NULL COMMENT '郵箱',
  `phone` varchar(11) DEFAULT NULL COMMENT '電話號碼',
  `address` varchar(64) DEFAULT NULL COMMENT '聯絡地址',
  `departmentId` int(11) DEFAULT NULL COMMENT '所屬部門',
  `jobLevelId` int(11) DEFAULT NULL COMMENT '職稱ID',
  `posId` int(11) DEFAULT NULL COMMENT '職位ID',
  `engageForm` varchar(8) DEFAULT NULL COMMENT '聘用形式',
  `tiptopDegree` enum('博士','碩士','本科','大專','高中','初中','小學','其他') DEFAULT NULL COMMENT '最高學歷',
  `specialty` varchar(32) DEFAULT NULL COMMENT '所屬專業',
  `school` varchar(32) DEFAULT NULL COMMENT '畢業院校',
  `beginDate` date DEFAULT NULL COMMENT '入職日期',
  `workState` enum('在職','離職') DEFAULT '在職' COMMENT '在職狀態',
  `workID` char(8) DEFAULT NULL COMMENT '工號',
  `contractTerm` double DEFAULT NULL COMMENT '合同期限',
  `conversionTime` date DEFAULT NULL COMMENT '轉正日期',
  `notWorkDate` date DEFAULT NULL COMMENT '離職日期',
  `beginContract` date DEFAULT NULL COMMENT '合同起始日期',
  `endContract` date DEFAULT NULL COMMENT '合同終止日期',
  `workAge` int(11) DEFAULT NULL COMMENT '工齡',
  PRIMARY KEY (`id`),
  KEY `departmentId` (`departmentId`),
  KEY `jobId` (`jobLevelId`),
  KEY `dutyId` (`posId`),
  KEY `nationId` (`nationId`),
  KEY `politicId` (`politicId`),
  KEY `workID_key` (`workID`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`departmentId`) REFERENCES `department` (`id`),
  CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`jobLevelId`) REFERENCES `joblevel` (`id`),
  CONSTRAINT `employee_ibfk_3` FOREIGN KEY (`posId`) REFERENCES `position` (`id`),
  CONSTRAINT `employee_ibfk_4` FOREIGN KEY (`nationId`) REFERENCES `nation` (`id`),
  CONSTRAINT `employee_ibfk_5` FOREIGN KEY (`politicId`) REFERENCES `politicsstatus` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1942 DEFAULT CHARSET=utf8;

其中較難理解的是：

CONSTRAINT 'employee_ibfk_4' FOREIGN KEY ('departmentId') REFERENCES 'nation' ('id'),

這句程式碼的意思是對整個employee表進行外來鍵約束，其中employee_ibfk_4是外來鍵的名稱（命名方式：表名_ibfk_數字，ibfk（InnoDB Foreign Key））。nationId是employee表的一個屬性，department是通過外來鍵連結的新表，並且利用id進行索引。

在mapper層，需要必要的配置：

<resultMap id="AllEmployeeInfo" type="org.javaboy.vhr.model.Employee" extends="BaseResultMap">
    <association property="nation" javaType="org.javaboy.vhr.model.Nation">
        <id column="nid" property="id"/>
        <result column="nname" property="name"/>
    </association>

當要進行查詢的時候

<select id="getEmployeeById" resultMap="AllEmployeeInfo">
    select e.*,p.`id` as pid,p.`name` as pname,n.`id` as nid,n.`name` as nname,d.`id` as did,d.`name` as dname,j.`id` as jid,j.`name` as jname,pos.`id` as posid,pos.`name` as posname from employee e,nation n,politicsstatus p,department d,joblevel j,position pos where e.`nationId`=n.`id` and e.`politicId`=p.`id` and e.`departmentId`=d.`id` and e.`jobLevelId`=j.`id` and e.`posId`=pos.`id` and e.`id`=#{id}
</select>

從select from這個結構中可以知道，裡面有多個表:employee, nation, politicsstatus, department, joblevel, position。利用where e.'nationId'=n.'id'可以實現employee表對nation表的某一項訪問。

還遇到其他的語句：

  1. like模糊查詢
and e.name like concat('%',#{emp.name},'%')

這個like語句是用於模糊查詢，其中‘%’可以匹配任意字元，concat()這個方法是用於字串拼接的，即'%#{emp.name}%'。最終可以匹配到任何字串中有#{emp.name}的字串。

2.COUNT(*)函式返回在給定的選擇中被選的行數。

語法：

SELECT COUNT(*) FROM table

3.常見資料庫訪問語句

1. select * from table
2. delete from table where id = #{id, jdbcType=INTEGER}
3. insert into table (.,.,) values (#{},#{})

背景二

一個網站需要有不同的操作員進行登入，而且需要顯示操作員的基本資訊和使用者角色。
在這裡插入圖片描述

來看一下sql語句

/*Table structure for table `hr` */

DROP TABLE IF EXISTS `hr`;

CREATE TABLE `hr` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'hrID',
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `phone` char(11) DEFAULT NULL COMMENT '手機號碼',
  `telephone` varchar(16) DEFAULT NULL COMMENT '住宅電話',
  `address` varchar(64) DEFAULT NULL COMMENT '聯絡地址',
  `enabled` tinyint(1) DEFAULT '1',
  `username` varchar(255) DEFAULT NULL COMMENT '使用者名稱',
  `password` varchar(255) DEFAULT NULL COMMENT '密碼',
  `userface` varchar(255) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `hr` */

insert  into `hr`(`id`,`name`,`phone`,`telephone`,`address`,`enabled`,`username`,`password`,`userface`,`remark`) values (3,'系統管理員','18568887789','029-82881234','深圳南山',1,'admin','$2a$10$ySG2lkvjFHY5O0./CPIE1OI8VJsuKYEzOYzqIa7AJR6sEgSzUFOAm','http://bpic.588ku.com/element_pic/01/40/00/64573ce2edc0728.jpg',NULL),(5,'李白','18568123489','029-82123434','海口美蘭',1,'libai','$2a$10$oE39aG10kB/rFu2vQeCJTu/V/v4n6DRR0f8WyXRiAYvBpmadoOBE.','https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1514093920321&di=913e88c23f382933ef430024afd9128a&imgtype=0&src=http%3A%2F%2Fp.3761.com%2Fpic%2F9771429316733.jpg',NULL),(10,'韓愈','18568123666','029-82111555','廣州番禺',1,'hanyu','$2a$10$oE39aG10kB/rFu2vQeCJTu/V/v4n6DRR0f8WyXRiAYvBpmadoOBE.','https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1517070040185&di=be0375e0c3db6c311b837b28c208f318&imgtype=0&src=http%3A%2F%2Fimg2.soyoung.com%2Fpost%2F20150213%2F6%2F20150213141918532.jpg',NULL),(11,'柳宗元','18568123377','029-82111333','廣州天河',1,'liuzongyuan','$2a$10$oE39aG10kB/rFu2vQeCJTu/V/v4n6DRR0f8WyXRiAYvBpmadoOBE.','https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1515233756&di=0856d923a0a37a87fd26604a2c871370&imgtype=jpg&er=1&src=http%3A%2F%2Fwww.qqzhi.com%2Fuploadpic%2F2014-09-27%2F041716704.jpg',NULL),(12,'曾鞏','18568128888','029-82111222','廣州越秀',1,'zenggong','$2a$10$oE39aG10kB/rFu2vQeCJTu/V/v4n6DRR0f8WyXRiAYvBpmadoOBE.','https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1517070040185&di=be0375e0c3db6c311b837b28c208f318&imgtype=0&src=http%3A%2F%2Fimg2.soyoung.com%2Fpost%2F20150213%2F6%2F20150213141918532.jpg',NULL);

/*Table structure for table `hr_role` */

DROP TABLE IF EXISTS `hr_role`;

CREATE TABLE `hr_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hrid` int(11) DEFAULT NULL,
  `rid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rid` (`rid`),
  KEY `hr_role_ibfk_1` (`hrid`),
  CONSTRAINT `hr_role_ibfk_1` FOREIGN KEY (`hrid`) REFERENCES `hr` (`id`) ON DELETE CASCADE,
  CONSTRAINT `hr_role_ibfk_2` FOREIGN KEY (`rid`) REFERENCES `role` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8;

/*Data for the table `hr_role` */

insert  into `hr_role`(`id`,`hrid`,`rid`) values (1,3,6),(35,12,4),(36,12,3),(37,12,2),(43,11,3),(44,11,2),(45,11,4),(46,11,5),(48,10,3),(49,10,4),(72,5,1),(73,5,2),(74,5,3);

/*Table structure for table `role` */

DROP TABLE IF EXISTS `role`;

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `nameZh` varchar(64) DEFAULT NULL COMMENT '角色名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

/*Data for the table `role` */

insert  into `role`(`id`,`name`,`nameZh`) values (1,'ROLE_manager','部門經理'),(2,'ROLE_personnel','人事專員'),(3,'ROLE_recruiter','招聘主管'),(4,'ROLE_train','培訓主管'),(5,'ROLE_performance','薪酬績效主管'),(6,'ROLE_admin','系統管理員'),(13,'ROLE_test2','測試角色2'),(14,'ROLE_test1','測試角色1'),(17,'ROLE_test3','測試角色3'),(18,'ROLE_test4','測試角色4'),(19,'ROLE_test4','測試角色4'),(20,'ROLE_test5','測試角色5'),(21,'ROLE_test6','測試角色6');

上面可以建立三個表，第一個表hr表示是操作員的基本資訊，第三個表role表示角色，即表示該操作員的使用者角色。第二個表hr_role使用外來鍵hr_role_ibfk_1和hr_role_ibfk_1來繫結第一表hr和第三個表role。

hr
在這裡插入圖片描述
hr_role
在這裡插入圖片描述
role
在這裡插入圖片描述
其中語句：

ON DELETE CASCADE;

其中，DELETE是指在主鍵表中刪除一條記錄。CASCADE表示級聯操作，如果主鍵表的記錄被刪除，外來鍵表中該行也相應被刪除。

同理，

on update cascade;

在update和delete後面可以新增：no action，set null，set default。

no action：表示不做任何操作
set null：表示外來鍵中將相應欄位設定為null
set default：表示設定為預設值

在HrMapper.xml中：

    <resultMap id="BaseResultMap" type="org.javaboy.vhr.model.Hr">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="phone" property="phone" jdbcType="CHAR"/>
        <result column="telephone" property="telephone" jdbcType="VARCHAR"/>
        <result column="address" property="address" jdbcType="VARCHAR"/>
        <result column="enabled" property="enabled" jdbcType="BIT"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="password" property="password" jdbcType="VARCHAR"/>
        <result column="userface" property="userface" jdbcType="VARCHAR"/>
        <result column="remark" property="remark" jdbcType="VARCHAR"/>
    </resultMap>
    <resultMap id="HrWithRoles" type="org.javaboy.vhr.model.Hr" extends="BaseResultMap">
        <collection property="roles" ofType="org.javaboy.vhr.model.Role">
            <id column="rid" property="id"/>
            <result column="rname" property="name"/>
            <result column="rnameZh" property="nameZh"/>
        </collection>
    </resultMap>

通過：以下的方式將role的屬性新增進來。

    <resultMap id="HrWithRoles" type="org.javaboy.vhr.model.Hr" extends="BaseResultMap">
        <collection property="roles" ofType="org.javaboy.vhr.model.Role">
            <id column="rid" property="id"/>
            <result column="rname" property="name"/>
            <result column="rnameZh" property="nameZh"/>
        </collection>
    </resultMap>

查詢所有的操作員：

    <select id="getAllHrs" resultMap="HrWithRoles">
        select hr.id, hr.name, hr.phone, hr.telephone, hr.address, hr.enabled, hr.username, hr.userface,
        hr.remark,r.`id` as rid,r.`name` as rname,r.`nameZh` as rnameZh from hr left join hr_role hrr on
        hr.`id`=hrr.`hrid` left join role r on hrr.`rid`=r.`id` where hr.`id`!=#{hrid}
        <if test="keywords!=null">
            and hr.name like concat('%',#{keywords},'%')
        </if>
        order by hr.id
    </select>

其中有三個表hr，hr_role，role，採用left join的方式組合在一起。

通過如下的方式對操作員的角色進行查詢：

    <select id="getHrRolesById" resultType="org.javaboy.vhr.model.Role">
    select r.* from role r,hr_role hrr where hrr.`rid`=r.`id` and hrr.`hrid`=#{id}
  </select>

其中有role和hr_role兩個表。

在hr_role資料表中：
在這裡插入圖片描述

可以實現一個操作員繫結多個角色。

其mapper層的實現方式：

Integer addRole(@Param("hrid") Integer hrid, @Param("rids") Integer[] rids);

  <insert id="addRole">
    insert into hr_role (hrid,rid) values
    <foreach collection="rids" item="rid" separator=",">
      (#{hrid},#{rid})
    </foreach>
  </insert>

從Mapper層知道傳入兩個引數，一個是hrid，一個是一個陣列rids，又從xml配置檔案中可以知道實現了一對多。hrid對應rids。
參考

Reference

相關文章