MyBatis 查询结果的某个字段为List

原创已于 2023-07-07 14:27:30 修改 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java #mysql

于 2022-12-26 14:28:01 首次发布

本文介绍了如何在MyBatis中处理查询结果的一个字段为List的情况，包括Entity实体的定义，预先编写查询语句，设置resultMap以传递查询条件，并在Mapper.java中进行操作。同时提到了一种效率较高但可能影响维护性的写法。

Entity实体

@Data
public class TextUrbanVO {

	@TableId
	@ApiModelProperty(value = "")
	private Long id;

	@ApiModelProperty(value = "市编号")
	private Long cityId;

	@ApiModelProperty(value = "城市名称")
	private String urbanName;

	@ApiModelProperty(value = "学校列表")
	private List<TextSchool> schools;

	@ApiModelProperty(value = "医院列表")
	private List<TextHospital> hospitals;
}

@Data
public class TextHospital {

	@ApiModelProperty(value = "")
	private Long id;

	@ApiModelProperty(value = "市id")
	private Long urbanId;

	@ApiModelProperty(value = "医院名称")
	private String hospitalName;

	@ApiModelProperty(value = "人数")
	private Integer people;
}

@Data
public class TextSchool {

	@ApiModelProperty(value = "")
	private Long id;

	@ApiModelProperty(value = "市id")
	private Long urbanId;

	@ApiModelProperty(value = "学校名称")
	private String schoolName;

	@ApiModelProperty(value = "人数")
	private Integer people;
}

事先定义需要根据条件查询的语句


	<!--根据市id查询医院列表-->
	<select id="hospitalByUrbanId" resultType="io.kelvins.bsip.device.api.entity.TextHospital">
		select * from text_hospital where urban_id = #{urbanId}
	</select>
	
	<!--根据市id查询学校列表-->
	<select id="schoolByUrbanId" resultType="io.kelvins.bsip.device.api.entity.TextSchool">
		select * from text_school where urban_id = #{urbanId}
	</select>

属性名	描述	必填
property	Entity对应的字段	是
javaType	Entity对应字段类型	否
ofType	Entity对应字段泛型	否
select	查询数据的方法，要写下全路径	是
column	作为select语句的参数传入，要写下全路径	是

resultMap里传入查询条件


	<resultMap id="TextUrbanMap" type="io.kelvins.bsip.device.api.entity.TextUrbanVO">
		<id property="id" column="id"/>
		<result property="cityId" column="city_id"/>
		<result property="urbanName" column="urban_name"/>
		<collection property="schools" 
					javaType="list" 
					ofType="io.kelvins.bsip.device.api.entity.TextSchool"
					select="io.kelvins.bsip.device.mapper.TextUrbanMapper.schoolByUrbanId" 
					column="{urbanId = id}"/>
		<collection property="hospitals" 
					javaType="list" 
					ofType="io.kelvins.bsip.device.api.entity.TextHospital"
					select="io.kelvins.bsip.device.mapper.TextUrbanMapper.hospitalByUrbanId" 
					column="{urbanId = id}"/>
					<!--或者	column="id"-->
	</resultMap>
	
	<!--查询市列表-->
	<select id="textUrbanList" resultMap="TextUrbanMap">
		select * from text_urban
	</select>

Mapper.java

/**
 * (TextUrban)数据库访问层
 *
 * @author kelvins
 * @date 2022-12-26 11:34:18
 */
public interface TextUrbanMapper extends BaseMapper<TextUrban> {

	/**
	 * 市列表
	 *
	 * @return
	 */
	List<TextUrbanVO> textUrbanList();

	/**
	 * 根据市id查询医院列表
	 *
	 * @param urbanId
	 * @return
	 */
	List<TextHospital> hospitalByUrbanId(@Param("urbanId") Long urbanId);

	/**
	 * 根据市id查询学校列表
	 *
	 * @param urbanId
	 * @return
	 */
	List<TextSchool> schoolByUrbanId(@Param("urbanId") Long urbanId);
}

第二种写法，效率高，但不利于维护


	<!--注意字段名称重复要起别名对应例如各个表中的id-->
	<resultMap id="TextUrbanMap" type="io.kelvins.bsip.device.api.entity.TextUrbanVO">
		<id property="id" column="id"/>
		<result property="cityId" column="city_id"/>
		<result property="urbanName" column="urban_name"/>
		<!--collection标签中不在需要select、column属性-->
		<collection property="schools" javaType="list" ofType="io.kelvins.bsip.device.api.entity.TextSchool" >
			<id property="id" column="s_id"/>
			<result property="urbanId" column="s_urban_id"/>
			<result property="schoolName" column="s_school_name"/>
			<result property="people" column="s_people"/>
		</collection>
		<collection property="hospitals" javaType="list" ofType="io.kelvins.bsip.device.api.entity.TextHospital">
			<id property="id" column="h_id"/>
			<result property="urbanId" column="h_urban_id"/>
			<result property="hospitalName" column="h_hospital_name"/>
			<result property="people" column="h_people"/>
		</collection>
	</resultMap>


	<!--查询市列表-->
	<select id="textUrbanList" resultMap="TextUrbanMap">
		SELECT
			urban.id,
			urban.city_id,
			urban.urban_name,
			school.id s_id,
			school.urban_id s_urban_id,
			school.school_name s_school_name,
			school.people s_people,
			hospital.id h_id,
			hospital.urban_id h_urban_id,
			hospital.hospital_name h_hospital_name,
			hospital.people h_people
		FROM
			text_urban urban
			LEFT JOIN text_school school ON urban.id = school.urban_id
			LEFT JOIN text_hospital hospital ON urban.id = hospital.urban_id
	</select>