主表:
附属表:
mapper 文件接口定义 :
Page<ClassSystem> pageList(ClassSystemBO classSystemBO);
mapper .xml 文件:
resultMap: 要点注意 -
1. <collection> 标签中的column="id" 一定要与主表的id的column命名相同,这是主表id作为参数出入附属表进行查询的条件
2. 主附表中都没有<id>标签,用<result>标签替换
<resultMap id="PageResultMap" type="com.anosi.cloud.entity.ClassSystem">
<result column="id" jdbcType="BIGINT" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="create_date" jdbcType="TIMESTAMP" property="createDate"/>
<result column="update_date" jdbcType="TIMESTAMP" property="updateDate"/>
<collection column="id" property="classes" ofType="map"
javaType="java.util.List" select="getClassesBySystemId">
<result column="class_id" property="id"></result>
<result column="class_name" jdbcType="VARCHAR" property="name"/>
<result column="start_time" jdbcType="TIME" property="startTime"/>
<result column="end_time" jdbcType="TIME" property="endTime"/>
<result column="day_status" jdbcType="TIME" property="statusCode"/>
<result column="order_number" property="orderNumber"/>
</collection>
</resultMap>
主表查询:
<select id="pageList" resultMap="PageResultMap">
SELECT
id ,name
FROM
class_system
WHERE delete_flag = 1
order by id
</select>
附属表查询: 要点注意 - resultType 这里既可以写map也能写JavaBean的名称,map会自动转换成JavaBean
<select id="getClassesBySystemId" parameterType="long" resultType="map">
select id, name, start_time, end_time,day_status
from classes
where delete_flg = 1 and class_system_id = #{id}
order by id
</select>
service 实现类中调用:
public PageInfo<ClassSystem> classSystemListPage(ClassSystemBO classSystemBO) {
PageHelper.startPage(classSystemBO.getPageNum(),classSystemBO.getPageSize());
Page<ClassSystem> page = classSystemMapper.pageList(classSystemBO);
PageInfo<ClassSystem> info = new PageInfo<>(page);
return info;
}
效果:
{
"success":true,
"data": - {
"total":7,
"list": - [
- {
"id":2,
"name":"班制二",
"classes": + [3]
"updateFlag":true
},
- {
"id":3,
"name":"班制一",
"classes": + [2]
"updateFlag":false
},
- {
"id":6,
"name":"全天",
"classes": - [
- {
"id":43,
"name":"全天",
"startTime":"00:00:00",
"endTime":"00:00:00"
}
],
"updateFlag":true
},
- {
"id":7,
"name":"mfs - test",
"classes": + [5]
"updateFlag":true
},
- {
"id":9,
"name":"大",
"classes": + [3]
"updateFlag":true
},
- {
"id":10,
"name":"da",
"classes": + [5]
"updateFlag":true
},
- {
"id":11,
"name":"sd",
"classes": + [4]
"updateFlag":true
}
],
"pageNum":1,
"pageSize":20,
"size":7,
"startRow":1,
"endRow":7,
"pages":1,
"prePage":0,
"nextPage":0,
"isFirstPage":true,
"isLastPage":true,
"hasPreviousPage":false,
"hasNextPage":false,
"navigatePages":8,
"navigatepageNums": - [
1
],
"navigateFirstPage":1,
"navigateLastPage":1
}