在实际开发中，会存在很多表。比如一个电商项目可能有：分类表、产品表、顾客表、订单表等。这些表之间是存在关系的。

表与表之间的三种关系


 判断技巧 例子
一对多 分别从两个角度思考问题：比如班级对学生。
从班级的角度看问题：一个班级可以对应很多学生。
从学生角度看问题：一个学生只能对应一个班级。		 班级对学生、部门对员工、用户对订单、人对银行卡、客户和订单、分类和商品
多对多 学生与课程、用户与角色
一对一 人对身份证

一对多

建表原则

先分清楚一方和多方，根据上面表格的判断技巧来判断，这样很容易搞清楚。在多方的表中添加一个字段，作为该表的外键，指向一方的主键。
image.png
例如一个人可以有很多银行卡，一个银行卡只能对应一个人。在从表(多方)里面新建一个外键指向主表(一方)的主键。这里从表是bank_card 、主表是person。bank_card里面的person_id做为外键指向person表的主键。

SQL语句

查询所有人对应的银行卡信息

一个人可以拥有多个银行卡，查询的是人，从person表出发（所谓从哪里出发指的是FROM 后先跟什么表）

  1. SELECT
  2.  person.* ,
  3.  bank_card.id AS bank_id,bank_card.`no`,bank_card.amount
  4.  FROM person LEFT JOIN bank_card ON person.id = bank_card.fn_person_id

本节末尾有其对应的JavaBean及SQL样例数据。

MyBatis对应的mapper

  1.     <select id="getPersonBankCard" resultMap="bankCards">
  2.         SELECT
  3.             person.* ,
  4.             bank_card.id AS bank_id,bank_card.`no`,bank_card.amount
  5.         FROM person LEFT JOIN bank_card ON person.id = bank_card.fn_person_id
  6.     </select>
  8.     <resultMap id="bankCards" type="com.lff.beans.Person">
  9.         <id property="id" column="id" />
  10.         <result property="name" column="name" />
  11.         <result property="age" column="age" />
  13.         <collection property="bankCards" ofType="com.lff.beans.BankCard">
  14.             <id property="id" column="bank_id" />
  15.             <result property="no" column="no" />
  16.             <result property="amount" column="amount" />
  17.         </collection>
  18.     </resultMap>

外界使用

  1. try(SqlSession session = MyBatisUtils.openSession()){
  2.     List<Person> personList = session.selectList("person.getPersonBankCard");
  3.     System.out.println(personList);
  4. }

查询每张银行卡把关联的人也查出来

从bank_card银行卡出发，根据条件关联其对应的人

  1. SELECT 
  2. bank_card.*,
  3. person.*
  4. FROM bank_card LEFT JOIN person ON person.id = bank_card.fn_person_id

多对多

如果是多对多，通常会创建一个中间表，把表的关系拆成两个一对多。在中间表至少包含两个字段，作为该表的外键指向一方表的主键。中间表不仅能有效的节省存储空间也能更容易的维护表与表之间的关系。
例子：人与角色之间的关系，一个人可以有很多个角色。一个角色可以有很多个人。
image.png

中间表至少包含两个字段：person_id外键、role_id外键。这两个外键作为联合主键使用。
如上表，person表里面的Jack既有作为丈夫的角色也有作为厨师的角色

SQL语句

查询每个人都对应哪些角色

从person表出发，关联中间表后，再查询角色表

  1. SELECT 
  2. person.*,
  3. role.`name` AS role_name,role.duty
  4. FROM person LEFT JOIN person_role ON person.id = person_role.person_id
  5. LEFT JOIN role ON role.id = person_role.role_id

