3.7私信列表功能

b) 私信列表所展现的是当前用户和某些人的会话
c) 设置会话id的目的，方便之后的筛选方便
d) 写mapper
e) 写service，就是思考业务层的功能，如果是直接拿取Dao层的话就直接简单实现就可以了

数据库设计

from_id,to_id：从哪里来，到哪里去
conversation_id:把上面哪两个id通过 _ 连在一起。对于方向的问题，其实他们都是两个人之间的会话，就不必要分成不同方向了，就规定小id在前。

@Mapper
public interface MessageMapper {
    // 查询当前用户的会话列表,针对每个会话只返回一条最新的私信.
    List<Message> selectConversations(int userId, int offset, int limit);

    // 查询当前用户的会话数量.
    int selectConversationCount(int userId);

    // 查询某个会话所包含的私信列表.
    List<Message> selectLetters(String conversationId, int offset, int limit);

    // 查询某个会话所包含的私信数量.
    int selectLetterCount(String conversationId);

    // 查询未读私信的数量
    int selectLetterUnreadCount(int userId, String conversationId);
}

对应的mapper.xml

<!--    复用-->
    <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>

<!--    从这个子查询中查询最大id的数量-->
    <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>

<!--    未读，状态 0 -->
    <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>

Service

简单调用一下DAO层

@Service
public class MessageService {

    @Autowired
    private MessageMapper messageMapper;

    public List<Message> findConversations(int userId, int offset, int limit) {
        return messageMapper.selectConversations(userId, offset, limit);
    }

    public int findConversationCount(int userId) {
        return messageMapper.selectConversationCount(userId);
    }

    public List<Message> findLetters(String conversationId, int offset, int limit) {
        return messageMapper.selectLetters(conversationId, offset, limit);
    }

    public int findLetterCount(String conversationId) {
        return messageMapper.selectLetterCount(conversationId);
    }

    public int findLetterUnreadCount(int userId, String conversationId) {
        return messageMapper.selectLetterUnreadCount(userId, conversationId);
    }

}

Controller

   //处理私信列表
    @RequestMapping(path = "/letter/list",method = RequestMethod.GET)
    public String getLetterList(Model model, Page page){
        User user = hostHolder.getUser();

        //设置分页信息
        page.setLimit(5);
        page.setPath("/letter/list");
        page.setRows(messageService.findConversationCount(user.getId())); //一共多少条数据

        //查询会话列表
        List<Message> conversationList = messageService.findConversations(user.getId(), page.getOffset(), page.getLimit());

        List<Map<String,Object>> conversations = new ArrayList<>();
        if (conversationList!=null){
            for (Message message : conversationList){
                Map<String,Object> map = new HashMap<>();
                map.put("conversation",message);
                //未读消息数量
                map.put("unreadCount",messageService.findLetterUnreadCount(user.getId(),message.getConversationId()));
                //显示会话包含几个消息
                map.put("letterCount",messageService.findLetterCount(message.getConversationId()));
                //判断目标Id,如果当前用户是发起者，那么targetId就是toId,否者，就是fromId
                int targetId = user.getId() == message.getFromId() ? message.getToId(): message.getFromId();
                map.put("target",userService.findUserById(targetId));
                map.put("createTime",message.getCreateTime());

                conversations.add(map);

            }

        }

        model.addAttribute("conversations",conversations);

        //查询未读消息数量
        int letterUnreadCount = messageService.findLetterUnreadCount(user.getId(),null);
        model.addAttribute("letterUnreadCount",letterUnreadCount);

        return "site/letter";
    }

    //在路径之中就包含会话ID
    @RequestMapping(path = "/letter/detail/{conversationId}",method = RequestMethod.GET)
    public String getLetterDetail(@PathVariable("conversationId") String conversationId,Page page,Model model){
        //分页信息
        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 message : letterList){
                Map<String,Object> map = new HashMap<>();
                map.put("letter",message);
                map.put("fromUser",userService.findUserById(message.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);
        }
    }

前端

<ul class="list-unstyled">
					<li class="media pb-3 pt-3 mb-3 border-bottom position-relative" th:each="map:${conversations}">
						<span class="badge badge-danger" th:text="${map.unreadCount}" th:if="${map.unreadCount!=0}">3</span>
						<a href="profile.html">
							<!--							$取变量，把地址给src，所以是变量，而不是直接去访问-->
							<img th:src="${map.target.headerUrl}" class="mr-4 rounded-circle user-header" alt="用户头像" >
						</a>
						<div class="media-body">
							<h6 class="mt-0 mb-3">
								<span class="text-success" th:utext="${map.target.username}">落基山脉下的闲人</span>
								<span class="float-right text-muted font-size-12" th:text="${#dates.format(map.conversation.createTime,'yyyy-MM-dd HH:mm:ss')}">2019-04-28 14:13:25</span>
							</h6>
							<div>
								<a th:href="@{|/letter/detail/${map.conversation.conversationId}|}" th:utext="${map.conversation.content}">米粉车, 你来吧!</a>
								<ul class="d-inline font-size-12 float-right">
									<li class="d-inline ml-2"><a href="#" class="text-primary">共<i th:text="${map.letterCount}"></i>条会话</a></li>
								</ul>
							</div>
						</div>
					</li>

				</ul>

坑：要注意model的对应属性！！！这里的conversation和conversations一个是总体的，一个是下面分属的属性！！名称要取好。