方法一、多表查询(一次性查询)
一个用户只有一个身份证,查询用户信息时,级联查出身份证信息
创建数据脚本
# 用户表
create table user(
id int(11) primary key auto_increment,
name varchar(20),
age int(2)
)engine=InnoDB default charset=utf8;
# 身份证
create table idcard(
id int(11) primary key auto_increment,
number varchar(18),
user_id int(11)
)engine=InnoDB default charset=utf8;
创建实体类
@Data
public class User {
private Integer id;
private String name;
private Integer age;
// 每个人有一个身份证
private IdCard idCard;
}
@Data
public class IdCard {
private Integer id;
private String number;
private Integer userId;
}
对象关系映射
<?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.test.quickstart.mapper.UserMapper">
<!-- 结果集映射 -->
<resultMap id="UserMap" type="User">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age" />
</resultMap>
<resultMap id="UserAndIdCardMap" type="User" extends="UserMap">
<!--
association:关联表映射
property: 映射的属性值
javaType: 映射的类型,这里使用类型别名
columnPrefix: 指定关联表列前缀。好处:避免列同名出现数据出错
-->
<association property="idCard" javaType="IdCard" columnPrefix="card_">
<id column="id" property="id"/>
<result column="number" property="number"/>
<result column="user_id" property="userId"/>
</association>
</resultMap>
<select id="selectOne2One" resultMap="UserAndIdCardMap">
select
u.id,
u.name,
u.age,
i.id as card_id,
i.number as card_number,
i.user_id
from user as u,idcard as i
where u.id=i.user_id and u.id=#{id}
</select>
</mapper>
配置文件参考 mybatis-config.xml
测试
/**
* @author han
* 2020/4/22 16:33
*/
public class Demo01Test {
private SqlSessionFactory sessionFactory;
@Before
public void before() throws IOException {
// 读取配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
sessionFactory = new SqlSessionFactoryBuilder().build(is);
}
/**
* 测试一对一关联查询
*/
@Test
public void testSelectOne2One() {
SqlSession session = sessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.selectOne2One(1);
System.out.println(user);
session.close();
}
}
方法二、使用关联的XXMapper对象方法嵌套查询(多次查询)
UserMapper.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.test.quickstart.mapper.UserMapper">
<resultMap id="UserAndIdCardMap2" type="User" extends="UserMap">
<!--
association:关联查询
property: 映射的属性值
javaType: 映射的类型,这里使用类型别名
columnPrefix: 指定关联表列前缀。好处:避免列同名出现数据出错
select: 嵌套查询,使用关联表的Mapper对象方法查询
column: 指定主查询中列的结果作为嵌套查询的参数
多参数时在column中用"{}"将参数包起来,=左侧的为mapper中定义的@Param,=右侧为主表列名
如:{userId=id,name=name}
fetchType: lazy 延迟加载,用到该对象时才发送sql查询
默认值是eager,将发出sql,查找关联数据
-->
<association property="idCard" javaType="IdCard"
column="{userId=id}" fetchType="lazy"
select="com.test.quickstart.mapper.IdCardMapper.selectByUserId" />
</resultMap>
<select id="selectOne2One2" resultMap="UserAndIdCardMap2">
select * from user where id=#{id}
</select>
</mapper>
IdCardMapper.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.test.quickstart.mapper.IdCardMapper">
<!-- 结果集映射 -->
<resultMap id="IdCardMap" type="IdCard">
<id column="id" property="id"/>
<result column="number" property="number"/>
<result column="user_id" property="userId" />
</resultMap>
<select id="selectByUserId" resultMap="IdCardMap">
select * from idcard where user_id=#{userId}
</select>
</mapper>
UserMapper.java
@Mapper
public interface UserMapper {
User selectOne2One2(@Param("id") Integer id);
}
IdCardMapper.java
@Mapper
public interface IdCardMapper {
IdCard selectByUserId(@Param("userId") Integer userId);
}