MyBatis对应的mapper

  1.     <select id="getPersonRoles" resultMap="roles">
  2.         SELECT
  3.             person.*,
  4.             role.`name` AS role_name,role.duty
  5.         FROM person LEFT JOIN person_role ON person.id = person_role.person_id
  6.         LEFT JOIN role ON role.id = person_role.role_id
  7.     </select>
  9.     <resultMap id="roles" type="com.lff.beans.Person">
  10.         <id property="id" column="id" />
  11.         <result property="name" column="name" />
  12.         <result property="age" column="age" />
  14.         <collection property="roles" ofType="com.lff.beans.Role">
  15.             <result property="name" column="role_name" />
  16.             <result property="duty" column="duty" />
  17.         </collection>
  18.     </resultMap>

外界使用

  1. try(SqlSession session = MyBatisUtils.openSession()){
  2.     List<Person> personList = session.selectList("person.getPersonRoles");
  3.     System.out.println(personList);
  4. }

查询每个角色都有什么人

SQL语句,从role角色表出发，先关联中间表，关联后再查询person表

  1. SELECT 
  2. role.id AS role_id ,role.`name` AS role_name ,role.duty,
  3. person.id AS person_id,  person.`name` AS person_name,person.age
  4. FROM role LEFT JOIN person_role ON role.id = person_role.role_id
  5. LEFT JOIN person ON person.id = person_role.person_id

一对一

建表原则：

  • 可以都放在一张表中
  • 也可以在任意一方添加一个外键指向另一方的主键。从表的唯一外键和主表的主键，形成主外键关系， 外键唯一用UNIQUE修饰

示例：人和人的身份证
image.png
id_card有一个外键引用person表的id。
每一个人都有一个唯一的身份证号

SQL查询语句

查询所有的人以及对应的身份证

从person表出发，根据条件查询其对应的身份证

  1. SELECT person.*  , id_card.`no`,id_card.address,id_card.person_id FROM person JOIN id_card ON person.id = id_card.person_id

MyBatis对应的mapper

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="person">
  7.     <select id="personList" resultMap="personMap">
  8.         SELECT person.*  , id_card.`no`,id_card.address,id_card.person_id FROM person JOIN id_card ON person.id = id_card.person_id
  9.     </select>
  11.     <resultMap id="personMap" type="com.lff.beans.Person">
  12.         <id property="id" column="id" />
  13.         <result property="name" column="name" />
  14.         <result property="age" column="age" />
  15.         <association property="idCard" javaType="com.lff.beans.IdCard">
  16.             <id property="id" column="id" />
  17.             <result property="no" column="no" />
  18.             <result property="address" column="address" />
  19.         </association>
  20.     </resultMap>
  22. </mapper>

外界使用

  1. try(SqlSession session = MyBatisUtils.openSession()){
  2.     List<Person> personList = session.selectList("person.personList");
  3. }

查询所有的身份证以及对应的人

从id_card表出发，根据条件查询出对应的人。

  1. SELECT id_card.* ,person.`name`,person.age  FROM id_card JOIN person ON id_card.person_id = person.id

MyBatis对应的mapper

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="idCard">
  7.     <select id="idCardList" resultMap="idCardMap">
  8.         SELECT id_card.* ,person.`name`,person.age  FROM id_card JOIN person ON id_card.person_id = person.id
  9.     </select>
  11.     <resultMap id="idCardMap" type="com.lff.beans.IdCard">
  12.         <id property="id" column="id" />
  13.         <result property="no" column="no" />
  14.         <result property="address" column="address" />
  16.         <association property="person" javaType="com.lff.beans.Person">
  17.             <id property="id" column="id" />
  18.             <result property="name" column="name" />
  19.             <result property="age" column="age" />
  20.         </association>
  22.     </resultMap>
  24. </mapper>

外界使用

  1. try(SqlSession session = MyBatisUtils.openSession()){
  2.     List<IdCard> idCardList = session.selectList("idCard.idCardList");
  3. }


综合案例

查询出人对应的信息，包括姓名、年龄、身份证信息、银行卡信息、角色信息。

SQL语句

  1. SELECT 
  2. person.*  , 
  3. id_card.`no`  AS id_no,id_card.address,
  4. bank_card.id AS bank_card_id,bank_card.`no` AS bank_card_no,bank_card.amount ,
  5. role.`name` AS role_name,role.duty
  6. FROM person JOIN id_card ON person.id = id_card.person_id 
  7. LEFT JOIN bank_card ON person.id = bank_card.fn_person_id
  8. LEFT JOIN person_role ON person.id = person_role.person_id 
  9. LEFT JOIN role ON role.id = person_role.role_id
  • 因为要查询出的是人，所以FROM后应先跟person表
  • 使用join关联出身份证信息
  • 使用LEFT JOIN查询出银行卡信息
  • LEFT JOIN person_role ON person.id = person_role.person_id

多对多的情况先关联中间表，先有中间表后，后面才能使用中间表里面的信息
当有多个表的字段相同时，要起一个别名，否则查询出来的字段会覆盖前面的。

MyBatis对应的mapper

  1.  <select id="getPersonAllInfo" resultMap="allInfo">
  2.         SELECT
  3.             person.*  ,
  4.             id_card.`no`  AS id_no,id_card.address,
  5.             bank_card.id AS bank_card_id,bank_card.`no` AS bank_card_no,bank_card.amount ,
  6.             role.`name` AS role_name,role.duty
  7.         FROM person JOIN id_card ON person.id = id_card.person_id
  8.         LEFT JOIN bank_card ON person.id = bank_card.fn_person_id
  9.         LEFT JOIN person_role ON person.id = person_role.person_id
  10.         LEFT JOIN role ON role.id = person_role.role_id
  12.     </select>
  14.     <resultMap id="allInfo" type="com.lff.beans.Person">
  16.         <id property="id" column="id" />
  17.         <result property="name" column="name" />
  18.         <result property="age" column="age" />
  20. <!--        关联的身份证信息：idCard为Person类的属性名-->
  21.         <association property="idCard" javaType="com.lff.beans.IdCard">
  22. <!--            这里的属性property都是类IdCard的属性名，column是数据库查询出来的字段-->
  23.             <result property="no" column="id_no" />
  24.             <result property="address" column="address" />
  25.         </association>
  26. <!--        当属性值是一个列表时如Person类的 List<BankCard> bankCards，使用collection标签，ofType对应列表里面每一个对象对应的类-->
  27.         <collection property="bankCards" ofType="com.lff.beans.BankCard">
  28.             <!--   这里的属性property都是类BankCard的属性名，column是数据库查询出来的字段-->
  29.             <result property="no" column="bank_card_no" />
  30.             <result property="amount" column="amount" />
  31.         </collection>
  33.         <collection property="roles" ofType="com.lff.beans.Role">
  34.             <result property="name" column="role_name" />
  35.             <result property="duty" column="duty" />
  36.         </collection>
  37.     </resultMap>

外界使用

  1. try(SqlSession session = MyBatisUtils.openSession()){
  2.     List<Person> personList = session.selectList("person.getPersonAllInfo");
  3.     System.out.println(personList);
  4. }

