mybatis一对一、一对多查询、多对多（使用注解）

1、创建数据库表

职员表&＃xff1a;

岗位信息表&＃xff1a;

2、创建对应实体类
岗位实体类

package com.hzsh.eomc.common.zhch.nyglgwsb.entity;import java.util.List;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.hzsh.eomc.common.zhch.ryzzwh.entity.EomcRyzzwhEntity;
import com.hzsh.eomc.common.zhch.ziyuan.entity.EomcZhchZiyuanEntity;import lombok.Data;&＃64;Data
public class EomcNyglgwsbEntity {private String id;/*** 岗位名称*/private String postName;/*** 岗位类型*/private String postType;/*** 岗位所属部门*/private String department;/*** 岗位描述*/private String description;/*** 岗位任职要求*/private String requirements;/*** 岗位对应任职人员&＃xff08;一对多&＃xff0c;一个岗位可能有多个任职人员&＃xff09;*/private List<EomcRyzzwhEntity> ryzzwhEntityList;}


职员实体类

package com.hzsh.eomc.common.zhch.ryzzwh.entity;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.hzsh.eomc.common.zhch.nyglgwsb.entity.EomcNyglgwsbEntity;import lombok.Data;&＃64;Data
public class EomcRyzzwhEntity {/*** 任职人员编号*/private String id;/*** 人员姓名*/private String employeeName;/*** 聘任证书*/private String certificate;/*** 备案表*/private String recordForm;/*** 资质证明*/private String qualification;/*** 人员所属岗位信息*/private String postId;private EomcNyglgwsbEntity eomcNyglgwsbEntity;}


3、创建持久层

查询职员信息&＃xff0c;并获取职员对应的岗位&＃xff08;一对一&＃xff09;

package com.hzsh.eomc.zhch.nlyys.ryzzwh.mapper;import java.util.List;import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
import org.apache.ibatis.annotations.One;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hzsh.eomc.common.zhch.nyglgwsb.entity.EomcNyglgwsbEntity;
import com.hzsh.eomc.common.zhch.ryzzwh.entity.EomcRyzzwhEntity;&＃64;Mapper
public interface EomcZhchRyzzwhMapper extends BaseMapper<EomcRyzzwhEntity>{/*** 查询所有任职人员&＃xff0c;并关联岗位信息* &＃64;return*/&＃64;Select("select * from eomc_zhch_ryzzwh")&＃64;Results(id &＃61; "ryzzwhMap", value &＃61; {&＃64;Result(id &＃61; true,column &＃61; "id",property &＃61; "id"),&＃64;Result(column &＃61; "employee_name",property &＃61; "employeeName"),&＃64;Result(column &＃61; "certificate",property &＃61; "certificate"),&＃64;Result(column &＃61; "record_form",property &＃61; "recordForm"),&＃64;Result(column &＃61; "qualification",property &＃61; "qualification"),&＃64;Result(column &＃61; "post_id",property &＃61; "postId"),&＃64;Result(column &＃61; "post_id", property &＃61; "eomcNyglgwsbEntity", one &＃61; &＃64;One(select &＃61; "com.hzsh.eomc.zhch.nlyys.ryzzwh.mapper.EomcZhchGwsbMapper.findPostById",fetchType &＃61; FetchType.EAGER))})List<EomcRyzzwhEntity> findRyzzwhAll();/*** 按条件查询任职人员&＃xff0c;并关联岗位信息* &＃64;param postName* &＃64;param postType* &＃64;param department* &＃64;param employeeName* &＃64;return*/&＃64;Select("select a.*,b.* from eomc_zhch_ryzzwh a,eomc_zhch_nyglgwsb b where a.post_id &＃61; b.id and (#{postName} &＃61; &＃39;&＃39; or post_name like &＃39;%${postName}%&＃39;) "&＃43; "and (#{postType} &＃61; &＃39;&＃39; or post_type &＃61; #{postType}) and (#{department} &＃61; &＃39;&＃39; or department &＃61; #{department})"&＃43; " and (#{employeeName} &＃61; &＃39;&＃39; or employee_name like &＃39;%${employeeName}%&＃39;) ")&＃64;ResultMap(value &＃61; "ryzzwhMap")List<EomcRyzzwhEntity> findRyzzwhByCondition(String postName,String postType,String department,String employeeName);&＃64;Select("select * from eomc_zhch_ryzzwh where post_id &＃61; #{postId} ")EomcRyzzwhEntity findRyzzzwhByPostId(String postId);}

