测本篇文章将简单说明联查标签的使用以及分页
联查涉及到两个标签分别为association标签(一对一)和collection标签(一对多)
1. association标签
下面代码是查询地址并根据地址里的用户外键联查出数据,地址对用户是一对一
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AddressVo {
private Integer id;
private String addr;
private String phone;
private String postcode;
private Integer userId;
private User user;
}
<resultMap id="selectAAU" type="addressVo" autoMapping="true">
// id标签是主键标签
<id column="id" property="id"></id>
// result标签是非主键标签
<result column="user_id" property="userId"></result>
<association property="user" column="user_id" javaType="user" autoMapping="true">
<id column="uid" property="id"></id>
</association>
</resultMap>
association中的属性及其意思
property 为实体类addressVo类中的用户对象名
column 相当于联合条件on后面的主表address的字段
javaType java的类型,这里写user,不写全类名是因为起了别名
autoMapping 自动装配注入
2. collection标签
一个用户对应多个地址,所以是一对多的关系,用collection标签
<resultMap id="selectUAA" type="userVo" autoMapping="true">
<id column="id" property="id"></id>
<result column="create_time" property="createTime"></result>
<result column="is_delete" property="isDelete"></result>
<collection property="addresses" column="id" javaType="list" ofType="address" autoMapping="true">
<id column="aid" property="id"></id>
<result column="user_id" property="userId"></result>
</collection>
</resultMap>
collection标签属性与association标签差不多,ofType是集合中每条数据的类型
3. 多条件查询分页
有两种方式,一种是手动分页,一种是导入依赖自动分页
3.1 手动分页
<!-- 分页查询用户和地址-->
<select id="userAndAddrPaging" parameterType="page" resultMap="pagingUser">
select u.*, a.*, a.id as aid
from (select id as uid, account, name, password, create_time, is_delete from user limit #{startNum},#{pageSize}) as u
left join address a on uid = a.user_id
</select>
<resultMap id="pagingUser" type="userVo" autoMapping="true">
<id column="uid" property="id"></id>
<result column="create_time" property="createTime"></result>
<result column="is_delete" property="isDelete"></result>
<collection property="addresses" ofType="address" autoMapping="true">
<id column="aid" property="id"></id>
<id column="user_id" property="userId"></id>
</collection>
</resultMap>
//测试类代码
@Test
public void userPaging() {
IUserMapper mapper = session.getMapper(IUserMapper.class);
Integer pageNum = 2;
Integer pageSize = 4;
Page page = new Page(pageNum, pageSize);
page.getStart();
List<UserVo> userVos = mapper.userAndAddrPaging(page);
System.out.println("总人数" + mapper.countUser());
userVos.stream().forEach(a -> System.out.println(a));
}
3.2 自动分页
添加依赖
<!--分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
</dependencies>
// 自动分页 地址 测试
@Test
public void addrPagingAuto() {
Address address = new Address();
// 下面代码后面必须紧跟着sql语句
PageHelper.startPage(2, 3);
List<Address> addresses = session.getMapper(IAddressMapper.class).selectCondition(address);
// 获取对象,里面有很多好用属性,比如count总数,查询的结果list
PageInfo<Address> pageInfo = PageInfo.of(addresses);
System.out.println(pageInfo);
}
补充:模糊查询用concat函数拼接,比如concat('%',#{name},'%')
看不懂的可以来下面链接中再仔细看看