springboot mybatis 一对一级联查询

---

mybatis 一对一级联查询

 

***********************

示例

 

person：id、name、age、card_id

card：id、card_no

一个person对应一个card，一张card对应一个person

 

*******************

pojo 层

 

Person

@Data
@EqualsAndHashCode(callSuper = false)
public class Person extends Model<Person> {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    private String name;

    private Integer age;

    private Integer cardId;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }

}

 

PersonMap

@Data
public class PersonMap {

    private Integer id;
    private String name;
    private Integer age;

    private Card card;
}

 

 

Card

@Data
@EqualsAndHashCode(callSuper = false)
public class Card extends Model<Card> {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    private Integer cardNo;


    @Override
    protected Serializable pkVal() {
        return this.id;
    }

}

 

CardMap

@Data
public class CardMap {

    private Integer id;
    private Integer cardNo;

    private Person person;
}

 

*******************

dao 层

 

PersonMapper

public interface PersonMapper extends BaseMapper<Person> {

    Person getByCardId(Integer cardId);
    List<PersonMap> getAllPersonMap();
}

 

CardMapper

public interface CardMapper extends BaseMapper<Card> {

    Card getCardById(Integer id);
    List<CardMap> getAllCardMap();
}

 

*******************

xml文件

 

personMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.PersonMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.pojo.Person">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <result column="card_id" property="cardId" />
    </resultMap>

    <resultMap id="BaseResultMap2" type="personMap">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <association column="card_id" property="card" select="com.example.demo.dao.CardMapper.getCardById"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, age, card_id
    </sql>

    <select id="getByCardId" resultMap="BaseResultMap">
        select * from person where card_id = #{cardId}
    </select>

    <select id="getAllPersonMap" resultMap="BaseResultMap2">
        select * from person;
    </select>
</mapper>

 

cardmapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.CardMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.pojo.Card">
        <id column="id" property="id" />
        <result column="card_no" property="cardNo" />
    </resultMap>

    <resultMap id="BaseResultMap2" type="cardMap">
        <id property="id" column="id"/>
        <result property="cardNo" column="card_no"/>
        <association property="person" column="id"
                     select="com.example.demo.dao.PersonMapper.getByCardId"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, card_no
    </sql>

    <select id="getCardById" resultMap="BaseResultMap">
        select * from card where id = #{id};
    </select>

    <select id="getAllCardMap" resultMap="BaseResultMap2">
        select id,card_no from card;
    </select>
</mapper>

 

*******************

controller层

 

PersonController

@RestController
@RequestMapping("/person")
public class PersonController {

    @Resource
    private PersonMapper personMapper;

    @RequestMapping("/getAll")
    public List<PersonMap> getAll(){
        return personMapper.getAllPersonMap();
    }
}

 

CardController

@RestController
@RequestMapping("/card")
public class CardController {

    @Resource
    private CardMapper cardMapper;

    @RequestMapping("/getAll")
    public List<CardMap> getAll(){
        return cardMapper.getAllCardMap();
    }

}

 

 

***********************

使用测试

 

localhost:8080/person/getAll

[
    {
        "id": 1,
        "name": "瓜田李下",
        "age": 23,
        "card": {
            "id": 1,
            "cardNo": 22
        }
    },
    {
        "id": 2,
        "name": "瓜田李下2",
        "age": 22,
        "card": {
            "id": 2,
            "cardNo": 20
        }
    }
]

              

 

localhost:8080/card/getAll

[
    {
        "id": 1,
        "cardNo": 22,
        "person": {
            "id": 1,
            "name": "瓜田李下",
            "age": 23,
            "cardId": 1
        }
    },
    {
        "id": 2,
        "cardNo": 20,
        "person": {
            "id": 2,
            "name": "瓜田李下2",
            "age": 22,
            "cardId": 2
        }
    }
]