4、测试
测试的话可以写个简单controller(省略service层),

一对多查询&＃xff08;一个职位信息对应多个职员&＃xff09;

&＃64;Controller
&＃64;RequestMapping("/hzsh/eomc-zhch/nyglgwsb")
public class EomcZhchNyglgwsbController {&＃64;Autowiredprivate EomcZhchNyglgwsbMapper eomcZhchNyglgwsbMapper;/*** 查询能源管理相关的岗位信息(并关联任职人员信息)* &＃64;param eomcNyglgwsbEntity* &＃64;return*/&＃64;ResponseBody&＃64;RequestMapping("/list")public List<EomcNyglgwsbEntity> queryNyglgwsbList(&＃64;RequestBody(required &＃61; false) EomcNyglgwsbEntity eomcNyglgwsbEntity) {QueryWrapper<EomcNyglgwsbEntity> queryWrapper &＃61; new QueryWrapper<EomcNyglgwsbEntity>();queryConditionSet(eomcNyglgwsbEntity, queryWrapper);List<EomcNyglgwsbEntity> nyglgwsbList &＃61; eomcZhchNyglgwsbMapper.findPostAll();System.out.println(nyglgwsbList.get(0));for(EomcNyglgwsbEntity nyglgwsbEntity:nyglgwsbList) {System.out.println(nyglgwsbEntity);}return nyglgwsbList;}
}


查询结果如下&＃xff1a;&＃xff08;一个岗位对应一个或多个职员信息&＃xff09;

一对一&＃xff08;一个任职人员对应一个岗位&＃xff09;

&＃64;Controller
&＃64;RequestMapping("/hzsh/eomc-zhch/ryzzwh")
public class EomcZhchRyzzwhController {&＃64;Autowiredprivate EomcZhchRyzzwhMapper eomcZhchRyzzwhMapper;&＃64;Autowiredprivate EomcZhchGwsbMapper eomcZhchGwsbMapper;/*** 查询任职人员信息&＃xff08;和对应能源岗位一起显示&＃xff09;&＃xff0c;当前端有传参数时&＃xff0c;即按条件查询&＃xff0c;当无参数时即查询出全部数据* * &＃64;param eomcRyzzwhEntity* &＃64;return*/&＃64;ResponseBody&＃64;RequestMapping("/list")public List<EomcRyzzwhEntity> queryRyzzwhList(&＃64;RequestBody(required &＃61; false) EomcRyzzwhEntity eomcRyzzwhEntity) {String employeeName &＃61; eomcRyzzwhEntity.getEmployeeName();String postName &＃61; eomcRyzzwhEntity.getEomcNyglgwsbEntity().getPostName();String postType &＃61; eomcRyzzwhEntity.getEomcNyglgwsbEntity().getPostType();String department &＃61; eomcRyzzwhEntity.getEomcNyglgwsbEntity().getDepartment();List<EomcRyzzwhEntity> ryzzwhListByCondition &＃61; eomcZhchRyzzwhMapper.findRyzzwhByCondition(postName, postType,department, employeeName);for(EomcRyzzwhEntity ryzzwhEntity:ryzzwhListByCondition) {System.out.println("**"&＃43;ryzzwhEntity);}return ryzzwhListByCondition;}
}

查询结果如下&＃xff1a;&＃xff08;一个职员对应一个岗位信息&＃xff09;

二、若是多对多关系&＃xff0c;即一个岗位对应多个任职人员&＃xff0c;一个职员也可以任职多个岗位

1. 此时将职员实体类修改为如下&＃xff1a;

