Mybatis的resultMap使用

一对一

建表

create table t_person(
  id int primary key AUTO_INCREMENT,
  name VARCHAR(50),
  age SMALLINT
)DEFAULT CHARSET = utf8 ENGINE =innodb;

create table t_passport(
  id int primary key AUTO_INCREMENT,
  note VARCHAR(50),
  create_time DATE,
  person_id int UNIQUE,
  FOREIGN KEY(person_id) REFERENCES t_person(id)
)DEFAULT CHARSET = utf8 ENGINE =innodb;

insert into t_person(name,age) values("zhangsan",18);
insert into t_person(name,age) values("lisi",19);
insert into t_passport(note,create_time,person_id) values("pass1",'2019-05-07',1);
insert into t_passport(note,create_time,person_id) values("pass2",'2019-05-07',2);

查询语句
注意：映射的时候使用association ，类型使用javaType

<mapper namespace="com.qf.dao.PersonDao">
    <resultMap id="person_passport" type="com.qf.pojo.Person" autoMapping="true">
        <id property="id" column="id"></id>
        <association property="passport" javaType="Passport" autoMapping="true">
            <id column="tid" property="id"></id>
        </association>
    </resultMap>

    <select id="queryPersonAndPassport" resultMap="person_passport">
       SELECT n.*,t.`id` tid,t.`create_time`,t.`note` FROM t_person n LEFT JOIN t_passport t ON n.`id` = t.`person_id`
       where n.id = #{id};
    </select>
</mapper>

一对多，多对多

建表

create table t_user( # 用户表 
  id int primary key AUTO_INCREMENT,
  name VARCHAR(50),
  gender char(1),
  regist_time DATE
)DEFAULT CHARSET = utf8 ENGINE =innodb;

create table t_order( # 订单表
  id int primary key AUTO_INCREMENT,
  price VARCHAR(50),
  note VARCHAR(50),
  create_time DATE,
  user_id int,
  FOREIGN KEY(user_id) REFERENCES t_user(id)
)DEFAULT CHARSET = utf8 ENGINE =innodb;
insert into t_user(name,gender,regist_time) values('zhangsan','1','2019-12-12');
insert into t_user(name,gender,regist_time) values('lisi','0','2019-12-11');
insert into t_order(price,note,create_time,user_id) values(3000.55,'哈哈','2019-12-12',1);
insert into t_order(price,note,create_time,user_id) values(600.55,'哈哈2','2019-12-12',1);
insert into t_order(price,note,create_time,user_id) values(900.55,'呵呵2','2019-12-12',2);
insert into t_order(price,note,create_time,user_id) values(4000.55,'呵呵2','2019-12-12',2);

查询语句
注意：映射的时候要使用collection，类型要用ofType

<mapper namespace="com.qf.dao.UserDao">
    <resultMap id="user_order" type="User" autoMapping="true">
        <id column="id" property="id"></id>
        <collection property="orders" ofType="Order" autoMapping="true">
            <id column="oid" property="id"></id>
        </collection>
    </resultMap>

    <select id="queryOneUserAndOrders" resultType="com.qf.pojo.User" resultMap="user_order">
        select u.id, u.name, u.gender, u.regist_time registTime, o.id oid, o.note, o.create_time, o.price
        from t_user u
        left join t_order o on u.id = o.user_id
        where u.id = #{id}
    </select>
</mapper>