1.私信列表

• 查询当前用户的会话列表
  每个会话只显示一条最新的私信

• 支持分页显示
  messagemapper

  @Mapper
  public interface MessageMapper {
  
    //查询当前用户会话列表,针对每一个会话只返回一条最新的私信
    List<Message> selectConversations(@Param("userId") int userId, @Param("offset") int offset, @Param("limit") int limit);
  
    //查询当前用户会话数量
    int selectConversationCount(@Param("userId") int userId);
  
    //查询某个会话所包含的私信列表
    List<Message> selectLetters(@Param("conversationId") String conversationId, @Param("offset") int offset, @Param("limit") int limit);
  
    //查询某个会话所包含的私信数量
    int selectLetterCount(@Param("conversationId") String conversationId);
  
    //查询未读私信数量
    int selectLetterUnreadCount(@Param("userId") int userId, @Param("conversationId") String conversationId);
  }

  <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="com.gpnu.community.dao.MessageMapper">
    <sql id="selectFields">
        id, from_id, to_id, conversation_id, content, status, create_time
    </sql>
  
    <sql id="insertFields">
        from_id, to_id, conversation_id, content, status, create_time
    </sql>
  
    <select id="selectConversations" resultType="Message">
        select
        <include refid="selectFields"></include>
        from message where id in
        (select max(id) FROM message
        WHERE status != 2
        and from_id != 1
        and (from_id = #{userId} or to_id = #{userId})
        group by conversation_id)
        order by id desc
        LIMIT #{offset},#{limit}
    </select>
  
    <select id="selectConversationCount" resultType="int">
        SELECT count(m.maxid)
        FROM (
                 SELECT max(id) AS maxid
                 FROM message
                 WHERE status != 2
                       AND from_id != 1
                       AND (from_id = #{userId} OR to_id = #{userId})
                 GROUP BY conversation_id
             ) AS m
    </select>
  
    <select id="selectLetters" resultType="Message">
        select
        <include refid="selectFields"></include>
        from message
        where status != 2
        and from_id != 1
        and conversation_id = #{conversationId}
        order by id DESC
        limit #{offset},#{limit}
    </select>
  
    <select id="selectLetterCount" resultType="int">
        SELECT count(id)
        FROM message
        WHERE status != 2
              AND from_id != 1
              AND conversation_id = #{conversationId}
    </select>
  
    <select id="selectLetterUnreadCount" resultType="int">
        select count(id)
        from message
        where status = 0
        and from_id != 1
        and to_id = #{userId}
        <if test="conversationId!=null">
            and conversation_id = #{conversationId}
        </if>
    </select>
  </mapper>

  2.私信详情

• 查询某个会话所包含的私信

• 支持分页显示
  controller

  @RequestMapping(value = "/letter/detail/{conversationId}", method = RequestMethod.GET)
    public String getLetterDetail(Model model, Page page, @PathVariable("conversationId") String conversationId) {
        User user = hostHolder.getUser();
        //分页
        page.setLimit(5);
        page.setPath("/letter/detail/" + conversationId);
        page.setRows(messageService.findLetterCount(conversationId));
  
        List<Message> letterList = messageService.findLetters(conversationId, page.getOffset(), page.getLimit());
        List<Map<String, Object>> letters = new ArrayList<>();
        if (letterList != null) {
            for (Message letter : letterList) {
                Map<String, Object> map = new HashMap<>();
                map.put("letter", letter);
                map.put("fromUser", userService.findUserById(letter.getFromId()));
                letters.add(map);
            }
        }
        model.addAttribute("letters", letters);
  
        //私信目标
        model.addAttribute("target", getLetterTarget(conversationId));
  
        return "/site/letter-detail";
    }
  
    private User getLetterTarget(String conversationId) {
        String[] ids = conversationId.split("_");
        int id0 = Integer.parseInt(ids[0]);
        int id1 = Integer.parseInt(ids[1]);
  
        if (hostHolder.getUser().getId() == id0) {
            return userService.findUserById(id1);
        } else {
            return userService.findUserById(id0);
        }
    }