mybatis关系映射示例

mybatis关系映射

sql

create table emp
(
    `id`      int auto_increment primary key,
    `name`    varchar(255) unique not null,
    `age`     int                 not null,
    `sex`     varchar(8)          not null,
    `address` varchar(255)        not null
) engine = innodb
  default character set = utf8;

create table workCard
(
    `id`       int auto_increment primary key,
    `realName` varchar(255) unique not null,
    `tel`      varchar(30)         not null,
    `position` varchar(255)        not null,
    `emp_id`   int,
    constraint fk_dpt_id foreign key (emp_id) references emp (id)
        on delete cascade
) engine = innodb
  default character set = utf8;


insert into emp
values (null, '陈平安', 23, '男', '骊珠洞天泥瓶巷');
insert into emp
values (null, '宁姚', 22, '女', '剑气长城');
insert into emp
values (null, '张山峰', 25, '男', '中土神州龙虎山');
insert into emp
values (null, '董三更', 40, '男', '剑气长城');
insert into emp
values (null, '陈清都', 60, '男', '剑气长城');

insert into workCard
values (null, '陈平安', '123123123', '职员', 1);
insert into workCard
values (null, '宁姚', '12345678', '部门经理', 2);

实体类

@Data
public class WorkCard {
    private int id;
    private String realName;
    private String tel;
    private String position;
    private Emp emp;
}

@Data
public class Emp {
    private Integer id;
    private String name;
    private int age;
    private String sex;
    private String address;
}

mapper接口

public interface EmpMapper {
     Emp findById(int id);
}

public interface WorkCardMapper {
    WorkCard findById(int id);
}

EmpMapper.xml

   <select id="findById" resultType="emp">
        select *
        from emp
        where id = #{id};
    </select>

WorkCardMapper.xml的2种写法

第一种

<resultMap id="myCard" type="workCard">
        <id column="cid" property="id"/>
        <result column="realName" property="realName"/>
        <result column="tel" property="tel"/>
        <result column="position" property="position"/>
         <association property="emp" javaType="Emp" column="emp_id">
        <id column="eid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
        </association>
    </resultMap>
     <select id="findById" resultMap="myCard">
     select
     e.id eid,name,age,sex,address,c.id cid,realName,tel,position
     from emp e inner join workCard c
     on e.id=emp_id where c.id=#{cid};
     </select>

第二种

<resultMap id="myCard" type="workCard">
        <id column="cid" property="id"/>
        <result column="realName" property="realName"/>
        <result column="tel" property="tel"/>
        <result column="position" property="position"/>
       <association property="emp" javaType="emp" column="emp_id"
                     select="com.bo.dao.EmpMapper.findById"/>
    </resultMap>
    <select id="findById" resultMap="myCard">
        select * from workCard where id=#{cid};
    </select>

emp_id是外键

mybatis默认的驼峰是关闭的，那么这种方式

<resultMap id="myCard" type="workCard">
        <id column="id" property="id"/>
        <result column="realName" property="realName"/>
        <result column="tel" property="tel"/>
        <result column="position" property="position"/>
         <association property="emp" javaType="Emp" column="emp_id">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
        </association>
    </resultMap>
    <select id="findById" resultMap="myCard">
        select * from workCard where id=#{id};
    </select>

column是数据库字段名，property是实体类名

15行的#{id}对应的是第二行的id(property)

这样查

 <resultMap id="myCard" type="workCard">
        <id column="id" property="id"/>
        <result column="realName" property="realName"/>
        <result column="tel" property="tel"/>
        <result column="position" property="position"/>
        <association property="emp" javaType="emp" column="emp_id"
                     select="com.bo.dao.EmpMapper.findById"/>
    </resultMap>
    <select id="findById" resultMap="myCard">
        select * from workCard where id=#{id};
    </select>

或者这样查

   <resultMap id="myCard" type="workCard">
        <id column="eid" property="id"/>
        <result column="realName" property="realName"/>
        <result column="tel" property="tel"/>
        <result column="position" property="position"/>
         <association property="emp" javaType="Emp" column="emp_id">
        <id column="cid" property="id"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
        </association>
    </resultMap>
     <select id="findById" resultMap="myCard">
     select
     e.id eid,name,age,sex,address,c.id cid,realName,tel,position
     from emp e inner join workCard c
     on e.id=emp_id where c.id=#{cid};
     </select>

2行的eid对应17行给emp别名e然后就是e.id；

然后7的cid对应的是18的cid

证明：

18行的cid改名为id，7行的cid也改为id，成功查出