玩“公主焊接”，顺便学习学习数据库关系映射与Spring Boot中MyBatis（SSM框架）的级联操作（bushi）

-- 公会
drop table if exists `guild`;
create table `guild`
(
   `id`   int         not null,
   `name` varchar(16) not null unique,
   primary key (`id`)
) engine = InnoDB
  default charset = utf8mb4;
-- 武器
drop table if exists `weapon`;
create table `weapon`
(
   `id`   int         not null,
   `name` varchar(16) not null unique,
   primary key (`id`)
) engine = InnoDB
  default charset = utf8mb4;
-- 角色
drop table if exists `character`;
create table `character`
(
   `id`       int         not null,
   `name`     varchar(16) not null unique,
   `type`     varchar(8)  not null,
   -- 这张表中，guild_id是外键
   `guild_id` int         not null,
   primary key (`id`),
   -- 指定外键建立关系
   foreign key (`guild_id`) references `guild` (`id`) on delete cascade on update cascade
) engine = InnoDB
  default charset = utf8mb4;
-- 武器-角色关系表
drop table if exists `weapon_character`;
create table `weapon_character`
(
   -- 这张表中，character_id、weapon_id都是外键
   `character_id` int not null,
   `weapon_id`    int not null,
   primary key (`character_id`, `weapon_id`),
   -- 指定外键建立联系
   foreign key (`character_id`) references `character` (`id`) on delete cascade on update cascade,
   foreign key (`weapon_id`) references `weapon` (`id`) on delete cascade on update cascade
) engine = InnoDB
  default charset = utf8mb4;
-- 初始化数据
insert into `guild`
values (0, '小小甜心'),
      (1, '恶魔伪王国军'),
      (2, '自卫团');
insert into `character`
values (0, '镜华', '后卫法师输出', 0),
      (1, '美美', '中卫物理输出', 0),
      (2, '未奏希', '前卫物理辅助', 0),
      (3, '宫子', '前卫物理坦克', 1),
      (4, '茜里', '中卫法师辅助', 1),
      (5, '伊莉亚', '中卫法师输出', 1),
      (6, '真步', '后卫法师辅助', 2),
      (7, '真琴', '前卫物理输出', 2),
      (8, '香澄', '后卫法师辅助', 2);
insert into `weapon`
values (0, '牺牲月渊杖'),
      (1, '细冰姬的蝴蝶结'),
      (2, '星域天球杖'),
      (3, '水之支配者神凑剑'),
      (4, '焰火牡丹花簪'),
      (5, '毁灭之伤冥神枪'),
      (6, '海龙神的发饰'),
      (7, '岚神风暴护手');
insert into `weapon_character`
values (0, 0),
      (0, 1),
      (1, 3),
      (1, 4),
      (2, 6),
      (2, 7),
      (3, 5),
      (3, 6),
      (4, 1),
      (4, 2),
      (5, 0),
      (5, 1),
      (6, 1),
      (6, 2),
      (7, 3),
      (7, 4),
      (8, 1),
      (8, 2);

-- 表a
create table `a` (
    `id` int unsigned auto_increment,
    ...,
    primary key (`id`)
)...;
-- 表b
create table `b` (
    `id` int unsigned auto_increment,
    `a_id` int unsigned not null, -- 注意这里
    ...,
    foreign key (`a_id`) references `a` (`id`) on delete cascade on update cascade,
    ...
)...;

-- 把角色表中的公会id字段设为外键并与公会表主键关联
alter table `character` add foreign key (`guild_id`) references `guild` (`id`) on delete cascade on update cascade;

select `guild`.*
from `guild`
       left join `character` on `guild`.id = `character`.guild_id
where `character`.name = '宫子';

select `character`.*
from `character`
       left join `guild` on `character`.guild_id = `guild`.id
where `guild`.name = '小小甜心';

select `weapon`.*
from `weapon`
       left join `weapon_character` on `weapon`.id = `weapon_character`.weapon_id
       left join `character` on `weapon_character`.character_id = `character`.id
where `character`.name = '镜华';

select `character`.*
from `character`
       left join `weapon_character` on `character`.id = `weapon_character`.character_id
       left join `weapon` on `weapon_character`.weapon_id = `weapon`.id
where `weapon`.name = '细冰姬的蝴蝶结';

<resultMap id="guildResultMap" type="com.example.relationmapping.dataobject.Guild">
   <id column="id" property="id"/>
   <result column="name" property="name"/>
</resultMap>
<select id="getById" resultMap="guildResultMap">
   select *
   from `guild`
   where id = #{id}
</select>

<resultMap id="characterResultMap" type="com.example.relationmapping.dataobject.Character">
   <id column="id" property="id"/>
   <result column="name" property="name"/>
   <result column="type" property="type"/>
   <association property="guild" select="com.example.relationmapping.dao.GuildDAO.getById" column="guild_id" fetchType="lazy"/>
   <collection property="weapons" ofType="com.example.relationmapping.dataobject.Weapon" fetchType="lazy">
      <id column="weapon_id" property="id"/>
      <result column="weapon_name" property="name"/>
   </collection>
</resultMap>
<select id="getByName" resultMap="characterResultMap">
   select `character`.*, `weapon`.id as weapon_id, `weapon`.name as weapon_name
   from `character`
          left join `weapon_character` on `character`.id = `weapon_character`.character_id
          left join `weapon` on `weapon_character`.weapon_id = `weapon`.id
   where `character`.name = #{name}
</select>

/**
 * 获取角色信息
 *
 * @param name 角色名
 */
private void getCharacterInfo(String name) {
   Character xcw = characterDAO.getByName(name);
   System.out.println("角色名：" + xcw.getName());
   System.out.println("角色类型：" + xcw.getType());
   System.out.println("角色公会：" + xcw.getGuild().getName());
   System.out.println("角色武器：");
   for (Weapon weapon : xcw.getWeapons()) {
      System.out.println(" - " + weapon.getName());
   }
}
/**
 * 获取武器信息
 *
 * @param name 武器名
 */
private void getWeaponInfo(String name) {
   Weapon weapon = weaponDAO.getByName(name);
   System.out.println("武器名：" + weapon.getName());
   System.out.println("使用该武器角色：");
   for (Character character : weapon.getCharacters()) {
      System.out.println(" - " + character.getName() + " " + character.getType() + " " + character.getGuild().getName());
   }
}
@Test
void contextLoads() {
   getCharacterInfo("真步");
   getWeaponInfo("水之支配者神凑剑");
}