二十五、 “我的消息”中MyBatis的left join左连查询（mapper，modal， implement）

MyBatis的left join左连查询2张表的数据

1. 在implement中的SysAnnouncementSendMapper调用方法：

getMyAnnouncementSendList（）和getOne（）

@Service
public class SysAnnouncementSendServiceImpl 
extends ServiceImpl<SysAnnouncementSendMapper, SysAnnouncementSend>
 implements ISysAnnouncementSendService
 {

    @Resource
    private SysAnnouncementSendMapper sysAnnouncementSendMapper;

    
    @Override
    public Page<AnnouncementSendModel> getMyAnnouncementSendPage(
Page<AnnouncementSendModel> page,
AnnouncementSendModel announcementSendModel)
 {
        return page.setRecords(sysAnnouncementSendMapper.getMyAnnouncementSendList(page, announcementSendModel));
}

    @Override
    public AnnouncementSendModel getOne(String sendId) 
{
       return sysAnnouncementSendMapper.getOne(sendId);

    }

（2）定义：SysAnnouncementSendMapper.xml

<mapper namespace="org.jeecg.modules.system.mapper.SysAnnouncementSendMapper">
    
    <resultMap id="AnnouncementSendModel" 
type="org.jeecg.modules.system.model.AnnouncementSendModel" >

       <result column="id" property="id" jdbcType="VARCHAR"/>
       <result column="annt_id" property="anntId" jdbcType="VARCHAR"/>
       <result column="user_id" property="userId" jdbcType="VARCHAR"/>

注意：

下面是跨表数据：数据来自sysAnnouncementSend表

    <result column="titile" property="titile" jdbcType="VARCHAR"/>
       <result column="msg_content" property="msgContent" jdbcType="VARCHAR"/>
。。。。。。

          </resultMap>
    
    <select id="getMyAnnouncementSendList" parameterType="Object"  resultMap="AnnouncementSendModel">
       select
              sas.id,
              sas.annt_id,
              sas.user_id,
              sas.read_flag,
下面是跨表数据：

              sa.titile as titile,
              sa.msg_content as msg_content,
。。。。。。。。。。。。。             
          sa.msg_abstract
       from sys_announcement_send sas

       left join sys_announcement sa ON sas.annt_id = sa.id
左联合查询：

       where sa.send_status = '1'
       and sa.del_flag = '0'
       and sas.user_id = #{announcementSendModel.userId}
       <if test="announcementSendModel.titile !=null and announcementSendModel.titile != ''">
              and sa.titile LIKE concat(concat('%',#{announcementSendModel.titile}),'%')
       </if>
       order by sas.read_flag,sa.send_time desc
    </select>

<!-- 查询一条消息 -->
    <select id="getOne" parameterType="String" resultMap="AnnouncementSendModel">
       select
       sas.id,
       sas.annt_id,
       sas.user_id,
       sas.read_flag,
       sa.titile as titile,
       sa.msg_content as msg_content,
       = #{sendId}
    </select>


    <!-- 修改为已读消息 -->
    <update id="updateReaded">
       update sys_announcement_send set read_flag = 1
        where user_id = #{userId} 
          and annt_id in
       <foreach collection="annoceIdList" index="index" item="id" open="(" separator="," close=")">
          #{id}
       </foreach>
    </update>
    
    
    <!--清除所有未读消息-->
    <update id="clearAllUnReadMessage">
       update sys_announcement_send set read_flag = 1
       where user_id = #{userId} and read_flag = 0
    </update>

</mapper>

(3)定义modal：AnnouncementSendModel.java让程序和页面vue读取数据

@Data
public class AnnouncementSendModel implements Serializable {
    private static final long serialVersionUID = 1L;
    
    /**id*/
    @TableId(type = IdType.ASSIGN_ID)
    private java.lang.String id;
    /**通告id*/
    private java.lang.String anntId;
    /**用户id*/
    private java.lang.String userId;
    /**标题*/
    private java.lang.String titile;
    /**内容*/
    private java.lang.String msgContent;
    /**发布人*/
    private java.lang.String sender;

（4）发送通告：SysAnnouncementServiceImpl的saveAnnouncement（）

同时向2表插入新增数据：

@Transactional(rollbackFor = Exception.class)
@Override
public void saveAnnouncement(SysAnnouncement sysAnnouncement) {
    if(sysAnnouncement.getMsgType().equals(CommonConstant.MSG_TYPE_ALL)) {
       sysAnnouncementMapper.insert(sysAnnouncement);
    }else {
       // 1.插入通告表记录sys_announcement表

       sysAnnouncementMapper.insert(sysAnnouncement);
       // 2.插入用户通告阅读标记表记录sys_announcement_send表

       String userId = sysAnnouncement.getUserIds();
       String[] userIds = userId.substring(0, (userId.length()-1)).split(",");
       String anntId = sysAnnouncement.getId();
       Date refDate = new Date();
       for(int i=0;i<userIds.length;i++) {
          SysAnnouncementSend announcementSend = new SysAnnouncementSend();
          announcementSend.setAnntId(anntId);
          announcementSend.setUserId(userIds[i]);
          announcementSend.setReadFlag(CommonConstant.NO_READ_FLAG);
          announcementSend.setReadTime(refDate);
          sysAnnouncementSendMapper.insert(announcementSend);
       }
    }