文章目录
1 准备环境
- 用户表
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL COMMENT '用户名',
`password` varchar(60) NOT NULL COMMENT '密码,加密存储',
`phone` varchar(11) DEFAULT NULL COMMENT '注册手机号',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='用户表';
- 收货地址表, 一个用户可以有多个收货地址
CREATE TABLE `tb_address` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`province` varchar(32) NOT NULL COMMENT '省',
`city` varchar(60) NOT NULL COMMENT '市',
`detail` varchar(11) DEFAULT NULL COMMENT '详细地址',
`user_id` bigint(20) NOT NULL ,
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='用户表';
- 实体类
@Data
@ToString
public class TbAddress implements Serializable{
private Long id;
private String province;
private String city;
private String detail;
private String createTime;
private String updateTime;
// 关联的用户信息
private TbUser user;
}
@Data
@ToString
public class TbUser implements Serializable {
private Long id;
private String username;
private String password;
private String phone;
private String createTime;
private String updateTime;
// 用户关联的地址
private List<TbAddress> addresses;
}
2 多对一的查询
比如:查询地址的时候查询对应的用户信息
方式一:级联属性封装
- 定义接口:
/**
* 根据地址id查询地址
*/
TbAddress selectById(@Param("id") Long id);
- 书写mapper,核心就是sql语句 + 结果映射
- sql
<!--这些信息肯定是无法直接映射到实体,只能通过自定义映射去做了--> <select id="selectById" resultMap="addressMapper"> select a.id, detail, user_id, username, u.id u_id, `password`, phone from tb_address a LEFT join tb_user u on a.user_id =u.id where a.id = #{id} </select>
- 结果映射
<resultMap id="addressMapper" type="study.wyy.mybatis.sqlmapper.model.TbAddress">
<id column="id" property="id"></id>
<result column="province" property="province"></result>
<result column="city" property="city"></result>
<result column="detail" property="detail"></result>
<result column="username" property="user.username"></result>
<!--剩下的这几个属性,都是在TbAddress的user属性中,所以user.id这样取值-->
<result column="u_id" property="user.id"></result>
<result column="password" property="user.password"></result>
<result column="phone" property="user.phone"></result>
</resultMap>
测试:
@Test
public void test01() {
TbAddress address = addressMapper.selectById(1L);
System.out.println(address);
}
输出:
TbAddress(id=1, province=湖北, city=武汉, detail=武汉市武昌区蛇山西山坡, createTime=null, updateTime=null, user=TbUser(id=1, username=kobe, password=123456, phone=13100001111, createTime=null, updateTime=null, addresses=null))
方式二:使用association标签定义关联的单个对象的封装规则
<resultMap id="addressMapper2" type="study.wyy.mybatis.sqlmapper.model.TbAddress">
<id column="id" property="id"></id>
<result column="province" property="province"></result>
<result column="city" property="city"></result>
<result column="detail" property="detail"></result>
<!--
property: TbAddress实体中关联用户实体的属性
javaType: 关联实体的类型
-->
<association property="user" javaType="study.wyy.mybatis.sqlmapper.model.TbUser">
<!--
就不需要通过user.的方式,这里配置的就是user属性的映射关系
-->
<result column="username" property="username"></result>
<result column="u_id" property="id"></result>
<result column="password" property="password"></result>
<result column="phone" property="phone"></result>
</association>
</resultMap>
方式三:使用association标签实现分步查询
分步查询很简单:比如说要查询当前地址,并且查出对应的用户信息:
- 根据地址ID查询地址
- 根据地址表的user_id查询用户表
<resultMap id="addressMapper3" type="study.wyy.mybatis.sqlmapper.model.TbAddress">
<id column="id" property="id"></id>
<result column="province" property="province"></result>
<result column="city" property="city"></result>
<result column="detail" property="detail"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
<!--
select: 表明当前属性是调用select指定的方法
select的取值规则:namespace + id
column: 指定那一列的值传给这个查询方法,一般就是关联字段。比如这里就是user_id,
-->
<association property="user" select="study.wyy.mybatis.sqlmapper.mapper.TbUserMapper.selectById"
column="user_id"></association>
</resultMap>
<select id="selectById2" resultMap="addressMapper3">
select
*
from
tb_address a
where
a.id = #{id}
</select>
<mapper namespace="study.wyy.mybatis.sqlmapper.mapper.TbUserMapper">
<select id="selectById" resultType="study.wyy.mybatis.sqlmapper.model.TbUser" parameterType="long">
select
*
from
tb_user
where
id = #{id}
</select>
</mapper>
测试
@Test
public void test02() {
TbAddress address = addressMapper.selectById2(1L);
address.getUser();
}
日志打印会发现打印了两条sql
select * from tb_address a where a.id = ?
select * from tb_user where id = ?
分步查询的延时策略
我们每次查询Address对象的时候,都将用户信息一起查询出来。现在期望用户信息在我们使用的时候再去查询;分步查询的基础之上加上两个配置:
<settings>
<!--延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。 特定关联关系中可通过设置 fetchType 属性来覆盖该项的开关状态。-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--开启时,任一方法的调用都会加载该对象的所有延迟加载属性。 否则,每个延迟加载属性会按需加载(参考 lazyLoadTriggerMethods)。-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
@Test
public void test02() {
TbAddress address = addressMapper.selectById2(1L);
System.out.println("get /user");
address.getUser();
}
20:28:46.007 [main] DEBUG study.wyy.mybatis.sqlmapper.mapper.TbAddressMapper.selectById2 - ==> Preparing: select * from tb_address a where a.id = ?
20:28:46.153 [main] DEBUG study.wyy.mybatis.sqlmapper.mapper.TbAddressMapper.selectById2 - ==> Parameters: 1(Long)
20:28:46.283 [main] DEBUG study.wyy.mybatis.sqlmapper.mapper.TbAddressMapper.selectById2 - <== Total: 1
get /user 在我调用getUser方法之后,才发出的查询用户的sql
20:28:46.284 [main] DEBUG study.wyy.mybatis.sqlmapper.mapper.TbUserMapper.selectById - ==> Preparing: select * from tb_user where id = ?
20:28:46.286 [main] DEBUG study.wyy.mybatis.sqlmapper.mapper.TbUserMapper.selectById - ==> Parameters: 1(Long)
20:28:46.301 [main] DEBUG study.wyy.mybatis.sqlmapper.mapper.TbUserMapper.selectById - <== Total: 1
3 一对多的查询
查询用户的时候,将该用户的收货地址列表查出来
定义mapper接口
TbUser selectByIdWithAddress(@Param("id") Long id);
方式一
使用collection标签配置集合属性的封装
<resultMap id="userMap2" type="study.wyy.mybatis.sqlmapper.model.TbUser">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
<!--
TbUser 的addresses属性对应的就是地址表,可能是多个地址,是个集合
ofType:指定集合中元素的类型
-->
<collection property="addresses" ofType="study.wyy.mybatis.sqlmapper.model.TbAddress">
<id property="id" column="a_id"></id>
<result column="city" property="city"></result>
<result column="detail" property="detail"></result>
<result column="province" property="province"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</collection>
</resultMap>
<select id="selectByIdWithAddress" resultMap="userMap2">
select
*,
a.id a_id
from
tb_user u
LEFT join
tb_address a
on
a.user_id =u.id
where
u.id = 1
</select>
@Test
public void test03() {
TbUser tbUser = tbUserMapper.selectByIdWithAddress(1L);
System.out.println(tbUser);
}
方式二:分步查询
- 先查用户
- 再查地址
<resultMap id="userMap3" type="study.wyy.mybatis.sqlmapper.model.TbUser">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
<!--
TbUser 的addresses属性对应的就是地址表,可能是多个地址,是个集合
select的取值规则:namespace + id
column: 指定那一列的值传给这个查询方法,一般就是关联字段。比如这里就是根据user表的id查询地址表了
-->
<collection property="addresses" select="study.wyy.mybatis.sqlmapper.mapper.TbAddressMapper.selectByUserId"
column="id">
<id property="id" column="a_id"></id>
<result column="city" property="city"></result>
<result column="detail" property="detail"></result>
<result column="province" property="province"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</collection>
</resultMap>
<select id="selectByIdWithAddress2" resultMap="userMap3">
select
*
from
tb_user u
where
u.id = #{id}
</select>
再去定义:study.wyy.mybatis.sqlmapper.mapper.TbAddressMapper.selectByUserId
<mapper namespace="study.wyy.mybatis.sqlmapper.mapper.TbAddressMapper">
<select id="selectByUserId" resultType="study.wyy.mybatis.sqlmapper.model.TbAddress">
select
*
from
tb_address a
where
a.user_id = #{userId}
</select>
</mapper>
4 扩展
前面的分步查询的时候,column: 指定那一列的值传给这个查询方法,一般就是关联字段。mybatis也是可以传递多个
<resultMap id="userMap3" type="study.wyy.mybatis.sqlmapper.model.TbUser">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
<!--
TbUser 的addresses属性对应的就是地址表,可能是多个地址,是个集合
select的取值规则:namespace + id
column: 比如说这里要传id:
userId是因为i查询地址表的时候,#{userId}这样取的值
-->
<collection property="addresses" select="study.wyy.mybatis.sqlmapper.mapper.TbAddressMapper.selectByUserId"
column="{userId=id}">
<id property="id" column="a_id"></id>
<result column="city" property="city"></result>
<result column="detail" property="detail"></result>
<result column="province" property="province"></result>
<result column="create_time" property="createTime"></result>
<result column="update_time" property="updateTime"></result>
</collection>
</resultMap>