package com.hzsh.eomc.common.zhch.ryzzwh.entity;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.hzsh.eomc.common.zhch.nyglgwsb.entity.EomcNyglgwsbEntity;import lombok.Data;&＃64;Data
public class EomcRyzzwhEntity {/*** 任职人员编号*/private String id;/*** 人员姓名*/private String employeeName;/*** 聘任证书*/private String certificate;/*** 备案表*/private String recordForm;/*** 资质证明*/private String qualification;/*** 人员所属岗位信息*/private String postId;private List<EomcNyglgwsbEntity> eomcNyglgwsbEntityList;

2.数据库职员表和岗位表之间需要建立一张关联表&＃xff0c;用于映射支援和岗位之间的关系

3.持久层代码如下&＃xff1a;

package com.hzsh.eomc.zhch.nlyys.nyglgwsb.mapper;import java.util.List;import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hzsh.eomc.common.zhch.nyglgwsb.entity.EomcNyglgwsbEntity;/*** * 查询岗位的接口*/
&＃64;Mapper
public interface EomcZhchNyglgwsbMapper extends BaseMapper<EomcNyglgwsbEntity>{/*** 查询所有岗位信息&＃xff0c;并携带该岗位所有任职人员信息* &＃64;return*/&＃64;Select("select * from eomc_zhch_nyglgwsb where flg_del &＃61; &＃39;0&＃39; and (#{postName} &＃61; &＃39;&＃39; or post_name like &＃39;%${postName}%&＃39;) "&＃43; "and (#{postType} &＃61; &＃39;&＃39; or post_type &＃61; #{postType}) "&＃43; "and (#{department} &＃61; &＃39;&＃39; or department &＃61; #{department})")&＃64;Results(id &＃61; "postMap" ,value &＃61; {&＃64;Result(id &＃61; true,column &＃61; "id",property &＃61; "id"),&＃64;Result(column &＃61; "post_name",property &＃61; "postName"),&＃64;Result(column &＃61; "post_type",property &＃61; "postType"),&＃64;Result(column &＃61; "department",property &＃61; "department"),&＃64;Result(column &＃61; "description",property &＃61; "description"),&＃64;Result(column &＃61; "requirements",property &＃61; "requirements"),&＃64;Result(column &＃61; "id",property &＃61; "ryzzwhEntityList", many &＃61; &＃64;Many (select &＃61; "com.hzsh.eomc.zhch.nlyys.ryzzwh.mapper.EomcZhchRyzzwhMapper.findEmployeeByPostId")) })List<EomcNyglgwsbEntity> findPost(String postName,String postType,String department);/*** 查询所有岗位信息&＃xff0c;并携带该岗位所有任职人员信息* &＃64;return*/&＃64;Select("select a.* from eomc_zhch_nyglgwsb a,(select * from eomc_zhch_postAndEmployConnect where flg_del &＃61; &＃39;0&＃39; and employee_id&＃61;#{employeeId}) b "&＃43; "where a.id &＃61; b.post_id")List<EomcNyglgwsbEntity> findPostByEmployeeId(String employeeId);

package com.hzsh.eomc.zhch.nlyys.ryzzwh.mapper;import java.util.List;import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
import org.apache.ibatis.annotations.One;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hzsh.eomc.common.zhch.nyglgwsb.entity.EomcNyglgwsbEntity;
import com.hzsh.eomc.common.zhch.ryzzwh.entity.EomcRyzzwhEntity;
/*** 查询职员的接口* */
&＃64;Mapper
public interface EomcZhchRyzzwhMapper extends BaseMapper<EomcRyzzwhEntity> {/*** 查询所有职员&＃xff0c;并携带其所任职的一个或多个岗位信息* &＃64;return*/&＃64;Select("select * from eomc_zhch_ryzzwh where flg_del &＃61; &＃39;0&＃39; and (#{employeeName} &＃61; &＃39;&＃39; or employee_name &＃61; #{employeeName})"&＃43; "and (#{department} &＃61; &＃39;&＃39; or department &＃61; #{department})")&＃64;Results(id &＃61; "ryzzwhMap", value &＃61; { &＃64;Result(id &＃61; true, column &＃61; "id", property &＃61; "id"),&＃64;Result(column &＃61; "employee_name", property &＃61; "employeeName"),&＃64;Result(column &＃61; "department", property &＃61; "department"),&＃64;Result(column &＃61; "certificate", property &＃61; "certificate"),&＃64;Result(column &＃61; "record_form", property &＃61; "recordForm"),&＃64;Result(column &＃61; "qualification", property &＃61; "qualification"),&＃64;Result(column &＃61; "id", property &＃61; "eomcNyglgwsbEntityList", many &＃61; &＃64;Many(select &＃61; "com.hzsh.eomc.zhch.nlyys.nyglgwsb.mapper.EomcZhchNyglgwsbMapper.findPostByEmployeeId", fetchType &＃61; FetchType.EAGER)) })List<EomcRyzzwhEntity> findRyzzwh(String employeeName, String department);/*** 根据岗位id查询出任职人员信息* &＃64;return*/&＃64;Select("select c.* from eomc_zhch_ryzzwh c,(select * from eomc_zhch_postAndEmployConnect where flg_del &＃61; &＃39;0&＃39; and post_id&＃61;#{postId}) b "&＃43; "where c.id &＃61; b.employee_id")List<EomcRyzzwhEntity> findEmployeeByPostId(String postId);}