写在前面:各位看到此博客的小伙伴,如有不对的地方请及时通过私信我或者评论此博客的方式指出,以免误人子弟。多谢!
通过前面三篇学习,了解了MP基本的CURD操作和分页查询,但是到目前为止我们仅限于单表操作,而实际中通常都是多表关联查询使用的比较多,这一篇就记录一下多表的关联查询。关联查询和单表查询其实差不多,在处理一对一,一对多等的方式上也跟Mybatis一样,不太清楚的可以看下之前整理的博客springboot整合mybatis使用collection查询 一对多 多对一 多对多查询
新建Wife类与User类关联:
@Data
public class Wife {
private int wifeId;
private int userId;
private String wifeName;
}
新建UserWifeDto表示关联查询返回的数据:
@Data
public class UserWifeDto {
private Integer userId;
private String userName;
private Integer age;
private String wifeName;
}
UserMapper中新建查询方法:
@Select("select u.*,w.wife_name from t_user u left join t_wife w on u.user_id = w.user_id where u.user_id = #{userId}")
UserWifeDto selectUserWife(UserQueryModel userQueryModel);
新建测试类RelationSelectTest,添加测试方法:
@Test
public void test(){
UserQueryModel userQueryModel = new UserQueryModel();
userQueryModel.setUserId(1);
UserWifeDto userDto = userMapper.selectUserWife(userQueryModel);
System.out.println(userDto);
}
执行结果:
如上测试:UserMapper中将查询sql直接写在了方法上,其实像是这种简单的关联查询直接使用@Select注解的方式还是更简单的,当然也可以在xml中写查询语句,如下也是一样的:
UserMapper中新建查询方法:
@Test
public void test1(){
UserQueryModel userQueryModel = new UserQueryModel();
userQueryModel.setUserId(1);
UserWifeDto userDto = userMapper.selectUserWifeXml(userQueryModel);
System.out.println(userDto);
}
UserMapper.xml中:
<select id="selectUserWifeXml" parameterType="com.example.springbootmp.dto.user.UserQueryModel"
resultType="com.example.springbootmp.dto.user.UserWifeDto">
select u.*,w.wife_name from t_user u left join t_wife w on u.user_id = w.user_id where u.user_id = #{userId}
</select>
上面虽然实现了关联查询,但是只是查询了Wife中的一个属性wifeName,如果有很多字段的时候,这样还是不太方便的,接下来将使用<association>做关联查询,对于<association>和<collection>标签的使用不熟悉的小伙伴可以去Mybatis的官网查看文档,或者看之前整理的博客springboot整合mybatis使用association做关联查询 一对一查询
修改下UserWifeDto,将wifeName改为Wife对象:
@Data
public class UserWifeDto {
private Integer userId;
private String userName;
private Integer age;
// private String wifeName;
private Wife wife;
}
UserMapper中新增selectUserWifeXmlByAss方法:
UserWifeDto selectUserWifeXmlByAss(UserQueryModel userQueryModel);
UserMapper.xml中:
<resultMap id="userWifeDtoMap" type="com.example.springbootmp.dto.user.UserWifeDto">
<id property="userId" column="user_id" />
<result property="userName" column="user_name" />
<result property="age" column="age" />
<association property="wife" column="user_id" javaType="com.example.springbootmp.domain.Wife">
<id property="wifeId" column="wife_id" />
<result property="userId" column="user_id" />
<result property="wifeName" column="wife_name" />
</association>
</resultMap>
<select id="selectUserWifeXmlByAss" parameterType="com.example.springbootmp.dto.user.UserQueryModel"
resultMap="userWifeDtoMap">
select u.*,w.* from t_user u left join t_wife w on u.user_id = w.user_id
where u.user_id = #{userId}
</select>
测试类RelationSelectTest中新增测试方法:
@Test
public void test2(){
UserQueryModel userQueryModel = new UserQueryModel();
userQueryModel.setUserId(1);
UserWifeDto userDto = userMapper.selectUserWifeXmlByAss(userQueryModel);
System.out.println(userDto);
}
执行结果:
上面的查询使用了association做的关联查询,而且页面没有分页,并且查询条件只是来自单表的,接下来测试将加上分页,并且查询条件也来自多张表,顺便用下<collection>标签回顾一下它的使用,这样更接近于实际场景。
新增Order类:
@Data
@TableName(value = "t_orders")
public class Order {
private int orderId;
private int userId;
private String number;
}
UserMapper中新增测试方法selectOrdersPage:
Page<UserOrdersDto> selectOrdersPage(Page<UserOrdersDto> page, UserQueryModel userQueryModel);
UserMapper.xml中:
<resultMap id="userOrderResultMap" type="com.example.springbootmp.dto.user.UserOrdersDto">
<id property="userId" column="user_id" />
<result property="userName" column="user_name" />
<result property="age" column="age" />
<collection property="orders" column="user_id" ofType="com.example.springbootmp.domain.Order">
<id property="orderId" column="order_id" />
<result property="userId" column="user_id" />
<result property="number" column="number" />
</collection>
</resultMap>
<sql id="user_orders">
select u.*,od.* from t_user u left join t_orders od on u.user_id = od.user_id
</sql>
<select id="selectOrdersPage" resultMap="userOrderResultMap" parameterType="com.example.springbootmp.dto.user.UserQueryModel">
<include refid="user_orders"></include>
<where>
<if test="userQueryModel.userId != null">
u.user_id = #{userQueryModel.userId}
</if>
<if test="userQueryModel.number != null">
and od.number <![CDATA[!= ]]> #{userQueryModel.number}
</if>
</where>
</select>
数据库中添加测试数据:
测试类中添加测试方法:
@Test
public void testPage1(){
Page page = new Page(1,3);
UserQueryModel queryModel = new UserQueryModel();
queryModel.setUserId(1);
queryModel.setNumber("100000");
Page<UserOrdersDto> userOrdersDtoPage = userMapper.selectOrdersPage(page,queryModel);
System.out.println(userOrdersDtoPage.getRecords() + "---" + userOrdersDtoPage.getTotal());
}
执行结果:
补充:查询参数为对象,对象中包含集合属性使用<foreach>遍历
UserQueryModel查询对象中加入List类型查询参数ageList:
@Data
public class UserQueryModel {
private Integer userId;
private String userName;
private Integer age;
private String number;
private List<Integer> ageList;
}
UserMapper中添加方法:
Page<UserWifeDto> selectUsersByAges(Page page, UserQueryModel userQueryModel);
数据库中修改下测试数据:
CurdTest中增加测试方法:
@Test
public void selectUsersByAges(){
Page<UserWifeDto> page = new Page<UserWifeDto>(1,3);
List<Integer> ageList = new ArrayList<>();
ageList.add(10);
ageList.add(15);
UserQueryModel queryModel = new UserQueryModel();
queryModel.setAgeList(ageList);
Page<UserWifeDto> userPage = userMapper.selectUsersByAges(page, queryModel);
System.out.println(userPage.getPages() + "---" + userPage.getTotal() + "---" + userPage.getRecords());
}
执行结果如下: