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
}
}
]