方法一、多表查询(一次性查询)
跟一对一配置差不多
一个用户拥有多种角色
创建数据脚本
# 用户表
create table user(
id int(11) primary key auto_increment,
name varchar(20),
age int(2)
)engine=InnoDB default charset=utf8;
# 角色表
create table role(
id int(11) primary key auto_increment,
name varchar(20),
user_id int(11)
)engine=InnoDB default charset=utf8;
创建实体类
@Data
public class User implements Serializable {
private Integer id;
private String name;
private Integer age;
private IdCard idCard;
// 一个用户拥有多种角色
private List<Role> roleList;
}
@Data
public class Role implements Serializable {
private Integer id;
private String name;
}
映射文件配置
<?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">
<!-- 开启二级缓存 -->
<cache />
<!-- 结果集映射 -->
<resultMap id="UserMap" type="User">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age" />
</resultMap>
<!-- 创建sql片段,通过include引用-->
<sql id="BaseColumnList">
id, name, age
</sql>
<resultMap id="UserAndRoleMap" type="User" extends="UserMap">
<!--
collection:关联映射
property: 映射的属性值
javaType: 映射的类型
ofType: 映射实体bean类型,这里使用类型别名
columnPrefix: 指定关联表列前缀。好处:避免列同名出现数据出错
-->
<collection property="roleList" javaType="java.util.ArrayList" ofType="Role" columnPrefix="role_">
<id column="id" property="id"/>
<result column="name" property="name"/>
</collection>
</resultMap>
<select id="selectOne2Many" resultMap="UserAndRoleMap">
select
u.id,
u.name,
u.age,
r.id as role_id,
r.name as role_name
from user as u,role as r
where u.id=r.user_id
</select>
</mapper>
配置文件参考 mybatis-config.xml
方法二、使用关联的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">
<!-- 开启二级缓存 -->
<cache />
<!-- 结果集映射 -->
<resultMap id="UserMap" type="User">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age" />
</resultMap>
<!-- 创建sql片段,通过include引用-->
<sql id="BaseColumnList">
id, name, age
</sql>
<resultMap id="UserAndRoleMap" type="User" extends="UserMap">
<!--
collection:关联映射
property: 映射的属性值
javaType: 映射的类型
ofType: 映射实体bean类型,这里使用类型别名
column: 指定主查询中列的结果作为嵌套查询的参数
多参数时在column中用"{}"将参数包起来,=左侧的为mapper中定义的@Param,=右侧为主表列名
fetchType: lazy 延迟加载,用到该对象时才发送sql查询
默认值是eager,将发出sql,查找关联数据
-->
<collection property="roleList" fetchType="lazy" javaType="java.util.ArrayList" ofType="Role"
column="{userId=id}" select="com.test.quickstart.mapper.RoleMapper.selectByUserId"/>
</resultMap>
<select id="selectOne2Many2" resultMap="UserAndRoleMap">
select * from user;
</select>
</mapper>
RoleMapper.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.RoleMapper">
<!-- 结果集映射 -->
<resultMap id="RoleMap" type="Role">
<id column="id" property="id"/>
<result column="name" property="name"/>
</resultMap>
<select id="selectByUserId" resultMap="RoleMap">
select * from role where user_id = #{userId}
</select>
</mapper>
UserMapper.java
@Mapper
public interface UserMapper {
User selectOne2Many(@Param("id") Integer id);
User selectOne2Many2(@Param("id") Integer id);
}
RoleMapper.java
@Mapper
public interface RoleMapper {
List<Role> selectByUserId(@Param("userId") Integer userId);
}
测试
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 testOne2Many1() {
SqlSession session = sessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.selectOne2Many(2);
System.out.println(user);
}
/**
* 测试一对多
*/
@Test
public void testOne2Many2() {
SqlSession session = sessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.selectOne2Many2(2);
// 使用关联对象时才发出sql查询
System.out.println(user.getRoleList());
}
}