mybatis注解開發，mybatis 一對一、一對多查詢、多對多（使用注解）

1、創建數據庫表

職員表：

崗位信息表：

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;@Data
public class EomcNyglgwsbEntity {private String id;/*** 崗位名稱*/private String postName;/*** 崗位類型*/private String postType;/*** 崗位所屬部門*/private String department;/*** 崗位描述*/private String description;/*** 崗位任職要求*/private String requirements;/*** 崗位對應任職人員（一對多，一個崗位可能有多個任職人員）*/private List<EomcRyzzwhEntity> ryzzwhEntityList;}

mybatis注解開發。職員實體類

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;@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、創建持久層

查詢職員信息，并獲取職員對應的崗位（一對一）

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;@Mapper
public interface EomcZhchRyzzwhMapper extends BaseMapper<EomcRyzzwhEntity>{/*** 查詢所有任職人員，并關聯崗位信息* @return*/@Select("select * from eomc_zhch_ryzzwh")@Results(id = "ryzzwhMap", value = {@Result(id = true,column = "id",property = "id"),@Result(column = "employee_name",property = "employeeName"),@Result(column = "certificate",property = "certificate"),@Result(column = "record_form",property = "recordForm"),@Result(column = "qualification",property = "qualification"),@Result(column = "post_id",property = "postId"),@Result(column = "post_id", property = "eomcNyglgwsbEntity", one = @One(select = "com.hzsh.eomc.zhch.nlyys.ryzzwh.mapper.EomcZhchGwsbMapper.findPostById",fetchType = FetchType.EAGER))})List<EomcRyzzwhEntity> findRyzzwhAll();/*** 按條件查詢任職人員，并關聯崗位信息* @param postName* @param postType* @param department* @param employeeName* @return*/@Select("select a.*,b.* from eomc_zhch_ryzzwh a,eomc_zhch_nyglgwsb b where a.post_id = b.id and (#{postName} = '' or post_name like '%${postName}%') "+ "and (#{postType} = '' or post_type = #{postType}) and (#{department} = '' or department = #{department})"+ " and (#{employeeName} = '' or employee_name like '%${employeeName}%') ")@ResultMap(value = "ryzzwhMap")List<EomcRyzzwhEntity> findRyzzwhByCondition(String postName,String postType,String department,String employeeName);@Select("select * from eomc_zhch_ryzzwh where post_id = #{postId} ")EomcRyzzwhEntity findRyzzzwhByPostId(String postId);}

4、測試
測試的話可以寫個簡單controller(省略service層),

一對多查詢（一個職位信息對應多個職員）

@Controller
@RequestMapping("/hzsh/eomc-zhch/nyglgwsb")
public class EomcZhchNyglgwsbController {@Autowiredprivate EomcZhchNyglgwsbMapper eomcZhchNyglgwsbMapper;/*** 查詢能源管理相關的崗位信息(并關聯任職人員信息)* @param eomcNyglgwsbEntity* @return*/@ResponseBody@RequestMapping("/list")public List<EomcNyglgwsbEntity> queryNyglgwsbList(@RequestBody(required = false) EomcNyglgwsbEntity eomcNyglgwsbEntity) {QueryWrapper<EomcNyglgwsbEntity> queryWrapper = new QueryWrapper<EomcNyglgwsbEntity>();queryConditionSet(eomcNyglgwsbEntity, queryWrapper);List<EomcNyglgwsbEntity> nyglgwsbList = eomcZhchNyglgwsbMapper.findPostAll();System.out.println(nyglgwsbList.get(0));for(EomcNyglgwsbEntity nyglgwsbEntity:nyglgwsbList) {System.out.println(nyglgwsbEntity);}return nyglgwsbList;}
}

sql一對多查詢。查詢結果如下：（一個崗位對應一個或多個職員信息）

一對一（一個任職人員對應一個崗位）