本节使用的SQL样例

  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  4. -- ----------------------------
  5. -- Table structure for bank_card
  6. -- ----------------------------
  7. DROP TABLE IF EXISTS `bank_card`;
  8. CREATE TABLE `bank_card` (
  9.   `id` int(11) NOT NULL AUTO_INCREMENT,
  10.   `no` int(11) NOT NULL,
  11.   `amount` decimal(18,2) DEFAULT NULL,
  12.   `fn_person_id` int(11) DEFAULT NULL,
  13.   PRIMARY KEY (`id`),
  14.   KEY `fn_person_id` (`fn_person_id`),
  15.   CONSTRAINT `fn_person_id` FOREIGN KEY (`fn_person_id`) REFERENCES `person` (`id`)
  16. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  18. -- ----------------------------
  19. -- Records of bank_card
  20. -- ----------------------------
  21. BEGIN;
  22. INSERT INTO `bank_card` VALUES (1, 123456, 1000.00, 1);
  23. INSERT INTO `bank_card` VALUES (2, 234567, 300.00, 1);
  24. INSERT INTO `bank_card` VALUES (3, 345678, 200.00, 3);
  25. INSERT INTO `bank_card` VALUES (4, 456789, 500.00, 4);
  26. COMMIT;
  28. -- ----------------------------
  29. -- Table structure for id_card
  30. -- ----------------------------
  31. DROP TABLE IF EXISTS `id_card`;
  32. CREATE TABLE `id_card` (
  33.   `id` int(11) NOT NULL AUTO_INCREMENT,
  34.   `no` int(30) DEFAULT NULL,
  35.   `address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  36.   `person_id` int(11) NOT NULL,
  37.   PRIMARY KEY (`id`),
  38.   KEY `fk_person_id` (`person_id`),
  39.   CONSTRAINT `fk_person_id` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
  40. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  42. -- ----------------------------
  43. -- Records of id_card
  44. -- ----------------------------
  45. BEGIN;
  46. INSERT INTO `id_card` VALUES (1, 123999, '北京市朝阳区。。。', 1);
  47. INSERT INTO `id_card` VALUES (2, 123888, '北京市昌平区。。。', 2);
  48. INSERT INTO `id_card` VALUES (3, 123777, '北京市海淀区。。。', 3);
  49. INSERT INTO `id_card` VALUES (4, 123666, '北京市通州区。。。', 4);
  50. COMMIT;
  52. -- ----------------------------
  53. -- Table structure for person
  54. -- ----------------------------
  55. DROP TABLE IF EXISTS `person`;
  56. CREATE TABLE `person` (
  57.   `id` int(11) NOT NULL AUTO_INCREMENT,
  58.   `name` varchar(30) CHARACTER SET utf8mb4 DEFAULT NULL,
  59.   `age` int(3) DEFAULT NULL,
  60.   PRIMARY KEY (`id`),
  61.   KEY `id` (`id`),
  62.   KEY `age` (`age`)
  63. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  65. -- ----------------------------
  66. -- Records of person
  67. -- ----------------------------
  68. BEGIN;
  69. INSERT INTO `person` VALUES (1, 'Jack', 18);
  70. INSERT INTO `person` VALUES (2, 'Mike', 22);
  71. INSERT INTO `person` VALUES (3, 'Jose', 28);
  72. INSERT INTO `person` VALUES (4, 'Cliff', 35);
  73. COMMIT;
  75. -- ----------------------------
  76. -- Table structure for person_role
  77. -- ----------------------------
  78. DROP TABLE IF EXISTS `person_role`;
  79. CREATE TABLE `person_role` (
  80.   `person_id` int(11) NOT NULL,
  81.   `role_id` int(11) NOT NULL,
  82.   PRIMARY KEY (`person_id`,`role_id`),
  83.   KEY `r_id` (`role_id`),
  84.   CONSTRAINT `p_id` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
  85.   CONSTRAINT `r_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
  86. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  88. -- ----------------------------
  89. -- Records of person_role
  90. -- ----------------------------
  91. BEGIN;
  92. INSERT INTO `person_role` VALUES (2, 1);
  93. INSERT INTO `person_role` VALUES (1, 2);
  94. INSERT INTO `person_role` VALUES (1, 3);
  95. COMMIT;
  97. -- ----------------------------
  98. -- Table structure for role
  99. -- ----------------------------
  100. DROP TABLE IF EXISTS `role`;
  101. CREATE TABLE `role` (
  102.   `id` int(11) NOT NULL AUTO_INCREMENT,
  103.   `name` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  104.   `duty` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  105.   PRIMARY KEY (`id`)
  106. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  108. -- ----------------------------
  109. -- Records of role
  110. -- ----------------------------
  111. BEGIN;
  112. INSERT INTO `role` VALUES (1, '程序员', '编码大神');
  113. INSERT INTO `role` VALUES (2, '丈夫', '爱妻子');
  114. INSERT INTO `role` VALUES (3, '厨师', '能够做一手好菜');
  115. INSERT INTO `role` VALUES (4, '摄影爱好者', '拍照达人');
  116. COMMIT;
  118. SET FOREIGN_KEY_CHECKS = 1;

本节涉及的JavaBean

Person类

  1. package com.lff.beans;
  3. import java.util.List;
  5. public class Person {
  6.     private Integer id;
  7.     private String name;
  8.     private Integer age;
  9.     private IdCard idCard;
  10.     private List<BankCard> bankCards;
  11.     private List<Role> roles;
  13.     public Integer getId() {
  14.         return id;
  15.     }
  17.     public void setId(Integer id) {
  18.         this.id = id;
  19.     }
  21.     public String getName() {
  22.         return name;
  23.     }
  25.     public void setName(String name) {
  26.         this.name = name;
  27.     }
  29.     public Integer getAge() {
  30.         return age;
  31.     }
  33.     public void setAge(Integer age) {
  34.         this.age = age;
  35.     }
  37.     public IdCard getIdCard() {
  38.         return idCard;
  39.     }
  41.     public void setIdCard(IdCard idCard) {
  42.         this.idCard = idCard;
  43.     }
  45.     public List<BankCard> getBankCards() {
  46.         return bankCards;
  47.     }
  49.     public void setBankCards(List<BankCard> bankCards) {
  50.         this.bankCards = bankCards;
  51.     }
  53.     public List<Role> getRoles() {
  54.         return roles;
  55.     }
  57.     public void setRoles(List<Role> roles) {
  58.         this.roles = roles;
  59.     }
  60. }

银行卡类

  1. package com.lff.beans;
  3. import java.math.BigDecimal;
  5. public class BankCard {
  6.     private Integer id;
  7.     private String no;
  8.     private BigDecimal amount;
  9.     private Person person;
  11.     public Integer getId() {
  12.         return id;
  13.     }
  15.     public void setId(Integer id) {
  16.         this.id = id;
  17.     }
  19.     public String getNo() {
  20.         return no;
  21.     }
  23.     public void setNo(String no) {
  24.         this.no = no;
  25.     }
  27.     public BigDecimal getAmount() {
  28.         return amount;
  29.     }
  31.     public void setAmount(BigDecimal amount) {
  32.         this.amount = amount;
  33.     }
  35.     public Person getPerson() {
  36.         return person;
  37.     }
  39.     public void setPerson(Person person) {
  40.         this.person = person;
  41.     }
  42. }

身份证类

  1. package com.lff.beans;
  3. public class IdCard {
  4.     private Integer id;
  5.     private String no;
  6.     private String address;
  7.     private Person person;
  9.     public Integer getId() {
  10.         return id;
  11.     }
  13.     public void setId(Integer id) {
  14.         this.id = id;
  15.     }
  17.     public String getNo() {
  18.         return no;
  19.     }
  21.     public void setNo(String no) {
  22.         this.no = no;
  23.     }
  25.     public String getAddress() {
  26.         return address;
  27.     }
  29.     public void setAddress(String address) {
  30.         this.address = address;
  31.     }
  33.     public Person getPerson() {
  34.         return person;
  35.     }
  37.     public void setPerson(Person person) {
  38.         this.person = person;
  39.     }
  40. }

角色类

  1. package com.lff.beans;
  3. import java.util.List;
  5. public class Role {
  6.     private Integer id;
  7.     private String name;
  8.     private String duty;
  9.     private List<Person> persons;
  11.     public Integer getId() {
  12.         return id;
  13.     }
  15.     public void setId(Integer id) {
  16.         this.id = id;
  17.     }
  19.     public String getName() {
  20.         return name;
  21.     }
  23.     public void setName(String name) {
  24.         this.name = name;
  25.     }
  27.     public String getDuty() {
  28.         return duty;
  29.     }
  31.     public void setDuty(String duty) {
  32.         this.duty = duty;
  33.     }
  35.     public List<Person> getPersons() {
  36.         return persons;
  37.     }
  39.     public void setPersons(List<Person> persons) {
  40.         this.persons = persons;
  41.     }
  42. }