@Controller
@RequestMapping("/hzsh/eomc-zhch/ryzzwh")
public class EomcZhchRyzzwhController {@Autowiredprivate EomcZhchRyzzwhMapper eomcZhchRyzzwhMapper;@Autowiredprivate EomcZhchGwsbMapper eomcZhchGwsbMapper;/*** 查詢任職人員信息（和對應能源崗位一起顯示），當前端有傳參數時，即按條件查詢，當無參數時即查詢出全部數據* * @param eomcRyzzwhEntity* @return*/@ResponseBody@RequestMapping("/list")public List<EomcRyzzwhEntity> queryRyzzwhList(@RequestBody(required = false) EomcRyzzwhEntity eomcRyzzwhEntity) {String employeeName = eomcRyzzwhEntity.getEmployeeName();String postName = eomcRyzzwhEntity.getEomcNyglgwsbEntity().getPostName();String postType = eomcRyzzwhEntity.getEomcNyglgwsbEntity().getPostType();String department = eomcRyzzwhEntity.getEomcNyglgwsbEntity().getDepartment();List<EomcRyzzwhEntity> ryzzwhListByCondition = eomcZhchRyzzwhMapper.findRyzzwhByCondition(postName, postType,department, employeeName);for(EomcRyzzwhEntity ryzzwhEntity:ryzzwhListByCondition) {System.out.println("**"+ryzzwhEntity);}return ryzzwhListByCondition;}
}

查詢結果如下：（一個職員對應一個崗位信息）

二、若是多對多關系，即一個崗位對應多個任職人員，一個職員也可以任職多個崗位

1. 此時將職員實體類修改為如下：

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;@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.數據庫職員表和崗位表之間需要建立一張關聯表，用于映射支援和崗位之間的關系

mybatis有什么用。3.持久層代碼如下：

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;/*** * 查詢崗位的接口*/
@Mapper
public interface EomcZhchNyglgwsbMapper extends BaseMapper<EomcNyglgwsbEntity>{/*** 查詢所有崗位信息，并攜帶該崗位所有任職人員信息* @return*/@Select("select * from eomc_zhch_nyglgwsb  where flg_del = '0' and (#{postName} = '' or post_name like '%${postName}%') "+ "and (#{postType} = '' or post_type = #{postType}) "+ "and (#{department} = '' or department = #{department})")@Results(id = "postMap" ,value = {@Result(id = true,column = "id",property = "id"),@Result(column = "post_name",property = "postName"),@Result(column = "post_type",property = "postType"),@Result(column = "department",property = "department"),@Result(column = "description",property = "description"),@Result(column = "requirements",property = "requirements"),@Result(column = "id",property = "ryzzwhEntityList", many = @Many (select = "com.hzsh.eomc.zhch.nlyys.ryzzwh.mapper.EomcZhchRyzzwhMapper.findEmployeeByPostId"))	  })List<EomcNyglgwsbEntity> findPost(String postName,String postType,String department);/*** 查詢所有崗位信息，并攜帶該崗位所有任職人員信息* @return*/@Select("select a.* from eomc_zhch_nyglgwsb a,(select *  from eomc_zhch_postAndEmployConnect where flg_del = '0' and employee_id=#{employeeId}) b "+ "where a.id = 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;
/*** 查詢職員的接口* */
@Mapper
public interface EomcZhchRyzzwhMapper extends BaseMapper<EomcRyzzwhEntity> {/*** 查詢所有職員，并攜帶其所任職的一個或多個崗位信息* @return*/@Select("select * from eomc_zhch_ryzzwh where flg_del = '0' and (#{employeeName} = '' or employee_name = #{employeeName})"+ "and (#{department} = '' or department = #{department})")@Results(id = "ryzzwhMap", value = { @Result(id = true, column = "id", property = "id"),@Result(column = "employee_name", property = "employeeName"),@Result(column = "department", property = "department"),@Result(column = "certificate", property = "certificate"),@Result(column = "record_form", property = "recordForm"),@Result(column = "qualification", property = "qualification"),@Result(column = "id", property = "eomcNyglgwsbEntityList", many = @Many(select = "com.hzsh.eomc.zhch.nlyys.nyglgwsb.mapper.EomcZhchNyglgwsbMapper.findPostByEmployeeId", fetchType = FetchType.EAGER)) })List<EomcRyzzwhEntity> findRyzzwh(String employeeName, String department);/*** 根據崗位id查詢出任職人員信息* @return*/@Select("select c.* from eomc_zhch_ryzzwh c,(select *  from eomc_zhch_postAndEmployConnect where flg_del = '0' and post_id=#{postId}) b "+ "where c.id = b.employee_id")List<EomcRyzzwhEntity> findEmployeeByPostId(String postId);}